Tens implementation of SQL Server database table partitioning

Tens implementation of SQL Server database table partitioning 2010-09-10 13:37 anonymous databasefont Size:T | T

In general, under the Tens data pressure, partitioning is a better way to improve performance. This article describes the implementation of a SQL Server database table partition.

Ad:51cto Net + 12th salon: The beauty of big data-how to drive user experience with data

Recently using a cache of SQL Server, the volume of data 100w a day growth, while accepting customer inquiries, speed due to the increasing volume of data more and more slowly, here thanks to Killkill and invitations, recently read a set of books good, interested students can read << life > >

Review the frequently Used indexes

I. Clustered index

The page level of the clustered index contains the index key and also the data page, so the answer to what is stored in the leaf level of the clustered index in addition to the key value is everything, that is, all the fields in each row are at the leaf level.
The other is that the data itself is part of the clustered index, and the clustered index keeps the data in the table in order based on the key value.

In SQL Server, all clustered indexes are unique, and if you do not specify a unique keyword when you create a clustered index, SQL Server guarantees the uniqueness of the index internally by adding a unique identifier (uniqueifier) to the record when needed. The unique identifier is a 4-byte value that is added to the data as a field attached to the clustered index key, and only those rows that are declared as index key fields and have duplicate values are added.

Two. Nonclustered indexes

For nonclustered indexes, the leaf level does not contain all the data. In addition to the key values, the index row in each leaf level (the lowest layer of the tree) contains a bookmark (bookmark) that tells SQL Server where to find the data row corresponding to the index key. A bookmark lesson can be in two formats. If a clustered index exists on the table, the bookmark is the clustered index key for the corresponding data row. If the table is a heap structure, or if there is no clustered index, the bookmark is a row identifier, row Identifier,rid, which locates the actual row in the format of the file number's slot number.

The presence or absence of nonclustered indexes does not affect the organization of data pagination, so each table is not limited to owning a nonclustered index as clustered indexes, and SQL Server 2005 can contain 249 nonclustered indexes per table SQL Server 2008 Each table can contain 999 nonclustered indexes. But it actually used to be a lot less than this number.

Three. Include index

The number of index key fields is limited to 16, a total of 900 bytes, and the included columns appear only at the leaf level and do not control the sorting of indexed rows in any way. Their purpose is to enable the leaf level to contain more information to make the index tuning capability of the overlay index (covering indexes) more effective. An overlay index is a nonclustered index that finds all the information that satisfies a query at its leaf level, so that the SQL There is no need for server to access data paging at all, and in some cases SQL Serer will silently add an inclusion column to the index. This can happen if the index is based on the partition table, which is where I am today the blogger O (∩_∩) O (partitioned table) without specifying on filegroup or no partition_scheme.

One. SQL SERVER Table partitioning describes:

The table partitioning technology introduced by SQL Server allows users to distribute data across different physical disks, improving the parallel processing performance of these disks to optimize query performance ...

Two. A SQL SERVER database table partition is done in three steps:

1. Create a partition function

2. Create a partition Schema

3. Partitioning the table

Based on the cache update mechanism, I use time to partition, where everyone uses the appropriate fields as a partition according to business requirements.

Create the number of database partition files, where the data stored for one year is divided into 12 partitions, and the folder containing the Primary folder and the FG1 FG2 FG3 FG4 is now set up in D disk. ............

2. IF EXISTS (SELECT name from sys.databases WHERE name = N' airavcache ')
4. DROP DATABASE [Airavcache]
6. GO
8. CREATE DATABASE [Airavcache]
10. On PRIMARY
12. (name=' Data Partition DB Primary FG ',
14. Filename=
16. ' D:\Data\Primary\AirAvCache Primary fg.mdf ',
18. Size=5,
20. MAXSIZE=500,
22. Filegrowth=1),
24. FILEGROUP [Airavcache FG1]
26. (NAME = ' Airavcache FG1 ',
28. FILENAME =
30. ' D:\Data\FG1\AirAvCache fg1.ndf ',
32. SIZE = 5MB,
34. MAXSIZE=500,
36. Filegrowth=1),
38. FILEGROUP [Airavcache FG2]
40. (NAME = ' Airavcache FG2 ',
42. FILENAME =
44. ' D:\Data\FG2\AirAvCache fg2.ndf ',
46. SIZE = 5MB,
48. MAXSIZE=500,
50. Filegrowth=1),
52. FILEGROUP [Airavcache FG3]
54. (NAME = ' Airavcache FG3 ',
56. FILENAME =
58. ' D:\Data\FG3\AirAvCache fg3.ndf ',
60. SIZE = 5MB,
62. MAXSIZE=500,
64. Filegrowth=1),
66. FILEGROUP [Airavcache FG4]
68. (NAME = ' Airavcache FG4 ',
70. FILENAME =
72. ' D:\Data\FG4\AirAvCache fg4.ndf ',
74. SIZE = 5MB,
76. MAXSIZE=500,
78. Filegrowth=1),
80. FILEGROUP [Airavcache FG5]
82. (NAME = ' Airavcache FG5 ',
84. FILENAME =
86. ' D:\Data\FG5\AirAvCache fg5.ndf ',
88. SIZE = 5MB,
90. MAXSIZE=500,
92. Filegrowth=1),
96. FILEGROUP [Airavcache FG6]
98. (NAME = ' Airavcache FG6 ',
100. FILENAME =
102. ' D:\Data\FG6\AirAvCache fg6.ndf ',
104. SIZE = 5MB,
106. MAXSIZE=500,
108. Filegrowth=1),
114. FILEGROUP [Airavcache FG7]
116. (NAME = ' Airavcache FG7 ',
118. FILENAME =
120. ' D:\Data\FG7\AirAvCache fg7.ndf ',
122. SIZE = 5MB,
124. MAXSIZE=500,
126. Filegrowth=1),
130. FILEGROUP [Airavcache FG8]
132. (NAME = ' Airavcache FG8 ',
134. FILENAME =
136. ' D:\Data\FG8\AirAvCache fg8.ndf ',
138. SIZE = 5MB,
140. MAXSIZE=500,
142. Filegrowth=1),
146. FILEGROUP [Airavcache FG9]
148. (NAME = ' Airavcache FG9 ',
150. FILENAME =
152. ' D:\Data\FG9\AirAvCache fg9.ndf ',
154. SIZE = 5MB,
156. MAXSIZE=500,
158. Filegrowth=1),
162. FILEGROUP [Airavcache FG10]
164. (NAME = ' Airavcache FG10 ',
166. FILENAME =
168. ' D:\Data\FG10\AirAvCache fg10.ndf ',
170. SIZE = 5MB,
172. MAXSIZE=500,
174. Filegrowth=1),
178. FILEGROUP [Airavcache FG11]
180. (NAME = ' Airavcache FG11 ',
182. FILENAME =
184. ' D:\Data\FG11\AirAvCache fg11.ndf ',
186. SIZE = 5MB,
188. MAXSIZE=500,
190. Filegrowth=1),
194. FILEGROUP [Airavcache FG12]
196. (NAME = ' Airavcache FG12 ',
198. FILENAME =
200. ' D:\Data\FG12\AirAvCache fg12.ndf ',
202. SIZE = 5MB,
204. MAXSIZE=500,
206. filegrowth=1)

After you've created it.

Open FG1 folder See more Airavcachefg1.ndf file

Create a partition function

2. Use Airavcache
4. GO
8. --Create a function
12. CREATE PARTITION FUNCTION [Airavcache PARTITION Range] (DATETIME)
14. As RANGE left for VALUES (' 2010-09-01 ',' 2010-10-01 ',' 2010-11-01 ',
16. ' 2010-12-01 ',' 2011-01-01 ',' 2011-02-01 ',' 2011-03-01 ',' 2011-04-01 ',
18. ' 2011-05-01 ',' 2011-06-01 ',' 2010-07-01 ');

Creating a Partition Schema

1. CREATE PARTITION Scheme [airavcache PARTITION scheme]
2. As PARTITION [Airavcache PARTITION Range]
3. to ([Airavcache FG1], [Airavcache FG2], [Airavcache fg3],[airavcache fg4],[airavcache Fg5],[airavcache FG6],[AirAvCache Fg7],[airavcache FG8],
4. [Airavcache fg9],[airavcache Fg10],[airavcache Fg11],[airavcache FG12]);

Create a table using the Airavcache partitiion scheme schema

2. CREATE TABLE [dbo].[ Avcache] (
4. [Citypair] [varchar] (6) not NULL,
6. [Flightno] [varchar] (+) NULL,
8. [Flightdate] [DateTime] not NULL,
10. [CacheTime] [DateTime] not NULL DEFAULT (getdate ()),
12. [Avnote] [varchar] (+) NULL
14. ) on [Airavcache Partition Scheme] (flightdate);
16. --note that the [Airavcache Partition scheme] architecture is used, depending on the flightdate partition

Query partition status

1. --View Usage
2. SELECT *, $PARTITION. [Airavcache Partition Range] (flightdate)
3. FROM dbo. Avcache

You can see that September and October have been separated.

Tens implementation of SQL Server database table partitioning