July 2, 2009 at 10:30 am
Hi everyone,
We have an application that was running very slowly on a search. There are multiple parameters, only one of which was being passed in. I found the problem field and initially I thought perhaps there was a problem with the index, but we do database maintenance which involves reindexing, plus passing in a value (rather than leaving it default to NULL) returned data quickly, so I figured maybe the index wasn't a problem.
Long story short, I incorporated an IF statement to run separate pieces of code based on whether that value was supplied so I was no longer using ISNULL. The procedure (meaning, the script itself in an open window) ran quickly for me so I had the user check it out. She told me it was still taking as long as 9 minutes to return the data. I realized then that executing the proc (as opposed to running the script in my open window) was also taking long.
I did an sp_recompile on the object. This seemed to help when I executed the proc; however, it didn't resolve the user's issue. I then did DBCC DROPCLEANBUFFERS. Same results. I waited till system usage was low and did a DBCC DBREINDEX on the two tables used by the "problem" join. This seemed to work - the user got her results back immediatley. Case closed.
This was two days ago. Today the user is again reporting that the system is hanging. I went ahead and did the DBCC DBREINDEX again, and it immediately resolved the problem.
So, my question is...does anyone know what could seemingly cause the indexes to go bad so quickly? Is it possible it's something besides the indexes? Do I need to do something in addition to the reindex? It seems excessive to have to continuously reindex these two tables, especially considering one table only has 63,393 records and the second only has 86,288. Far from large in our company!
Thanks!
July 2, 2009 at 10:49 am
Does this help? http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Were the indexes actually fragmented? If not, if may be stale statistics. If that's the case then am update statistics should fix the problem next time
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply