Indexes quickly fragmenting

Question

Post reply

Indexes quickly fragmenting

sqlsc-1053844

SSC Enthusiast

Points: 159
More actions
March 26, 2009 at 11:03 am

#384224

I'm writing for a database that has recently needed indexes to be able to pull queries in a short amount of time. My problem is that these indexes are reaching 80+ fragmentation in less than a week. I'm working on a script that will reorg/rebuild as necessary but I wanted to make sure I'm not putting a band aid on a bigger problem. Here's a sample query on a [problematic] table that uses index heavily.
OPEN SYMMETRIC KEY SymKeyGlobal DECRYPTION BY CERTIFICATE CertificateName;
SELECT tblIndex_Phone.intPK_MasIndex
FROM tblIndex_Phone
WHERE tblIndex_Phone.intPK_MasIndex IN(1,2,3,4,5,6)
AND CONVERT(nvarchar, DecryptByKey(tblIndex_Phone.strPhone)) = '5551231234';
CLOSE SYMMETRIC KEY SymKeyGlobal;
Let me know if more information is needed to help diagnose. Any helpful information will be appreciated. Thank you for any assistance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

indexes become fragmented due to inserts.

say you have an index on "LastName".

if i insert someone whose lastname starts with "S", it will probably fragment the index, as it forces anew page into the chain in the middle of the names starting wiht "R" and "T"

on tables where you have inserts, you need to leave a bit of space, for example:

CREATE UNIQUE INDEX [IX__MYTABLE] ON [MYTABLE](LASTNAME) WITH FILLFACTOR = 90

so it leaves 10 percent for gorwth.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 2

Check out the fill factor of the table, seems like you may need to adjust it see article:-

http://qa.sqlservercentral.com/articles/Performance+Tuning/whocaresaboutfillfactor/1196/

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

maechismo_8514 SSCoach Points: 17510 More actions · Answer 3

sqlsc (3/26/2009)
I'm writing for a database that has recently needed indexes to be able to pull queries in a short amount of time. My problem is that these indexes are reaching 80+ fragmentation in less than a week. I'm working on a script that will reorg/rebuild as necessary but I wanted to make sure I'm not putting a band aid on a bigger problem. Here's a sample query on a [problematic] table that uses index heavily.
OPEN SYMMETRIC KEY SymKeyGlobal DECRYPTION BY CERTIFICATE CertificateName;
SELECT tblIndex_Phone.intPK_MasIndex
FROM tblIndex_Phone
WHERE tblIndex_Phone.intPK_MasIndex IN(1,2,3,4,5,6)
AND CONVERT(nvarchar, DecryptByKey(tblIndex_Phone.strPhone)) = '5551231234';
CLOSE SYMMETRIC KEY SymKeyGlobal;
Let me know if more information is needed to help diagnose. Any helpful information will be appreciated. Thank you for any assistance.

On top of this, can we ask you the size of the table please? and also the fillfactor that you are setting to? and How heavy the table gets modified?

Basically, you should Rebuild indexes if you are experience that heavy fragmentation, there is no point in defragging the table if you have 80% fragmentation.