dmv io读写高的sql

Dynamic management views (DMVs) and dynamic management functions (DMFs) are system views and system functions that return metadata of the system state. On querying the related system objects, database administrators can understand the internals of SQL Server. It allows us to monitor the performance of the SQL Server instance, and diagnose issues with it.

动态管理视图（DMV）和动态管理功能（DMF）是返回系统状态元数据的系统视图和系统功能。 在查询相关的系统对象时，数据库管理员可以了解SQL Server的内部。 它使我们能够监视SQL Server实例的性能并诊断其问题。

SQL Server 2017 ships with a number of new and enhanced dynamic management views and dynamic management functions that will help DBAs monitor the health and performance of SQL Server instances. A few existing DMV’s such as sys.dm_os_sys_info and sys.dm_db_file_space_usage have been enhanced. Some have also been newly built and available only for SQL Server 2017.

SQL Server 2017附带了许多新的和增强的动态管理视图和动态管理功能，这些功能将帮助DBA监视SQL Server实例的运行状况和性能。 一些现有的DMV（例如sys.dm_os_sys_info和sys.dm_db_file_space_usage）已得到增强。 其中一些也是新建的，仅适用于SQL Server 2017。

The new or enhanced DMV’s/DMF’s fall into the three categories:

新的或增强的DMV / DMF分为三类：

• • sys.dm_db_log_stats
    sys.dm_db_log_stats
  • sys.dm_db_log_info
    sys.dm_db_log_info
  • sys.dm_db_stats_histogram
    sys.dm_db_stats_histogram
  • sys.dm_db_file_space_usage
    sys.dm_db_file_space_usage
  • sys.sys.dm_db_tuning_recommendations
    sys.sys.dm_db_tuning_recommendations
• • sys.dm_tran_version_store_space_usage
    sys.dm_tran_version_store_space_usage
• • sys.dm_os_host_info
    sys.dm_os_host_info
  • sys.dm_os_sys_info
    sys.dm_os_sys_info

()

Let’s take a look at them by creating a new query here, and typing in select * from. The first view I want to work with is sys.dm_db_log_stats. It requires the database ID number you want to investigate, so I’m going to type in the number one inside the parenthesis to indicate the master database.

让我们通过在此处创建一个新查询并输入select * from来查看它们。 我要使用的第一个视图是sys.dm_db_log _ stats 。 它需要您要调查的数据库ID号，因此我将在括号内键入数字1来指示主数据库。

Let’s go ahead and execute the statement. This DMV returns information about the transaction log files. The information includes the recovery model of the database.

让我们继续执行该语句。 该DMV返回有关事务日志文件的信息。 该信息包括数据库的恢复模型。

We can see that Python2017 database is using the FULL recovery model. The other columns give the total log size in megabytes. Also, we can see some information about the last time the log was backed up. In this case, the database transaction log was never backed up, so it’s reporting as of January 1, 1900.

我们可以看到Python2017数据库正在使用FULL恢复模型。 其他列给出了总的日志大小（以兆字节为单位）。 另外，我们可以看到有关上次备份日志的一些信息。 在这种情况下，从未备份数据库事务日志，因此该报告自1900年1月1日起开始报告。

select 	 db_NAME(database_id) dbname,	 recovery_model,	 current_vlf_size_mb,	 total_vlf_count,	 active_vlf_count,	 active_log_size_mb,	 log_truncation_holdup_reason,	 log_since_last_checkpoint_mb  from 	sys.dm_db_log_Stats(5)

It is this easy to project the data for the entire database by cross-joining with the sys.databases system view. The derived output gives very useful information about the database and the log structure.

通过与sys.databases系统视图交叉联接，可以很容易地为整个数据库投影数据。 派生的输出提供有关数据库和日志结构的非常有用的信息。

select 	 dbs.name,	 b2.recovery_model,	 b2.current_vlf_size_mb,	 b2.total_vlf_count,	 b2.active_vlf_count,	 b2.active_log_size_mb,	 b2.log_truncation_holdup_reason,	 b2.log_since_last_checkpoint_mb  from   sys.databases AS dbs  CROSS APPLY sys.dm_db_log_Stats(dbs.database_id) b2  where dbs.database_id=b2.database_id

()

The sys.dm_db log_info also deals with the log files. It requires the database ID for input. This view specifically looks at virtual log files or VLFs. These make up the transaction log of the database and having a large number of VLFs can negatively impact the startup and recovery time of your database. With this view, we can see how many VLFs your database currently has, along with their size and status. This management view replaces the database console command, dbcc log info.

sys.dm_db log_info也处理日志文件。 它需要输入数据库ID。 该视图专门查看虚拟日志文件或VLF。 它们构成了数据库的事务日志，并且拥有大量的VLF会对数据库的启动和恢复时间产生负面影响。 通过此视图，我们可以查看您的数据库当前有多少个VLF，以及它们的大小和状态。 该管理视图替换了数据库控制台命令dbcc log info 。

select 		 db_NAME(database_id) dbname,		 file_id,		 vlf_begin_offset,		 vlf_size_mb,		 vlf_sequence_number,		 vlf_active,		 vlf_status	 from 		sys.dm_db_log_info(5) b

In the following sample T-SQL, we can see that the output of the DMV is joined with sys.databases, a system view to get consolidated data about all the databases of the SQL instance

在以下示例T-SQL中，我们可以看到DMV的输出与sys.databases连接在一起，sys.databases是一个系统视图，用于获取有关SQL实例的所有数据库的合并数据

The DMV sys.dm_db_log_info is explained with an example

DMV sys.dm_db_log_info 通过示例进行说明

()

The new DMV returns the detailed tuning recommendations information. This requires an enabling of the option. If the database engine identifies the plan that is causing regression then a suggestion will be made for fixing it using this dmv.

新的DMV返回详细的调整建议信息。 这需要启用选项。 如果数据库引擎识别出导致回归的计划，则将提出使用此dmv修复该计划的建议。

()

The sys.dm_tran_version_store_space_usage is used to track version store usage per database in tempdb. This is pretty useful in proactively monitoring the tempdb sizing on the version store usage requirement of each database. This DMV returns the aggregated version store space consumed in tempdb per database. It doesn’t have any arguments that need to be supplied. The results show the databaseID, the count of pages reserved in tempdb for version store records, and the total space in kilobytes.

sys.dm_tran_version_store_space_usage用于跟踪tempdb中每个数据库的版本存储使用情况。 在根据每个数据库的版本存储使用要求主动监视tempdb大小时，这非常有用。 此DMV返回每个数据库在tempdb中消耗的聚合版本存储空间。 它没有任何需要提供的参数。 结果显示了databaseID，在tempdb中为版本存储记录保留的页数以及总空间（以千字节为单位）。

For example, the Python2017 database is set with ALLOW_SNAPSHOT_ISOLATION option.

例如，Python2017数据库使用ALLOW_SNAPSHOT_ISOLATION选项设置。

ALTER DATABASE Python2017 SET ALLOW_SNAPSHOT_ISOLATION ON; CREATE TABLE SQLShackTest(id INT,name VARCHAR(20)) INSERT INTO SQLShackTest VALUES(1,'thanvitha'),(2,'SQLShack') UPDATE SQLShackTest set id=100where name='SQLShack' SELECT   DB_NAME(database_id) as 'DatabaseName',  reserved_page_count,  reserved_space_kb ,*FROM sys.dm_tran_version_store_space_usage;

( )

The next dmv is sys.dm_db_stats_histogram. It requires two arguments, object_ID and a stats_ID that we get it in sys.stats system view. This DMV is similar to dbcc show_statistics with histogram command.

下一个dmv是sys.dm_db_stats_histogram 。 它需要两个参数，即object_ID和stats_ID，我们可以在sys.stats系统视图中获取它。 此DMV与带有histogram命令的dbcc show_statistics相似。

CREATE TABLE SQLShack(id INT IDENTITY (1,1) CONSTRAINT PK_ID PRIMARY KEY,name VARCHAR(20)) INSERT INTO SQLShack(name) VALUES('SQLShack' )GO 10000 select * from sys.stats where OBJECT_ID=object_id('SQLShack') ALTER TABLE sqlshack REBUILD select * from sys.dm_db_stats_histogram (298952637,1)

Let’s take a look at the statistics for this object. The results contain details on the frequency of occurrence for each distinct value in the table. These are the values the query optimizer uses to help choose a plan when running a query. 

让我们看一下该对象的统计信息。 结果包含有关表中每个不同值的出现频率的详细信息。 这些是查询优化器在运行查询时用来帮助选择计划的值。

This particular table had 10,000 records with a unique integer in each. The rows are evaluated between the ranges 0 and 1, 1 and 9,999, and, and 10,000. So the histogram shows that data split into three steps. The first step includes the values between zero and the range high key of one. The second step includes the numbers between 1 and 9999. It shows we have 99,997 records that are within that range and one record that exactly matches the high key of 9999. Finally, the last range is for all the numbers between 9999 and 10000. There aren’t any within that range, and there is only a single one that matched the high key.

该特定表具有10,000个记录，每个记录中都有一个唯一的整数。 将在0和1、1和9,999以及10,000范围之间评估行。 因此，直方图显示数据分为三个步骤。 第一步包括介于零和范围高键1之间的值。 第二步包括1到9999之间的数字。这表明我们在该范围内有99,997条记录，而一条记录与9999的高键完全匹配。最后，最后一个范围是9999和10000之间的所有数字。不在这个范围内，只有一个与高键匹配。

()

The sys.dm_os_host_info returns information about the host operating system that the server instance is running. This will also run in Linux machines and will identify the distribution of Linux as well. 

sys.dm_os_host_info返回有关服务器实例正在运行的主机操作系统的信息。 这也将在Linux机器上运行，并且还将识别Linux的发行版。

select * from sys.dm_os_host_info

()

The sys.dm_os_sys_info returns some useful information about the resources used by, as well as available to, the SQL Server instance. The three newly added columns are:

sys.dm_os_sys_info返回有关SQL Server实例使用的资源以及对SQL Server实例可用的资源的一些有用信息。 三个新添加的列是：

• socket_count
  socket_count
• cores_per_socket
  cores_per_socket
• numa_node_count
  numa_node_count

()

The sys.dm_db_file_space_usage is the DMV that returns space usage information of the database.

sys.dm_db_file_space_usage是DMV，它返回数据库的空间使用情况信息。

The newly added column modified_extent_page_count column gives us the flexibility to manage backups. The modified page count can be used to track differential changes in the database since the last full backup to decide if a differential backup is beneficial. The column is the total number of pages modified in the allocated extents of the file since the last full database backup.

新增加的一栏modified_extent_page_count栏使我们可以灵活地管理备份。 自上次完整备份以来，修改后的页数可用于跟踪数据库中的差异更改，以确定差异备份是否有益。 该列是自上次完整数据库备份以来在文件的分配范围内修改的页面总数。

演示时间到了 (It’s demo time)

Let’s consider a backup strategy for VLDB

让我们考虑VLDB的备份策略

1. Weekly full backup
   每周完整备份
2. Daily differential backup
   每日差异备份
3. Hourly log backups
   每小时日志备份

The differential backup is a collection of modified pages since the last full backup. The DMV sys.dm_db_file_space_usage introduces a new column called modified_extent_page_count that gives us details about the modified pages since the last full backup. The value of this column is used as a reference for initiating the differential backup process.

差异备份是自上次完整备份以来已修改页面的集合。 该DMV sys.dm_db_file_space_usage引入了一个名为modified_extent_page_count新列，让我们详细了解自上次完全备份修改过的页面。 此列的值用作启动差异备份过程的参考。

1. Backup the database
   备份数据库
2. sys.dm_db_file_space_usage dmvsys.dm_db_file_space_usage dmv
3. Validate the differential change, the amount of data changes since the last full backup; we see a 14.55% change in the data
   验证差异更改，自上次完全备份以来的数据更改量； 我们看到数据有14.55％的变化
4. Build the logic to initiate a differential backup process; in our case, if there’s a change of 10% or more, a differential backup would be initiated
   建立逻辑以启动差异备份过程； 在我们的情况下，如果变化10％或更多，将启动差异备份

BACKUP DATABASE Python2017 to disk='f:\PowerSQL\Python2017.bak' SELECT CAST(ROUND((SUM(modified_extent_page_count)*100.0)/SUM(allocated_extent_page_count),2) as decimal(10,2)) as [Diff %]from sys.dm_db_file_space_usage   update [dbo].[SQLShack]  set name='SQLShack_1'The following query highlights how much data has been changed since the last full backupSELECT CAST(ROUND((SUM(modified_extent_page_count)*100.0)/SUM(allocated_extent_page_count),2) as decimal(10,2)) as [Percentage change since last backup]from sys.dm_db_file_space_usage

IF ( (SELECT CAST(ROUND((SUM(modified_extent_page_count)*100.0)/SUM(allocated_extent_page_count),2) as decimal(10,2)) as [Percentage Diff changes since last backup]from sys.dm_db_file_space_usage) >10)BACKUP DATABASE Python2017 to disk='f:\PowerSQL\Python2017.bak'  WITH DIFFERENTIAL

结语 (Wrapping up)

The overview of new dynamic management views and dynamic management functions that ship with SQL Server 2017 have been explained in detail. They can be put to use to get more insight into the status of the system. It can be informative and useful for the administrators to understand the metadata pertaining to the SQL Server instance. To me, the sys.dm_db_file_space_usage and sys.dm_db_tuning_recommendations turns out to be something I was looking for, for a long time. How about you? Please feel free to comment!

SQL Server 2017附带的新动态管理视图和动态管理功能的概述已详细说明。 可以使用它们来更深入地了解系统状态。 对于管理员来说，了解与SQL Server实例有关的元数据可能是有用的信息。 对我来说，很长一段时间以来， sys.dm_db_file_space_usage和sys.dm_db_tuning_recommendations都是我一直在寻找的东西。 你呢？ 请随意发表评论！

目录 (Table of contents)

Top 8 new (or enhanced) SQL Server 2017 DMVs and DMFs for DBAs

适用于DBA的前8大新（或增强）SQL Server 2017 DMV和DMF

翻译自:

dmv io读写高的sql