August 13, 2007 at 4:16 pm
Hi
I am running update statistics on one of my Prod DB once in a week. Now the SP Execution is becoming slower every day. The scenario is as below:
1. DB Size is >1 TB
2. Data is loaded once in a week via many SP/ETL jobs. (One of this SP is slowing down)
3. Only update statistics is scheduled to be executed.
Do u suggest to use INDEXDEFRAG also to improve the performance? Please advice if you have any idea.
(Rebuilding index may be a very costly operation as tables are pretty big)
Regards
Utsab
August 13, 2007 at 4:37 pm
I would suggest a defrag of the indexes. Rebuild them if you have a maintenance period. Statistics are great, but good statistics on a bad index is only going to help so much.
August 13, 2007 at 5:04 pm
Where's the performance hit taking place? On SELECT's???
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2007 at 5:58 pm
Jeff brings a good point (I was in a meeting while responding the first time so I'm sorry if I was brief). The issue is that if you're noticing a slowdown on SELECT statements there's a reasonable chance that you have increasing fragmentation on your indexes (which can be aided by doing a defrag/rebuild). If you're noticing increasing slowness on your insert/update/delete work then that's likely something that index cleanup operations aren't going to help with.
There's a lot of other potential issues that could be at stake. You might be better off for the time being isolating what types of operations you're experiencing the slowdown on.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply