March 19, 2014 at 3:49 am
Hi experts,
What is the different of impact between the below two indexes.
1)CREATE NONCLUSTERED INDEX [IX_ABC_LastComm_XDMSiteID] ON [dbo].[ABC]
(
[AssetID] ASC,
[LastComm] ASC,
[ABCID] ASC,
[XDMSiteID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
2)CREATE UNIQUE NONCLUSTERED INDEX [ABC_AssetID_UNCI] ON [dbo].[ABC]
(
[AssetID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
When the indexes are used? and will SQL Server avoid using the first one?
March 19, 2014 at 5:12 am
The first one need more work to be maintained by the server. It will be modified each time that one of the 4 column will be modified. The second one will be modified only when AssetID column will be modified. If you never update any of the columns in both indexes, then the main difference will be in the index's size.
The usage of each index depends on the queries that you have, table's size and the data distribution. Here are some examples:
Suppose we have the fallowing queries:
SELECT * FROM MyTable WHERE AssetID = 1
SELECT * FROM MyTable WHERE AssetID = 1 and LastComm = 1
If the table is very small, no index will be used. If the table is big table, but none of the indexes is selective enogh, then again the server won't use either of the indexes.
If the table is a big table and AssetID is a unique column, then most chances are that the second index will be used for both queries, but if the first index is a covering index the server will use it for both queries (by the way if it was created this way to be covering index, you should consider recreating the index and adding the columns as included columns).
If AssetID is not selective column and the combination of AssetID and LastComm is more selective and selective enough to be used, then there is a good chance that the first index will be used for the second query and a table scan for the first one.
To make a long story shorter, without knowing anything about the table's size, data in the table and queries, no one will be able to tell you which of the indexes is better and will be used by the server.
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 19, 2014 at 5:47 am
Thanks a ton Adi..
This is what i was looking..
The table is have 150 million records and is around 1TB in size. I think still the index usage depends on the query used right...
1.My doubt is if i use the second query mentioned by you and consider that i have another index having lastcomm and another column as index keys will it use the first index or 2 & 3??
March 19, 2014 at 6:02 am
In theory, you're more likely going to see use of the first index in most situations and no use on the second, depending. Because they both have the same leading edge, first column, they're both going to have similar or the same, set of statistics in the histogram based on the same data, that first column. But the second column will have additional density settings, making it more selective, because of the other columns in the key. That makes it very attractive to the optimizer. The one thing the second index has going for it is that it's unique. That will sometimes be attractive to the optimizer, depending on the query. But both indexes really depend on the queries being run.
If that column is unique, then the first index can also be marked as unique, even if it has extra columns in it. So then, you don't need the second index at all.
If that column is unique, is it the primary key of the table? If so, is it also already a clustered index? If true, you've got three indexes, all with the same leading edge which is seriously wasting resources and storage. Further, the clustered index, where the data is stored, is even more likely to be used than the other indexes.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply