Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DaleTurley
Yak Posting Veteran
76 Posts |
 Posted - 2012-02-08 : 04:11:17
|
| Hi,Just looking for some advice as to where to go next on some trouble shooting before i contact Microsoft directly. Any help would be much appreciated.We had a problem on a live server that brought the server to a stand still. A query on the main page of a web application was maxing out a core of the CPU each time it was executed (used by over 200 users). After looking into the query it was joining to a table with 7 indexes. The fragmentation levels are as below.Clustered 0%NonClustered1 6%NonClustered2 25%NonClustered3 3%NonClustered4 5%NonClustered5 17%NonClustered6 25%When the index that is 5% fragmented (the one it seeks) is rebuilt the query returns in under 1 second with next to nothing resource usage. When the index is reorganized the fragmentation goes to 2% and the problem still exists. Our re-indexing procedure runs nightly and rebuilds any indexes that are 30% or above fragmentation.I'm trying to think of any ways i can find why this is happening, like where the fragments of the indexes are etc so if anyone has any suggestions...ThanksDale |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-08 : 08:28:13
|
| Auto Update Stats is on?Is the column this index on a datetime type or an identity?Tried updating statistics with fullscan? |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-08 : 09:43:34
|
quote:
Originally posted by russell
Auto Update Stats is on?
And maybe needs to b set to ASYNC rather than SYNC (the later is the default I think). Although I think ?? this would only cause slow-down on queries when an auto-update of stats was triggered, so would not be every time. |
|
|
|
DaleTurley
Yak Posting Veteran
76 Posts |
Posted - 2012-02-09 : 05:04:18
|
| Hi, thanks for the replies.Yes the index is on a DATETIME column and not an identity, AUTO UPDATE STATS is set to TRUE and AUTO UPDATE STATS ASYNCH is set to FALSE.I'll have a read through that linked document as well as trying updating stats as appose to rebuilding the index.I'll reply back with any information i can get on this.Thanks |
|
|
|
|
|
|
Index Fragmentation Problem