SQL Server Performance Forum – Threads Archive
Is is possible to lower the priority of defrag?
Our site does a regular index defrag (DBCC INDEXDEFRAG) every night. During the time thatthe index defrag runs, our disks saturate and performance for other users suffers (It goes from subsecond performance to serveral minutes). We’d like to be able to tell SQL Server (SQL Server 2000 running on Win2k in this case) that the index defrag is a lower priority than any user transactions. Is there any way in SQL Server 2000 to do make the index defrag be of a lower priority than the other transactions?
Not possible in real scenario. It does not hold locks long term and thus will not block running queries or updates. _________
Satya SKJ
Is it really necessary to run DBCC INDEXDEFRAG every night? You might find that running it once a week is enough. What you might consider doing is to DBCC SHOWCONTIG to see if you really need to do this nightly. For example, one night run DBCC INDEXDEFRAG, then run DBCC SHOWCONTIG at the end of the next day. If the density is in good shape, then don’t run DBCC INDEXDEFRAG, and the night day run DBCC SHOWCONTIG again to see what is happening. You may find that running DBCC INDEXDEFRAG once a week is enough. In addition, the size of your fillfactor will partially determine how often you need to run DBCC INDEXDEFRAG. So you may want to example what your current fillfactor is and adjust it to see how changes to it affect the issue of fragmentation. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Thank you both for your time. It sounds like SQL Server in fact has no way in which to reduce the priority of the defrag process. As far as running defrag less frequently or more carefully, we do in fact already run a script that looks at fill factor etc in order to determine which indexes to defrag, so that we don’t defrag more often than we need to. As far as defrag blocking out other users, I agree that it doesn’t actually lock the table, but it was degrading performance so much that jobs were not getting processed within our time window. It would have been nice if the defrag process watched the disk queue lengths and backed off when it noticed that that disks were saturated or when other processes were also waiting on I/O. In our case, since SQL Server was bottlenecking on I/O, our resolution has turned out to be to buy faster disks.
Well SQL Server does have a limitation of not being able to set the priority of a particular process. Also too bad that you cann’t monitor the disk activity directly using SQL Server. But consider this.. If you cerate a perfmon trace to run parallely with the DEFRAG job, I think you can monitor the disk activity along. The idea is to capture the activity in a file and then depending on the load on the system in past say 30 minutes, decide if the defragmentation of the next table can be done or not. Essentially this solution works if you have multiple tables to defrag and also they are taking considerable amt of time to defrag. Just a thought. What do others have to say about this? Gaurav
I support running PERFMON along with DEFRAG job will give you assessment on performance to judgewith. Also keep in mind server resources also play vital role in gaining performance. _________
Satya SKJ
]]>