SQL Server Enterprise Platform Management Practice book Notes--sql Server database file allocation method

Original: SQL Server Enterprise Platform Management Practice book Notes--sql Server database file allocation method

1, file allocation methods and file space Check method

The most common commands for checking data files and table sizes are: sp_spaceused

This command has three defects: 1, the use of each data file and log file is not visible. 2, this stored procedure relies on the SQL Server stored in some system views of the spatial usage statistics calculated results, if not updated spatial statistics, such as the first big data inserted, sp_spaceused results are not accurate. 3, this command is mainly for ordinary users of the database, for the tempdb database stored in some system temporary data objects, it is not possible to use this stored procedures to statistics.

You can view the details of the stored procedure through the sp_helptext sp_spaceused command.

SQL Server files are divided into data files (. mdf,.ndf) and log files (. ldf). Different file viewing methods are different.

Data content is stored as a page and can be viewed through the DBCC PAGE command

DBCC PAGE (<db_id>,<file_id>,<page_id>,<forma_id>)

For example, we want to see the No. 3230 page in the AdventureWorks_Data data file in the AdventureWorks database. We can get from the results of sp_helpdb from db_id, file_id can be obtained from the results of sp_helpfile.

FORMAT_ID is the specified output format, with 1, 2, and 33 values. Generally speaking, 3 this output format is more intuitive. Before you run the DBCC page, you also need to turn on trace flag 3604.

Let's take a look at the details:

Here are a few more important information, such as objectid=133575514, indexid=1

It tells us that this page belongs to the 133575514 object with an index of ID 1. You can get what object it belongs to by using the following statement.

SelectS.name,o.name fromsys.sysobjects oInner JoinSys.schemas S onO.uid=s.schema_idwhereO.id=133575514GoSelectId,indid,name fromsys.sysindexeswhereId=133575514  andIndid=1
GoexecSp_helpindex N'dimproduct'Go

We can see that this page belongs to the DimProduct table, and then the index below the table: Pk_dimproduct_productkey, which is based on the ProductKey column

You can use the following command to see which extents are in a table in the database, and what is the ID of the first page in that area:

The above table uses the contents of so many areas, and the majority of each zone is stored as 8 consecutive pages, only the first is 1 pages, the page ID is 217.

Support for large rows

In SQL Server, rows cannot span pages, and data for all fields that belong to the same row are placed on the same page, and the maximum amount of data for a page is 8064B (8KB). So the General data type field consists of a row that can add up to no more than 8KB, but this limit does not include the data types such as varchar (max), nvarchar (max), varbinary (max), In the 2005 version of this large type of data field broke through the 8KB limit, convenient for users, if not more than 8KB, the data will be stored in the ordinary data page. If the total length exceeds the 8kb,sql server, these fields are separated and stored separately in a page called Row-overflow (row overflow).

Calculation method for data file space usage

The simplest way to view a database is to view it in Management Studio by right-clicking the database name and selecting the standard report in the report item:

The statistical results here and the results of sp_spaceused statistics slightly different, because this is according to the district statistics, because each area has 8 pages, and these 8 pages are not necessarily all used, but if according to the district statistics by default is all used, and according to the page statistics results are different.

The two statistical methods are different, the use of the scene is not the same, according to district statistics results faster

According to district statistics:

If we use a command, we can do it by district statistics.

DBCC Showfilestats

Since SQL Server allocates new space in most of the time by district, and the information on the System allocation page is always updated in real time, it is more accurate and reliable according to this statistic method. It is safe to execute with high server load and does not add additional system burden. Therefore, it is a good choice to investigate the use of database data file level.

According to the page statistics

If you want to know how much space a particular table or index uses, you need to analyze it from the page level. Here we can choose sp_spaceused or DBCC SHOWCONTIG

Of course, after SQL Server2005 a new sys.dm_db_index_physical_stats to see the storage details of the index, including the number of pages, the percentage of lock slices, etc.

sp_spaceused calculates storage space based on the two administrative views of Sys.allocation_units and sys.partitions. When the index is deleted and the spatial information of the table is not real, the two tables may not reflect the accurate information of the database in time.

This parameter can be added to Undateusage, which requires SQL Server to update the statistics in the management view for this directive. Doing so will be a resource-intensive effort. It is recommended not to be used easily in the bad environment of production.

Another drawback of sp_spaceused is that you can only query one object at a time, and you can't find all the tables at once, and we could do this through the Sys.dm_db_partition_stats view:

SelectO.name,SUM(P.reserved_page_count) asReserved_page_count,SUM(P.used_page_count) asUsed_page_count,SUM(            Case  when(p.index_id<2) Then(P.in_row_data_page_count+P.lob_used_page_count+p.row_overflow_used_page_count)ElseP.lob_used_page_count+P.row_overflow_used_page_countEnd            ) asDataPages,SUM(                Case  when(p.index_id<2) ThenRow_countElse 0               End                )  asrowcounts fromSys.dm_db_partition_stats PInner Joinsys.objects o onP.object_id=O.object_idGroup  byO.name

Sp_spaceused method is simple, but the function is weak, also is not the most accurate method. It is not particularly recommended.

Sys.dm_db_partition_stats will come more directly and have little effect on system performance.

DBCC Showcontig (or sys.dm_db_index_physical_stats) is the most accurate way to check the allocation of database space, which can show how many pages, extents, and even the average amount of data on the page. But it also pays a performance price, and SQL Server cannot maintain such bottom-level statistics from the overall performance perspective. In order to complete this command, the library must be scanned. In short, the more accurate the results, the greater the scope of the scan.

In short: If the manager just look at the overall use of data files, DBCC Showfilestats is a better choice. If you want to see the space usage of each object, you can use the dynamic management view Sys.dm_db_partition_stats. DBCC SHOWCONTIG is a good choice if you want to understand the usage and fragmentation of each page, each area.

Log file

The log file for the database is the one that contains all the log information used to recover the database. Each database must have at least one log file, and of course there can be more than one. The recommended file name extension for log files is. ldf. Unlike database files, which are organized by the 8KB organization, log files are not grouped by page or area.

The SQL Server database engine internally divides each physical log file into multiple virtual log units. The virtual log unit does not have a fixed size, and the number of virtual log units contained in a physical log file is not fixed. Administrators cannot configure or set the size or number of virtual log units. However, SQL Server attempts to control the number of virtual log units, limiting it to a reasonable range, but there is a special case where log files are incremented every time and at least one virtual log unit is added. So, if a log file undergoes multiple small auto-growth, the number of virtual log units inside will be much larger than the normal log file. This can affect the efficiency of log file management, and it may take a long time for the database to start.

The transaction log is a wrapped file. For example, suppose you have a database that contains a physical log file that is divided into 5 virtual log units. When you create a database, the logical log file starts at the beginning of the physical log file. The new log record is added to the end of the logical log and then extended to the end of the physical log.

When the end of the logical log reaches the end of the physical log file, the new log record wraps around to the beginning of the physical log file, continuing to write back

Viewing log file usage is straightforward. Let's look at the following statement:

DBCC SQLPERF (Logspace)

Shows the log size and usage ratios for all databases on SQL Server. The execution of the statement does not burden SQL Server, and the result of this statement is always correct. You can run this command on SQL Server at any time.

SQL Server Enterprise Platform Management Practice book Notes--sql Server database file allocation method