tag:blogger.com,1999:blog-596112675619409125.post7978018617552904308..comments2024-03-18T16:06:57.064-07:00Comments on SQL Server Techniques: How many Clustered Indexes you may have?Slava Muryginhttp://www.blogger.com/profile/11665340455730972723noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-596112675619409125.post-37820429745818560082015-10-17T19:27:32.474-07:002015-10-17T19:27:32.474-07:00Uwe, that was not cheating.
try to insert records ...Uwe, that was not cheating.<br />try to insert records one by one and results will be even worse:<br />INSERT INTO dbo.tbl_Test_Heap ( TextField )<br />SELECT REPLICATE('A',950) <br />GO 700<br />Because of those 4 PFS degrees of "filling" heaps have that problem.<br />You can try to avoid that inserting data by batches, but if you insert on one-by-one basis you hit that issue.Slava Muryginhttps://www.blogger.com/profile/11665340455730972723noreply@blogger.comtag:blogger.com,1999:blog-596112675619409125.post-25814338993612674672015-10-17T07:09:58.549-07:002015-10-17T07:09:58.549-07:00Hi Slava - your example is "cheating" a ...Hi Slava - your example is "cheating" a benefit for a clustered index because you are not exceeding the full available space on a data page (8.060 Bytes).<br />If I have 8,060 Bytes for each page available why do you add 7 records only to it per transaction?<br /><br />Replay your example as follows and compare it with the "benefits" of your clustered index result!<br /><br />-- Create your demo table<br />CREATE TABLE tbl_Test_Heap( TextField VARCHAR(1000) );<br />GO<br /><br />-- now i insert 8 records in ONE page<br /> ;WITH a1 AS (SELECT 1 AS a UNION ALL SELECT 1),<br /> a2 AS (SELECT a FROM a1 UNION ALL SELECT a FROM a1),<br /> a3 AS (SELECT a FROM a2 UNION ALL SELECT a FROM a2)<br />INSERT INTO dbo.tbl_Test_Heap ( TextField )<br />SELECT TOP 8 REPLICATE('A',950) FROM a3;<br />GO 87<br />GO<br /><br />-- and have to add 4 more records to get to 700 records<br />INSERT INTO dbo.tbl_Test_Heap (TextField)<br />VALUES<br />( REPLICATE('A', 950)),<br />( REPLICATE('A', 950)),<br />( REPLICATE('A', 950)),<br />( REPLICATE('A', 950))<br />;<br />GO<br /><br />-- now compare the result (89 pages!)<br />SELECT used_page_count, row_count<br />FROM sys.dm_db_partition_stats st WHERE OBJECT_NAME(OBJECT_ID) = 'tbl_Test_Heap';<br />GO<br /><br />A clustered index add new records by scanning the free space on the data page while a heap is only scanning the PFS which have only 4 degrees of "filling": 50%, 80%, 95% and 100%!<br /><br />Best from Germany, UweUwe Rickebhttps://www.blogger.com/profile/04147656208188819825noreply@blogger.comtag:blogger.com,1999:blog-596112675619409125.post-76498031882819297542015-09-21T11:04:28.567-07:002015-09-21T11:04:28.567-07:00It is not a question of 100 records, it is how man...It is not a question of 100 records, it is how many fits on one or two pages. If you have less than three pages of data you do not need clustered index unless you are in Azure, which requires it.Slava Muryginhttps://www.blogger.com/profile/11665340455730972723noreply@blogger.comtag:blogger.com,1999:blog-596112675619409125.post-35790275053091420972015-09-20T03:38:06.406-07:002015-09-20T03:38:06.406-07:00This is just brilliant!
Just wanted to add that, i...This is just brilliant!<br />Just wanted to add that, in some special cases the RID can be a lot cheaper than a clustered index; I would use heaps only when the row count is below 100, otherwise would just do as you showed in this clear example.<br /><br />I came across your blog redirected from a LinkedIn group and now i'm bookmarking it. ThanksJulianhttps://www.blogger.com/profile/14040650666222743218noreply@blogger.com