July 14, 2010 at 2:05 pm
I am a developer/DBA for a software company and I am working with developers on a performance enhancement project for SQL Server on 2005/2008. We are doing some load testing and I have some test scripts that I have been running while doing some index tuning and such...and this week I was testing one script it was taking 4 seconds to run...I added some indexes and it dropped to .7 seconds...then later in the day it was back up to 4 seconds again. Well I checked my indexes and they were all there...so I ran an index defrag script and a rebuild all stats script and it dropped back to .7 seconds again. Then today I had the QA team do some controlled testing while I ran a trace and after the trace I ran the query again and it was back up to 4 seconds!! I ran the update stats script and it dropped back to .7 seconds again.
I am just confused...anyone have any ideas?
-chris
July 14, 2010 at 2:40 pm
What is the value of STATISTICS_NORECOMPUTE for the index in question?
It seems like the index distribution statistics are not recomputed...
How did you add the indexes (using T-SQL, SSMS->NEW INDEX or SSMS -> table designer)?
Maybe the issue described here has not been resolved yet...
July 14, 2010 at 2:41 pm
- do you have auto-update statistics enabled for the database ?
- how is the fragmentation rate of your indexes ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 14, 2010 at 3:03 pm
I created the indexes with a T-SQL script...and the value is set to OFF...the default.
July 14, 2010 at 3:05 pm
Auto-statistics update is turned on.
I just did a rebuild on all my indexes that had more than 30% fragmentation.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply