分区后性能下降?
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
