关于SQLServer2005表分区的讨论

发布时间：2020-12-12 15:17:29 所属栏目：MsSql教程来源：网络整理

导读：xpcc ?发表于：2010-07-13 09:50:59 我用sqlserver2005的分区表功能，建了一个大数据量的分区表，过程如下：建文件组 ALTER DATABASE [aqs2211] ADD FILEGROUP [fg2008] ALTER DATABASE [aqs2211] ADD FILEGROUP [fg2009] ALTER DATABASE [aqs2211] ADD FI

我用sqlserver2005的分区表功能，建了一个大数据量的分区表，
过程如下：
建文件组
ALTER DATABASE [aqs2211] ADD FILEGROUP [fg2008]
ALTER DATABASE [aqs2211] ADD FILEGROUP [fg2009]
ALTER DATABASE [aqs2211] ADD FILEGROUP [fg2010]
ALTER DATABASE [aqs2211] ADD FILEGROUP [fg2011]
ALTER DATABASE [aqs2211] ADD FILEGROUP [fg2012]

ALTER DATABASE [aqs2211] ADD FILE
(NAME = N'aqs2211_Data2008',
FILENAME = N'G:/xp/data/aqs2211_Data2008.ndf',
SIZE = 2048KB,FILEGROWTH = 1024KB ) TO FILEGROUP [fg2008]

ALTER DATABASE [aqs2211] ADD FILE
(NAME = N'aqs2211_Data2009',
FILENAME = N'G:/xp/data/aqs2211_Data2009.ndf',FILEGROWTH = 1024KB ) TO FILEGROUP [fg2009]

ALTER DATABASE [aqs2211] ADD FILE
(NAME = N'aqs2211_Data2010',
FILENAME = N'G:/xp/data/aqs2211_Data2010.ndf',FILEGROWTH = 1024KB ) TO FILEGROUP [fg2010]

ALTER DATABASE [aqs2211] ADD FILE
(NAME = N'aqs2211_Data2011',
FILENAME = N'G:/xp/data/aqs2211_Data2011.ndf',FILEGROWTH = 1024KB ) TO FILEGROUP [fg2011]

ALTER DATABASE [aqs2211] ADD FILE
(NAME = N'aqs2211_Data2012',
FILENAME = N'G:/xp/data/aqs2211_Data2012.ndf',FILEGROWTH = 1024KB ) TO FILEGROUP [fg2012]

建分区函数：
CREATE PARTITION FUNCTION auditinfotb_pf ( datetime )
AS RANGE right?
FOR VALUES ('2008-01-01','2009-01-01','2010-01-01','2011-01-01')

建分区架构：
CREATE PARTITION FUNCTION auditinfotb_pf ( datetime )
AS RANGE right?
FOR VALUES ('2008-01-01','2011-01-01')

建分区表
CREATE TABLE auditinfotb (
? id char(20) NOT NULL,
? localCode varchar(6),
? appCode char(5) NOT NULL,
? userIP varchar(15) DEFAULT NULL,
? userRoleCode varchar(80) DEFAULT NULL,
? privilegeCode varchar(512) DEFAULT NULL,
? succeed char(1) DEFAULT NULL,
? errMsg varchar(256) DEFAULT NULL,
? isLocalUser char(1) DEFAULT NULL,
? accessTime datetime NOT NULL,
? userName varchar(128) NOT NULL,
? userIDN varchar(32) DEFAULT NULL,
? orgCode char(12) DEFAULT NULL,
? policeType char(2) DEFAULT NULL,
? dutyLevel char(4) DEFAULT NULL,
? station char(4) DEFAULT NULL,
? charge char(4) DEFAULT NULL,
? ministry char(3) DEFAULT NULL,
? certRole char(6) DEFAULT NULL,
? orgName varchar(256) DEFAULT NULL,
? appName varchar(256) DEFAULT NULL,
? appType char(4) DEFAULT NULL,
? provCode char(2) DEFAULT NULL,
? cityCode char(2) DEFAULT NULL,
? countyCode char(2) DEFAULT NULL,
? unit1Code char(2) DEFAULT NULL,
? unit2Code char(2) DEFAULT NULL,
? unit3Code char(2) DEFAULT NULL,
) ON auditinfotbPS(accesstime)
;

分区表建好后我用导入导出工具，把数据导入进来
然后创建索引
create index INDEX_auditinfo_orgcode on auditinfotb (accesstime,orgcode);

索引建好后，我查询某日的记录的时候
比如：
select *from auditinfotb?
where accesstime>'2009-06-01'
and accesstime<'2009-06-02'
就会全表扫描，
而另一个时间段比如
select *from auditinfotb?
where accesstime>'2009-09-06'
and accesstime<'2009-09-07' 就会利用上索引，
这是怎么回事呢？ ? ? ?

SQL77? 回复于：2010-07-13 09:57:49

索引建好后，我查询某日的记录的时候
比如：
select *from auditinfotb ?
where accesstime>'2009-06-01'
and accesstime<'2009-06-02'
就会全表扫描，
而另一个时间段比如
select *from auditinfotb ?
where accesstime>'2009-09-06'
and accesstime<'2009-09-07' 就会利用上索引，

要不要用索引会根据你的数据统计信息,

假如你的表里面数据1000条,
'2009-06-01'的数据才1条两条

'2009-09-06'是表里面有几百条,

第一个肯定会用索引查找,再有序局部扫描加书签
第二个不在选择点上,则选择表扫描会更快,书签查找是比较耗费I/O的

xpcc? 回复于：2010-07-13 11:17:27

补充：
比如
select * from auditinfotb2
where accesstime>'2009-05-01'
and accesstime<'2009-05-02'
能利用索引
这部分数据是44051条
而select * from auditinfotb2
where accesstime>'2009-06-01'
and accesstime<'2009-06-02'
就需要全部扫描，这部分数据是134716
这些数据相对于整个分区表8千多万数据来说，某一天的还是很少的，没道理利用不上索引的，而且
select count(*) from auditinfotb2
where accesstime>'2009-06-01'
and accesstime<'2009-06-02'的话，也能利用上索引，这又是怎么回事呢？

Haiwer? 回复于：2010-07-13 11:27:42

分区表建好后我用导入导出工具，把数据导入进来
然后创建索引
create index INDEX_auditinfo_orgcode on auditinfotb (accesstime,orgcode)
ON auditinfotbPS(accesstime)

你的表没有聚集索引，应该

create CLUSTERED index INDEX_auditinfo_orgcode on auditinfotb (accesstime,orgcode)
ON auditinfotbPS(accesstime)
;

xpcc?? 回复于：2010-07-13 11:41:08

to Haiwer(海阔天空)?
一定要建聚集索引吗？非聚集索引不行吗？
create index INDEX_auditinfo_orgcode on auditinfotb (accesstime,orgcode)
ON auditinfotbPS(accesstime);
和
create index INDEX_auditinfo_orgcode on auditinfotb (accesstime,orgcode);
应该是一样的吧？
?

SQL77?? 回复于：2010-07-14 09:10:17

引用 7 楼 xpcc 的回复:
补充：
比如
select * from auditinfotb2
where accesstime>'2009-05-01'
and accesstime<'2009-05-02'
能利用索引
这部分数据是44051条
而select * from auditinfotb2
where accesstime>'2009-06-01'
and accesstime<'2009-0……

而select * from auditinfotb2
where accesstime>'2009-06-01'
and accesstime<'2009-06-02'
就需要全部扫描，这部分数据是134716
这些数据相对于整个分区表8千多万数据来说，某一天的还是很少的，没道理利用不上索引的，而且
select count(*) from auditinfotb2
where accesstime>'2009-06-01'
and accesstime<'2009-06-02'的话，也能利用上索引，这又是怎么回事呢？

没道理利用不上索引???那你觉得为什么要用得上索引呢,??有时候是会估算失败,你可以尝试指定你的索引执行看一下效率.

select count(*) from auditinfotb2
where accesstime>'2009-06-01'
and accesstime<'2009-06-02'的话，也能利用上索引，这又是怎么回事呢？
这是COUNT(*) 不是*,
*号是所有数据,页COUNT(*)只是一个标量聚合,只取行数就行了,不用把数据给弄出来

xpcc? 回复于：2010-07-14 10:04:24

to SQL77：
我觉得'2009-06-01'的记录是134716，相对于所在分区的3千多万记录来说是很少的，
（几个分区的记录数如下：
3 34687516
1 14474092
4 7721438
2 25723830
）
在有索引的情况下是应该走索引的。我如果用with (index=...) 强制使用索引的话，就能利用上索引，返回结果就很快
?select top 20 * From auditinfotb?
where accesstime>'2009-06-01'
and accesstime<'2009-06-02'
------------耗时32秒
select top 20 * From auditinfotb with (index=index_auditinfo_orgcode)
where accesstime>'2009-06-01'
and accesstime<'2009-06-02'
-------------耗时0秒

Garnett_KG?? 回复于：2010-07-14 11:13:34

6/1=13471笔，如果使用索引seek,那么意味着至少有13471个随机IO(忽略索引层级不谈）.

通常随机IO的代价要远远高于顺序IO，因为数据分布的问题，随机IO需要移动更多次的磁臂才能读取到数据，而
顺序IO可以使用预读等机制提供更快的读取速度。

回到你的问题，在未分区前，8000W的数据做scan可代价要高于13471次随机IO，故会使用seek.

但分区后，变成只需要扫描一个表分区即可，所以，优化器会更倾向于选择分区表扫描。

再看你提的
?select top 20 * From auditinfotb?
where accesstime>'2009-06-01'
and accesstime<'2009-06-02'
------------耗时32秒
select top 20 * From auditinfotb with (index=index_auditinfo_orgcode)
where accesstime>'2009-06-01'
and accesstime<'2009-06-02'
------------- 耗时0秒

第2个查询耗时0秒的原因是，ＳＱＬＳＥＲＶＥＲ只使用了20次索引查找，所以耗时很短。这样没有什么可比性。

要了解优化器为什么没有采用索引查找，请打开IO/CPU读数，把TOP 20 去掉，然后再比较看看。

xpcc?? 回复于：2010-07-14 15:06:08

to Garnett_KG：
select * From auditinfotb?
where accesstime>'2009-06-01'
and accesstime<'2009-06-02'
----1分51秒
select * From auditinfotb with (index=index_auditinfo_orgcode)
where accesstime>'2009-06-01'
and accesstime<'2009-06-02'
------22秒
另外：打开“IO/CPU读数”是什么意思，是studio里面“查询”菜单中的“包含实际的执行计划”么？
另外：程序中是翻页查询的，所以语句中是有top关键字的，怎样才能让查询优化器判断应该走索引呢？

Garnett_KG?? 回复于：2010-07-14 15:34:23

引用 32 楼 xpcc 的回复:

to Garnett_KG：
select * From auditinfotb
where accesstime>'2009-06-01'
and accesstime<'2009-06-02'
----1分51秒
select * From auditinfotb with (index=index_auditinfo_orgcode)
where accesstime>……

你可以打开profiler看看这两条语句的reads/cpu各是多少。
也可以打开以下开关，看执行的效率
set statistics io on
set statistics time on

xpcc?? 回复于：2010-07-14 15:38:02

补充：
select * From auditinfotb?
where accesstime>'2009-06-01 00:00:00.000'
and accesstime<'2009-06-01 23:06:43.000'
---13秒--133232行--能利用索引

select * From auditinfotb?
where accesstime>'2009-06-01 00:00:00.000'
and accesstime<'2009-06-01 23:06:44.000'
---1分53秒--133234行--全表扫描

优化器判断到了到达一定量就要走全表扫描？可是这133234条是临界点？
而
select * From auditinfotb?
where accesstime>'2009-06-02 00:00:00.000'
and accesstime<'2009-06-02 18:24:44.000'
--10秒--119654行--索引扫描
select * From auditinfotb?
where accesstime>'2009-06-02 00:00:00.000'
and accesstime<'2009-06-02 18:24:45.000'
--1分52秒--119656行--表扫描
临界点是变化的？

xpcc?? 回复于：2010-07-14 15:55:10

to Garnett_KG：
使用：
set statistics io on
set statistics time on
-----------1-----------
select * From auditinfotb?
where accesstime>'2009-06-01'
and accesstime<'2009-06-02'

--结果---(134716 行受影响)

表 'auditinfotb'。扫描计数 5，逻辑读取 985197 次，物理读取 0 次，预读 981352 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

-------------2---------------
select * From auditinfotb with (index=index_auditinfo_orgcode)
where accesstime>'2009-06-01'
and accesstime<'2009-06-02'

--结果---(134716 行受影响)

表 'auditinfotb'。扫描计数 5，逻辑读取 281459 次，物理读取 2 次，预读 34391 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

------疑问：-------
从数据上看是不是后者的开销小啊？

Garnett_KG?? 回复于：2010-07-14 16:08:19

引用 41 楼 xpcc 的回复:

to Garnett_KG：
使用：
set statistics io on
set statistics time on
-----------1-----------
select * From auditinfotb
where accesstime>'2009-06-01'
and accesstime<'2009-06-02'

--结果---(134716 ……

seems strange...

1)再加上一个开关
set statistics profile on
set statistics io on
set statistics time on
把CPU的耗用也贴出来。

2) 执行 UPDATE STATISTICS auditinfotb WITH FULLSCAN

然后再比较一次。

xpcc?? 回复于：2010-07-14 16:33:09

to Garnett_KG：

set statistics profile on
set statistics io on
set statistics time on

-------1----------
select * From auditinfotb?
where accesstime>'2009-06-01'
and accesstime<'2009-06-02'
----结果---
SQL Server 分析和编译时间:?
? CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

SQL Server 执行时间:
? CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
SQL Server 分析和编译时间:?
? CPU 时间 = 0 毫秒，占用时间 = 6 毫秒。
SQL Server 分析和编译时间:?
? CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(134716 行受影响)
表 'auditinfotb'。扫描计数 5，逻辑读取 985197 次，物理读取 0 次，预读 981389 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(3 行受影响)

(1 行受影响)

SQL Server 执行时间:
? CPU 时间 = 7906 毫秒，占用时间 = 112579 毫秒。
SQL Server 分析和编译时间:?
? CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

SQL Server 执行时间:
? CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

134716 1 SELECT * FROM [auditinfotb] WHERE [accesstime]>@1 AND [accesstime
134716 1 |--Parallelism(Gather Streams) 1 2 1 Parallelism Gather Streams
134716 4 |--Table Scan(OBJECT:([aqs2211].[dbo].[auditinfotb]),WHERE:(

-------------2-------------
select * From auditinfotb with (index=index_auditinfo_orgcode)
where accesstime>'2009-06-01'
and accesstime<'2009-06-02'
--结果-----
SQL Server 分析和编译时间:?
? CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

SQL Server 执行时间:
? CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
SQL Server 分析和编译时间:?
? CPU 时间 = 0 毫秒，占用时间 = 222 毫秒。

(134716 行受影响)
表 'auditinfotb'。扫描计数 5，逻辑读取 281579 次，物理读取 2 次，预读 146308 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(6 行受影响)

(1 行受影响)

SQL Server 执行时间:
? CPU 时间 = 3876 毫秒，占用时间 = 17893 毫秒。
SQL Server 分析和编译时间:?
? CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

SQL Server 执行时间:
? CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

134716 1 select * From auditinfotb with (index=index_auditinfo_orgcode) ?
134716 1 |--Parallelism(Gather Streams) 1 2 1 Parallelism Gather Streams
134716 4 |--Nested Loops(Inner Join,OUTER REFERENCES:([PtnIds1015],
0 0 |--Compute Scalar(DEFINE:([PtnIds1015]=RangePartitionNew(
134716 4 | |--Index Scan(OBJECT:([aqs2211].[dbo].[auditinfotb].
134716 134716 |--RID Lookup(OBJECT:([aqs2211].[dbo].[auditinfotb]),

xpcc?? 回复于：2010-07-14 16:36:28

to Garnett_KG：
关于“ UPDATE STATISTICS auditinfotb WITH FULLSCAN”
我导完数据，就建的索引，之后没有数据的插入和更改，索引统计数据应该是没变化的。
而且以前也执行过这个命令，之后还是效果一样（没走索引）。

Garnett_KG?? 回复于：2010-07-14 16:59:05

DBCC SHOW_STATISTICS (auditinfotb,index_auditinfo_orgcode);
这个统计结果是什么？

xpcc?? 回复于：2010-07-14 16:59:49

只把stmttext粘来
---------------1-----------------
SELECT * FROM [auditinfotb] WHERE [accesstime]>@1 AND [accesstime]<@2
? |--Parallelism(Gather Streams)
? |--Table Scan(OBJECT:([aqs2211].[dbo].[auditinfotb]),WHERE:([aqs2211].[dbo].[auditinfotb].[accessTime]>'2009-06-01 00:00:00.000' AND [aqs2211].[dbo].[auditinfotb].[accessTime]<'2009-06-02 00:00:00.000') PARTITION ID:((3))) ?
--------------2-----------------
select * From auditinfotb with (index=index_auditinfo_orgcode) where accesstime>'2009-06-01' and accesstime<'2009-06-02'
? |--Parallelism(Gather Streams)
? |--Nested Loops(Inner Join,[Bmk1000],[Expr1020]) PARTITION ID:([PtnIds1015]) WITH UNORDERED PREFETCH)
? |--Compute Scalar(DEFINE:([PtnIds1015]=RangePartitionNew([aqs2211].[dbo].[auditinfotb].[accessTime],(1),'2008-01-01 00:00:00.000','2009-01-01 00:00:00.000','2010-01-01 00:00:00.000','2011-01-01 00:00:00.000')))
? | |--Index Scan(OBJECT:([aqs2211].[dbo].[auditinfotb].[INDEX_auditinfo_orgcode]),WHERE:([aqs2211].[dbo].[auditinfotb].[accessTime]>'2009-06-01 00:00:00.000' AND [aqs2211].[dbo].[auditinfotb].[accessTime]<'2009-06-02 00:00:00.000') PARTITION ID:((3)))
? |--RID Lookup(OBJECT:([aqs2211].[dbo].[auditinfotb]),SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD PARTITION ID:([PtnIds1015]))

DBCC SHOW_STATISTICS (auditinfotb,index_auditinfo_orgcode);

INDEX_auditinfo_orgcode 07 2 2010 11:32AM 82492454 82492454 198 0.002288518 20 YES

6.014314E-06 12 orgCode
1.368666E-08 20 orgCode,accessTime

010000000000 0 3 0 1
110000276618 255614 4762 11167 22.89012
310114171600 320880 1869 18147 17.68226
320000100000 522600 3926 2866 182.3447
320100160400 505985 16483 707 715.679
320100381000 390815 41962 231 1691.84
320100461300 507212 74051 244 2078.738
320100462600 331489 10094 46 7206.283
320100520000 301837 13655 118 2557.941
320100762000 433356 5991 180 2407.533
320102080000 518240 9359 229 2263.057
320102300000 213875 147421 21 10184.52
320102620000 478132 82780 25 19125.28
320102740000 361109 56880 19 19005.74
320103080000 249553 63230 33 7562.212
320103520000 324562 49379 34 9545.941
320103600000 296676 186029 13 22821.23
320103700000 475025 129679 12 39585.42
320104560000 491540 124924 78 6301.795
320105240000 503327 107950 60 8388.783
320105580000 363926 110717 15 24261.73
320106260000 463210 111286 74 6259.595
320106540000 313236 51021 16 19577.25
320106660000 401062 10318 13 30850.92
320107080000 481115 8329 43 11188.72
320107600000 341956 123302 21 16283.62
320111300000 369669 130337 86 4298.477
320111520000 313831 137079 56 5604.125
320111680000 290240 104017 31 9362.581
320112240000 475191 34669 77 6171.312
320112600000 257689 14921 19 13562.58
320113240000 332513 53437 57 5833.562
320113560000 239006 55310 19 12579.26
320113720000 260995 25365 12 21749.58
320114520000 505232 27906 58 8710.896
320114690000 214364 18705 13 16489.54
320121320000 458462 28268 155 2957.819
320121520000 85442 115534 47 1817.915
320121740000 493361 14628 45 10963.58
320123300000 492209 78834 130 3786.223
320123370000 139361 137420 59 2362.051
320123540000 195398 497 20 9769.9
320123780000 374412 20512 39 9600.308
320124520000 383772 153653 134 2863.97
320124680000 246211 55348 23 10704.83
320125520000 378455 141847 123 3076.87
320160560000 441975 26980 123 3593.293
320170520000 226497 48307 55 4118.127
320190160000 474816 171384 142 3343.775
320193000000 459875 1353 49 9385.204
320200360000 521570 9693 448 1164.219
320202000000 275677 54857 287 960.5471
320203280000 481869 11496 95 5072.305
320204230000 314390 95309 56 5614.107
320205050000 381834 10902 55 6942.437
320205420000 259426 40905 31 8368.581
320210090000 418227 23850 115 3636.757
320211220000 406139 34797 74 5488.365
320212260000 523775 43284 85 6162.059
320281210000 539392 14818 159 3392.403
320282130200 286549 24383 179 1600.832
320282820500 400572 13529 132 3034.636
320282880400 311413 14773 33 9436.758
320292030000 380761 15315 113 3369.566
320292740000 416577 5497 130 3204.438
320300400000 526286 21090 806 652.9603
320300480000 42381 7391 71 596.9155
320315100000 525913 23346 958 548.9697
320321000000 290257 60083 237 1224.713
320322610000 365274 96026 275 1328.269
320323600000 358157 9656 202 1773.054
320324000000 296401 45616 158 1875.956
320324760000 193279 26229 64 3019.984
320382000000 414355 169230 578 716.8771
320382900000 159566 18906 71 2247.408
320400000000 449328 5288 718 625.805
320402240000 395468 42010 196 2017.694
320402270000 202060 167169 8 25257.5
320404080000 383988 92617 38 10104.95
320405220000 454691 29014 126 3608.659
320411210000 260928 47289 47 5551.66
320478010000 389712 254 66 5904.727
320481150400 347585 21511 90 3862.056
320481320000 389508 27272 63 6182.667
320481590000 370067 4871 49 7552.388
320482240900 365277 10315 204 1790.573
320483140000 459434 6875 235 1955.038
320483370000 369233 94684 77 4795.234
320483480000 230073 14162 25 9202.92
320500110000 464296 631 760 610.9158
320502230000 371636 4859 442 840.8054
320503250000 190844 14571 136 1403.265
320504570000 350790 8839 179 1959.721
320506510000 465712 62233 241 1932.415
320507530000 219360 11753 73 3004.931
320581020000 434056 19275 212 2047.434
320581970000 175313 130291 201 872.204
320582960000 349442 4965 349 1001.266
320583240000 381769 3829 319 1196.768
320583610000 195835 24945 79 2478.924
320584510000 310529 46171 298 1042.044
320584920000 453845 38547 202 2246.757
320585530000 384986 211056 162 2376.457
320585550000 207334 510625 7 29619.14
320585580000 256355 73074 11 23305
320585600000 247056 126945 17 14532.71
320585610000 58101 347958 2 29050.5
320585620000 0 304221 0 1
320585640000 203049 100694 4 50762.25
320598560000 493643 36289 246 2006.679
320600240000 486868 9034 268 1816.672
320600410000 298705 9366 99 3017.222
320602250000 422448 13598 316 1336.861
320602630000 513394 24424 51 10066.55
320612520000 459303 33143 201 2285.09
320621261000 380221 146373 185 2055.249
320621630000 244048 11158 54 4519.407
320623260000 652404 5935 248 2630.661
320623710000 521918 9437 100 5219.18
320681440000 488197 13319 345 1415.064
320681530000 149908 31143 37 4051.568
320682330000 558138 58267 185 3016.962
320682371300 139175 299 32 4349.219
320682600000 478624 8144 97 4934.268
320682840000 243032 13025 40 6075.8
320683000000 501647 2535 226 2219.677
320683710000 242071 28187 87 2782.425
320684110000 399467 5375 166 2406.428
320684240800 225512 6770 21 10738.67
320684241000 333338 37003 112 2976.232
320684510000 345940 110936 45 7687.556
320684630000 215960 55891 20 10798
320684790000 441115 36004 32 13784.84
320700300200 416491 876 746 558.299
320721240000 249799 1366 504 495.6329
320800000000 467385 14158 901 518.7403
320800271100 491003 9629 223 2201.807
320802070000 276018 4286 75 3680.24
320802200000 251860 700 30 8395.333
320811120000 287830 27352 205 1404.049
320812160000 356344 2149 131 2720.183
320812460000 516744 34043 152 3399.632
320813131300 425398 18659 155 2744.503
320813410000 294155 6779 61 4822.213
320826160200 381243 3561 190 2006.542
320829180000 512621 62241 325 1577.295
320830010100 534073 22603 247 2162.239
320830190000 267209 16088 46 5808.891
320831000000 397321 7539 148 2684.601
320897060000 395786 35079 133 2975.834
320900320000 616271 5738 537 1147.618
320902550000 477734 2296 111 4303.91
320921240400 568524 14963 170 3344.259
320922000000 260925 513445 63 4141.667
320923250300 477073 8291 328 1454.491
320925010300 1019741 12663 383 2662.509
320925570000 346800 6013 165 2101.818
320928250400 472742 695921 268 1763.963
320928680000 393036 76971 37 10622.59
320981230000 259236 25854 56 4629.214
320981570000 351184 4534 104 3376.769
320981740000 256380 5984 35 7325.143
320982230300 503789 92105 135 3731.77
320982680000 522338 12442 67 7796.089
321000231200 558711 23935 453 1233.358
321002580000 523093 10646 209 2502.837
321003241200 373263 102 105 3554.886
321003690000 371061 14192 44 8433.204
321011510000 311014 30602 136 2286.868
321023530000 422989 33828 110 3845.354
321081510000 516706 29351 335 1542.406
321084250000 426250 180681 87 4899.425
321088230600 485729 82292 205 2369.41
321088241300 19087 140836 9 2120.778
321088710000 283482 5677 102 2779.235
321096000000 335535 18841 213 1575.282
321100550000 530958 2496 725 732.3558
321102140100 380628 33808 163 2335.141
321111340000 482043 26416 164 2939.287
321121760000 207801 5568 98 2120.418
321181160000 425874 16534 285 1494.295
321182360000 387460 19703 136 2848.971
321183540000 272936 13873 65 4199.016
321200240000 377555 87684 459 822.5599
321202000000 6901 3949 35 197.1714
321281890000 637068 1567 906 703.1656
321284550000 205810 549 237 868.3966
321302710000 516651 9 626 825.3211
321322330000 451577 50792 404 1117.765
321322660000 263564 23721 22 11980.18
321323240800 370614 38637 4804 77.14696
321323530000 255184 37201 43 5934.512
321398000000 292079 5551 109 2679.624
330185340000 438826 2 5592 78.47389
340822430000 320947 23 11557 27.77079
371329500000 208877 122 9340 22.3637
500112660000 402570 17 39671 10.14771
812200520000 283772 9 31405 9.035886

1、
? name update rows rows sampled steps density average key length string index
? INDEX_auditinfo_orgcode 07 2 2010 11:32AM 82492454 82492454 198 0.002288518 20

hudeini007?? 回复于：2010-07-14 19:04:10

SQL77?? 回复于：2010-07-14 20:41:06

引用 15 楼 xpcc 的回复:
to SQL77：
我觉得'2009-06-01'的记录是134716，相对于所在分区的3千多万记录来说是很少的，
（几个分区的记录数如下：
3 34687516
1 14474092
4 7721438
2 25723830
）
在有索引的情况下是应该走索引的。我如果用with (index=...) 强制使用索引的话，就能利用上索引，返回结果就很快
select top 2……

KG哥也讲得很明白了,
你只取了20笔,不是所有数据,所以只查找了20次就没有继续查找了,你返回所有列的数据,肯定还得其它的书签I/O,如果不是在选择点内,优化器会选择表扫描 ?

Garnett_KG? 回复于：2010-07-15 08:52:03

引用 57 楼 xpcc 的回复:

DBCC SHOW_STATISTICS (auditinfotb,index_auditinfo_orgcode);

INDEX_auditinfo_orgcode 07 2 2010 11:32AM 82492454 82492454 198 0.002288518 20 YES

6.014314E-06 12 orgCode
1.368666E-08 20 orgCode……

看起来是你的索引有问题，我看你楼顶的索引是这样建的
create index INDEX_auditinfo_orgcode on auditinfotb ( accesstime,orgcode);

但从dbcc show_statistics的结果来看，你的索引的键值顺序是 orgcode+accesstime

你最好再好好检查一下你的索引！！

xpcc?? 回复于：2010-07-15 09:32:52

to Garnett_KG:
不好意思，是我定楼的没写对，
我最开始是
create index INDEX_auditinfo_orgcode on auditinfotb (accesstime,orgcode);
发现索引利用不好，又删了反复试了几次，
最近这次的索引是
create index INDEX_auditinfo_orgcode on auditinfotb (orgcode);
我记得这样在分区表上建索引是默认的对齐索引，sqlserver自动在索引列里加上分区字段accesstime，
我以为
create index INDEX_auditinfo_orgcode on auditinfotb (accesstime,orgcode);
和
create index INDEX_auditinfo_orgcode on auditinfotb (orgcode);
效果是一样的，所以也在顶楼粘了最开始的语句。
而且我也建过下面这样的索引
create index INDEX_auditinfo_orgcode on auditinfotb (accesstime);
效果一样。
不过，昨晚我又用
create CLUSTERED index INDEX_auditinfo_2_orgcode2 on auditinfotb (accesstime)
ON auditinfotbPS2(accesstime);
这个语句建了聚集索引，这次没蓝屏。
我这回再查就能会聚集索引扫描了。
看来这种在分区表中按范围过滤记录是必须建聚集索引才能利用索引了？

Garnett_KG?? 回复于：2010-07-15 09:42:05

引用 85 楼 xpcc 的回复:

to Garnett_KG:
不好意思，是我定楼的没写对，
我最开始是
create index INDEX_auditinfo_orgcode on auditinfotb (accesstime,orgcode);
发现索引利用不好，又删了反复试了几次，
最近这次的索引是
create index INDEX_auditinfo_orgcode on auditinfotb (o……

create index INDEX_auditinfo_orgcode on auditinfotb (accesstime,orgcode);
和
create index INDEX_auditinfo_orgcode on auditinfotb (orgcode);
的效果肯定不一样啦！！

注意键值的顺序问题，orgcode在前的话 where accesstime>@1 and accesstime<@2 是无法使用索引的。

难怪看你的执行计划中即使你强制使用index也是走的scan.

xpcc?? 回复于：2010-07-15 09:45:57

如果分区表必须建一个聚集索引的话，那我还有个疑问
我这个大表在分区前建的是如下几个索引：
create index INDEX_auditinfo_orgcode on auditinfotb (accesstime,orgcode);
create index INDEX_auditinfo_username on auditinfotb (accesstime,username);
create index INDEX_auditinfo_useridn on auditinfotb (useridn,accesstime);
create index INDEX_auditinfo_userip on auditinfotb (userip,accesstime);
create index INDEX_auditinfo_appcode on auditinfotb (appcode,accesstime);
分别对应程序查询页面的5个条件，这样无论选什么条件都能保证利用上一个索引，能很快的进行分页查询
那么，现在我把表进行分区了，那必须建个聚集索引
create CLUSTERED index INDEX_auditinfo_time on auditinfotb (accesstime);
那我还有必要建上面那5个索引么？似乎在count(*)的时候，上面5个索引也能利用上，不过如果没有这
5个索引，只用这个聚集索引是否也慢不到哪去呢？

Garnett_KG?? 回复于：2010-07-15 09:51:14

你不要把分区表跟聚集索引搞混了。

这两个东西可以独立存在的，不是说你分区后就必须要建聚集索引。

你的最开始的问题在于分区后索引没有建正确(orgcode,accesstime)，所以没有用上索引。你后来在accesstime上建立聚集索引后，where acesstime>@1 and accesstime@<@2 自然就可以index seek

xpcc?? 回复于：2010-07-15 10:20:06

to Garnett_KG:
我最开始是create index INDEX_auditinfo_orgcode on auditinfotb (accesstime,orgcode);
后来也
create index INDEX_auditinfo_orgcode on auditinfotb (accesstime);
过，
效果都是有时能利用上索引，有时利用不上。
如果说现在索引顺序不对的话，那它在查找出的记录数少的使用应该也不走索引的呀，而现在的现象似乎是查出的结果集超过一定量就利用不上索引。

xpcc??? 回复于：2010-07-15 10:40:31

to Garnett_KG:
下面测试数据是我另一个环境上的，win7+sqlserver2008

------1----------------
select * From auditinfotb
where accessTime>'2009-06-01'
and accessTime<'2009-06-05'

查询结果返回：9分43秒 529809行

执行计划：
SELECT * FROM [auditinfotb] WHERE [accessTime]>@1 AND [accessTime]<@2
? |--Table Scan(OBJECT:([aqs2212].[dbo].[auditinfotb]),SEEK:([PtnId1001]=(3)),WHERE:([aqs2212].[dbo].[auditinfotb].[accessTime]>'2009-06-01 00:00:00.000' AND [aqs2212].[dbo].[auditinfotb].[accessTime]<'2009-06-05 00:00:00.000') ORDERED FORWARD)

io/cpu:
SQL Server 分析和编译时间:?
? CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
SQL Server 分析和编译时间:?
? CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(529809 行受影响)
表 'auditinfotb'。扫描计数 1，逻辑读取 966451 次，物理读取 27796 次，预读 944731 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(2 行受影响)

?SQL Server 执行时间:
? CPU 时间 = 13073 毫秒，占用时间 = 575729 毫秒。 ?

---------------2-------------------
select * From auditinfotb with (index=index_auditinfo_orgcode)
where accessTime>'2009-06-01'
and accessTime<'2009-06-05'

返回结果：37秒 529809行

执行计划：
select * From auditinfotb with (index=index_auditinfo_orgcode) where accessTime>'2009-06-01' and accessTime<'2009-06-05'
? |--Nested Loops(Inner Join,OUTER REFERENCES:([Bmk1000],[PtnId1001],[Expr1006]) OPTIMIZED WITH UNORDERED PREFETCH)
? |--Compute Scalar(DEFINE:([Expr1005]=BmkToPage([Bmk1000])))
? | |--Index Seek(OBJECT:([aqs2212].[dbo].[auditinfotb].[INDEX_auditinfo_orgcode]),SEEK:([PtnId1001]=(3) AND [aqs2212].[dbo].[auditinfotb].[accessTime] > '2009-06-01 00:00:00.000' AND [aqs2212].[dbo].[auditinfotb].[accessTime] < '2009-06-05 00:00:00.000') ORDERED FORWARD)
? |--RID Lookup(OBJECT:([aqs2212].[dbo].[auditinfotb]),SEEK:([PtnId1001]=[PtnId1001] AND [Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD) ?

io、cpu：
SQL Server 分析和编译时间:?
? CPU 时间 = 0 毫秒，占用时间 = 984 毫秒。

(529809 行受影响)
表 'auditinfotb'。扫描计数 1，逻辑读取 532052 次，物理读取 7395 次，预读 9079 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(5 行受影响)

?SQL Server 执行时间:
? CPU 时间 = 4992 毫秒，占用时间 = 36798 毫秒。

---------------其中索引------------
/****** Object: Index [INDEX_auditinfo_orgcode] Script Date: 07/15/2010 10:39:58 ******/
CREATE NONCLUSTERED INDEX [INDEX_auditinfo_orgcode] ON [dbo].[auditinfotb]?
(
[accessTime] ASC,
[orgCode] ASC
)WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,SORT_IN_TEMPDB = OFF,IGNORE_DUP_KEY = OFF,DROP_EXISTING = OFF,ONLINE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)
GO

Garnett_KG?? 回复于：2010-07-15 10:54:18

引用 90 楼 xpcc 的回复:

to Garnett_KG:
我最开始是create index INDEX_auditinfo_orgcode on auditinfotb (accesstime,orgcode);
后来也
create index INDEX_auditinfo_orgcode on auditinfotb (accesstime);
过，
效果都是有时能利用上索引，有时利用不上。
如果说现在……

请看回16#回复

另外，之所以在accesstime上建聚集索引之后就会一直能用的上seek,是因为聚集索引的叶子中本身就包含数据，不需要再查找键值。

你找找聚集索引、非聚集索引、索引覆盖这方面的资料看看，自己理解一下。
xpcc?? to Garnett_KG：你在16楼说“要了解优化器为什么没有采用索引查找，请打开IO/CPU读数，把TOP 20 去掉，然后再比较看看。” 那你看93楼的对比数据，似乎全表扫描的开销还是比索引扫描的开销大，优化器为何会选择表扫描呢？

（编辑：李大同）

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

xpcc?发表于：2010-07-13 09:50:59