SQL Server practice: Table Partitioning

Recently, I used a SQL Server cache to increase the data volume by a day and receive queries from customers. The speed is getting slower and slower as the data volume grows.

Review Frequently Used Indexes

I. Clustered Index

The page level of the clustered index includes the index key and data page. Therefore, the answer to what the leaf level of the clustered index stores besides the key value is everything, that is, all fields in each row are in the leaf level.

Another talk is that data itself is part of the clustered index, which maintains the Data Order in the table based on the key value.

In SQL Server, all clustered indexes are unique. If the unique keyword is not specified when the clustered index is created, SQL Server adds a unique identifier (uniqueifier) to the past record as needed) the uniqueness of the index is ensured internally. The unique identifier is a 4-byte value and is added to the data as a field appended to the clustered index key, only rows declared as index key fields with duplicate values will be added.

Ii. Non-clustered Index

For non-clustered indexes, the leaf level does not contain all the data. In addition to the key value, the index row in each leaf level (the lowest layer of the tree) contains a bookmark to tell SQL server where to locate the data row corresponding to the index key. A bookmarked course can be in two formats. If there is a clustered index on the table, the bookmarks are the clustered index keys of the corresponding data rows. If the table is in a heap structure, that is, if no clustered index is found, the bookmarkdonet is a row identifier, RID, locate the actual row in the format of the file number page number slot number.

The existence or absence of a non-clustered index does not affect the organization of data paging. Therefore, each table is not limited to having a non-clustered index as a clustered index, SQL Server 2005 each table can contain 249 non-clustered indexes SQL Server 2008 each table can contain 999 non-clustered indexes, but actually used less than this number.

Iii. Index inclusion

The maximum number of index key fields is 16, with a total size of 900 bytes. The inclusion column only appears at the leaf level and does not control the sorting of index rows in any way. They aim to enable the leaf level to contain more information so as to make full use of the index optimization capabilities covering the index (covering index. covering indexes is a non-clustered index. You can find all the information that meets the query conditions at the leaf level, so that SQL server does not need to access data pagination at all, in some cases, SQL Serer quietly adds an inclusive column to the index. This may occur when the index is created in the partition table, that is, the blog O (partition _ partition) O (partitioned table) I posted today does not specify on filegroup or no partition_scheme.

I. SQL Server table partition introduction:

The Table Partitioning technology introduced by SQL Server allows users to distribute data to different physical disks, improve the parallel processing performance of these disks to Optimize Query performance ......

2. SQL Server database table partitions are completed in three steps:

1. Create a partition function

2. Create a partition Architecture

3. Partition the table

Based on the cache update mechanism, I use time for partitioning. Here we use suitable fields as partitions according to business requirements.

Number of database partition files created. The data stored for one year is divided into 12 partitions, you need to create a data folder on drive D, which contains the primary folder and FG1 FG2 FG3 fg4 ............

If Exists ( Select Name from SYS. databases where name = N ' Airavcache ')
Drop database [airavcache]
Go
Create Database [airavcache]
On Primary
(Name = ' Data Partition dB primary fg ',
Filename =
' D: \ data \ primary \ airavcache primary FG. MDF ',
Size = 5 ,
Maxsize = 500 ,
Filegrowth = 1 ),
Filegroup [airavcache FG1]
(Name = ' Airavcache FG1 ',
Filename =
' D: \ data \ FG1 \ airavcache fg1.ndf ',
Size = 5 MB,
Maxsize = 500 ,
Filegrowth = 1 ),
Filegroup [airavcache FG2]
(Name = ' Airavcache FG2 ',
Filename =
' D: \ data \ FG2 \ airavcache fg2.ndf ',
Size = 5 MB,
Maxsize = 500 ,
Filegrowth = 1 ),
Filegroup [airavcache FG3]
(Name = ' Airavcache fg3 ',
Filename =
' D: \ data \ FG3 \ airavcache fg3.ndf ',
Size = 5 MB,
Maxsize = 500 ,
Filegrowth = 1 ),
Filegroup [airavcache fg4]
(Name = ' Airavcache fg4 ',
Filename =
' D: \ data \ fg4 \ airavcache fg4.ndf ',
Size = 5 MB,
Maxsize = 500 ,
Filegrowth = 1 ),
Filegroup [airavcache fg5]
(Name = ' Airavcache fg5 ',
Filename =
' D: \ data \ fg5 \ airavcache fg5.ndf ',
Size = 5 MB,
Maxsize = 500 ,
Filegrowth = 1 ),

Filegroup [airavcache fg6]
(Name= 'Airavcache fg6 ',
Filename=
'D: \ data \ fg6 \ airavcache fg6.ndf ',
Size=5 MB,
Maxsize=500,
Filegrowth=1),

filegroup [airavcache fg7]
(name = ' airavcache fg7 ',
filename =
' D: \ data \ fg7 \ airavcache fg7.ndf ',
size = 5 MB,
maxsize = 500 ,
filegrowth = 1 ),

filegroup [airavcache fg8]
(name = ' airavcache fg8 ',
filename =
' D: \ data \ fg8 \ airavcache fg8.ndf ',
size = 5 MB,
maxsize = 500 ,
filegrowth = 1 ),

filegroup [airavcache fg9]
(name = ' airavcache fg9 ',
filename =
' D: \ data \ fg9 \ airavcache fg9.ndf ',
size = 5 MB,
maxsize = 500 ,
filegrowth = 1 ),

filegroup [airavcache fg10]
(name = ' airavcache fg10 ',
filename =
' D: \ data \ fg10 \ airavcache fg10.ndf ',
size = 5 MB,
maxsize = 500 ,
filegrowth = 1 ),

filegroup [airavcache fg11]
(name = ' airavcache fg11 ',
filename =
' D: \ data \ fg11 \ airavcache fg11.ndf ',
size = 5 MB,
maxsize = 500 ,
filegrowth = 1 ),

Filegroup [airavcache fg12]
(Name= 'Airavcache fg12 ',
Filename=
'D: \ data \ fg12 \ airavcache fg12.ndf ',
Size=5 MB,
Maxsize=500,
Filegrowth=1)

 

After creation

 

Open the FG1 folder and check that the airavcachefg1.ndf file is missing.

 

Create a partition function

Use airavcache
Go

--Create a function

Create PartitionFunction[Airavcache partition range] (datetime)
AsRangeLeft ForValues ('2010-09-01 ', '2017-10-01', '2017-11-01 ', '2017-12-01', '2017-01-01 ', '2017-02-01', '2017-03-01 ', '2017-04-01 ', '2017-05-01', '2017-06-01 ', '2017-07-01 ');

 

Create a partition Architecture

Create Partition Scheme [airavcache Partition Scheme]
As Partition [airavcache partition range]
To ([Airavcache FG1], [airavcache FG2], [airavcache FG3], [airavcache fg4], [airavcache fg5], [airavcache fg6], [airavcache fg7], [airavcache fg8],
[Airavcache fg9], [airavcache fg10], [airavcache fg11], [airavcache fg12]);

 

 

Create a table using the airavcache partitiion scheme Architecture

Create Table [DBO]. [avcache] (
[Citypair] [varchar] ( 6 ) Not Null ,
[Flightno] [varchar] ( 10 ) Null ,
[Flightdate] [datetime] Not Null ,
[Cachetime] [datetime] Not Null Default (getdate ()),
[Avnote] [varchar] ( 300 ) Null
) On [Airavcache Partition Scheme] (flightdate ); -- Note that the [airavcache Partition Scheme] architecture is used here, based on the flightdate Partition

 

Query partition information

-- View usage
Select * , $ Partition. [airavcache partition range] (flightdate)
From DBO. avcache

 

We can see that September and October were separated.