Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Indexing in SQL Server 2005 Expand / Collapse
Author
Message
Posted Tuesday, December 19, 2006 6:52 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, January 17, 2014 11:05 AM
Points: 779, Visits: 222
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/aingold/2770.asp
Post #331682
Posted Monday, January 8, 2007 12:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 28, 2014 6:11 AM
Points: 223, Visits: 139

One book to read is Relational Database Index Design and the Optimizers by Tapio Lahdenmaki and Mike Leach.

This Book contains basics and a "little" further.

Regards

JT

a DBA




Post #334973
Posted Monday, January 8, 2007 5:15 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697

The only comment I'd make is that the order of columns in a covered index "just depends" , generally the most restrictive ( selective) is best placed first - but not always - so it's worth making a few tests including putting the columns in reverse order, I tend to create a number of variations for a particular query and see which the optimiser finds best ( not for simple queries or all queries obviously ) Also worth noting that changing data volumes can change the index selection process.

Nice article.



The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #335006
Posted Monday, January 8, 2007 7:44 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, January 17, 2014 11:05 AM
Points: 779, Visits: 222
Colin, those are both good points that deserve mentioning! Thank you for pointing them out. It is definitely important to try changing up your high-cost queries to see how the query processor will handle them as part of the baseline/test process. Also, re-evaluating efficiency at a later date when data has changed is a good part of the full database lifecycle.
Post #335034
Posted Monday, January 8, 2007 8:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 27, 2010 3:03 PM
Points: 16, Visits: 7

Great Article!  You know what would make your query even more valuable (to me, at least) is a rowcount for the table in the resultset.  The indexes I would want to pay the most attention to for potentially dropping are the indexes on the largest tables.  I would add that to the order by.  Anyone know how to get the rowcount in 2005?  The rowcnt field that was available on sysindexes doesn't appear to have made the transition from 2000 to 2005.

Luke




Post #335081
Posted Monday, January 8, 2007 9:19 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, January 17, 2014 11:05 AM
Points: 779, Visits: 222

Luke-

Try joining to sys.dm_db_index_physical_stats to determine rowcount... that should work.

Post #335089
Posted Monday, January 8, 2007 9:31 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, January 17, 2014 11:05 AM
Points: 779, Visits: 222

(post-coffee)

Wait, that's a function...  try APPLY.

Post #335094
Posted Monday, January 8, 2007 9:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:26 AM
Points: 304, Visits: 522

(mid-coffee)

a join seemed to worked, I think...  would it have been better to use APPLY? TIA, I've never tried it.  And thank you for the article!

>L<

SELECT o.name AS object_name, i.name AS index_name,

i.type_desc, u.user_seeks, u.user_scans, u.user_lookups, u.user_updates,

p.record_count

FROM sys.indexes i

JOIN sys.objects o

ON i.object_id = o.object_id

LEFT JOIN sys.dm_db_index_usage_stats u

ON i.object_id = u.object_id

AND i.index_id = u.index_id

AND u.database_id = DB_ID()

JOIN (SELECT object_id,record_count

FROM master.sys.dm_db_index_physical_stats(DB_ID(), NULL,NULL,NULL,'DETAILED') ) p

ON i.object_id = p.object_id

WHERE o.type <> 'S' -- No system tables!

ORDER BY (ISNULL(p.record_count,0) +ISNULL(u.user_seeks, 0) + ISNULL(u.user_scans, 0) + ISNULL(u.user_lookups, 0)

+ ISNULL(u.user_updates, 0)), o.name, i.name

Post #335113
Posted Monday, January 8, 2007 10:33 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962

Great article with many good tips.

You said, "I once had a developer look me straight in the eye and say that there would never be a problem with adding as many indexes as possible to a table; the query processor would simply select the ones it needed and ignore the rest."  That might be OK if this is a pre-built, static, never changing database.  Too many indexes are going to cause a bad impact on INSERT, UPDATE, and DELETE.  Remember all those indexes have to be updated when the data changes.



ATB

Charles Kincaid

Post #335125
Posted Monday, January 8, 2007 11:08 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, January 17, 2014 11:05 AM
Points: 779, Visits: 222

Yup... see my comment two lines down:

Unnecessary indexes can force the query processor to evaluate more before deciding on an optimum execution plan, can slow down transactional processing and can take up a great deal of space on disk.

Post #335133
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse