About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Monday, February 20, 2017

Heap Scan (Table Scan) Vs Clustered Index Scan

Let’s try out which scan would be more effective with below scenario
   1) When selecting all columns without filter and order by
   2)  When selecting all columns with order by.

Before that let’s understand what HEAP and CI are

Heap: Heap is table without Cluster Index, it has one row in sys.partition with index=0, by default a heap has a single partition. SQL Server uses IAM (Internal Allocation Map) to move through the leaf. The data pages and the rows within them are not arranged in any order and are not linked. The information with data page connection are recorded in IAM pages.

Clustered Index:  Clustered Index are organized in B-Tree structure with top most node called Root and bottom most are leaf nodes, in between top and bottom are intermediates nodes. The Leaf node consists of Index Key column value and reference to data page of underlying table. The pages in each level are linked in doubly linked list pattern. The pages in the data chain and the rows in them are ordered on the value of the clustered index key. All inserts are made at the point where the key value in the inserted row fits in the ordering sequence among existing rows.

Now let’s create a table and insert around 4 Million records in it.

CREATE TABLE HeapVsClusteredScanTable
(
        ColNum BIGINT NOT NULL,
        ColBigNum BIGINT NOT NULL,
        CharacterColumn CHAR(50)
)
GO
INSERT INTO HeapVsClusteredScanTable
(
        ColNum, ColBigNum, CharacterColumn
)
SELECT
        ColNum,
        ColNum + 5000000,
        LEFT(REPLICATE((CAST(ColNum as VARCHAR(50))),50),50)
FROM
(
        SELECT
                ColNum = row_number() over(order by newid() asc)
        FROM master..spt_values a
        CROSS APPLY master..spt_values b
        WHERE a.type = 'P' AND a.number <= 2000 AND a.number > 0 AND
        b.type = 'P' AND b.number <= 2000 AND b.number > 0
) mydata

We have not created any index on this table, so it is a heap. Lets query index stat. Below it shows total pagecount  37384 and there is only one page level.

select page_count, index_depth, page_level = index_level, page_count, record_count
from sys.dm_db_index_physical_stats(db_id(),object_id('HeapVsClusteredScanTable'),null,null,'DETAILED');
GO
page_count           index_depth page_level page_count           record_count
-------------------- ----------- ---------- -------------------- --------------------
37384                1           0          37384                4000000

(1 row(s) affected)

Now lets query page allocation

select allocated_page_page_id, next_page_page_id, previous_page_page_id
from sys.dm_db_data base_page_allocations(db_id(),object_id('HeapVsClusteredScanTable'),null,null,'DETAILED')
where page_type_desc is not null and page_type_desc = 'DATA_PAGE'

allocated_page_page_id next_page_page_id previous_page_page_id
---------------------- ----------------- ---------------------
400                    NULL              NULL
401                    NULL              NULL
402                    NULL              NULL
403                    NULL              NULL
404                    NULL              NULL
405                    NULL              NULL
406                    NULL              NULL

Above result tells in case of Heap there no information stored for next and previous page.

Now lets see the IO measure, CPU time and Elapsed time when querying Heap with and without ORDER BY

Without Order By

SET STATISTICS IO on
set statistics time on
go
select * from HeapVsClusteredScanTable
(4000000 row(s) affected)
Table 'HeapVsClusteredScanTable'. Scan count 1, logical reads 37384, physical reads 0, read-ahead reads 4, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 6844 ms,  elapsed time = 93444 ms.

With Order By

SET STATISTICS IO on
set statistics time on
go
select * from HeapVsClusteredScanTable order  by ColNum
(4000000 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 36373, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'HeapVsClusteredScanTable'. Scan count 5, logical reads 37384, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 21109 ms,  elapsed time = 206015 ms.

Above result shows that with “Order By” Claus the execution time and read-ahead read increate significantly.

Introduce Clustered Index: Now Let’s create a clustered index on table

CREATE CLUSTERED INDEX cix_HeapVsClusteredScanTable
ON HeapVsClusteredScanTable(ColNum)
WITH(MAXDOP=1)
Lets query index physical stat

select page_count, index_depth, page_level = index_level, record_count
from sys.dm_db_index_physical_stats(db_id(),object_id('HeapVsClusteredScanTable'),null,null,'DETAILED');
GO
page_count           index_depth page_level record_count
-------------------- ----------- ---------- --------------------
37384                3           0          4000000
79                   3           1          37384
1                    3           2          79

(3 row(s) affected)

Above output shows that the index has 3 level, the leaf level consist of 37384 pages whereas intermediate level consist of 79 pages and root level consist of 1 page.

Lets now query page allocation dmv

select allocated_page_page_id, next_page_page_id, previous_page_page_id, page_level, page_type_desc
from sys.dm_db_database_page_allocations(db_id(),object_id('HeapVsClusteredScanTable'),null,null,'DETAILED')
where page_type_desc is not null and page_type_desc IN( 'DATA_PAGE', 'INDEX_PAGE')
ORDER BY CASE WHEN page_type_desc = 'INDEX_PAGE' THEN 0 ELSE allocated_page_page_id END ASC

allocated_page_page_id next_page_page_id previous_page_page_id page_level page_type_desc
---------------------- ----------------- --------------------- ---------- ---------------
61128                  61129             45911                 1          INDEX_PAGE
61129                  61130             61128                 1          INDEX_PAGE
61130                  61131             61129                 1          INDEX_PAGE
61131                  61132             61130                 1          INDEX_PAGE
61132                  61133             61131                 1          INDEX_PAGE
61133                  61134             61132                 1          INDEX_PAGE

Here the above output shows that each page in clustered index has pointer to previous and next page. (Doubly linked list pattern)

Now lets see the IO measure, CPU time and Elapsed time when querying Clustered Indexed table with and without ORDER BY

Without Order By

SET STATISTICS IO on
set statistics time on
go
select * from HeapVsClusteredScanTable

(4000000 row(s) affected)

Table 'NumbersTable'. Scan count 1, logical reads 37465, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 6672 ms,  elapsed time = 82045 ms.

With Order By

SET STATISTICS IO on
set statistics time on
go
select * from HeapVsClusteredScanTable order by ColNum

(4000000 row(s) affected)

Table 'NumbersTable'. Scan count 1, logical reads 37465, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 SQL Server Execution Times:
   CPU time = 6657 ms,  elapsed time = 86031 ms.

Conclusion: Here we can see, with Cluster Index on table, the Order By query make a significant performance compare to Heap table with order by Clause. However general select statement without order by doesn’t make big difference in term of elapsed and cpu time with Clustered table.

Further we can conclude that you can keep your table in heap if it is very.


 Enjy coding…SQL J


Post Reference: Vikram Aristocratic Elfin Share

Tuesday, November 29, 2016

Max Key length warning in SQL Server 2016 for NC Index

There are certain times when your index gets created with warning message

Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'test_index' has maximum length of 1705 bytes. For some combination of large values, the insert/update operation will fail.

Remember SQL Server 2016 increase maximum key size for Non Cluster Index from 900 byte to 1700 byte. That seems interesting correct!  You can now have more key column in your NC index Wow!!

Well it is not so wow! The larger index comes with lot of cons, which you need to consider before designing larger NC index, I will write a separate note on this.

Let reciprocate the above error to understand more on it.

CREATE SCHEMA indexDemo GO

CREATE TABLE indexDemo.IndexByteSQL2016 ( MyCol1 varchar(1705) );

Here we created table with a single column of length 1705, just do remember SQL Server 2016 allow only 1700 byte key column length.  


Now lets create NC index
CREATE INDEX test_index ON indexDemo.IndexByteSQL2016 (MyCol1);

Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'test_index' has maximum length of 1705 bytes. For some combination of large values, the insert/update operation will fail.

NC index created with warning, this warning we cannot ignore since it will affect your insertion. Let try to fire some insert statement to test this warning

INSERT INTO indexDemo.IndexByteSQL2016
SELECT cast(replicate('a', 700) AS varchar(1000)); -- Success
(1 row(s) affected)

INSERT INTO indexDemo.IndexByteSQL2016
SELECT cast(replicate('b', 901) AS varchar(1000)); -- Success
(1 row(s) affected)

INSERT INTO indexDemo.IndexByteSQL2016
SELECT cast(replicate('c', 1703) AS varchar(2000)); -- Fail
Msg 1946, Level 16, State 3, Line 20
Operation failed. The index entry of length 1703 bytes for the index 'test_index' exceeds the maximum length of 1700 bytes for nonclustered indexes.


The above insertion fails, why? Though you are trying to insert record of permissible length defined by your datatype i.e. varchar(1705) but this column is participating in NC index key creation and in SQL Server 2016 the permissible length of NC IndexKey columns is 1700.

So this particular value cast(replicate('c', 1703) AS varchar(2000) won’t get space in index creation thus insertion fails


Enjy coding…SQL J

Post Reference: Vikram Aristocratic Elfin Shar