June 3, 2011 at 6:37 am
If you check DBCC SHOW_STATISTICS for any clustered index or nun clustered index of a table, you'll see data sampling for only first column of the index [If an index contains more than one columns] [Third part of the result]
June 3, 2011 at 6:57 am
sqlnaive (6/3/2011)
If you check DBCC SHOW_STATISTICS for any clustered index or nun clustered index of a table, you'll see data sampling for only first column of the index [If an index contains more than one columns] [Third part of the result]
Ya but why do you assume this is the way the stats actually work??
I makes no sense in my head but I can't contradict that statement either with y current knowledge.
June 3, 2011 at 7:11 am
If a table contains 5-10 statistics and some performance issue arises because of statistics, then is there a way to nail down to the level where we can get the culprit statistics ?
June 3, 2011 at 7:29 am
There's not performance probleme because there are too much stats but because they are OUT OF DATE.
have you tried running the update stats AFTER the insert?
June 3, 2011 at 7:53 am
sqlnaive (6/2/2011)
Ninja, Updating the stats with 20% may faten the process. However In our scenario, it may not resolve the issue entirely. There are two points:1. The entire dataset on which the second proc works is populated in first proc in the table. So if we update the stats with 20% before the first batch, will that count for the records pushed into the table in proc 1 ?
2. I think we should update the stats in between the two procs. However how to implement it, seems tricky. We use service account with limited rights to run the batch. On the other hand, updating stats require some extra rights which may be of some issue.
If the account that you use to execute the procedure does not have enough rights to update statistics, then you can add the EXECUTE AS clause to the stored procedure to give it the necessary rights.
CREATE PROCEDURE dbo.MyProc
WITH EXECUTE AS 'dbo'
AS
... rest of code...
June 17, 2011 at 3:01 am
Hello All, Sorry to bring this thread alive again. Just wanted to let you all know that we did lots of code changes and finally it looked like problem is solved. However I just again got some issue. The procedures were running good from last two weeks and suddenly today it took lot of time. This time it was the procedure containing few updates and inserts upto 0.4 Million records. Usually it used to take 1 minute to complete but today it took more than half an hour.
June 17, 2011 at 3:45 am
The fragmentation level in tables looks good to me. The only difference I can see is in number of rows sample in statistics based on clustered indexes. Currently it shows just 8% of rows sampled out of total Rows. Earlier it was 100%. Can this be a potential issue ?
June 17, 2011 at 7:07 am
Please post the actual execution plan of the bad and good run (better to have both, but the bad is required).
So what's the stats update frequency / sequence in the job now?
June 18, 2011 at 7:24 am
There's no question how important it is to update statistics. We've seen it fix problems over and over again. So we run it for an hour nightly in our own job, plus many, many hours each weekend. The question we have is how to determine a sampling rate that is sufficient.
I can tell that when you don't specify a sampling rate, sql server uses roughly 1% sampling, give or take a bit. ( auto update stats or if you run update statistics with no sampling parameter ).
Over the past few years we've done fullscan, but at 40 million rows and growing that no longer acceptable. So we're currently at 90% and looking at some targeted jobs where we might do fullscan on specific tables or indexes.
I don't think we're going to have acceptable run times on this over the weekend until we get down below 30%
June 18, 2011 at 10:11 am
One setting that I have seen mentioned in this discussion, and it may be of no value, but then again it could be.
Using sys.databases determine if "is_auto_update_stats_async_on"
My reason for asking this, is:
AUTO_UPDATE_STATISTICS_ASYNC
When ON
Queries that initiate an automatic update of out-of-date statistics
will not wait for the statistics to be updated before compiling.
Subsequent queries will use the updated statistics when they are available.
When OFF
Queries that initiate an automatic update of out-of-date statistics
wait until the updated statistics can be used in the query optimization plan.
And note:
Setting this option to ON has no effect unless AUTO_UPDATE_STATISTICS is set to ON.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply