January 15, 2009 at 10:30 am
I have a reindexing job setup on one of the production servers. After reindexing , some of the procs run slow and when I rebuild index again on those tables which the proc uses, performance becomes fine..Any idea what's going on? Should I make any change to the re-indexing job. All I am doing is running DBCC reindex and update stats after that.
Thanks..
January 15, 2009 at 10:39 am
did you free the db's proccache after the reindex ?
Possibly the old cached plans nolonger serve optimal with the new statistics and organisation.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 15, 2009 at 10:52 am
Hi,
Would it possible to post the source code please.
If the prior posters recommendation does not improve things, I am wondering if perhaps you need to look into performing more focused statistics updates.
Perhaps you could also make available the statistical information for the table in question using DBCC SHOW_STATISTICS.
Cheers,
January 15, 2009 at 12:09 pm
Freeing Procache will cause query performance to go down in the beginning, Isn't it..
January 15, 2009 at 12:15 pm
cherie (1/15/2009)
Freeing Procache will cause query performance to go down in the beginning, Isn't it..
That really depends. Freeing the proccache just removes the plans from cache, so when you run the procedure next it will create the plan and re-add it to cache. If the plan that was previously in cache was a bad one, it is possible that the recreation of the plan along with the execution of the procedure will be faster and it is possible that it will be slower.
In this case where re-indexing has caused things to slow down, it seems most likely that it would be because of a bad plan that is cached, so I believe the thought of the poster above was that this would be a benefit in the long run.
January 15, 2009 at 12:28 pm
One more question..why is it then that when I manually rebuild one of the table index, performance gets better and then again after a week with reindexing..it goes down again.
January 15, 2009 at 12:40 pm
cherie (1/15/2009)
One more question..why is it then that when I manually rebuild one of the table index, performance gets better and then again after a week with reindexing..it goes down again.
Probably not the answer you are looking for but it just depends on a number of things.
I would say to look at the statistics IO and look at the query plan and see what it is doing. Check it before you rebuild the index and after you rebuild it and then again when it is running "slow". If you have a table that changes enough, it is possible that even with the rebuilding of the index that the statistics within the database tell the Optimizer that a scan is the best thing to do by weeks end, but that when you rebuild the index manually (which rebuilds the statistics with fullscan) the optimizer sees things differently.
There are a lot of possibilities I would say. Digging in and finding out what SQL is doing is probably your best best to remedy the situation long term.
January 15, 2009 at 12:48 pm
Thanks Mike..I'll do some more diggin' and see what's going on.
Thanks a lot..
January 15, 2009 at 12:51 pm
Indeed, freeing the proc cache cause all sql (including sprocs) to be re-evaluated for a plan the next time they execute.
With well organized indexes (including clustered please) and up to date statistics, that should give you better performance.
If performance degrades very fast, you may have to rebuild indexes faster, chose other (clustered) indexes, or even alter your crud schemas.
I've seen cases where the app inserted an "empty" row to get the next id and then updated all columns because that's the way they wrote it. This caused massive page splits, hence degrading rate was huge.
And as your data grow, queries may need to be re-tuned because of rule no. 1 violation. (Tell the system everything you know !)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 15, 2009 at 10:16 pm
cherie (1/15/2009)
I have a reindexing job setup on one of the production servers. After reindexing , some of the procs run slow and when I rebuild index again on those tables which the proc uses, performance becomes fine..Any idea what's going on? Should I make any change to the re-indexing job. All I am doing is running DBCC reindex and update stats after that.Thanks..
I will obviously take more memory and CPu, that'y
January 15, 2009 at 10:59 pm
Some other things...
Are you shrinking your database between index rebuilds?
If your tables fragments very fast is it possible to look for another column(s) that can server as a better clustered index. I have to warn you this will be really tricky.
"Keep Trying"
January 16, 2009 at 2:03 am
Hi,
I would suggest following the previous recommendations with regard to determining and developing an understanding of how your data structures are being affected/used.
Reviewing the statistics for the indexes of your affected tables should assist in your investigation.
If you can isolate the volatile indexes you may wish to investigate whether tweaking the FILLFACTOR could prove beneficial.
Be sure to let us all know how you get on.
Cheers,
January 16, 2009 at 6:45 am
How are the FILLFACTOR and PADINDEX values set? And are you performing this through the GUI or through a script?
If scripting, could you provide the script, otherwise the values you are entering the GUI?
January 16, 2009 at 9:40 am
I have a job setup which runs the script below on the database:
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
January 16, 2009 at 9:44 am
Chirag: I can't change indexes..:-( I have to go to developers for that, which is a really-really long process.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply