SQL Server Performance Forum – Threads Archive
Timeouts
Hi guys, Here is my problem: We do have an website application with 24/7. We should not have downtime even for 5 min.Currently we have a job for index maintenance that first will ouput dbcc showcontig result into a table and then execute next step in a job to check if today is not Sunday and ExtentSwitches > Extents
then do indexdefrag.If today is sunday and number of rows > 100000 then defrag, otherwise, rebuild. The problem is during all this index maintenace we do have timeouts from the application site and i am trying to investigate what the problem. My first question is is there a way to monitor timeouts using Profiler to check which select statement or SP generates timeouts? 2.i don’t think rebuilding indexes using dbreindex is a good idea even for a short amount of time on a life system.I guess indexdefrag is just enough but it still generates timeouts. 3.What is a good estimation to which indexes needs to be defrag? Do you think by cheking ExtentSwitches > Extents only is a good idea? 4.i would appreciate if you have some sample script that will do auto defrag indexes. 5. Also, what is a good aproach to do auto defrag on Sql Server 2005 systems ( 24/7, no downtime at all) ? Thanks
http://sqlserver-qa.net/blogs/tools/archive/tags/indexing/default.aspx fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
quote:Originally posted by satya
http://sqlserver-qa.net/blogs/tools/archive/tags/indexing/default.aspx fyi.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Thanks a lot
Do provide feedback on your findings, as I need to update blog with further information [<img src=’/community/emoticons/emotion-1.gif’ alt=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
]]>