July 16, 2013 at 10:45 pm
There is a SP which usually takes around 3 minutes to execute. Since last few days it is taking around 30-35 minutes.
So we did Rebuilding of all the index and updating the statistics.After that we run the same SP (which USED to take 3 minutes), after rebuilding & Updating the statistics the SP executed relatively fast and it executed by taking some 3-4 minutes. After that there were no operations done on DB for next 5-6 hours. After 5-6 hours we again tried to run the SP, but it again took 30-35 minutes.
We have observed a common pattern that when we Rebuild & update the statistics,the SP runs pretty quickly. But that is not feasible solution,that whenever we have to run the SP you Rebuild & Update the statistics.Moreover for fragmentation to come into picture, we need to do some insert/update/delete which we are not doing. Then WHY only after rebuild & Update statistics the SP runs smootly.
July 16, 2013 at 11:55 pm
Is Auto Update of Statistics on the Database Disabled??....Check both AUTO CREATE STATISTICS and AUTO UPDATE STATISTICS.........If any of them are disabled then enable them as follows:
-- Enable Auto Create of Statistics
ALTER DATABASE <DB_Name>
SET AUTO_CREATE_STATISTICS ON;
-- Enable Auto Update of Statistics
ALTER DATABASE <DB_Name>
SET AUTO_UPDATE_STATISTICS ON;
-- Update Statistics for whole database
EXEC sp_updatestats
Hope this helps. 🙂
July 17, 2013 at 12:05 am
vinu512 (7/16/2013)
Is Auto Update of Statistics on the Database Disabled??....Check both AUTO CREATE STATISTICS and AUTO UPDATE STATISTICS.........If any of them are disabled then enable them as follows:
-- Enable Auto Create of Statistics
ALTER DATABASE <DB_Name>
SET AUTO_CREATE_STATISTICS ON;
-- Enable Auto Update of Statistics
ALTER DATABASE <DB_Name>
SET AUTO_UPDATE_STATISTICS ON;
-- Update Statistics for whole database
EXEC sp_updatestats
Hope this helps. 🙂
Hi Vinu,
Thanks for quick reply.
I check for auto statistics update, But they are already ON.
Is there anything else which you would like to suggest .
July 17, 2013 at 12:17 am
vinu512 (7/16/2013)
Is Auto Update of Statistics on the Database Disabled??....Check both AUTO CREATE STATISTICS and AUTO UPDATE STATISTICS.........If any of them are disabled then enable them as follows:
-- Enable Auto Create of Statistics
ALTER DATABASE <DB_Name>
SET AUTO_CREATE_STATISTICS ON;
-- Enable Auto Update of Statistics
ALTER DATABASE <DB_Name>
SET AUTO_UPDATE_STATISTICS ON;
-- Update Statistics for whole database
EXEC sp_updatestats
Hope this helps. 🙂
Is there any way through that we can check that AUTO CREATE STATISTICS and AUTO UPDATE STATISTICS is enabled or disabled...
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 17, 2013 at 12:22 am
The large difference in duration could be due to "parameter sniffing". When the SP is executed the first time (after reog indexes / update stats) an execution plan is generated. SQL determines what values can be used as parameters and generates a plan using the supplied values. When you execute the SP another time using different values, SQL will re-use the plan that is allready been generated. This plan can be sub-optimal for the values of the parameters.
Take a look at an article from Jes Schultz: http://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/
July 17, 2013 at 12:31 am
kapil_kk (7/17/2013)
vinu512 (7/16/2013)
Is Auto Update of Statistics on the Database Disabled??....Check both AUTO CREATE STATISTICS and AUTO UPDATE STATISTICS.........If any of them are disabled then enable them as follows:
-- Enable Auto Create of Statistics
ALTER DATABASE <DB_Name>
SET AUTO_CREATE_STATISTICS ON;
-- Enable Auto Update of Statistics
ALTER DATABASE <DB_Name>
SET AUTO_UPDATE_STATISTICS ON;
-- Update Statistics for whole database
EXEC sp_updatestats
Hope this helps. 🙂
Is there any way through that we can check that AUTO CREATE STATISTICS and AUTO UPDATE STATISTICS is enabled or disabled...
Yes we check it through
SELECT DATABASEPROPERTY('dbname','IsAutoUpdateStatistics') AS IsAutoUpdateStatistics
If it returns 1 then yes(autoUpdateStatistics in on) else otherwise 🙂
July 17, 2013 at 12:38 am
HanShi (7/17/2013)
The large difference in duration could be due to "parameter sniffing". When the SP is executed the first time (after reog indexes / update stats) an execution plan is generated. SQL determines what values can be used as parameters and generates a plan using the supplied values. When you execute the SP another time using different values, SQL will re-use the plan that is allready been generated. This plan can be sub-optimal for the values of the parameters.Take a look at an article from Jes Schultz: http://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/
Hi HanShi,
Thanks for the article. It was good to read it. I had gone through the article but, i am not changing the parameter as they are mention in the article. After 4-5 hours i am executing the query with the same parameter as i had executed before 4-5 hours.
Is there anything else you would like to convey me. Thanks.
July 17, 2013 at 12:47 am
Shadab Shah (7/17/2013)
kapil_kk (7/17/2013)
vinu512 (7/16/2013)
Is Auto Update of Statistics on the Database Disabled??....Check both AUTO CREATE STATISTICS and AUTO UPDATE STATISTICS.........If any of them are disabled then enable them as follows:
-- Enable Auto Create of Statistics
ALTER DATABASE <DB_Name>
SET AUTO_CREATE_STATISTICS ON;
-- Enable Auto Update of Statistics
ALTER DATABASE <DB_Name>
SET AUTO_UPDATE_STATISTICS ON;
-- Update Statistics for whole database
EXEC sp_updatestats
Hope this helps. 🙂
Is there any way through that we can check that AUTO CREATE STATISTICS and AUTO UPDATE STATISTICS is enabled or disabled...
Yes we check it through
SELECT DATABASEPROPERTY('dbname','IsAutoUpdateStatistics') AS IsAutoUpdateStatisticsIf it returns 1 then yes(autoUpdateStatistics in on) else otherwise 🙂
Thanks Shadab....
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 17, 2013 at 12:47 am
Shadab Shah (7/17/2013)
kapil_kk (7/17/2013)
vinu512 (7/16/2013)
Is Auto Update of Statistics on the Database Disabled??....Check both AUTO CREATE STATISTICS and AUTO UPDATE STATISTICS.........If any of them are disabled then enable them as follows:
-- Enable Auto Create of Statistics
ALTER DATABASE <DB_Name>
SET AUTO_CREATE_STATISTICS ON;
-- Enable Auto Update of Statistics
ALTER DATABASE <DB_Name>
SET AUTO_UPDATE_STATISTICS ON;
-- Update Statistics for whole database
EXEC sp_updatestats
Hope this helps. 🙂
Is there any way through that we can check that AUTO CREATE STATISTICS and AUTO UPDATE STATISTICS is enabled or disabled...
Yes we check it through
SELECT DATABASEPROPERTY('dbname','IsAutoUpdateStatistics') AS IsAutoUpdateStatisticsIf it returns 1 then yes(autoUpdateStatistics in on) else otherwise 🙂
+1
July 17, 2013 at 1:01 am
Shadab Shah (7/17/2013)
vinu512 (7/16/2013)
Is Auto Update of Statistics on the Database Disabled??....Check both AUTO CREATE STATISTICS and AUTO UPDATE STATISTICS.........If any of them are disabled then enable them as follows:
-- Enable Auto Create of Statistics
ALTER DATABASE <DB_Name>
SET AUTO_CREATE_STATISTICS ON;
-- Enable Auto Update of Statistics
ALTER DATABASE <DB_Name>
SET AUTO_UPDATE_STATISTICS ON;
-- Update Statistics for whole database
EXEC sp_updatestats
Hope this helps. 🙂
Hi Vinu,
Thanks for quick reply.
I check for auto statistics update, But they are already ON.
Is there anything else which you would like to suggest .
Is it possible for you to post the DDL of the tables being used by the SP, some sample data and the code of the SP?
Try this :
Copy paste the code of the SP in a new query window, declare the parameters which are being passed into the SP as Local Temporary Variables and then execute it as a simple batch of code rather than executing it as an SP. (Hope you understand what I am trying to say).
It could be a case of Parameter Sniffing.
Please try the above steps and tell us if there is any difference in the execution time.
July 17, 2013 at 1:13 am
vinu512 (7/17/2013)
Shadab Shah (7/17/2013)
vinu512 (7/16/2013)
Is Auto Update of Statistics on the Database Disabled??....Check both AUTO CREATE STATISTICS and AUTO UPDATE STATISTICS.........If any of them are disabled then enable them as follows:
-- Enable Auto Create of Statistics
ALTER DATABASE <DB_Name>
SET AUTO_CREATE_STATISTICS ON;
-- Enable Auto Update of Statistics
ALTER DATABASE <DB_Name>
SET AUTO_UPDATE_STATISTICS ON;
-- Update Statistics for whole database
EXEC sp_updatestats
Hope this helps. 🙂
Hi Vinu,
Thanks for quick reply.
I check for auto statistics update, But they are already ON.
Is there anything else which you would like to suggest .
Is it possible for you to post the DDL of the tables being used by the SP, some sample data and the code of the SP?
Try this :
Copy paste the code of the SP in a new query window, declare the parameters which are being passed into the SP as Local Temporary Variables and then execute it as a simple batch of code rather than executing it as an SP. (Hope you understand what I am trying to say).
It could be a case of Parameter Sniffing.
Please try the above steps and tell us if there is any difference in the execution time.
Hi,
HHhhmmm..... I don't think that this is a problem with parameter sniffing. To my understanding Parameter sniffing occurs when we are changing the parameter (i.e. for some parameter the result return is more values and for some parameter the result set return less value).
In my case i am executing the SP with the same parameter which i had used before. So, Parameter Sniffing .....:hehe:
July 17, 2013 at 1:25 am
Take a look at these two links:
Perhaps you can find some differences between both executions and pin-point the problem to specific wait-stats, (b)locking, etc.
July 17, 2013 at 5:39 am
Rebuilding the index defragments the indexes and updates the statistics using a full scan against the data. Then, you're updating statistics. So, the question is, how is that second update of statistics occurring? Are you using sp_updatestats? If so, that does a sampled update.
Instead of rebuilding the statistics, try just doing a statement against the tables in question UPDATE STATISTICS ... WITH FULL SCAN. That will give you the most up to date statistics. From the sounds of things, I think that you have a volatile set of data and the automatic update of statistics is not doing enough for you. You might also look at trace flag 2371. This changes the way that the auto update decides when it should fire. That could also possibly improve your performance.
You might still be looking at a parameter sniffing issue. My suggestion. Get the execution plan when you run the query right after you update the statistics and save it. Then, when you run it hours later and it runs slow, get the execution plan again. Compare the two. Are they different? If so, look at the SELECT operator properties and see what the Compile values are for the parameters. Are they different? If so, look to the statistics (DBCC SHOW_STATISTICS) to see how each of the different values are distributed within your system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply