Index Usage

Question

Index Usage

aruram

SSCrazy

Points: 2379
More actions
April 23, 2008 at 10:18 am

#206419

Hi,
Here is the output from SYS.DM_DB_INDEX_USAGE_STATS for one of the tables
The table has a composite clustered PK, a unique nonclustered key(UK_FTS) and bunch nonclustered keys.
1) From the output, is it safe to assume that the keys with low seek values (<1000) are not effectively being used and drop them?
2) Would it be better to create a clustered index on the unique key? The scan count seems to indicate range searches on the col
3) The high value for lookup on the clustered index is not optimal? Would it be better to look at using a composite non clustered covering index?
4) Any other suggestions looking at the usage stats?
IndexName Seek Scan Lookup Update
______________________________________________
PK_TEST 6897311407099298703
UK_FTS 32473312780298675
idxTestPlan22148910298675
idxTestId000 298675
idxTestResult1720810298675
idxRunDate662900298675
idxSubGroup111710298675
idxDefect 8420298675
idxBlocked2800298675
idxTester1000 298675
idxLoadNum000298675
idx_ddts 000298675
Thanks!

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Without seeing the table structure, or the index definitions, it's hard to say.

You could probably widen a couple of the indexes and it would reduce the lookups. Without knowing the queries, it's hard to give specifics

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aruram SSCrazy Points: 2379 More actions · Answer 2

Okay, but don't the 0 values for seeks indicate that the index is not being used? Thanks.

Here is the table structure-

[testPlanName] [nvarchar](90) NOT NULL,

[testId] [nvarchar](60) NOT NULL,

[subGroup] [nvarchar](25) NOT NULL,

[detail] [char](20) NOT NULL,

[priority] [int] NULL,

[tester] [nvarchar](50) NULL,

[lstUpdUser] [nvarchar](50) NOT NULL,

[lstUpdDate] [datetime] NOT NULL,

CONSTRAINT [PK_TEST_CASE_LINK] PRIMARY KEY CLUSTERED

( [testPlanName] ASC,

[testId] ASC,

[subGroup] ASC,

[detail] ASC

)

here are the non clustered indexes-

idxTestId testId

idxlstUpdDate lstUpdDate

idxTester tester

idxTestPlanNametestPlanName

idxDetail detail

idxSubGroupsubGroup

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Yes. I was more referring to the other NC indexes.

As I mentioned in another thread, that cluster is exceedingly wide. The recommendations for clustered indexes is that they are as narrow as possible. The clustered index key is present in all of the NC indexes, as it is the rows's address. A wide clustered index means that all of the nonclustered indexes are wide, increasing the number of pages and space that they occupy and making them less efficient,

What is the unique constrain on?

What are the common queries on this table? What columns are selected, what columns are filtered on?

Is there any preferred order for data retrieved from this table?

The index idxTestPlanName is redundant, because it is a left-based subset of another index (in this case, the PK) SQL is probably only using that index because the leaf level is smaller (being a NC index) than the clustered index.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 4

you might like to read some work I did on this

http://sqlblogcasts.com/blogs/grumpyolddba/archive/2008/02/18/analysing-indexes-summary.aspx

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/