Is "query cost" accurate? - 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 2008 Forums

SQL Server Administration (2008)

Is "query cost" accurate?

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-09-20 : 16:58:40

I have two queries (below) that are slightly different. If I run them both and compare the "query cost" it says that the first one will cost 3% relative to the batch and the second one will cost 97%. But the first one takes 8 seconds to execute while the second one is almost instant. I turned on IO stats which are provided below. It seems like the second one if far more efficient but not according to the "query cost". Can someone shed some light on this for me?


SELECT DISTINCT Courses.CourseID,  
   Courses.ClientID,  
   Courses.CourseTitle,  
   Courses.ActiveStatus,  
   Courses.Type,  
   IsNull(CourseCategories.CategoryName, '') As CategoryName  
FROM Courses  
LEFT OUTER JOIN CourseCategories  
ON Courses.CourseCatID = CourseCategories.CourseCatID  
INNER JOIN Subscription  
ON Subscription.CourseID = Courses.CourseID  
INNER JOIN Students  
ON Subscription.StudentsID = Students.StudentsID  
WHERE (students.departmentid in  
   ( 
   SELECT DepartmentID  
   FROM dbo.FuncGetAdminDepartments(6114) 
   ) )  
AND students.clientid = 0363  
ORDER BY Courses.ActiveStatus ASC,  
   Courses.CourseTitle ASC
   
SELECT DISTINCT Courses.CourseID,  
   Courses.ClientID,  
   Courses.CourseTitle,  
   Courses.ActiveStatus,  
   Courses.Type,  
   IsNull(CourseCategories.CategoryName, '') As CategoryName  
FROM Courses  
LEFT OUTER JOIN CourseCategories  
ON Courses.CourseCatID = CourseCategories.CourseCatID  
INNER JOIN Subscription  
ON Subscription.CourseID = Courses.CourseID  
INNER JOIN Students  
ON Subscription.StudentsID = Students.StudentsID  
WHERE exists  
   ( 
   SELECT null
   FROM dbo.FuncGetAdminDepartments(6114) as blah
   inner join students on Students.DepartmentID = blah.departmentid
   )   
AND students.clientid = 0363  
ORDER BY Courses.ActiveStatus ASC,  
   Courses.CourseTitle ASC

(166 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Courses'. Scan count 1, logical reads 415, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Subscription'. Scan count 4146, logical reads 14755, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Students'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#39B3E537'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CourseCategories'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(166 row(s) affected)
Table 'CourseCategories'. Scan count 11, logical reads 70, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Students'. Scan count 16, logical reads 55, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#4ADE7139'. Scan count 12, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Courses'. Scan count 13, logical reads 1631, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Subscription'. Scan count 4624, logical reads 17194, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-21 : 03:37:35

Costs are estimates. They are generated at optimisation time based on the optimiser's estimates of row counts. Anything that makes row count estimates wrong (like table variables) will make the costs inaccurate.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2011-09-21 : 04:50:19

"Table 'Subscription'. Scan count 4146, logical reads 14755
...
Table 'Subscription'. Scan count 4624, logical reads 17194,"

You need an index to reduce those figures down

Some of the others have higher SCAN counts than I would be happy with - I generally investigate anything that is scan count > 1

If you run this in SSMS with the "Show Actual Query plan" it will give you a suggestion for indexes that could be added.

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-09-21 : 11:19:41

quote:
Originally posted by Kristen

"Table 'Subscription'. Scan count 4146, logical reads 14755
...
Table 'Subscription'. Scan count 4624, logical reads 17194,"

You need an index to reduce those figures down

Some of the others have higher SCAN counts than I would be happy with - I generally investigate anything that is scan count > 1

If you run this in SSMS with the "Show Actual Query plan" it will give you a suggestion for indexes that could be added.

It isn't recommending any indexes. The plan shows a few non-clustered index scans but doesn't recommend any indexes.

I guess my main question is which query should I use. The one that is reported to have a much lower cost or the one that takes 1/10th the time to execute?

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-09-21 : 11:21:41

quote:
Originally posted by GilaMonster

Costs are estimates. They are generated at optimisation time based on the optimiser's estimates of row counts. Anything that makes row count estimates wrong (like table variables) will make the costs inaccurate.

--
Gail Shaw
SQL Server MVP

Ah - so the estimator is probably inaccurate since the function uses a table variable.

Kristen
Test

22859 Posts

Posted - 2011-09-21 : 12:16:49

"I guess my main question is which query should I use. The one that is reported to have a much lower cost or the one that takes 1/10th the time to execute?"

I would want one that had fewer Scans and Logical Reads.

I think its remarkable that SQL can suggest indexes

, but I don't rely on it. Maybe the function is preventing it make the recommendation.

If I've understood correctly you are getting 166 rows output? If so

Table 'Subscription'. Scan count 4624, logical reads 17194

seems out of all proportion to me.

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-09-21 : 13:00:42

So when this...

SELECT DepartmentID
FROM dbo.FuncGetAdminDepartments(6114)

returns lots of rows (855 in the case of the id used above) then the "not exists" is much faster. when it returns only a couple rows then using "IN" is faster. i knew that using "IN" is better for small recordsets. how do you deal with scenarios where sometimes it returns a small number of records and sometimes a large number and you don't know ahead of time?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-21 : 13:29:51

quote:
Originally posted by ferrethouse

I guess my main question is which query should I use. The one that is reported to have a much lower cost or the one that takes 1/10th the time to execute?

Do you want the query to run fast or do you want some useless measure that only the optimiser uses to be low?

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-21 : 13:33:45

Honestly, I'd start optimising this by seeing if I could remove that function. Multi-statement table valued functions are the second-worst functions in terms of performance.

p.s. IN and EXISTS (when the optimiser can cost properly) perform just about the same in all cases I've tested.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2011-09-21 : 18:05:51

" how do you deal with scenarios where sometimes it returns a small number of records and sometimes a large number and you don't know ahead of time?"

I tend to make my judgements about IN and EXISTS based on whether there are likely to be duplicates in the list, or not.

WHERE EXISTS (SELECT * FROM MyTable WHERE SomeColumn = OuterColumName)

is efficient if there will be many duplicate values of SomeColumn. EXISTS only has to find one matching value and then it can stop.

Whereas

WHERE OuterColumName IN (SELECT SomeColumn FROM MyTable)

may produce a list of all the matching values, and then see if OuterColumnName is in the list.

If there are no Duplicate Values I personally prefer to use a JOIN instead of either EXISTS or IN.

But as Gail says the optimiser will often / usually spot the opportunity to streamline it.

Beware that NOT IN will cause results you may not be expecting if the list includes a NULL value (which may make NOT EXISTS preferable).

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-21 : 19:31:04

EXISTS and IN result in identical execution plans under just about all cases I've tested (unless you do something silly like Col1 + Col2 IN (select Col3 + Col4...), JOIN is in general less performant (though not by much)

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2011-09-22 : 02:48:01

I've not tested it Gail, but does:

WHERE MyColumn IN (SELECT LotfOfDuplicateValues FROM MyTable)

perform the same as an EXISTS?

I suppose I have thought that it must do the whole "SELECT LotfOfDuplicateValues FROM MyTable" to a work table first, and thus I was giving it a value "hint" by using EXISTS - but I suppose there is no reason why SQL should perform an IN any differently to an EXISTS.

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-09-27 : 22:19:36

quote:
Originally posted by GilaMonster

Honestly, I'd start optimising this by seeing if I could remove that function. Multi-statement table valued functions are the second-worst functions in terms of performance.

p.s. IN and EXISTS (when the optimiser can cost properly) perform just about the same in all cases I've tested.

--
Gail Shaw
SQL Server MVP

Good to know. Out of curiosity - what are worst performing functions?

Sachin.Nand

2937 Posts

Posted - 2011-09-28 : 04:12:01

I would recommend to go for an inline table valued function.Cause if you use them with a Cross Apply they effectively become part of the main query and are processed at once.

PBUH

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-28 : 04:26:48

quote:
Originally posted by ferrethouse

quote:
Originally posted by GilaMonster

Honestly, I'd start optimising this by seeing if I could remove that function. Multi-statement table valued functions are the second-worst functions in terms of performance.

Good to know. Out of curiosity - what are worst performing functions?

Scalar user-defined functions, especially ones that do data access.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources