Index analysis

Question

Index analysis

scott.laird

Mr or Mrs. 500

Points: 521
More actions
September 3, 2014 at 6:45 am

#298974

A hypothetical example:
Table Y has an index: X.
Index X has 3 columns: A,B,C with an included column of: T.
Table: Y has 5 million rows...index: X has 10,000 reads and writes per day. Multiple apps use this table and a hundred or more different select statements are being executed.
Stored procedure / logic changes have been made over time. Column C is no longer referenced in any of the query extractions.
How can I see / prove programmatically that index X is too wide - the index is needed, but the added value is not what is was - if I remove C from the index, the queries will still use index: X (without a scan or secondary lookup).

Viewing 7 posts - 1 through 6 (of 6 total)

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

I'd simply set up a test to show it. Create a copy of your tables and show queries with and without Column C. Specifically, focus on the execution plan to show that it hasn't changed. You could also show some benefits in reduced reads, but that's probably going to be somewhat marginal.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

er.mayankshukla SSCarpal Tunnel Points: 4190 More actions · Answer 2

How can I see / prove programmatically that index X is too wide

You can use this query to see the index size:

SELECT index_depth AS D

, index_level AS L

, record_count AS 'Count'

, page_count AS PgCnt

,avg_fragmentation_in_percent AS 'PercentFragmentation'

, avg_page_space_used_in_percent AS 'PgPercentFull'

, min_record_size_in_bytes AS 'MinLen'

, max_record_size_in_bytes AS 'MaxLen'

, avg_record_size_in_bytes AS 'AvgLen'

FROM sys.dm_db_index_physical_stats

(DB_ID ('master')

, OBJECT_ID ('demo2')

, 1 /*-->Index ID*/, NULL, 'DETAILED');

I use this query for fragmentation checking.

The index size will of Level 1, hence in the output of this query, check the MinLen corresponding to the row with L (Index_level) column of value 1

if I remove C from the index, the queries will still use index: X (without a scan or secondary lookup).

Yes , you can remove C from index key, if not using in where clause for any select

You can move your column C to included column , if it is a part of select list.

As it will not affect your key column width

scott.laird Mr or Mrs. 500 Points: 521 More actions · Answer 3

Grant Fritchey (9/3/2014)
I'd simply set up a test to show it. Create a copy of your tables and show queries with and without Column C. Specifically, focus on the execution plan to show that it hasn't changed. You could also show some benefits in reduced reads, but that's probably going to be somewhat marginal.

Thanks for responding...

The above would work on the scenarios that I know of, but what about the scenarios that I am unaware of?

I want to use what I need as of today - not what I needed X months ago. Take what you want, but use what you take.

I want to reduce the size of the index and increase the performance on the writes.

I am looking for a proactive, programmatic approach to detect indexes that are stiff effective, but are adding too much work that is not required.

Thanks

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Core problem there is that you may not know what you don't know, meaning, it's entirely possible for there to be queries coming from sources that you don't know that need that additional column. There's no way to be 100% on this. Test it, validate it, then cross your fingers and monitor the system.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

scott.laird Mr or Mrs. 500 Points: 521 More actions · Answer 5

Grant Fritchey (9/3/2014)
Core problem there is that you may not know what you don't know, meaning, it's entirely possible for there to be queries coming from sources that you don't know that need that additional column. There's no way to be 100% on this. Test it, validate it, then cross your fingers and monitor the system.

Thanks, I figured that there was no easy solution.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 6

Agreed, there's no easy way to tell if an index has extra columns. You can use the missing index stats to get a feel for missing column(s) though. Thus, if you do remove it, you'll very likely be "told" by SQL if it actually was needed in an existing query(ies).

But, since that's fine tuning rather than core tuning, first make sure you have the best clustered index on each table. That's the real key to overall performance. Then later worry about removing extra column(s) in covering index(es).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".