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.

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=’

‘ />]. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

]]>