Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to use filegroup and partition in sql server". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Basic knowledge

The concept of table partitioning and filegroup has not been very clear, today through specific examples to learn what filegroup and partition, and their role.

1.1 manage the properties of files through filegroups

From the user's point of view, there are only three types of data objects that need to be stored for filegroups that need to be specified for the created objects: tables, indexes, and large objects (LOB).

Use filegroups to isolate users and files, allowing users to build tables and indexes against filegroups rather than files on the actual disk. That is, you can specify that tables and indexes are stored on different files.

Using filegroups to manage files can make different files in the same filegroup distribute on different hard disks, which greatly improves the performance of IO.

SQL SERVER will automatically allocate the newly added space according to the initial size and growth of each file. Assuming that the size of file An in the same filegroup is twice that of file B, and adding three pages of data (Page), 2 pages will be allocated to file An and 1 page to file B.

1.2 Classification of documents

Primary document: this document is a must, and there can only be one. This file stores additional information such as the location of other files. The extension is .MDF

Secondary files: you can build as many as you want and store them for different purposes. The extension .ndf is used to hold data, not logs.

Log files: store logs with a .ldf extension

After SQL SERVER 2008, file stream data files and full-text index files are also added.

We can use the sys.database_files view to see the files in the database:

Double-click the code to select all 1select*from sys.database_files1.3 to create the filegroup and create the index in the specified filegroup

Filegroups can be created through TSQL statements or through SSMS, which will be mentioned later. I won't repeat it here. Let's focus on how to create the index in the specified filegroup without putting it with the data. First, let's take a look at the filegroup I created, and the files corresponding to these filegroup, as shown in the following figure:

Then we test it with the following TSQL statement

Double click the code 12 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 25 26 27 28use TESTDB-step1. Insert data select * into OrderDetail from AdventureWorks2008R2.Sales.SalesOrderDetail-- step2: check the index information of the table and find that all pages are on pagefid=1, and the indexid is 0. 0. Because the clustered index is not created before the heap table dbcc ind (TESTDB, [dbo.OrderDetail],-1)-- step3: create the clustered index on the partition, do not put the clustered index in the filegroup of IndexStorage, because the clustered index is the data itself. -- if the clustered index on IndexStorage Then all the data will be create clustered index idx_c_SSalesOrderDetailID on OrderDetail (SalesOrderDetailID)-- step4 on the file corresponding to the filegroup IndexStorage: at this time, it is found that the original indexid=0 has become index=1 dbcc ind (TESTDB, [dbo.OrderDetail]). -1)-- step5: create a nonclustered index CREATE NONCLUSTERED INDEX idx_nc_SalesOrderID ON dbo.OrderDetail (SalesOrderID) on IndexStorage CREATE NONCLUSTERED INDEX idx_nc_CarrierTrackingNumber ON dbo.OrderDetail (CarrierTrackingNumber) on IndexStorage CREATE NONCLUSTERED INDEX idx_nc_UnitPrice ON dbo.OrderDetail (UnitPrice) on IndexStorage on the file group of IndexStorage-- step6: look at the page information again and we find that there is only pagefid=1 with indexid=1 That is to say, the clustered index is on the file TESTDB.MDF-- and the pagefid=3 corresponding to indexid=2,3,4 indicates that the index has been built on the filegroup of IndexStorage, which corresponds to the file IndexStorage.ndf. Dbcc ind (TESTDB, [dbo.OrderDetail],-1)-- step7: create a composite index, CREATE NONCLUSTERED INDEX idx_nc_com ON dbo.OrderDetail (SalesOrderID,CarrierTrackingNumber,UnitPrice)-- step8: the filegroup,filefid=1. Dbcc ind of Primary is used by default (TESTDB, [dbo.OrderDetail],-1)

Summary:

Create a clustered index on a partition, and do not put a clustered index in the filegroup of IndexStorage, because a clustered index is the data itself. If you clustered the index on IndexStorage, then all the data will be on the file corresponding to the filegroup of IndexStorage.

When creating a nonclustered index, specify which filegroup to put the index in by adding on [filegroup] at the end of the index creation statement. If you don't add it, the default filegroup will be used. Our default filegroup here is priamry.

1.4 benefits of using multiple files

Using multiple files to distribute data to multiple hard drives can greatly improve IO performance. Putting it on a disk has little effect.

Scene description

A large number of concurrent statements are sent from the application to modify the records in the same table, while the table architecture design and user business logic make these changes concentrated on the same page, or a small number of pages. These pages are sometimes called Hot Page. Such bottlenecks usually only occur on typical OLTP systems with more concurrent users. This bottleneck can not be solved by improving the hardware configuration. Only by modifying the table design or business logic to spread the changes to as many pages as possible can the concurrency performance be improved.

In the real world, you can imagine the following situation. In a stock trading system, every transaction will have a serial number, which is incremental and can not be repeated. On the other hand, all transaction requests sent by clients are stored in the same transaction table. For every new transaction, a new record is inserted. If the designer chooses to build a clustered index on the serial number (which is also natural), it is easy to encounter the PAGELATCH resource bottleneck of Hot Page. Only one user can insert a transaction at a time.

How can we solve or alleviate this bottleneck?

The easiest way is to build a clustered index on a different data column rather than on an Identity field. In this way, the data in the table is sorted in other ways, and inserts at the same time have the opportunity to be scattered on different pages.

If it is really necessary to build a clustered index on the field of Identity, it is recommended to create several Partition on the table based on some other data column. Dividing a table into several partitions can increase the number of pages that accept new data.

Let's take the stock trading system above as an example. Different stocks belong to different industries. Developers can divide a trading table into several partitions according to the industry attributes of the stock. In SQL Server, each partition of a partitioned table (Partitioned Table) is a separate unit of storage. Rows of data belonging to different partitions are stored strictly separately. So transactions that take place at the same time, because of their different industries, will also be kept in different partitions. In this way, transactions from different industries can be inserted at the same point in time. The Hot Page on each partition (the page that accepts the insertion of new data) is less hot.

In my case, there is a SalesOrderDetail table with a large amount of data, and I want to partition it according to the UnitPrice field. Let's take a look at the specific steps.

Step1: creating filegroup

There seems to be no create filegroup in sql server, just adding filegroup to an off-the-shelf database. In the following code, first create the database, and then add four filegroup,tsql codes as follows:

Double-click the code to select all 12 3 4 5 6 7 9 10 11 12 talk step 1-- create a database create database TEST USE MASTER GO-- 400000 lines are divided into 5 filegroups, PRIMARY plus the following four filegroups,-- naming rules: FG_ database name _ table name _ field name _ serial number ALTER DATABASE TEST ADD FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_1; ALTER DATABASE TEST ADD FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_2 This is the end of ALTER DATABASE TEST ADD FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_3; ALTER DATABASE TEST ADD FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_4; GO's "how to use filegroup and partition in sql server". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.