欢迎来到Doc100.Net免费学习资源知识分享平台!
您的位置:首页 > 程序异常 >

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

更新时间: 2014-01-05 02:18:12 责任编辑: Author_N1

 

分区后性能下降?
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])
上一篇:上一篇
下一篇:下一篇

 

随机推荐程序问答结果

 

 

如对文章有任何疑问请提交到问题反馈,或者您对内容不满意,请您反馈给我们DOC100.NET论坛发贴求解。
DOC100.NET资源网,机器学习分类整理更新日期::2014-01-05 02:18:12
如需转载,请注明文章出处和来源网址:http://www.doc100.net/bugs/t/8239/
本文WWW.DOC100.NET DOC100.NET版权所有。