Queries and supplements after database partitions and queries after database partitions

Queries and supplements after database partitions and queries after database partitions

-- View the partition and partition range. select * from sys. partitions where object_id = object_id ('secureusblog '); select * from sys. partition_range_values; -- view the partition architecture select * from sys. partition_schemes; -- view the partition where the value of a specific partition column belongs. select M2. $ partition. part_mediasec_func ('20170101') as partition_num; -- view the select * from SecureUsbLog where M2. $ partition. part_mediasec_func (logTime) = 3 -- view the number of records contained in each partition select $ partition. part_mediasec_func (logTime) as partition_num, count (*) as record_numfrom SecureUsbLog group by $ partition. part_mediasec_func (logTime) order by $ partition. part_mediasec_func (logTime );

This part is for future expansion. For example, you can manually add partitions when there are not enough partitions in the future.

-- Add the file group alter database M2add filegroup [FG10]; go -- file group alter database M2add file (name = FG10_data, filename = 'C: \ esafenet \ FG10_data.ndf ', size = 1 MB) to filegroup [FG10]; -- add a PARTITION, and then expand USE M2 go alter partition scheme Part_mediasec_scheme next used FG10 alter partition function Part_mediasec_func () split range ('2014 23:59:59 ') GO

Database logs, partitions are a good choice. If no partitions are available, another method is to split the logs into tables and put the data of each month into different tables.

In short, the log partition is a good choice, but it is not the only choice. Another one is syslog. What is syslog?

I will share the next episode.

How can I improve query efficiency after SQL partitioning?

You may not understand the purpose of partitioning.
If the partition is purely for partitioning. That makes no sense.

First. After reading your partition method, there is no big problem in general.

If not partitioned
When
SELECT * FROM tbl1 WHERE tbl = '2017-12-31'
.
The database needs to retrieve data from a large index (including rows in the year-11)

If yes.
When
SELECT * FROM tbl WHERE tbl = '2017-12-31'
.
The database only needs to retrieve data from a small index (rows in)

For example:
If it is not partitioned, it is like a haystack.
So partitioning is like fishing needles from a river.

Note: If the query condition does not contain the partition condition.
It is like fishing needles in the river, but you don't know which river you want to fetch.
(This is "It would be slower if it was a cross-region issue ")

========================

Next let's take a look at your SQL statement for querying data for two years.

Select * from tbl where tm between '2017-01-01 'and '2017-01-01'
Select * from tbl1 where tm between '2017-01-01 'and '2017-01-01'

Your two SQL statements basically depend on the index type after the database is analyzed.
Theoretically, non-clustered indexes are not used.
If there is a clustered index, then the clustered index is used. If not, the whole table is scanned directly.

Partition Table
At most databases can be analyzed. In this search, you do not need to retrieve the partitions in year 08 and Year 11.
However, you need to retrieve all the partitions in the 09-year period and the 10-year period.

For unpartitioned tables
As described above, the specific query policy depends on the index type.

Database Table partition Optimization

The SQL Server data table partition process is divided into three steps:

1) create a partition function

2) create a partition scheme

3) partition the table

Step 1: Create a partition function

The partition function defines [u] how [/u], that is, how you want SQL Server to partition data. Here, we will not use a table as an example, but summarize the technology of data segmentation.

Partitions are implemented by specifying the dividing line of each partition. For example, assume that we have a mers MERs table that contains information about all the Customers of the enterprise. The customer information is identified by a unique customer number, ranging from 1 to 1000000. We can use the following Partition Function (customer_Partfunc) to divide the table into four partitions equally:

Create partition function customer_partfunc (int)
AS RANGE RIGHT
For values (250000,500 000, 750000)

The split boundary specifies four partitions. The first partition contains all records whose values are less than 250000. The second partition contains all records with values between 250000 and 499999. The third partition contains all records with values between 500000 and 749999. All other records greater than or equal to 750000 are included in the Fourth partition.

Note that the "range right" clause is used in this example. This indicates that the demarcation value is on the right side of the partition. Similarly, if the range left clause is used, the first partition contains all records whose values are less than or equal to 250000; the second partition will contain all records with values between 250001 and 500000, and so on.

Step 2: Create a partition scheme

Once you have created a partition function that defines how to partition data, the next step is to create a partition scheme, defining [u] where [/u], that is, where you want to partition the data. This is a straightforward process. For example, if I have four file groups named from "fg1" to "fg4", the following partition scheme can be used:

Create partition scheme customer_partscheme
As partition customer_partfunc
TO (fg1, fg2, fg3, fg4)

Note that we have connected a partition function to the partitioning scheme, but we have not connected the partitioning scheme to any specific database table. This is the time to reuse the function. We can use this function to apply the Partition Scheme (or just a partition function) to any data in the database table.

Step 3: partition the table

After creating the partition scheme, you can partition the table. This is the simplest step. You only need to add the "ON" clause in the table creation statement to specify the table partitioning scheme and the table columns to apply the partitioning scheme. You do not need to specify the partition function, because the partition function has been defined in the Partition Scheme.

For example, if you want to use the preceding partitioning scheme to create a customer table, you need to use the following Transact-SQL statement:

Create table MERs (FirstName nvarchar (40), LastName nvarchar (40), CustomerNumber int)
ON customer_partscheme (CustomerNumber)... the remaining full text>