sql server - Performance of Non Clustered Indexes on Heaps vs Clustered Indexes -

this 2007 white paper compares performance individual select/insert/delete/update , range select statements on table organized clustered index vs on table organized heap non clustered index on same key columns ci table.

generally clustered index option performed better in tests there 1 structure maintain , because there no need bookmark lookups.

one potentially interesting case not covered paper have been comparison between non clustered index on heap vs non clustered index on clustered index. in instance have expected heap might perform better once @ nci leaf level sql server has rid follow directly rather needing traverse clustered index.

is aware of similar formal testing has been carried out in area , if results?

to check request created 2 tables following scheme:

• 7.9 million records representing balance information.
• an identity field counting 1 7.9 million
• a number field grouping records in 500k groups.

the first table called heap got non clustered index on field group. second table called clust got clustered index on sequential field called key , nonclustered index on field group

the tests run on i5 m540 processor 2 hyperthreaded cores, 4gb memory , 64-bit windows 7.

microsoft sql server 2008 r2 (rtm) - 10.50.1600.1 (x64)  apr  2 2010 15:48:46  developer edition (64-bit) on windows nt 6.1 <x64> (build 7601: service pack 1)   

update on 9 mar 2011: did second more extensive benchmark running following .net code , logging duration, cpu, reads, writes , rowcounts in sql server profiler. (the commandtext used mentioned in results.)

note: cpu , duration expressed in milliseconds

• 1000 queries
• zero cpu queries eliminated results
• 0 rows affected eliminated results

int[] idlist = new int[] { 6816588, 7086702, 6498815 ... }; // 1000 values here. using (var conn = new sqlconnection(@"data source=myserver;initial catalog=mydb;integrated security=sspi;"))             {                 conn.open();                 using (var cmd = new sqlcommand())                 {                     cmd.connection = conn;                     cmd.commandtype = commandtype.text;                     cmd.commandtext = "select * heap common_key between @id , @id+1000";                      cmd.parameters.add("@id", sqldbtype.int);                     cmd.prepare();                     foreach (int id in idlist)                     {                         cmd.parameters[0].value = id;                          using (var reader = cmd.executereader())                         {                             int count = 0;                             while (reader.read())                             {                                 count++;                             }                             console.writeline(string.format("key: {0} => {1} rows", id, count));                         }                     }                 }             } 

end of update on 9 mar 2011.

select performance

to check performanc numbers performed following queries once on heap table , once on clust table:

select * heap/clust group between 5678910 , 5679410 select * heap/clust group between 6234567 , 6234967 select * heap/clust group between 6455429 , 6455729 select * heap/clust group between 6655429 , 6655729 select * heap/clust group between 6955429 , 6955729 select * heap/clust group between 7195542 , 7155729 

---

the results of benchmark heap:

rows  reads cpu   elapsed  ----- ----- ----- -------- 1503  1510  31ms  309ms 401   405   15ms  283ms 2700  2709  0ms   472ms 0     3     0ms   30ms 2953  2962  32ms  257ms 0     0     0ms   0ms 

update on 9 mar 2011: cmd.commandtext = "select * heap group between @id , @id+1000";

• 721 rows have > 0 cpu , affect more 0 rows

counter   minimum    maximum average  weighted --------- ------- ---------- ------- --------- rowcounts    1001      69788    6368         -          cpu            15        374      37   0.00754 reads        1069      91459    7682   1.20155 writes          0          0       0   0.00000 duration   0.3716   282.4850 10.3672   0.00180 

end of update on 9 mar 2011.

---

for table clust results are:

rows  reads cpu   elapsed  ----- ----- ----- -------- 1503  4827  31ms  327ms 401   1241  0ms   242ms 2700  8372  0ms   410ms 0     3     0ms   0ms 2953  9060  47ms  213ms 0     0     0ms   0ms 

update on 9 mar 2011: cmd.commandtext = "select * clust group between @id , @id+1000";

• 721 rows have > 0 cpu , affect more 0 rows

counter   minimum    maximum average  weighted --------- ------- ---------- ------- --------- rowcounts    1001      69788    6056         - cpu            15        468      38   0.00782 reads        3194     227018   20457   3.37618 writes          0          0       0       0.0 duration   0.3949   159.6223 11.5699   0.00214 

end of update on 9 mar 2011.

---

select join performance

cmd.commandtext = "select * heap/clust h join keys k on h.group = k.group h.group between @id , @id+1000";

---

the results of benchmark heap:

873 rows have > 0 cpu , affect more 0 rows

counter   minimum    maximum average  weighted --------- ------- ---------- ------- --------- rowcounts    1009       4170    1683         - cpu            15         47      18   0.01175 reads        2145       5518    2867   1.79246 writes          0          0       0   0.00000 duration   0.8215   131.9583  1.9095   0.00123 

---

the results of benchmark clust:

865 rows have > 0 cpu , affect more 0 rows

counter   minimum    maximum average  weighted --------- ------- ---------- ------- --------- rowcounts    1000       4143    1685         - cpu            15         47      18   0.01193 reads        5320      18690    8237   4.97813 writes          0          0       0   0.00000 duration   0.9699    20.3217  1.7934   0.00109 

update performance

the second batch of queries update statements:

update heap/clust set amount = amount + 0 group between 5678910 , 5679410 update heap/clust set amount = amount + 0 group between 6234567 , 6234967 update heap/clust set amount = amount + 0 group between 6455429 , 6455729 update heap/clust set amount = amount + 0 group between 6655429 , 6655729 update heap/clust set amount = amount + 0 group between 6955429 , 6955729 update heap/clust set amount = amount + 0 group between 7195542 , 7155729 

---

the results of benchmark heap:

rows  reads cpu   elapsed  ----- ----- ----- --------  1503  3013  31ms  175ms 401   806   0ms   22ms 2700  5409  47ms  100ms 0     3     0ms   0ms 2953  5915  31ms  88ms 0     0     0ms   0ms 

update on 9 mar 2011: cmd.commandtext = "update heap set amount = amount + @id group between @id , @id+1000";

• 811 rows have > 0 cpu , affect more 0 rows

counter   minimum    maximum average  weighted --------- ------- ---------- ------- --------- rowcounts    1001      69788    5598       811          cpu            15        873      56   0.01199 reads        2080     167593   11809   2.11217 writes          0       1687     121   0.02170 duration   0.6705   514.5347 17.2041   0.00344 

end of update on 9 mar 2011.

---

the results of benchmark clust:

rows  reads cpu   elapsed  ----- ----- ----- --------  1503  9126  16ms  35ms 401   2444  0ms   4ms 2700  16385 31ms  54ms 0     3     0ms   0ms  2953  17919 31ms  35ms 0     0     0ms   0ms 

update on 9 mar 2011: cmd.commandtext = "update clust set amount = amount + @id group between @id , @id+1000";

• 853 rows have > 0 cpu , affect more 0 rows

counter   minimum    maximum average  weighted --------- ------- ---------- ------- --------- rowcounts    1001      69788    5420         - cpu            15        594      50   0.01073 reads        6226     432237   33597   6.20450 writes          0       1730     110   0.01971 duration   0.9134   193.7685  8.2919   0.00155 

end of update on 9 mar 2011.

---

delete benchmarks

the third batch of queries ran delete statements

delete heap/clust group between 5678910 , 5679410 delete heap/clust group between 6234567 , 6234967 delete heap/clust group between 6455429 , 6455729 delete heap/clust group between 6655429 , 6655729 delete heap/clust group between 6955429 , 6955729 delete heap/clust group between 7195542 , 7155729 

---

the result of benchmark heap:

rows  reads cpu   elapsed  ----- ----- ----- --------  1503  10630 62ms  179ms 401   2838  0ms   26ms 2700  19077 47ms  87ms 0     4     0ms   0ms 2953  20865 62ms  196ms 0     4     0ms   9ms 

update on 9 mar 2011: cmd.commandtext = "delete heap group between @id , @id+1000";

• 724 rows have > 0 cpu , affect more 0 rows

counter   minimum    maximum average  weighted --------- ------- ---------- ------- --------- rowcounts     192      69788    4781         - cpu            15        499      45   0.01247 reads         841     307958   20987   4.37880 writes          2       1819     127   0.02648 duration   0.3775  1534.3383 17.2412   0.00349 

end of update on 9 mar 2011.

---

the result of benchmark clust:

rows  reads cpu   elapsed  ----- ----- ----- --------  1503  9228  16ms  55ms 401   3681  0ms   50ms 2700  24644 46ms  79ms 0     3     0ms   0ms 2953  26955 47ms  92ms 0     3     0ms   0ms 

update on 9 mar 2011:

cmd.commandtext = "delete clust group between @id , @id+1000";

• 751 rows have > 0 cpu , affect more 0 rows

counter   minimum    maximum average  weighted --------- ------- ---------- ------- --------- rowcounts     144      69788    4648         - cpu            15        764      56   0.01538 reads         989     458467   30207   6.48490 writes          2       1830     127   0.02694 duration   0.2938  2512.1968 24.3714   0.00555 

end of update on 9 mar 2011.

---

insert benchmarks

the last part of benchmark execution of insert statements.

insert heap/clust (...) values (...), (...), (...), (...), (...), (...)

---

the result of benchmark heap:

rows  reads cpu   elapsed  ----- ----- ----- --------  6     38    0ms   31ms 

update on 9 mar 2011:

string str = @"insert heap (group, currency, year, period, domain_id, mtdamount, mtdamount, ytdamount, amount, ytd_restated, restated, auditdate, audituser)                     values";                      (int x = 0; x < 999; x++)                     {                         str += string.format(@"(@id + {0}, 'eur', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, 'test'),  ", x);                     }                     str += string.format(@"(@id, 'cad', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, 'test') ", 1000);                      cmd.commandtext = str; 

• 912 statements have > 0 cpu

counter   minimum    maximum average  weighted --------- ------- ---------- ------- --------- rowcounts    1000       1000    1000         - cpu            15       2138      25   0.02500 reads        5212       7069    6328   6.32837 writes         16         34      22   0.02222 duration   1.6336   293.2132  4.4009   0.00440 

end of update on 9 mar 2011.

---

the result of benchmark clust:

rows  reads cpu   elapsed  ----- ----- ----- --------  6     50    0ms   18ms 

update on 9 mar 2011:

string str = @"insert clust (group, currency, year, period, domain_id, mtdamount, mtdamount, ytdamount, amount, ytd_restated, restated, auditdate, audituser)                     values";                      (int x = 0; x < 999; x++)                     {                         str += string.format(@"(@id + {0}, 'eur', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, 'test'),  ", x);                     }                     str += string.format(@"(@id, 'cad', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, 'test') ", 1000);                      cmd.commandtext = str; 

• 946 statements have > 0 cpu

counter   minimum    maximum average  weighted --------- ------- ---------- ------- --------- rowcounts    1000       1000    1000         -       cpu            15       2403      21   0.02157 reads        6810       8997    8412   8.41223 writes         16         25      19   0.01942 duration   1.5375   268.2571  6.1463   0.00614 

end of update on 9 mar 2011.

---

conclusions

although there more logical reads going on when accessing table clustered & nonclustered index (while using nonclustered index) performance results are:

• select statements comparable
• update statements faster clustered index in place
• delete statements faster clustered index in place
• insert statements faster clustered index in place

of course benchmark limited on specific kind of table , limited set of queries, think based on information can start saying virtually better create clustered index on table.

update on 9 mar 2011:

as can see added results, conclusions on limited tests not correct in every case.

the results indicate statements benefit clustered index update statements. other statements 30% slower on table clustered index.

some additional charts plotted weighted duration per query heap vs clust.

as can see performance profile insert statements quite interesting. spikes caused few data points take lot longer complete.

end of update on 9 mar 2011.