1. Database
Copy Code code as follows:
--Size of all databases
EXEC sp_helpdb
--Status of all databases
Select Name,
User_access_desc,--User access mode
STATE_DESC,--database state
Recovery_model_desc,--Recovery model
Page_verify_option_desc,--page detection options
Log_reuse_wait_desc--Log reuse wait
From sys.databases
--The size of a database: calculated space by the page, performance impact, basic accuracy, sometimes inaccurate
Use test
Go
EXEC sp_spaceused
Go
--Can @updateusage = ' true ' to run DBCC UPDATEUSAGE
exec sp_spaceused @updateusage = ' true '
--For a database, display the number of pages and rows in the catalog view and correct
DBCC updateusage (' test ')
2. Data file
Copy Code code as follows:
--View all files and sizes in a database
Sp_helpfile
--View all files in the database, path, status, size
Select Db_name (database_id) dbname,
TYPE_DESC,--data or log
Name,--the logical name of the file
Physical_name,--The physical path of the file
State_desc,-File status
Size * 8.0/1024 as ' file size (MB)
From Sys.master_files
--Extent computing space by region, no performance impact, basic accuracy, totalextents*64/1024, MB
--also applies to the file size of tempdb, but does not include log files
DBCC SHOWFILESTATS
3. log file
Copy Code code as follows:
--View Log file database, path, status, size
Select Db_name (database_id) dbname,
TYPE_DESC,--data or log
Name,--the logical name of the file
Physical_name,--The physical path of the file
State_desc,-File status
Size * 8.0/1024 as ' file size (MB)
From Sys.master_files
where Type_desc = ' LOG '
--The log size of all databases, space usage
DBCC SQLPERF (LOGSPACE)
4, data file, log file I/O statistics information
Copy Code code as follows:
--I/O statistics for data and log files, including file size
Select database_id,
FILE_ID,
File_handle,--windows file handle
Sample_ms--The number of milliseconds since the computer started
Num_of_reads,
Num_of_bytes_read,
Io_stall_read_ms,--waiting for read time
Num_of_writes,
Num_of_bytes_written,
Io_stall_write_ms,
Io_stall--The total time that the user waits for the file to complete I/O operations
Size_on_disk_bytes-The actual number of bytes that the file occupies on the disk
From Sys.dm_io_virtual_file_stats (db_id (' Test '),--Database ID
1)--Data file ID
UNION ALL
Select database_id,
FILE_ID,
File_handle,--windows file handle
Sample_ms--The number of milliseconds since the computer started
Num_of_reads,
Num_of_bytes_read,
Io_stall_read_ms,--waiting for read time
Num_of_writes,
Num_of_bytes_written,
Io_stall_write_ms,
Io_stall--The total time that the user waits for the file to complete I/O operations
Size_on_disk_bytes-The actual number of bytes that the file occupies on the disk
From Sys.dm_io_virtual_file_stats (db_id (' Test '),--Database ID
2)--log file ID
5, objects, including: tables, indexes, indexed views, etc.
Copy Code code as follows:
--not necessarily accurate: the number of rows in a table, retention size, data size, index size, unused size
exec sp_spaceused @objname = ' Temp_lock '
--Accurate: but with performance impact
exec sp_spaceused @objname = ' Temp_lock ',
@updateusage = ' true '
--by page statistics, no performance impact, sometimes inaccurate
/*======================================================
Calculate space usage for multiple objects at once
Sys.dm_db_partition_stats returns page and row count information for each partition (table and index) in the current database
========================================================*/
Select O.name,
SUM (p.reserved_page_count) as Reserved_page_count,--reserved pages, including tables and indexes
SUM (p.used_page_count) as Used_page_count,--pages are used, including tables and indexes
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
else P.lob_used_page_count +
P.row_overflow_used_page_count
End) as data_pages, a data page that contains the data in the table, the LOB data in the index, and the row overflow data in the index
SUM (case when p.index_id < 2
Then P.row_count
else 0
End) as row_counts--data rows, containing the number of rows in the table, not including the number of data entries in the index
From Sys.dm_db_partition_stats P
INNER JOIN sys.objects o
On p.object_id = o.object_id
where p.object_id= object_id (' Table name ')
GROUP BY O.name
--by page or district statistics, have performance impact, accurate
--Displays space information for the data and indexes of all tables or views in the current database
--Include: logical fragmentation, zone fragmentation (fragmentation rate), average page density
DBCC SHOWCONTIG (Temp_lock)
--sql server recommended dynamic performance functions, accurate
SELECT *
From Sys.dm_db_index_physical_stats (
DB_ID (' Test '),--Database ID
OBJECT_ID (' Test.dbo.temp_lock '),--Object ID
NULL,--index ID
NULL,--sub-area code
' Limited '--default,null, ' limited ', ' sampled ', ' detailed ', Default ' limited '
--' limited ' mode runs the fastest, the number of pages scanned is the least, for the heap scans all pages, only the parent page above the leaf level is scanned for the index
--' sampled ' mode will return the heap, 1% samples of all pages in the index statistics, if less than 1000 pages, then use ' detailed ' instead of ' sampled '
--' detailed ' mode scans all pages, returns all statistics
)
--Find out which objects are needed to be rebuilt
Use test
Go
If object_id (' Extentinfo ') is not null
drop table Extentinfo
Go
CREATE TABLE Extentinfo
([file_id] smallint,
page_id int,
Pg_alloc int,
ext_size int,
obj_id int,
index_id int,
Partition_number int,
partition_id bigint,
Iam_chain_type varchar (50),
Pfs_bytes varbinary (10)
)
Go
/*====================================================================
The panel information that is queried is the disk area information of the data file for the database, and the log file is not in the disk area
Command format: DBCC extentinfo (DBNAME,TABLENAME,INDEXID)
DBCC extentinfo (' [Test] ', ' Extentinfo ', 0)
======================================================================*/
Insert Extentinfo
EXEC (' DBCC EXTENTINFO (' test ') ')
Go
--Each section has a data
Select file_id,
OBJ_ID,--Object ID
index_id,--index ID
page_id, which page is the first page in this area, the page number
Pg_alloc,--the number of pages allocated in this area
Ext_size,--How many pages does this panel contain?
Partition_number,
PARTITION_ID,
Iam_chain_type,--iam chain type: inline data, row overflow data, large object data
Pfs_bytes
From Extentinfo
ORDER BY FILE_ID,
OBJ_ID,
INDEX_ID,
PARTITION_ID,
Ext_size
/*=====================================================================================================
The disk area information of the data file of the database, by calculating the number and actual number of each object theoretically, if the difference between the two is very large,
Then you should rebuild the object.
1. Each record is a section
2. If the pg_alloc is smaller than the ext_size, the actual number of pages allocated in each area is less than the theoretical number of pages in the area,
Then there will be one more record, and the pages that should belong to this area should be placed in the corresponding section of this record.
So there is only one record (that is, a district), now there are 2 records (that is, 2 districts),
Results in the actual district number 2 larger than the theoretical district quantity of 1.
========================================================================================================*/
Select file_id,
OBJ_ID,
INDEX_ID,
PARTITION_ID,
Ext_size,
COUNT (*) as ' number of actual areas ',
SUM (pg_alloc) as ' actually contains the number of pages ',
Ceiling (SUM (pg_alloc) * 1.0/ext_size) as ' the number of areas in theory ',
Ceiling (SUM (pg_alloc) * 1.0/ext_size)/COUNT (*) * 100.00 as ' theoretical number of extents/actual area '
From Extentinfo
Group BY FILE_ID,
OBJ_ID,
INDEX_ID,
PARTITION_ID,
Ext_size
Having ceiling (sum (pg_alloc) *1.0/ext_size) < count (*)
--Filter: The number of theoretical area < actual area, that is, the percentage is less than 100%
Order by partition_id, obj_id, index_id, [file_id]
6. tempdb Database
Copy Code code as follows:
Space use of--tempdb database
/*======================================================
Objects contained in tempdb:
User objects: are explicitly created by the user, which is in the scope of the user session,
Can be in the scope of a routine that creates an object (stored procedure, trigger, function).
1. User-defined tables, indexes
2. system tables, indexes
3. Global temporary tables, indexes
4. Local temporary table, index
5. Table variables
6. Tables returned in table-valued functions
Internal objects: is created as needed by the SQL Server database engine to process SQL Server statements.
Internal objects can be created and deleted in the statement scope.
Each internal object requires at least 9 pages, an IAM page, and a zone containing 8 pages.
1. Cursors, spool operations, temporary large objects (lobs), stored worksheets
2. Working files for hash joins, hash aggregation operations
3. If the SORT_IN_TEMPDB option is set, the rebuild sort results for creating and rebuilding indexes are stored in tempdb;
The intermediate result of the group by, order by, and union operation.
Version store: A collection of data pages that contains the data needed to support row versioning features, primarily supporting snapshot transaction isolation levels.
And some other new features that improve database concurrency performance.
1. Common version Store: The version of the row generated by the data modification transaction in a database that uses the snapshot isolation level, committed read isolation level.
2. Online Index build version store: Row versions generated for data modification transactions for online indexing operations,
Multiple active result sets, after which the row version is generated by the trigger.
As mentioned above, because the sys.allocation_units and sys.partitions views do not record the internal objects in tempdb, the version store
So these 2 views and sp_spaceused cannot accurately reflect the spatial use of tempdb.
To analyze the existing workload of tempdb:
1. Set up the automatic growth of tempdb
2. Monitor the use of tempdb space by simulating individual inquiries and tasks
3. Perform some system maintenance operations (rebuild index) by simulating and monitor tempdb space usage
4. Based on the amount of space used in tempdb 2 and 3, predict the space used by the total workload and adjust the value for the concurrency of the task.
5. Set the initial size of tempdb in the build environment and turn on automatic growth based on the value obtained from 4.
In addition, the number and size of files in tempdb not only need to meet the actual usage needs, but also consider performance optimization.
Monitor space usage for tempdb:
1. Can be traced through SQL Trace, but because it is not expected to cause a large number of use of the tempdb statement at what time,
and SQL Trance operation is more expensive, if always open will produce a large number of tracking files, the burden on the hard disk is heavier, generally not.
2. Lightweight monitoring is the ability to run DBCC commands, dynamic performance views-functions that monitor the operation of the system at certain intervals,
Record the results in a file, which is a good choice for a very busy system.
========================================================*/
Select db_name (database_id) as DB,
Max (file_id) as ' File ID ',
SUM (User_object_reserved_page_count) as ' user object reserved pages ',----contains unused pages in the allocated area
SUM (Internal_object_reserved_page_count) as ' internal objects reserved pages ',--contains unused pages in the allocated area
SUM (Version_store_reserved_page_count) as ' version stores reserved pages ',
SUM (Unallocated_extent_page_count) as ' the number of pages contained in unallocated zone '--does not contain unused pages in allocated area
Allocated mixed area of SUM (mixed_extent_page_count) as ' file: Allocated and Unassigned pages '--contains IAM pages
From Sys.dm_db_file_space_usage
Where database_id = 2
Group by db_name (database_id)
--able to reflect the overall allocation of the tempdb space at that time, the application space of the session is running the statement
SELECT
T1.SESSION_ID,
T1.internal_objects_alloc_page_count,
T1.user_objects_alloc_page_count,
T1.internal_objects_dealloc_page_count,
T1.user_objects_dealloc_page_count,
T.text
From Sys.dm_db_session_space_usage T1--a cumulative space request reflecting each session
INNER join sys.dm_exec_sessions as T2
On t1.session_id = t2.session_id
INNER JOIN sys.dm_exec_requests T3
On t2.session_id = t3.session_id
Cross apply sys.dm_exec_sql_text (T3.sql_handle) t
where t1.internal_objects_alloc_page_count>0 or
T1.user_objects_alloc_page_count >0 or
T1.internal_objects_dealloc_page_count>0 or
T1.user_objects_dealloc_page_count>0
--Returns the page allocation and release activity in tempdb,
-Sys.dm_db_task_space_usage Returns a value only if the task is running
--When the request completes, the values are aggregated by session aggregation in the Sys.dm_db_session_space_usage
Select t.session_id,
T.REQUEST_ID,
T.DATABASE_ID,
T.user_objects_alloc_page_count,
T.internal_objects_dealloc_page_count,
T.internal_objects_alloc_page_count,
T.internal_objects_dealloc_page_count
From Sys.dm_db_task_space_usage t
INNER JOIN sys.dm_exec_sessions E
On t.session_id = e.session_id
INNER JOIN sys.dm_exec_requests R
On t.session_id = r.session_id and
t.request_id = r.request_id
