首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

分区后性能下降?解决思路

2013-12-21 
分区后性能下降?1.sql server20052.有一个表600w+的数据,分区前查询一次约15s,分区后查询一次用时35s3.分

分区后性能下降?
1.sql server2005
2.有一个表600w+的数据,分区前查询一次约15s,分区后查询一次用时35s
3.分区是按天分区的(每天能产生给50w条数据)
4.服务器是ibm3850,raid5,本来是想5个分区各放一个文件组的,可是跨文件组实现slide window没实现,所以就把这个表单独放到一个分区中。
PS:有人说做了raid后,没有必要分多个文件组


--新建聚集索引
CREATE CLUSTERED INDEX [GPSTimeIndex] ON [dbo].[GPSDATA] 
(
GPSTime 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) ON [PRIMARY]
--gps时间,车牌索引
CREATE NONCLUSTERED INDEX [GpsTimeVeIDIndex] ON [dbo].[GPSDATA] 
(
[GPSTime] ASC,
[VeID] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

-- Create a new file group
ALTER DATABASE ttas ADD FILEGROUP fg1
go

-- Add a file to the file group, we can now use the file group to store data
ALTER DATABASE ttas ADD FILE (NAME = N'TTAS1',FILENAME = 'F:\Database\TTAS_Gps.ndf',SIZE = 3072KB ,MAXSIZE = UNLIMITED,FILEGROWTH = 10%) TO FILEGROUP fg1
go
---------------------------------------------------------------------------------------------------------------
--GpsData分区
---------------------------------------------------------------------------------------------------------------
BEGIN TRANSACTION
--分区函数
CREATE PARTITION FUNCTION [Fun_GpsData](datetime) AS RANGE LEFT FOR VALUES (N'2013-09-24T00:00:00', N'2013-09-25T00:00:00', N'2013-09-26T00:00:00', N'2013-09-27T00:00:00', N'2013-09-28T00:00:00', N'2013-09-29T00:00:00', N'2013-09-30T00:00:00', N'2013-10-01T00:00:00', N'2013-10-02T00:00:00', N'2013-10-03T00:00:00', N'2013-10-04T00:00:00', N'2013-10-05T00:00:00', N'2013-10-06T00:00:00', N'2013-10-07T00:00:00', N'2013-10-08T00:00:00', N'2013-10-09T00:00:00', N'2013-10-10T00:00:00', N'2013-10-11T00:00:00', N'2013-10-12T00:00:00', N'2013-10-13T00:00:00', N'2013-10-14T00:00:00', N'2013-10-15T00:00:00', N'2013-10-16T00:00:00', N'2013-10-17T00:00:00', N'2013-10-18T00:00:00', N'2013-10-19T00:00:00', N'2013-10-20T00:00:00', N'2013-10-21T00:00:00', N'2013-10-22T00:00:00', N'2013-10-23T00:00:00', N'2013-10-24T00:00:00', N'2013-10-25T00:00:00', N'2013-10-26T00:00:00', N'2013-10-27T00:00:00', N'2013-10-28T00:00:00', N'2013-10-29T00:00:00', N'2013-10-30T00:00:00', N'2013-10-31T00:00:00', N'2013-11-01T00:00:00', N'2013-11-02T00:00:00', N'2013-11-03T00:00:00', N'2013-11-04T00:00:00', N'2013-11-05T00:00:00', N'2013-11-06T00:00:00', N'2013-11-07T00:00:00', N'2013-11-08T00:00:00', N'2013-11-09T00:00:00', N'2013-11-10T00:00:00', N'2013-11-11T00:00:00', N'2013-11-12T00:00:00', N'2013-11-13T00:00:00', N'2013-11-14T00:00:00', N'2013-11-15T00:00:00', N'2013-11-16T00:00:00', N'2013-11-17T00:00:00', N'2013-11-18T00:00:00', N'2013-11-19T00:00:00', N'2013-11-20T00:00:00', N'2013-11-21T00:00:00', N'2013-11-22T00:00:00', N'2013-11-23T00:00:00', N'2013-11-24T00:00:00', N'2013-11-25T00:00:00', N'2013-11-26T00:00:00', N'2013-11-27T00:00:00', N'2013-11-28T00:00:00', N'2013-11-29T00:00:00')

--分区方案
CREATE PARTITION SCHEME [Plan_GpsData] AS PARTITION [Fun_GpsData] TO (
[fg1], [fg1], [fg1], [fg1], [fg1],
[fg1], [fg1], [fg1], [fg1], [fg1],
[fg1], [fg1], [fg1], [fg1], [fg1],
[fg1], [fg1], [fg1], [fg1], [fg1],
[fg1], [fg1], [fg1], [fg1], [fg1],
[fg1], [fg1], [fg1], [fg1], [fg1],
[fg1], [fg1], [fg1], [fg1], [fg1],
[fg1], [fg1], [fg1], [fg1], [fg1],
[fg1], [fg1], [fg1], [fg1], [fg1],
[fg1], [fg1], [fg1], [fg1], [fg1],
[fg1], [fg1], [fg1], [fg1], [fg1],
[fg1], [fg1], [fg1], [fg1], [fg1],
[fg1], [fg1], [fg1], [fg1], [fg1],
[fg1], [fg1], [fg1], [fg1], [fg1],
[fg1], [fg1], [fg1], [fg1], [fg1],
[fg1], [fg1], [fg1], [fg1], [fg1],
[fg1], [fg1], [fg1], [fg1], [fg1],
[fg1], [fg1])

--索引
CREATE CLUSTERED INDEX [GPSTimeIndex] ON [dbo].[GPSDATA] 
(
[GPSTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [Plan_GpsData]([GPSTime])
CREATE NONCLUSTERED INDEX [GpsTimeVeIDIndex] ON [dbo].[GPSDATA] 
(
[GPSTime] ASC,
[VeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [Plan_GpsData]([GPSTime])



COMMIT TRANSACTION


[解决办法]
不管语句是怎么写的,最好是这种样式,这样才能使用上分区的优点:

select *
from [GPSDATA] 
where [GPSTime] ='xxxx-xx-xx'
[解决办法]
1、如果你的查询实际上跨了很多区,分区的效果不明显。
2、分区是否比较平衡,特别是分区索引,如果某个区间数据很多,某个区间数据很小,就会失衡。
3、看执行计划

热点排行