July 13, 2012 at 11:11 am
Hi All,
Could anyone please help me understand what is Constant scan operator and how it affects the performance of a query ?
A small background -
I am rewriting a stored procedure which selects value from a view. This view is having a date column which is fetchedthrough a udf.
Previously, the stored procedure was doing a key lookup. After rewriting the SP, now I can see that a constant scan is happening- which i am unable to understand.
It will be great if someone can shed some light on this issue.
July 13, 2012 at 11:58 am
shovankar (7/13/2012)
Hi All,Could anyone please help me understand what is Constant scan operator and how it affects the performance of a query ?
A small background -
I am rewriting a stored procedure which selects value from a view. This view is having a date column which is fetchedthrough a udf.
Previously, the stored procedure was doing a key lookup. After rewriting the SP, now I can see that a constant scan is happening- which i am unable to understand.
It will be great if someone can shed some light on this issue.
Without something to examine it is going to be pretty challenging for anybody to answer this. We have no ddl, no proc code, no udf code, and zero knowledge of your system.
I can say that a scalar udf in your view is not going to perform or scale very well.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 15, 2012 at 10:54 am
The Constant Scan operator just generates any constant values necessary for the query. They're not table scans, they have just about no performance impact.
If you're looking for the source of performance problems, constant scans aren't it. If the functions you're using are scalar UDFs, they're far more likely to be the cause
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2012 at 11:04 am
CELKO (7/15/2012)
The short answer is that UDFs and other non-declarative proprietary Microsoft extensions cannot be optimized. They get executed row by row, hence scans.
Incorrect and a non-sequitur fallacy.
While data-accessing scalar user-defined functions are a performance nightmare more often than not if they are used within other functions (as in SELECT <stuff>, dbo.SomeFunction(..), <more stuff> FROM ...), if they are called as scalar operations they are not too bad and can certainly be optimised as any other block of code can be.
The fact that scalar user defined functions are executed row by row does not imply that any time they are used there will be scans. in fact, scalar user defined functions within an execution plan appear as Compute Scalar operators, not seeks or scans of any form.
Inline table-valued functions perform very well, they are treated by the optimiser much as view are .
In fact, the only function that will result in a scan in the execution plan is the multi-statement user-define function and that will be a table scan, not a constant scan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply