June 1, 2011 at 10:36 am
I have been working on one issue where one Procedure usually takes 6-9 secs to complete but at times it takes around 30 mins to complete. After index rebuild and update stats, it again starts working fine.
So I guess there is nothing wrong with query. Now there is a normal select statement in the procedure which uses three tables as inner joins and two as left outer joins. The problem seems to be with two specific tables where the dataset for current run gets populated in other procedure [which gets executed earlier than the problematic one in the same batch].
There are few points:
1. The two tables contain clustered index with columns A, B, C, D
But there are three nonclustered indexes with columns [A, B], [B, C] and [D]. I strongly think it may be an issue.
2. Though we have autoupdatestatistics set to ON at database level, It seems all the stats for these two tables are not getting updated properly [those which are on indexes as well as system created ones]
Can anyone please suggest something ? I'm planning to drop all the indexes and system created stats on the tables and then recreate the clustered index only with the same four columns.
June 1, 2011 at 10:38 am
Target those tables and run update stats early in the day (after a decent amount of data has started being added to those tables).
June 1, 2011 at 10:54 am
We already have a job created before our batch starts to rebuild the indexes. However the main thing is that one of the procedure within the batch populates data in those two tables and then later we have to fetch data based on the joins which I mentioned earlier.
When I checked the DBCC SHOW_STATISTICS for the main clustered index, I didnot found the EQ_ROWS values for the current value of clustered index.
June 1, 2011 at 10:58 am
Rebuild the index only update the stats for THAT index, not all other columns.
Gail's article is still what makes the most sense. If not that then it's parameter sniffing.
When do you rebuild? Right after the load? If not then maybe you need to issue and update stats during the load for the relevant stats. You can also try option(recompile) at the statement if the update stats doesn't work which should take care of parameter sniffing.
June 1, 2011 at 11:42 am
Hello Ninja, We do rebuild for all the indexes in the tables [which I think updates the stats on those indexes as well].
Thereafter our batch starts which populates further data into the tables and at the end of batch when we try to fetch records on pagination basis from those tables, it gets stuck [at times and not always]. Then we have to do update stats and it works fine.
People say that I should look into the query. however if the same query is taking just few seconds, I strongly think that the issue is not in code but how to manage the table's condition. And right now I'm not getting any idea on how to deal with this. One more thing here, that our sproc definition already has "With Recompile".
June 1, 2011 at 11:47 am
sqlnaive (6/1/2011)
Hello Ninja, We do rebuild for all the indexes in the tables [which I think updates the stats on those indexes as well].Thereafter our batch starts which populates further data into the tables and at the end of batch when we try to fetch records on pagination basis from those tables, it gets stuck [at times and not always]. Then we have to do update stats and it works fine.
People say that I should look into the query. however if the same query is taking just few seconds, I strongly think that the issue is not in code but how to manage the table's condition. And right now I'm not getting any idea on how to deal with this. One more thing here, that our sproc definition already has "With Recompile".
As I said, READ THE FREAKING link I posted.
Update the stats RIGHT AFTER the load and it will ALWAYS work.
Thank you.
June 1, 2011 at 12:24 pm
Ninja, I had already gone through the link. It's pretty useful. I agree with your point in having Update Stats job after the loading of tables, that is, in between of our batch. However if we go with update stats with full scan, it may take lot of time and we may breach our SLA. In other case if we do a sample update stats then it may or may not be effective.
I wish if somehow we could update the stats "value" wise as well for a particular stats.
June 1, 2011 at 1:58 pm
Then try this, don't rebuild the index but only do the stats.
I'm pretty sure that doing a simple update stats with 20% should be enough and wouldn't take hours to run.
June 2, 2011 at 4:32 am
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.
June 2, 2011 at 5:54 am
It's a 20% sample... so check 1 row, skip 4, check 1 row... etc.
Not sure this is exactly how it is implemented but it wouldn't make any sense to scan the first 20% and ignore the rest.
All it takes is ALTER permission on the table or view.
If it fixes the problem I don't see much of an issue.
The first step is to add the update stats after the insert in your code in the dev area and see if it fixes it. Then you can worry about how you implement the permission change.
June 2, 2011 at 6:06 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 all data required for the second proc are populated by the first proc, then why not using a separate table (maybe even a temp table), populate it, add the indexes requierd for the second sproc and use that one instead of the original one? (you could insert those data in the final target table at the end of the second sproc if required...)
If the current process runs in less than 10 seconds I don't think indexing a temp table will take a considerable amount of time (especially if it helps to avoid 30min duration)
June 2, 2011 at 10:08 am
Doing some more R&D on Statistics... Really exciting. DBCC SHOW_STATISTICS is wonderful. Cross verofying it with execuiton plan as well. However can anyone tell where to get colmodctrs value ?
June 2, 2011 at 10:15 am
Should be in sysindexes or sysstats... not sure.
You can go in the master db and check the source code for sp_updatestats I know it uses that column in there.
June 3, 2011 at 3:17 am
Great knowledge feast on statistics these days. I was checking that statistics on any index maintains informaiton based on the first column of the index only. Now if our query is based on all the columns included in the index, how that statistics helps optimizer to make efficient plan ?
June 3, 2011 at 5:34 am
sqlnaive (6/3/2011)
Great knowledge feast on statistics these days. I was checking that statistics on any index maintains informaiton based on the first column of the index only. Now if our query is based on all the columns included in the index, how that statistics helps optimizer to make efficient plan ?
Never heard that info before... Where did you read that?
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply