February 19, 2017 at 10:52 pm
Hi ,
I have a table with 100 million records with below index structure.
CREATE CLUSTERED INDEX IDX_ID ON TEST(ID)
CREATE NONCLUSTERED INDEX IDX_NC_ID ON TEST(ID)
Here Clustered and Non-Clustered index are created on same column. Now my doubt is it will affect the performance or it will take unnecessary memory?
Is it duplicate index? If it is unnecessarly taking memory means Can we remove Non-Clustered Index?
Thanks in Advance..!!!
Regards,
Arun
February 19, 2017 at 11:51 pm
certainly they are duplicate. The clustered index can benefit the same queries as the non clustered one. But there is a chance that someone might have created it intentionally for a narrow index for a particular scenario, as the clustered index contains all other columns.Can you share the table definition?
Also you can verify the index usage statistics DMV to decide whetehr to remove it.
February 20, 2017 at 12:15 am
Thanks Roshan...
Now i have found how it is created after discussing with development team... They have created table and index like below code....
CREATE TABLE TEST(ID INT NOT NULL)
CREATE CLUSTERED INDEX IDX_ID ON TEST(ID)
ALTER TABLE TEST ADD CONSTRAINT PK_ID PRIMARY KEY(ID)
If we go through sys.index_columns DMV it is showing as Clustered and Non-Clustered index for same column.
Pls suggest me...
February 20, 2017 at 1:36 am
There are two indexes because you created an clustered Index before specifying the Primary Key. The primary key is usually the clustered index unless specified otherwise.
So since creating the clustered index first and then creating a primary key, the primary key got created as the non clustered index on the ID column.
So as stated earlier there might be a reason for this (2 indexes), but it almost looks to me that they only wanted to create the primary key but didn't use correct method which then resulted in the two indexes.
February 20, 2017 at 2:07 am
Thanks for your reply... Additionally i want to know some information. Both Index will take some memory right? It will cause any performance issue?
February 20, 2017 at 3:10 am
balu.arunkumar - Monday, February 20, 2017 2:07 AMThanks for your reply... Additionally i want to know some information. Both Index will take some memory right? It will cause any performance issue?
Generally, Both clustered and non-clustered index will store in the disk/storage, when they has been calling from the user it will be placed in the memory/buffer.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 20, 2017 at 3:25 am
Depending on things like fragmentation and fill factor, having a clustered index doesn't use any more disk space than not having one. This is because a clustered index is nothing more than an ordering of the rows in a table on the clustering key. But there is indeed a performance penalty - maintaining two indexes every time there's a data modification is clearly more expensive than maintaining just one. As Joe has already suggested, though, check the index usage DMV to see which of the indexes is actually being used.
John
February 20, 2017 at 3:32 am
And keep in mind that even if the DMV shows both are used (and it probably will), the smaller index may not be necessary and removing it probably won't hurt query performance.
Test with the index disabled and see if you notice any problems.
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
February 20, 2017 at 7:27 am
It does depend on how the queries are consuming your indexes. If you have an exact duplicate clustered index and a non-clustered index and you have a query that needs to scan the index, and both indexes are covering, you're more likely to see the non-clustered index used because it will generally be smaller than the clustered index (where all the data is stored).
However, this is a pretty rare edge case. Most of the time, if your indexes are properly constructed, and the queries are well written, you're going to be seeing seeks (that's a big part of indexing in the first place). In this case, the secondary, non-clustered index, will very seldom if ever get used.
As to extra memory, no, the existence of the index doesn't add memory overhead. It does add storage overhead, by existing. There will also be a lot of extra processing to maintain that index, that could affect memory (as well as CPU and tempdb and other resources).
At the root, what are you trying to achieve with this additional index?
"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
February 20, 2017 at 10:39 am
You need to look at some actual stats, from the system views SQL provides, rather than just guessing.
The sys.dm_db_index_* and sys.dm_db_missing_index_* views can give you valuable stats on the indexes are being used. You might also need to look at query plans to see if the nonclustered index is being scanned, which means it likely would need to be its own separate index.
But in fact you should dig deeper than that. It's more likely than not that the table has a better column(s) for the clustered index than just an ID column anyway. The stats will give you that info as well (once you know how to read and interpret them, at least 🙂 ).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply