Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexing in SQL Server 2005


Indexing in SQL Server 2005

Author
Message
Aaron Ingold
Aaron Ingold
Right there with Babe
Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)

Group: General Forum Members
Points: 787 Visits: 222
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/aingold/2770.asp
jtango
jtango
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 Visits: 156

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





colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2725 Visits: 715

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/
Aaron Ingold
Aaron Ingold
Right there with Babe
Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)

Group: General Forum Members
Points: 787 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.
LukeEmbree
LukeEmbree
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
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





Aaron Ingold
Aaron Ingold
Right there with Babe
Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)

Group: General Forum Members
Points: 787 Visits: 222

Luke-

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


Aaron Ingold
Aaron Ingold
Right there with Babe
Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)

Group: General Forum Members
Points: 787 Visits: 222

(post-coffee)

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


Lisa Slater Nicholls
Lisa Slater Nicholls
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 610

(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


Charles Kincaid
Charles Kincaid
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1043 Visits: 2383

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.



ATBCharles Kincaid
Aaron Ingold
Aaron Ingold
Right there with Babe
Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)

Group: General Forum Members
Points: 787 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.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search