Seventh--dmvs and DMFs (3)--monitoring tempdb with a DMV and DMF

Original: Seventh--dmvs and DMFs (3)--monitoring tempdb with a DMV and DMF

Objective:

We all know that TempDB is the system database for SQL Server and that the daily operation of SQL Server relies heavily on this library. Therefore, it is particularly important to monitor performance issues with TempDB . Over a long period of time, many people have overlooked the importance of TempDB and ignored its performance problems. This is not a good thing because the performance of tempdb affects the performance of other user databases, so always be aware of the performance of tempdb .

In the aggregation of some queries, sort operations, cursor operations and version store operations, online index creation, user object storage such as temporary tables, etc., will be used in tempdb, as DBAs, need to constantly monitor tempdb, To identify operations that are resource-intensive. This can be done using database-related DMVs .

When using these DMVs , it is necessary to understand some basic concepts of howSQL Server organizes the data. So get to know the page and the area first.

As you know,SQL Server stores databases primarily through two types of files. is the data file (mdf/ndf) and the log file (ldf). Only data files are discussed here. Because pages and extents do not apply to log files.

A data file is a format file that SQL Server stores the objects of the database, such as tables and indexes. These data files are made up of smaller units called pages. A page holds 8K of data.

In addition, the district has a page to store, a district has 8 sequential pages composed of. So, a district has 64K,1MB has a quarter.

Objects that contain data are assigned to pages in the extents. There are two types of zones--the unified and mixed zones, a unified area is unique to a single object, and the mixing zone can hold 8 different objects that can be placed into 8 pages. Because a mixed zone can share an entire area, it is also called a shared area. When the table is very small, it is placed in a mixed area until it is large enough to occupy a zone, and the mixing zone is integrated into a single zone.

This article shows you how to monitor the performance of TempDB . You can also identify the sessions and tasks that cause the TempDB space to increase.

Preparatory work:

This article will generate the data for the million and store it in a local temporary table in TempDB . You then monitor the page assignment and reallocation situation.

Steps:

1. Connect to SQL Server

2. Enter the following code:

Use tempdbgo--check if the table exists if object_id (' [dbo].[ Tbl_tempdbstats] ') is not NULL DROP TABLE [dbo]. [tbl_tempdbstats]--creates table to hold the detail of page Assignment CREATE TABLE [dbo].    [Tbl_tempdbstats] (session_id SMALLINT, database_id SMALLINT, User_objects_alloc_page_count BIGINT, User_objects_dea    Lloc_page_count BIGINT, Internal_objects_alloc_page_count BIGINT, Internal_objects_dealloc_page_count BIGINT go--collects the allocation details of the current session before executing the query insert INTO [dbo].        [Tbl_tempdbstats] SELECT session_id, database_id, User_objects_alloc_page_count, User_objec Ts_dealloc_page_count, Internal_objects_alloc_page_count, Internal_objects_dealloc_page_co Unt from sys.dm_db_session_space_usage WHERE session_id = @ @SPIDGO-check if the table exists if object_id (' tempdb.dbo. #tb L_sampledata ') is not a NULL DROP table tempdb.dbo. #tbl_SampleDataGO-generates data and inserts a temporary table select TOP 10000000 sc1.object_i       D, sc1.column_id, Sc1.name, Sc1.system_type_idinto tempdb.dbo. #tbl_SampleDataFROM sys.columns as SC1 cross JOIN sys.co Lumns as SC2 cross JOIN sys.columns as Sc3order by sc1.column_idgo--re-collects data page assignments after inserting data insert INTO [dbo].        [Tbl_tempdbstats] SELECT session_id, database_id, User_objects_alloc_page_count, User_objec Ts_dealloc_page_count, Internal_objects_alloc_page_count, Internal_objects_dealloc_page_co Unt from sys.dm_db_session_space_usage WHERE session_id = @ @SPID

3. then enter the following code and note the data differences before and after the execution:

Use Tempdbgoselect  *from    [dbo].[ Tbl_tempdbstats]

4. The results are as follows:

5. run the following query to find the TempDB space allocation situation:

SELECT  db_name (fsu.database_id) as DatabaseName,        mf.name as Logicalfilename,        Mf.physical_name as Physicalfilepath,        sum (fsu.unallocated_extent_page_count) * 8.0/1024 as FREE_SPACE_IN_MB,        sum (fsu.version_ Store_reserved_page_count            + fsu.user_object_reserved_page_count            + fsu.internal_object_reserved_page_ Count            + fsu.mixed_extent_page_count) * 8.0/1024 as Used_space_in_mbfrom    sys.dm_db_file_space_usage as FSU        INNER JOIN sys.master_files as MF on fsu.database_id = mf.database_id                                             and fsu.file_id = Mf.file_idgroup by fsu.database_id,        fsu.file_id,        mf.name,        mf.physical_name

6. The results are as follows:

Analysis:

in the opening of this article, we first created a table Tbl_tempdbstats to store statistics for page allocations and releases. The analysis information is then obtained by querying the sys.dm_db_session_space_usage. Inserts all user-defined objects and system-built objects into the table.

The next query will result in the data and insert the temporary table #tbl_SampleData. Causes the allocation of tempdb to change.

After inserting the data, check the tbl_tempdbstats table, you can draw some contrast information, finally through a DMV,sys.dm_db_file_space_usage. You can see the distribution in megabytes .

Note:sys.dm_db_file_space_usage, sys.dm_db_session_space_usage These two DVM only applies to tempdb.

Seventh--dmvs and DMFs (3)--monitoring tempdb with a DMV and DMF