September 17, 2008 at 2:15 am
Hi
I was wondering if anyone had any experience with columns that have been indexed more than once in a table.
We currently have several tables where a column is indexed more than once,
i.e.
Index1 Column1, Column3
Index2 Column2, Column3, Column4
Index3 Column3, Column5
Obviously the indexes have been added to tune different queries.
I'd like to hear how people would deal with this situation, if we replaced Index1 and 3 with a new Index with Column1, Column3, Column5 would that affect read performance?
Thanks.
Stef.
September 17, 2008 at 2:51 am
Hello,
It depends on how your data is being accessed. If all queries on the table go via Column1 and column3 or Column3 and Column5, then by combining the indexes you would have a performance hit. This is because the index will be physically bigger and therefore slower to read. How big the performance hit is depends on the size of the data-types involved, server activity, etc so it might well be insignificant.
Having said that, you might consider replacing some of your existing non-clustered indexes with "Covering Indexes" i.e. ones that include enough columns so that the query does not need to access the underlying data pages. (See BOL "Creating Indexes with Included Columns")
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
September 17, 2008 at 12:30 pm
No you should not alter your indexes for that reason. Generally the selectivity of the first column defines whether the optimiser will use the index. as all your start with a different column it's unlikely they would work better joined together. It's actually slightly more complicated than that because size as in width and number of pages and the actual queries all affect this too.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 17, 2008 at 2:01 pm
Stef (9/17/2008)
I'd like to hear how people would deal with this situation, if we replaced Index1 and 3 with a new Index with Column1, Column3, Column5 would that affect read performance?
Potentially, because if you have a query that filters only on Column3, Column5, it will no longer be able to do an index seek if you drop index 3.
A query can only seek on an index if the where conditions are a left-based subset of the index keys
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
September 17, 2008 at 2:16 pm
Run the following query on a schedule and store in a table for later analysis:
SELECT
OBJECT_NAME( s.[object_id] ) AS TableName
, i.name AS IndexName
, s.user_updates
FROM
sys.dm_db_index_usage_stats s
INNER JOIN
sys.indexes i
ON
s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE
db_name(s.database_id) = db_name(db_id())
AND objectproperty(i.object_id, 'IsIndexable') = 1
AND LEFT(i.name, 3) NOT IN ('PK_', 'UC_')
AND s.user_updates > 0 AND s.user_seeks = 0
AND s.user_scans = 0 AND s.user_lookups = 0
ORDER BY
OBJECT_NAME( s.[object_id] )
Alternatively, loosen the fliters of the above query somewhat to retrieve indexes with low usage in your SQL instance.
After some time (say a few days or so) look at your results.
Are some of your duplicate indexes included in that list? Then perhaps you should consider dropping those indexes.
If not, then you will need to go through the more laborious process of tracking down which queries are using which index etc.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 18, 2008 at 3:29 am
Thanks guys, didn't know about the first column being used for selectivity by the optimiser, that will be useful.
I'll let you know how it goes, thanks for the input guys.
Stef.
September 18, 2008 at 12:38 pm
there a series of posts which I did here http://www.grumpyolddba.co.uk/sql2005/sql2005.htm
I don't actually have anything which directly relates to your question, I just did a presentation on indexes but again didn't directly cover your question - I'll perhaps look into writing something up as it's a question I'd not considered.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 19, 2008 at 1:16 pm
blogs.msdn.com/sqlprogrammability/archive/2007/06/29/detecting-overlapping-indexes-in-sql-server-2005.aspx
blogs.ameriteach.com/chris-randall/2007/8/15/sql-server-2005-detecting-overlapping-indexes.html
http://blogs.msdn. com/mssqlisv/archive/2007/06/29/detecting-overlapping-indexes-in-sql-server-2005.aspx
http://blogs.msdn.com/mssqlisv/archive/2007/06/29/ detecting-overlapping-indexes-in-sql-server-2005.aspx
Amazing what a very simple web search can do for you.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply