Data Tuning Advisor - Slow - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Data Tuning Advisor - Slow

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2010-05-01 : 10:49:19

I used to use this tool all of the time and the entire process including recommendations took about 45 minutes. I generally consume a 3-5 meg trace file. Lately I have been letting it run over night and it will isn't done. It looks like it is still working (app not frozen) but it never seems to finish. It is normal that it would take in excess of 12 hours for a 3-5 meg tuning trace file?

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2010-05-01 : 14:23:59

After more research I found that it never finished when I try to process this sql statement...

SELECT DepartmentID,DepartmentName FROM Departments WHERE ClientID = 81 AND departments.departmentid in (select DepartmentID from dbo.FuncGetAdminDepartments(2310)) AND ParentDeptID = 2980 ORDER BY DepartmentName ASC

But it runs fine and fast when I process this sql statement...

SELECT DepartmentID,DepartmentName FROM Departments WHERE ClientID = 81 AND ParentDeptID = 2980 ORDER BY DepartmentName ASC

It also runs fine on this...

select DepartmentID from dbo.FuncGetAdminDepartments(2310)

Can the advisor not process functions contained in a sub-query?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources