Optimizing partitions for sqlserver--tables and Indexes (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 partition Functions              --****************** --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. Create a partition schema--****************** --CREATE PARTITION SCHEME Dailyps as PARTITION DAILYPF to (DailyFG1, DailyFG2, D AilyFG3, DailyFG4, DailyFG5, DailyFG6); --******************--4. Building a table on a partitioned schema--****************** If object_id (' ordersdaily ', ' U ') is a null CREATE TABLE ordersdaily (OrderDate DATETIME2 (0) not NU LL, OrderId int IDENTITY not NULL, Ordername nvarchar (no) 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

--*******************************--Create schema--******************************* --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 (SY Sdatetime () as DATE) as DATETIME2 (0))) as OrderDate, case is T.N% 3 = 0 Then ' Robot ' when t.n% 4 = 0 T HEN ' 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-T.N% 3 = 0 Then ' Flying Monkey ' when T.N% 4 = 0 Then ' Junebug '   ELSE ' Pen ' END as ordername          & nbsp From ph.tally as t            WHERE N < =; INSERT ordersdaily (OrderDate, Ordername)               SELECT DATEADD (ss, T.N, DATEADD (Dd,-1,cast (CAST (Sysdatetime () as DATE) as DATETIME2 (0))) as orderdate, & nbsp;              Case-T.N% 2 = 0 Then ' Turtle ' when T. N% 5 = 0 Then ' Eraser '   ELSE ' Pen ' END as Ordername          &nbs P From ph.tally as t            WHERE N < = up; INSERT ordersdaily (OrderDate, Ordername) SELECT DATEADD (SS, T.N, Cast (CAST (Sysdatetime () as DATE) as DATETIME2 (0)) as OrderDate, case is 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 INDEX             --******************            --Add a clustered index             ALTER TABLE ordersdaily            ADD CONSTRAINT PKOrdersDaily                 PRIMARY KEY CLUSTERED (OrderDate, ORDERID)             GO --Align 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 when 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                        p.partition_id = dbps.partition_id             join    sys.indexes si                     on P.object_id = si.object_id                         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                         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 have to disable or remove the index of this partition before switching to it. 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.

Optimizing partitions for sqlserver--tables and Indexes (ii)