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 |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2011-05-05 : 20:11:49
|
| I'm sharing this with people in case it helps anybody because it helped me a lot and I don't really understand it. I have a lookup table that gets rebuilt every 45 minutes for reporting and is used in 80% of my report queries. All of a sudden my queries slowed to a crawl today and Mgmt Studio (and sys.dm_db_missing_index_details) kept telling me that I should create an index on that lookup table that already existed. Eventually I tried unchecking the "Use index" box in the Options of the index and hitting OK, then doing the reverse, and somehow that worked! |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2011-05-06 : 03:31:41
|
| This happend becuase when you uncheck "Use Index", basically you are disabling your index. And when you check again and press OK, SQL Server REBUILD the said index and resultantly removing all of its fregmentation. And once your index is totally defregmented, obviously your database regain its performance. So keep an eye on your indexes fregmentation and execute scripts through a job or manually to remove this fregmentation. Tara Kizar has written an effient script for this purpose http://weblogs.sqlteam.com/tarad/archive/2007/04/17/60176.aspx--------------------------http://connectsql.blogspot.com/ |
 |
|
|
Jahanzaib
Posting Yak Master
115 Posts |
Posted - 2011-05-06 : 07:55:30
|
| you have mentioned 80% use in reporing purpose , what is the ratio of Selection and Insert,update,delete on this table,if Selection is heavy then you will have to mention fillfactor from 95 to 100 or 0 and DML also heavy then fillfactor must be 75 tp 85,first mark the red line and then decideRegards,Syed Jahanzaib Bin HassanMCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBAMy Blogwww.aureus-salah.com |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2011-05-06 : 08:05:45
|
| Who asked abt fillfactor ??? |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2011-05-06 : 15:53:11
|
| The table gets dropped and rebuilt, so there's one batch on inserts and no updates, every 45 minutes. That said, I don't see any reason the fill factor should be anything but 0 (i.e. 100% full), and I don't see any reason there would be fragmentation, but I appreciate the guesses. |
|
|
|
|
|
|
|
|
bizarre index usage behavior