sql-server – 堆上的非聚簇索引与聚簇索引的性能

This 2007 White Paper较量了组织为聚簇索引的表上的单个select / insert / delete / update和range select语句的机能与组织为具有与CI表沟通的键列上的非聚簇索引的堆的表的机能.

凡是,聚簇索引选项在测试中示意更好,由于只必要维护一个布局,由于不必要书签查找.

本文未涉及的一个也许风趣的案例是堆上的非聚簇索引与聚簇索引上的非聚簇索引之间的较量.在谁人例子中,我本来祈望堆乃至可以在NCI叶级别上执行得更好SQL Server有一个RID可以直接跟从而不必要遍历聚簇索引.

是否有人知道在这个规模举办了相同的正式测试,假如是的话,功效是什么？

办理要领

> 790万条代表余额信息的记录.
>一个身份字段,从1到790万
>一个数字字段,对约莫500k组中的记录举办分组.

第一个名为heap的表在字段组上得到了非聚积索引.第二个名为clust的表在名为key的次序字段上得到了聚簇索引,在字段组上得到了非聚积索引

测试在具有2个超线程内核,4Gb内存和64位窗口7的I5 M540处理赏罚器上运行.

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: I did a second more extensive benchmark by running the following .net code and logging Duration,CPU,Reads,Writes and RowCounts in Sql Server Profiler. (The CommandText used will be mentioned in the results.)

NOTE: CPU and Duration are expressed in milliseconds

• 1000 queries
• zero CPU queries are eliminated from the results
• 0 rows affected are eliminated from the 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 * from heap where common_key between @id and @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机能

为了搜查performanc数字,我在堆表上执行了以下一次查询,在clust表上执行了一次：

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

这个基准测试的功效是针对堆的：

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 * from heap where group between @id and @id+1000";

• 721 Rows have > 0 CPU and affect more than 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.

对付表格clust功效是：

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 * from clust where group between @id and @id+1000";

• 721 Rows have > 0 CPU and affect more than 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 WITH JOIN机能

cmd.CommandText =“select * from heap / clust h join keys k on h.group = k.group where h.group between @id and @id 1000”;

这个基准测试的功效是针对堆的：

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!