Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Both tables have same structure and 19972 rows in each table. for practicing indexing, i created both tables having same structure and created

clustered index on persontb(BusinessEntityID)

and

nonclustered index on Persontb_NC(BusinessEntityId)

and table structure

BusinessEntityID int
FirstName varchar(100)
LastName  varchar(100)                                                                                                                       

 -- Nonclusted key on businessentityid takes 38%
SELECT  BusinessEntityId from Persontb_NC
WHERE businessentityid BETWEEN 400 AND 4000

-- CLustered key businessentityid takes 62%
SELECT BusinessEntityId  from persontb 
WHERE businessentityid BETWEEN 400 AND 4000

enter image description here

Why clustered index takes 62% and non clustered 38%?

share|improve this question
    
Why vote for close? –  Registered User Jun 18 '13 at 14:40
add comment

migrated from stackoverflow.com Jun 19 '13 at 2:23

This question came from our site for professional and enthusiast programmers.

3 Answers

Yes the clustered index has fewer rows per page than the non clustered index as the leaf pages of the clustered index must store the values for the other two columns (FirstName and LastName).

The leaf pages of the NCI store only the BusinessEntityId values and a row locator (RID if the table is a heap or the CI key otherwise).

So the estimated costs reflect the greater number of reads and IO requirement.

If you were to declare the NCI as

nonclustered index on Persontb_NC(BusinessEntityId) INCLUDE (FirstName, LastName)

then it would be similar to the clustered index.

share|improve this answer
add comment

Clustered index contains not only data from column index is on, but also data from all other columns. (There can only be one clustered index per table)

Nonclustered index contains only data from indexed column(s), and a row_id pointer to where the rest of data is.

Therefore this particular nonclustered index is lighter and less reading is required to scan/seek through it and this particular query will work faster.

However, have you tried to retrieve FirstName and LastName as well, it would be different and clustered index should perform better.

share|improve this answer
add comment

The percentages between the query plans are meaningless to compare outright. You must benchmark the queries to have a valid comparison. Additionally, small row counts have a tendency to hide performance differences between indexing strategies. By increasing the row count to 10 million you can gain a clearer picture of the performance differences.

There is a sample script that creates 3 tables, your two from above, and a third with both a clustered and non-clustered index.

USE [tempdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[t1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [c1] [varchar](200) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[t2](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [c1] [varchar](200) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[t3](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [c1] [varchar](200) NULL
) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX CIX_t1 ON t1(id)

CREATE NONCLUSTERED INDEX IX_t2 ON t2(id)

CREATE CLUSTERED INDEX CIX_t3 ON t3(id)
CREATE NONCLUSTERED INDEX IX_t3 ON t3(id)

Populate the tables with 10 million rows

DECLARE @i INT
DECLARE @j int
DECLARE @t DATETIME
SET NOCOUNT ON
SET @t = CURRENT_TIMESTAMP
SET @i = 0
WHILE @i < 10000000
BEGIN
--populate with strings with a length between 100 and 200 
INSERT INTO t1 (c1) VALUES (REPLICATE('x', 101+ CAST(RAND(@i) * 100 AS INT)))
SET @i = @i + 1
END

PRINT 'Time to populate t1: '+ CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR(10)) + ' ms'
SET @t = CURRENT_TIMESTAMP


SET @i = 0
WHILE @i < 10000000
BEGIN
--populate with strings with a length between 100 and 200 
INSERT INTO t2 (c1) VALUES (REPLICATE('x', 101+ CAST(RAND(@i) * 100 AS INT)))
SET @i = @i + 1
END

PRINT 'Time to populate t3: '+ CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR(10)) + ' ms'
SET @t = CURRENT_TIMESTAMP

SET @i = 0
WHILE @i < 10000000
BEGIN
--populate with strings with a length between 100 and 200 
INSERT INTO t3 (c1) VALUES (REPLICATE('x', 101+ CAST(RAND(@i) * 100 AS INT)))
SET @i = @i + 1
END

PRINT 'Time to populate t3: '+ CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR(10)) + ' ms'

We can use sys.dm_db_index_physical_stats to see the size on disk of the indexes.

SELECT  OBJECT_NAME(OBJECT_ID) table_name, index_id, index_type_desc, 
record_count, page_count, page_count / 128.0 size_in_mb, avg_record_size_in_bytes
FROM    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('t1'), NULL, NULL, 'detailed')
WHERE   index_level = 0 
UNION ALL
SELECT  OBJECT_NAME(OBJECT_ID) table_name, index_id, index_type_desc, 
record_count, page_count, page_count / 128.0 size_in_mb, avg_record_size_in_bytes
FROM    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('t2'), NULL, NULL, 'detailed')
WHERE   index_level = 0 
UNION ALL
SELECT  OBJECT_NAME(OBJECT_ID) table_name, index_id, index_type_desc, 
record_count, page_count, page_count / 128.0 size_in_mb, avg_record_size_in_bytes
FROM    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('t3'), NULL, NULL, 'detailed')
WHERE   index_level = 0 

And the results:

table_name  index_id    page_count  size_in_mb  avg_record_size_in_bytes    index_type_desc
t1  1   211698  1653.890625 167.543 CLUSTERED INDEX
t2  0   209163  1634.085937 165.543 HEAP
t2  2   22272   174.000000  16  NONCLUSTERED INDEX
t3  1   211698  1653.890625 167.543 CLUSTERED INDEX
t3  2   12361   96.570312   8   NONCLUSTERED INDEX

T1's clustered index is around 1.6 GB in size. T2's non-clustered index is 170 MB (90% savings in IO). T3's non-clustered index is 97 MB, or about 95% less IO than T1.

So, based off of the IO required, the original query plan should have been more along the lines of 10%/90%, not 38%/62%. Also, since the non-clustered index is likely to fit entirely in memory, the difference may be greater still, as disk IO is very expensive.

share|improve this answer
1  
It is a bit of a leap to infer that your 10%/90% figure is more accurate than the 38%/62%. Strings with a length between 100 and 200 will certainly be a gross overestimate of space requirements for a firstname/lastname pair so you will have lower page density than the OP. When I try against your example data the estimated costs show up as 87%/13%. –  Martin Smith Jun 19 '13 at 8:04
1  
SQL Server does aleady refer to the data_pages in sys.allocation_units. You can see this from CREATE TABLE T1(C INT);CREATE TABLE T2(C INT);UPDATE STATISTICS T1 WITH PAGECOUNT = 1;UPDATE STATISTICS T2 WITH PAGECOUNT = 100 then comparing the estimated costs SELECT * FROM T1;SELECT * FROM T2; –  Martin Smith Jun 19 '13 at 8:05
    
Please re-read the first sentence in my answer. Comparing costs directly is meaningless. For the performance difference between the OP's queries, a better estimate can be derived empirically by calculating the reduction in the size of the indexes (and therefore the number of IO's), not by the costs from the optimizer. –  StrayCatDBA Jun 19 '13 at 13:28
1  
Generally speaking it is yes but in this case the reason why the query optimiser costs the clustered index as more than the non clustered index (the subject of this question) is precisely because of the different page counts. –  Martin Smith Jun 19 '13 at 13:31
1  
According to http://www.qdpma.com/ppt/CostFormulas2.ppt‎ The formula used to cost an Index Scan or Index Seek without lookup is (version dependant) IO (0.003125 + 0.00074074 per page) and CPU (0.0001581 + 0.0000011 per row). The fixed costs and rows are equal for CI and NCI so the only variable is pages. –  Martin Smith Jun 19 '13 at 13:45
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.