Re-Index slows the query | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Re-Index slows the query

Every sunday there is scheduled maintenace job ( reindex all the tables in the database)
We have query which used to run in 2 seconds.. after the re-index job it takes 180 seconds. If we disable the maintenace job for a week or 2 .. then if we run the query it populates the values in 2 seconds. we are thinking that re-index slows the query. database integrity or dbcc checkdb do not have any erros. Any idea why ?
To find out what is really happening, capture the execution plan of the query when it runs fast and when it runs slow, and compare the differences. This should help in indentifying the cause of this issue. ————————————————————–
Brad M. McGehee, SQL Server MVP
Technical Editor/Moderator www.SQL-Server-Performance.Com
Director of DBA Education for www.Red-Gate.Com
www.sqlbrad.com
www.sqlHawaii.com
We noticed that 2 tables are taking lot of utilization. ( in exceution plan ).
Those 2 tables have proper index, update stats with full scan.
after 2 weeks of time, utilization percent reduces ( in execution plan )
no clue why
After 2 weeks it reduces due to the fragmentation, it is better to maintain a regular scheduled job to perfrm UPDATE STATISTICS & DBREINDEX on these tables to attain the performance. http://sqlserver-qa.net/blogs/tools…statistics-auto-update-statistics-option.aspx andhttp://sqlserver-qa.net/blogs/perft…th-update-statistics-feature-in-sql-2005.aspx still few of them applies to SQL 2000 too. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.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.
]]>