June 29, 2015 at 7:18 am
Dealing with 3rd party database. When analyzing tables/indexes found 2 indexes on one table. Which would not be bad but
index #1 clustered index on column a.
index #2 non clustered unique index on column a.
For the life of me I can't think of a scenario why you would not want just a clustered unique index and drop index #2, anyone think of any scenario where this would not be true?
June 29, 2015 at 10:38 am
Hmm, I'm guessing but I would imagine that the table was initially designed with the clustered index and then at some point someone wanted to enforce some form of business logic and added the unique index.
What do the index stats look like? Is it being used?
June 29, 2015 at 11:45 am
My instinct is to change the clustered index to be unique and drop the non-clustered because I can't think of an instance where the optimizer would choose the non-clustered unique index over the clustered index. It might if all that is included in the query is column a, but that would easily be met by changing the clustered index to be unique.
My guess is similar to DBA in the Cold's. The table was dsigned with the clustered index and then it was decided that column A also needed to be unique and the unique constraint was created, which is enforced by creating a unique non-clustered index.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 29, 2015 at 12:03 pm
Jack Corbett (6/29/2015)
My instinct is to change the clustered index to be unique and drop the non-clustered because I can't think of an instance where the optimizer would choose the non-clustered unique index over the clustered index. It might if all that is included in the query is column a, but that would easily be met by changing the clustered index to be unique.My guess is similar to DBA in the Cold's. The table was dsigned with the clustered index and then it was decided that column A also needed to be unique and the unique constraint was created, which is enforced by creating a unique non-clustered index.
+1
June 29, 2015 at 12:06 pm
more like sloppy development work have also found cases where they had duplicate unique indexes on same field in same table. Caught this thanks to Ozar's blitzindex.
June 29, 2015 at 12:17 pm
i have a couple of tables like that in a vendor database; with a single column index on the PK;
the funny thing is, that NC index gets used a lot when i look at the index stats, and it's of course a lot smaller than the PK index as well,
so i've left mine alone with the no harm no foul motto rolling through my mind.
Lowell
June 29, 2015 at 12:35 pm
Lowell (6/29/2015)
i have a couple of tables like that in a vendor database; with a single column index on the PK;the funny thing is, that NC index gets used a lot when i look at the index stats, and it's of course a lot smaller than the PK index as well,
so i've left mine alone with the no harm no foul motto rolling through my mind.
I can see that happening if, as I said in my first post, all the is being accessed is the single column. Then potentially there are fewer pages that have to be read, but in I'm not actually sure that would be true because it should never have to go to the leaf level of the clustered index if all that is required is the key column. I think I'll throw together a quick test.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 29, 2015 at 1:56 pm
Here's a quick test I threw together to see what happens:
IF OBJECT_ID('dbo.KeyTest', 'U') IS NOT NULL
BEGIN;
DROP TABLE dbo.KeyTest;
END;
CREATE TABLE KeyTest
(
ID INT NOT NULL
IDENTITY(1, 1),
OtherDate CHAR(500) DEFAULT 'A'
);
CREATE CLUSTERED INDEX CX_KeyTest ON dbo.KeyTest(ID);
GO
INSERT INTO dbo.KeyTest
(
OtherDate
)
SELECT TOP 500
AC.name
FROM
sys.all_columns AS AC;
GO
SET STATISTICS IO ON;
SELECT
KT.ID
FROM
dbo.KeyTest AS KT;
SELECT
*
FROM
dbo.KeyTest AS KT;
SET STATISTICS IO OFF;
GO
CREATE UNIQUE NONCLUSTERED INDEX UX_KeyTest ON dbo.KeyTest(ID);
GO
SET STATISTICS IO ON;
SELECT
KT.ID
FROM
dbo.KeyTest AS KT;
SELECT
*
FROM
dbo.KeyTest AS KT WITH (INDEX = UX_KeyTest);
SET STATISTICS IO OFF;
GO
SELECT
OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
SUM(a.total_pages) AS total_pages,
SUM(a.used_pages) AS used_pages,
SUM(a.data_pages) AS data_pages,
8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM
sys.indexes AS i
JOIN sys.partitions AS p
ON p.object_id = i.object_id AND
p.index_id = i.index_id
JOIN sys.allocation_units AS a
ON a.container_id = p.partition_id
WHERE
i.object_id = OBJECT_ID('dbo.KeyTest', 'U')
GROUP BY
i.object_id,
i.index_id,
i.name
ORDER BY
OBJECT_NAME(i.object_id),
i.index_id;
GO
CREATE UNIQUE CLUSTERED INDEX CX_KeyTest ON dbo.KeyTest(ID) WITH DROP_EXISTING;
GO
ALTER INDEX UX_KeyTest ON dbo.KeyTest DISABLE;
GO
SET STATISTICS IO ON;
SELECT
KT.ID
FROM
dbo.KeyTest AS KT;
SELECT
*
FROM
dbo.KeyTest AS KT;
SET STATISTICS IO OFF;
GO
SELECT
OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
SUM(a.total_pages) AS total_pages,
SUM(a.used_pages) AS used_pages,
SUM(a.data_pages) AS data_pages,
8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM
sys.indexes AS i
JOIN sys.partitions AS p
ON p.object_id = i.object_id AND
p.index_id = i.index_id
JOIN sys.allocation_units AS a
ON a.container_id = p.partition_id
WHERE
i.object_id = OBJECT_ID('dbo.KeyTest', 'U')
GROUP BY
i.object_id,
i.index_id,
i.name
ORDER BY
OBJECT_NAME(i.object_id),
i.index_id;
GO
To summarize, if just returning the key column, the non-clustered index does fewer reads, but as soon as you add the second column, if the non-clustered index is used it requires more reads due to the Bookmark/Key lookup. You also have to take into account the maintenance that having both indexes adds to inserts/updates/deletes. That's why I like using either Jason Strate's index analysis script[/url] or Brent Ozar Unlimited's sp_BlitxIndex both of which do some calculations to show the costs of indexes.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 30, 2015 at 1:29 am
Lowell (6/29/2015)
the funny thing is, that NC index gets used a lot when i look at the index stats
Yes, it will. Doesn't mean it's useful.
If you create 5 identical indexes on a table (index that will be used by the queries), wait a while then check index usage stats, all 5 will have been used.
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
July 22, 2015 at 9:06 pm
For the life of me I can't think of a scenario why you would not want just a clustered unique index and drop index #2, anyone think of any scenario where this would not be true?
I know Jack already talked about this but I'll give you one of my real life examples.
When I build a tally table[/url] I create it with a clustered index then throw a unique non-clustered index on top of it. The performance gain is minuscule but it generates fewer reads which is desirable.
-- Itzik Ben-Gan 2001
July 22, 2015 at 9:29 pm
tcronin 95651 (6/29/2015)
Dealing with 3rd party database. When analyzing tables/indexes found 2 indexes on one table. Which would not be bad butindex #1 clustered index on column a.
index #2 non clustered unique index on column a.
For the life of me I can't think of a scenario why you would not want just a clustered unique index and drop index #2, anyone think of any scenario where this would not be true?
It depends upon the query.
Imagine a large table that has 8000 bytes per row. Each row will be on a separate page, and a lot of pages may need to be read to get satisfy a query.
Now if that column is an integer (4 bytes), about 2000 rows could be on that single page.
If the query needs more than one row, and only needs the indexed column, which one would be more efficient?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply