Optimized sqlserver--table and partition index (ii)

Brief introduction

Before a simple introduction of the grammar and some basic concepts, after a period of time, I think it is necessary to elaborate through examples to summarize this part of the content. As previously mentioned, partitioning is about large objects (tables) divided into smaller chunks to manage, and the basic unit is row. This also gives a big advantage, such as when a database maintains backup and restore operations, such as when a large number of user visits can lead to deadlocks.

The next step is to improve our understanding of this knowledge through a large number of instances, from partitioning to presentation partitioning and some real-world cases.

--****************** --1. Creating a Partition function --****************** --create the partition FUNCTION:DAILYPF DECLARE @StartDay Date=dateadd (Dd,-3,cast (Sysdatetime () as DATE)); CREATE PARTITION FUNCTION DAILYPF (DATETIME2 (0)) As RANGE right for VALUES (@StartDay, DATEADD (dd,1, @StartDay), DATEADD (dd,2, @StartDay), DATEADD (dd,3, @StartDay), DATEADD (dd,4, @StartDay)); GO

The range partition function specifies the bounds of the range, and the left and right keywords specify that when the database engine sorts the interval values by the remaining leftmost, the boundary values belong to that side, leaving the default. The partition range cannot have an interval.

--****************** --2. Creating filegroups --****************** ALTER DATABASE partitionthis ADD FILEGROUP DailyFG1 GO ALTER DATABASE partitionthis ADD FILEGROUP DailyFG2 GO ALTER DATABASE partitionthis ADD FILEGROUP DailyFG3 GO ALTER DATABASE partitionthis ADD FILEGROUP DailyFG4 GO ALTER DATABASE partitionthis ADD FILEGROUP DailyFG5 GO ALTER DATABASE partitionthis ADD FILEGROUP DailyFG6 GO

Here we build 6 filegroups, and we can create files for filegroups,

Next I create a partitioning scheme for filegroups:

--****************** --3. Creating a Partition Schema --****************** -- CREATE PARTITION SCHEME Dailyps As PARTITION DAILYPF To (DailyFG1, DailyFG2, DailyFG3, DailyFG4, DailyFG5, DailyFG6); --****************** --4. Building a table on a partitioned schema --****************** If object_id (' ordersdaily ', ' U ') is null CREATE TABLE ordersdaily ( OrderDate DATETIME2 (0) not NULL, OrderId int IDENTITY not NULL, Ordername nvarchar (a) not NULL ) on Dailyps (OrderDate) GO

Here we map the partition function into a single filegroup and call the partition function we created earlier. Then create the table on the partition file and apply the partition function to the

OrderDate time. Here we also need to insert part of the test data for easy observation while creating a schema to facilitate query partitioning

--******************************* --Creating schemas --******************************* --create a schema for "Partition Helper" objects CREATE SCHEMA [ph] AUTHORIZATION dbo; GO --Inserting test data INSERT ordersdaily (OrderDate, Ordername) SELECT DATEADD (SS, T.N, DATEADD (Dd,-3,cast (CAST (Sysdatetime () as DATE) as DATETIME2 (0)))) as OrderDate, Case if T.N% 3 = 0 Then ' Robot ' when t.n% 4 = 0 Then ' Badger ' ELSE ' Pen ' END as Ordername From ph.tally as T--tally is a 1 to 100,000 self-growing table with only one field N WHERE N < = 1000; INSERT ordersdaily (OrderDate, Ordername) SELECT DATEADD (SS, T.N, DATEADD (Dd,-2,cast (CAST (Sysdatetime () as DATE) as DATETIME2 (0)))) as OrderDate, Case if T.N% 3 = 0 Then ' Flying Monkey ' when t.n% 4 = 0 Then ' Junebug ' ELSE ' Pen ' END as Ordername From ph.tally as T WHERE N < = 2000; INSERT ordersdaily (OrderDate, Ordername) SELECT DATEADD (SS, T.N, DATEADD (Dd,-1,cast (CAST (Sysdatetime () as DATE) as DATETIME2 (0)))) as OrderDate, Case if T.N% 2 = 0 Then ' Turtle ' when t.n% 5 = 0 Then ' Eraser ' ELSE ' Pen ' END as Ordername From ph.tally as T WHERE N < = 3000; INSERT ordersdaily (OrderDate, Ordername) SELECT DATEADD (SS, T.N, Cast (CAST (Sysdatetime () as DATE) as DATETIME2 (0))) as OrderDate, Case if T.N% 3 = 0 Then ' Lasso ' when t.n% 2 = 0 Then ' cattle Prod ' ELSE ' Pen ' END as Ordername From ph.tally as T WHERE N < = 4000; GO The associated index is created immediately --****************** --7. Create an index --****************** --Add a clustered index ALTER TABLE ordersdaily ADD CONSTRAINT pkordersdaily PRIMARY KEY CLUSTERED (Orderdate,orderid) GO --Aligning the index -- CREATE nonclustered INDEX ncorderidordersdaily On ordersdaily (ORDERID) GO --Non-aligned index CREATE nonclustered INDEX ncordernameordersdailynonaligned On ordersdaily (Ordername) on [PRIMARY] GO

After the establishment of the partition file data and other conditions, we can look at the corresponding files and data, can be the same as the following DMV to view

SELECT schema_name (so.schema_id) as Schema_name, object_name (p.object_id) as object_name, P.partition_number, P.data_compression_desc, Dbps.row_count, Dbps.reserved_page_count * 8/1024. As RESERVED_MB, SI.INDEX_ID, case if si.index_id = 0 Then ' (heap!) ' ELSE Si.name END as Index_name, Si.is_unique, SI.DATA_SPACE_ID, Mappedto.name as Mapped_to_name, Mappedto.type_desc as Mapped_to_type_desc, Partitionds.name as Partition_filegroup, Pf.name as Pf_name, Pf.type_desc as Pf_type_desc, Pf.fanout as Pf_fanout, Pf.boundary_value_on_right, Ps.name as Partition_scheme_name, Rv.value as Range_value From Sys.partitions P JOIN sys.objects So On p.object_id = so.object_id and so.is_ms_shipped = 0 Left JOIN Sys.dm_db_partition_stats as dbps On p.object_id = dbps.object_id and p.partition_id = dbps.partition_id JOIN sys.indexes si On p.object_id = si.object_id and p.index_id = si.index_id Left JOIN sys.data_spaces mappedto On si.data_space_id = mappedto.data_space_id Left JOIN Sys.destination_data_spaces DDS On si.data_space_id = dds.partition_scheme_id and P.partition_number = dds.destination_id Left JOIN sys.data_spaces partitionds On dds.data_space_id = partitionds.data_space_id Left JOIN Sys.partition_schemes as PS On dds.partition_scheme_id = ps.data_space_id Left JOIN Sys.partition_functions as PF On ps.function_id = pf.function_id Left JOIN sys.partition_range_values as RV On pf.function_id = rv.function_id and dds.destination_id = Case Pf.boundary_value_on_right When 0 then rv.boundary_id ELSE rv.boundary_id + 1 END

Query results

It is possible to find that data from different filegroups is inserted into different files and indexed by date distribution.

Then we can better understand the meaning of the partition by partitioning the switch, first to create a new filegroup (DAILYF7) to switch the partition, while creating a partition table Ordersdailyload, and insert 5,000 data into this table to create an index, and so on, and so on, the table is repeated again and again, to achieve the new subject line alignment. Note that 5,000 data must be within a specified range, such as using a check constraint to make data within 11.30-12.1 days.

Code:

We must disable or remove the index of this partition before switching.
ALTER INDEX ncordernameordersdailynonaligned on ordersdaily DISABLE;
GO
ALTER TABLE Ordersdailyload
SWITCH to ordersdaily PARTITION 6;
GO

, the partition switch has changed to 5,000 data in filegroup 6, and 7 has become empty.

If you need to switch back to execute

ALTER TABLE PARTITION 6
SWITCH to ordersdaily Ordersdailyload;
GO

If you need to merge partitions

ALTER PARTITION FUNCTION DAILYPF ()
MERGE RANGE (' 2015-11-27 00:00:00.000 ')

Result: Two partitions of this boundary point will be merged into one

Summarize:

With the above code and examples, we can learn how to use partitions. At the same time we need to know the meaning of partitioning. But to know that the partition is also a double-edged sword, it can be regarded as a performance option, management options, extensible tools, in improving data query, maintenance performance, but also on the database backup and restore policy, index maintenance, concurrency and variable partition lock and other side effects, so specific whether the table partition should be based on the actual situation to judge, It is then recommended that a tool (DataBase Tuning adcisor) run the workload to provide recommendations for partitioning.

Optimized sqlserver--table and partition index (ii)