SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored procedure very slow execution


Stored procedure very slow execution

Author
Message
Ed Wagner
Ed Wagner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16431 Visits: 10050
As a word of caution, I believe you can do online index rebuild only with the Enterprise Edition of SQL Server. If you have EE, great. If not, taking down the index on a very large table will most likely result in the table being effectively offline. You aren't going to efficiently query 3M rows without lots of disk I/O.

If the table has to remain online, consider reorganizing the indexes instead of rebuilding. Doing a rebuild generally yields the best results, but reorganizing is sometimes necessary.

Another word of caution, if you alter your clustered index, all nonclustered indexes will be dropped, then the clustered index is changed and built, then nonclustered indexes get recreated. This is necessary because the clustered index gets inherited in by all the nonclustered indexes. This can take considerable time and should always be tested on a test system first.

I completely understand being under the gun to get something fixed, but please don't rush too much. Think it through, make sure you understand how it works and test it out.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39149 Visits: 32616
sqlnaive (1/25/2013)
Grant, in addition to this, does it means just declaring internal parameters won't resolve parameter snififng ? Is updating stats mandatory (I know there's always "as per situation" criteria but in generalized way) ? I am asking this because at times it becomes hard to update stats on big tables on daily basis in prod environment. In that case what would you suggest ?


"internal parameters" are called variables. And SQL Server can't sniff variables. The difference is that with a parameter on a stored procedure you get a precise seek against the statistics based on the value provided. With a variable you get a sampled average of the statistics. The issue is, are your queries better off with precise values or with averages? I can't tell you, you have to investigate it. But, the other issue comes into play when the statistics are bad (out of date or sampled instead of a full scan). Then, the precise seek returns a value, but it's an imprecise value leading the optimizer to make bad choices again. So, if you have lots of data being added to these tables and your statistics are going out of date (and this becomes especially true when dealing with date based data that is usually filtering for the latest information) then your query plans are going to go wonky. Your solutions are limited, update the stats.

Other than that... you can get into trying weird stuff. If the date range being searched against regularly is the latest data, then create a filtered index that includes today's date. Then the data set will be very small and you could update the statistics several times during the day (assuming the automatic updates don't do it for you).

But the issue is, if you want fast queries, you need good statistics and there's very few options around that.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86304 Visits: 45232
Ed Wagner (1/25/2013)
As a word of caution, I believe you can do online index rebuild only with the Enterprise Edition of SQL Server.


and only in SQL 2005 above, so on SQL 2000, it's ofline index rebuilds.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


sqlnaive
sqlnaive
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4269 Visits: 2774
Grant Fritchey (1/25/2013)
sqlnaive (1/25/2013)
Grant, in addition to this, does it means just declaring internal parameters won't resolve parameter snififng ? Is updating stats mandatory (I know there's always "as per situation" criteria but in generalized way) ? I am asking this because at times it becomes hard to update stats on big tables on daily basis in prod environment. In that case what would you suggest ?


"internal parameters" are called variables. And SQL Server can't sniff variables. The difference is that with a parameter on a stored procedure you get a precise seek against the statistics based on the value provided. With a variable you get a sampled average of the statistics. The issue is, are your queries better off with precise values or with averages? I can't tell you, you have to investigate it. But, the other issue comes into play when the statistics are bad (out of date or sampled instead of a full scan). Then, the precise seek returns a value, but it's an imprecise value leading the optimizer to make bad choices again. So, if you have lots of data being added to these tables and your statistics are going out of date (and this becomes especially true when dealing with date based data that is usually filtering for the latest information) then your query plans are going to go wonky. Your solutions are limited, update the stats.

Other than that... you can get into trying weird stuff. If the date range being searched against regularly is the latest data, then create a filtered index that includes today's date. Then the data set will be very small and you could update the statistics several times during the day (assuming the automatic updates don't do it for you).

But the issue is, if you want fast queries, you need good statistics and there's very few options around that.


For the big tables which are constantly getting affected by DMLs and hence impacting the statistics, what should be the solution in production environment ? Though we have enabled automatic updates of statistics on those tables, but I believe these work only on the basis of the amount of records getting impacted by the DMLs and/or on percentage basis only.
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39149 Visits: 32616
Yes, the default behavior is that 20% of the data must be modified before statistics are updated automatically (for those desperate to correct me, yes, I know it's more complex a formula than that, but when it comes to very large tables, this is effectively the formula, right?). You can set a traceflag, 2371, to change this behavior. Here are the details. In general, you have to figure out if either of these automated updates are adequate or if you need to manually update statistics. If you do manually update the statistics, you further have to determine if you can use the sampled scans or a full scan to get your statistics. There are no hard and fast rules for me to provide here. You have to figure out your own situation. Just know that there is no one size fits all solution for statistics updates.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86304 Visits: 45232
Grant Fritchey (1/28/2013)
You can set a traceflag, 2371, to change this behavior. Here are the details.


For SQL 2008 R2 and above only unfortunately.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


sqlnaive
sqlnaive
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4269 Visits: 2774
GilaMonster (1/28/2013)
Grant Fritchey (1/28/2013)
You can set a traceflag, 2371, to change this behavior. Here are the details.


For SQL 2008 R2 and above only unfortunately.


Grant/Gail, It's very very good article and rather very great thing provided by MS. However as it mentions:
"The downside is that updating the statistics results in recompilation of the queries accessing the table. This again can increase the risk of getting a different query plan for the next executions of queries against those tables. On the other side, the dynamic threshold to trigger update statistics should address issues encountered like not finding a new month or new fiscal year for month end reporting in the statistics and hence choosing a sub-optimal plan."

Don't you think once the statistics are upgraded, even if the new query plan is created, it will be optimal ?

And secondly, what does it mean by the issues like "not finding a new month or new fiscal year for month end reporting in the statistics and hence choosing a sub-optimal plan" ?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86304 Visits: 45232
sqlnaive (1/29/2013)
Don't you think once the statistics are upgraded, even if the new query plan is created, it will be optimal ?


Maybe, maybe not. If all the stats are up to date and the optimiser has enough time then it should be good enough.

And secondly, what does it mean by the issues like "not finding a new month or new fiscal year for month end reporting in the statistics and hence choosing a sub-optimal plan" ?


http://sqlinthewild.co.za/index.php/2011/03/22/statistics-row-estimations-and-the-ascending-date-column/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Radu Costescu
Radu Costescu
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 152
Hello again,

I tried updating the statistics using sp_updatestats. Well, now is it worst because executing the stored procedure code is also slow in execution now. Is good that it behave now consistent, but is bad that the problem is not solved yet... Sad

___________________________________________________________________
Is better shut up and look stupid than open your mouth and eliminate any doubt
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86304 Visits: 45232
sp_updatestats does sampled updates. That may not be good enough.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search