October 4, 2012 at 8:45 am
Hi All
I understand that parameter sniffing is a good thing
It becomes a bad thing when the parameters used in the first execution of the Procedure are atypical to the parameters used in subsequent executions of the procedure, right?
So my question is, I've read that one of the causes of high CPU utilization on a SQL Server could be bad Parameter Sniffing.
When dealing with bad parameter sniffing, how does it affect CPU negatively? I figured that it would be more taxing to disk or memory but not CPU.
Am I missing something here?
October 4, 2012 at 9:38 am
SQLSACT (10/4/2012)
Hi AllI understand that parameter sniffing is a good thing
It becomes a bad thing when the parameters used in the first execution of the Procedure are atypical to the parameters used in subsequent executions of the procedure, right?
So my question is, I've read that one of the causes of high CPU utilization on a SQL Server could be bad Parameter Sniffing.
When dealing with bad parameter sniffing, how does it affect CPU negatively? I figured that it would be more taxing to disk or memory but not CPU.
Am I missing something here?
You should read the 3 part series from Gail. http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]
_______________________________________________________________
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/
October 4, 2012 at 9:38 am
Please check the following link:
http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/
October 4, 2012 at 11:53 pm
Sean Lange (10/4/2012)
SQLSACT (10/4/2012)
Hi AllI understand that parameter sniffing is a good thing
It becomes a bad thing when the parameters used in the first execution of the Procedure are atypical to the parameters used in subsequent executions of the procedure, right?
So my question is, I've read that one of the causes of high CPU utilization on a SQL Server could be bad Parameter Sniffing.
When dealing with bad parameter sniffing, how does it affect CPU negatively? I figured that it would be more taxing to disk or memory but not CPU.
Am I missing something here?
You should read the 3 part series from Gail. http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]
Thanks, I understand the concept of parameter sniffing and when it's not a good thing
What I'm trying to understand is which of your resources are taxed the most when dealing with bad parameter sniffing
October 5, 2012 at 12:59 am
Since the release of SQL 2008 there are less problems with paramater sniffing than you had on previous versions, as you can use the OPTION(RECOMPILE) on an SQL statement which will bypass the need to do this to some extent, it might not solve all the problems but a large proportion of them will be removed.
I wouldnt suggest this on every query in an SP only those that seriously underperform.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 5, 2012 at 1:34 am
SQLSACT (10/4/2012)
What I'm trying to understand is which of your resources are taxed the most when dealing with bad parameter sniffing
CPU usually. IO maybe, depending whether the data is in cache or not. Typically parameter sniffing problems result in SQL using a plan that's optimal for small numbers of rows (loop join, key lookup, sort, etc) on huge resultsets hence it ends up doing massive amounts more work than it would had it picked a plan optimal for large numbers of rows
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
October 5, 2012 at 1:45 am
GilaMonster (10/5/2012)
SQLSACT (10/4/2012)
What I'm trying to understand is which of your resources are taxed the most when dealing with bad parameter sniffingCPU usually. IO maybe, depending whether the data is in cache or not. Typically parameter sniffing problems result in SQL using a plan that's optimal for small numbers of rows (loop join, key lookup, sort, etc) on huge resultsets hence it ends up doing massive amounts more work than it would had it picked a plan optimal for large numbers of rows
Thanks
This is where my confusion comes in
I've read that when dealing with a SQL server that it under CPU stress, one of the things to check is bad parameter sniffing.
I can understand Excessive Compilation being a problem on CPU resources. Because SQL is using a plan that is already in cache (even though it's not an optimal one), how exactly does it affect CPU?
Thanks
October 5, 2012 at 2:05 am
Because executing a query plan takes CPU. If the plan is not efficient then it takes more CPU than if it were efficient.
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply