Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...
SQL Server Forum / DB Engine / SQL Server / December 2007

Tip: Looking for answers? Try searching our database.

Table Indexes Getting Fragmented and Performance Drops

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Kyle R. Hanrahan - 26 Dec 2007 21:29 GMT
We have been having problems with some of our internal tables.  We have a
table that contains quite a few columns, and three indexes.

The primary key on this table is a guid, and there are about 8,000,000+ rows
in the table.  We are finding that the indexes on this table, after about
four days, get very fragmented and the scan density drops to about 10%-12%
and the query optomizer starts doing full table scans instead of using the
indexes.

After doing a dbcc dbreindex on the table everything looks good again and
queries start performing faster, we are trying to figure out why these
indexes go downhill so fast and what can we do to stop them from getting so
beat up.
Signature

Kyle Hanrahan, MCSE, MCTS (SQL Server)

Reply to this Message
John Bell - 26 Dec 2007 22:33 GMT
Hi Kyle

Using a GUID in a clustered index can cause fragmentation of the index if
you are using NEWID() instead of NEWSEQUENTIALID() Check out
http://www.informit.com/articles/article.asp?p=25862&seqNum=1&rl=1
http://www.sqlmag.com/articles/index.cfm?articleid=46821&
http://www.sqljunkies.com/Article/4067A1B1-C31C-4EAF-86C3-80513451FC03.scuk

John

> We have been having problems with some of our internal tables.  We have a
> table that contains quite a few columns, and three indexes.
[quoted text clipped - 9 lines]
> indexes go downhill so fast and what can we do to stop them from getting so
> beat up.
Reply to this Message
TheSQLGuru - 27 Dec 2007 15:31 GMT
To help you, we need table and index schema at a minimum, preferably with
some sample data.  We also need usage pattern information (how many deletes,
inserts updates per unit time) as well a the types of select queries
executed.  Indexed column value distributions can be helpful as well.

In general GUIDs are absolutely horrid for primary keys, especially if you
cluster on them.  16 bytes (which has numerous negative consequences if
clustered), difficult to validate uniqueness, non-sequential, slow to
generate using NEWID, etc, etc, etc.
Signature

Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net

> We have been having problems with some of our internal tables.  We have a
> table that contains quite a few columns, and three indexes.
[quoted text clipped - 9 lines]
> indexes go downhill so fast and what can we do to stop them from getting
> so beat up.
Reply to this Message
mlbrooke@googlemail.com - 28 Dec 2007 14:06 GMT
I work with SQL databases used for an Email archive product I support
which run into the 10,s of millions of rows per table and had exactly
the issue you are talking about: Running a DBCC Showcontig would show
very low scan densities and doing a drop and re-create of indexes
wouldn't change the scan density even when configured to be 90%.

If you are running SQL 2000 there is a bug that can cause this (link
below):

http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=256&threadid=
48326&enterthread=y

Hope this helps, BTW the company I work for is Quest Software and one
of our tools SQL Capacity manager will free up the space in those
tables - but you will still need to do as the article says.

Regards

On Dec 26, 9:29 pm, "Kyle R. Hanrahan" <vbnet...@newsgroup.nospam>
wrote:
> We have been having problems with some of our internal tables.  We have a
> table that contains quite a few columns, and three indexes.
[quoted text clipped - 11 lines]
> --
> Kyle Hanrahan, MCSE, MCTS (SQL Server)
Reply to this Message
Kyle R. Hanrahan - 28 Dec 2007 14:57 GMT
We believe we are on the right track with this issue.  We have found that it
is the GUIDS that we are using that is causing the kind of fragmentation
that we are seeing.

When a GUID is a primary key on a table (some of our clients are SQL 2000 so
right now newsequenceid() will not work), the pages get split very easy due
to the nature of guids being almost random, so they may be inserted anywhere
in the index.

We changed the fill factor of the primary index to 50 to try and compensate
for the page splitting issues, so we will see what happens from here.

I work with SQL databases used for an Email archive product I support
which run into the 10,s of millions of rows per table and had exactly
the issue you are talking about: Running a DBCC Showcontig would show
very low scan densities and doing a drop and re-create of indexes
wouldn't change the scan density even when configured to be 90%.

If you are running SQL 2000 there is a bug that can cause this (link
below):

http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=256&threadid=
48326&enterthread=y

Hope this helps, BTW the company I work for is Quest Software and one
of our tools SQL Capacity manager will free up the space in those
tables - but you will still need to do as the article says.

Regards

On Dec 26, 9:29 pm, "Kyle R. Hanrahan" <vbnet...@newsgroup.nospam>
wrote:
> We have been having problems with some of our internal tables. We have a
> table that contains quite a few columns, and three indexes.
[quoted text clipped - 13 lines]
> --
> Kyle Hanrahan, MCSE, MCTS (SQL Server)
Reply to this Message
TheSQLGuru - 28 Dec 2007 16:55 GMT
Why don't you consider making the PK a NONclustered index and simply having
a heap table?

Signature

Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net

> We believe we are on the right track with this issue.  We have found that
> it is the GUIDS that we are using that is causing the kind of
[quoted text clipped - 47 lines]
>> --
>> Kyle Hanrahan, MCSE, MCTS (SQL Server)
Reply to this Message
Andrew J. Kelly - 29 Dec 2007 14:58 GMT
A fill factor of 50% can reduce fragmentation but will increase your I/O up
to double and will make memory utilization less efficient as well. There is
a sequential guid solution available for 2000 as well in the form of an
extended sp. See here for more details.

http://www.sqldev.net/xp/xpguid.htm

Signature

Andrew J. Kelly    SQL MVP
Solid Quality Mentors

> We believe we are on the right track with this issue.  We have found that
> it is the GUIDS that we are using that is causing the kind of
[quoted text clipped - 47 lines]
>> --
>> Kyle Hanrahan, MCSE, MCTS (SQL Server)
Reply to this Message
TheSQLGuru - 30 Dec 2007 16:04 GMT
I have a client that uses that xp.  Unfortunately they call it from a farm
of middle-tier servers and thus get a variety of hotspots in the table
instead of one monotonically increasing GUID for calling it from the same
machine consistently.  Still better than non-sequential ones though.

Signature

Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net

>A fill factor of 50% can reduce fragmentation but will increase your I/O up
>to double and will make memory utilization less efficient as well. There is
[quoted text clipped - 55 lines]
>>> --
>>> Kyle Hanrahan, MCSE, MCTS (SQL Server)
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.