--测试环境
--INTEL XEON 2.0G(8核)
--7200转 160G 单磁盘
--4G 内存
--SQL SERVER2008 企业中文版SP2 (测试时无其他程序运行)
--创建新文件组
--ALTER DATABASE TEST REMOVE FILEGROUP [TEST_HIS_PF1]
ALTER DATABASE TEST ADD FILEGROUP [TEST_HIS_PF1]
GO
--创建分区文件
--ALTER DATABASE TEST REMOVE FILE TEST_HIS_PF3
ALTER DATABASE TEST
ADD FILE
(NAME = N'TEST_HIS_PF1',FILENAME = N'D:\LLH_TEST\TEST_DATA\TEST_HIS_PF1.NDF',SIZE = 5MB,MAXSIZE = UNLIMITED,FILEGROWTH = 5%)
TO FILEGROUP [TEST_HIS_PF1]
GO
--创建分区函数
--DROP PARTITION FUNCTION TEST_PF_TEST
CREATE PARTITION FUNCTION TEST_PF_TEST(VARCHAR(8))
AS RANGE RIGHT FOR VALUES
('20100701','20100801','20100901','20101001','20101101','20101201','20110101','20110201')
GO
--drop PARTITION SCHEME TEST_PS_T_TEST_TMP
CREATE PARTITION SCHEME TEST_PS_T_TEST_TMP
AS PARTITION TEST_PF_TEST TO (TEST_HIS_PF1,TEST_HIS_PF1,TEST_HIS_PF1,TEST_HIS_PF1,TEST_HIS_PF1,TEST_HIS_PF1,TEST_HIS_PF1,TEST_HIS_PF1,TEST_HIS_PF1)
--创建分区表(分区表和源表表结构一致)
CREATE TABLE [dbo].[T_TEST_TMP](
[OCCUR_DATE] [varchar](8) NOT NULL,
[SERIAL_NO] [varchar](12) NOT NULL,
[ORDER_NO] [varchar](20) NOT NULL,
[ORDER_TYPE] [varchar](1) NOT NULL,
[CUST_CODE] [varchar](10) NOT NULL,
[BRANCH_CODE] [varchar](3) NOT NULL,
[CURRENCY_TYPE] [varchar](2) NOT NULL,
[INTEREST_DATE] [varchar](8) NOT NULL,
[DATE] [varchar](8) NOT NULL,
[INTEREST] [numeric](19, 4) NOT NULL,
[FIX_BORROW] [numeric](19, 4) NOT NULL,
[TAKEUP_BORROW] [numeric](19, 4) NOT NULL,
[BEF_RTN_COMMISSION] [numeric](19, 4) NOT NULL,
[PUNISH] [numeric](19, 4) NOT NULL,
[BAD_PUNISH] [numeric](19, 4) NOT NULL,
[INTEREST_SUM] [numeric](19, 4) NOT NULL,
[FIX_BORROW_SUM] [numeric](19, 4) NOT NULL,
[TAKEUP_BORROW_SUM] [numeric](19, 4) NOT NULL,
[BEF_RTN_COMMISSION_SUM] [numeric](19, 4) NOT NULL,
[PUNISH_SUM] [numeric](19, 4) NOT NULL,
[BAD_PUNISH_SUM] [numeric](19, 4) NOT NULL,
CONSTRAINT [PK_T_TEST_TMP] PRIMARY KEY CLUSTERED
(
[DATE] ASC,[CUST_CODE] ASC,[OCCUR_DATE] ASC,[SERIAL_NO] ASC,[ORDER_NO] ASC,[CURRENCY_TYPE] ASC
)
) ON TEST_PS_T_TEST_TMP(DATE)
GO
--测试时T_INTR_PRCES_HIS_TMP_IDX1和T_INTR_PRCES_HIS_TMP_IDX2只存在一个
CREATE INDEX T_INTR_PRCES_HIS_TMP_IDX1 ON T_TEST_TMP(DATE) --全表索引
CREATE INDEX T_INTR_PRCES_HIS_TMP_IDX2 ON T_TEST_TMP(DATE) ON TEST_PS_T_TEST_TMP(DATE) --分区索引
--T_TEST_TMP 存在114W条数据
--测试步骤如下(每次运行脚本前清除缓存和执行计划)
--清除sql server缓存和执行计划
--DBCC DROPCLEANBUFFERS
--DBCC FREEPROCCACHE
--普通表
--T_TEST 主键(DATE, CUST_CODE, OCCUR_DATE, SERIAL_NO, ORDER_NO, CURRENCY_TYPE) 全表索引1 DATE
--扫描计数 1，逻辑读取 3467 次，物理读取 41 次，预读 3449 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。占用时间 = 648 毫秒。
SELECT COUNT(1) FROM T_TEST WHERE DATE>='20101201' AND DATE<='20101230'
--分区表,使用全表索引
--T_TEST_TMP 主键(DATE, CUST_CODE, OCCUR_DATE, SERIAL_NO, ORDER_NO, CURRENCY_TYPE) 全表索引 (DATE)
--扫描计数 9，逻辑读取 3525 次，物理读取 96 次，预读 3548 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 占用时间 = 1959 毫秒。
SELECT COUNT(1) FROM T_TEST_TMP WHERE DATE>='20101201' AND DATE<='20101230'
--分区表,使用分区索引
--T_TEST_TMP 主键(DATE, CUST_CODE, OCCUR_DATE, SERIAL_NO, ORDER_NO, CURRENCY_TYPE) 分区索引 (DATE)
--扫描计数 9，逻辑读取 3525 次，物理读取 107 次，预读 3545 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。占用时间 = 1876 毫秒。
--SELECT COUNT(1) FROM T_TEST_TMP WHERE DATE>='20101201' AND DATE<='20101230'
上述测试现象
1 三个查询执行计划中都用到了非聚集索引DATE
2 测试分区表分区内查询不管采用分区索引还是全表索引,在单磁盘条件下效率比普通表慢大约3倍
疑惑
1 是否我测试过程有问题或者有遗漏
2 如果没有问题,分区表在多CPU和单磁盘的情况下效率下降如此之多,不可思议
麻烦请各位高手帮忙看看问题出在哪里?
谢谢指教,剩余分数不多,全部献上....
------解决方案--------------------
测好了，你的查询条件需要在一个分区里，你把显示执行计划打开，能看到第二个查询的聚集索引查询里的已分区是true，分区数是1，第二个查询的逻辑读取比第二个的少。