I am running SQL Server 2008 Enterprise Edition and want to monitor the following performance metrics i.e. via dynamic management views (from within SQL):
我正在运行SQL Server 2008 Enterprise Edition，并希望监视以下性能指标，即通过动态管理视图（在SQL中）：
Average/Maximum Read/Write I/O Waits in ms per database file for sliding time window.
对于滑动时间窗口，每个数据库文件的平均/最大读/写I / O等待时间（毫秒）。
That is: 4 numbers per database file: avg read wait, max read wait, avg write wait, max write wait. All in ms, and all for some sane (or even better configurable) sliding time window.
即：每个数据库文件4个数字：avg read wait，max read wait，avg write wait，max write wait。全部以ms为单位，全部用于一些理智（甚至更好的可配置）滑动时间窗口。
How can I do that?
我怎样才能做到这一点？
PS: I have the VIEW SERVER STATE permission and can read sys.dm_os_performance_counters, sys.database_files, sys.dm_io_virtual_file_stats etc etc
PS：我有VIEW SERVER STATE权限，可以读取sys.dm_os_performance_counters，sys.database_files，sys.dm_io_virtual_file_stats等等
PS2: At least 1 tool (Quest Spotlight 7 for SQL Server) is able to provide Max I/O Wait in ms per database file. So there has to be some way ..
PS2：至少有一个工具（SQL Server的Quest Spotlight 7）能够以每个数据库文件的毫秒数提供最大I / O等待。所以必须有一些方法..
4 个解决方案
#1
4
Below is the query that SSMS's Activie Monitor uses. They label the io_stall field as total wait time. You could add the fs.io_stall_read_ms and fs.io_stall_write_ms fields to get the read/write specific numbers.
以下是SSMS的Activie Monitor使用的查询。它们将io_stall字段标记为总等待时间。您可以添加fs.io_stall_read_ms和fs.io_stall_write_ms字段以获取读/写特定数字。
SELECT
d.name AS [Database],
f.physical_name AS [File],
(fs.num_of_bytes_read / 1024.0 / 1024.0) [Total MB Read],
(fs.num_of_bytes_written / 1024.0 / 1024.0) AS [Total MB Written],
(fs.num_of_reads + fs.num_of_writes) AS [Total I/O Count],
fs.io_stall AS [Total I/O Wait Time (ms)],
fs.size_on_disk_bytes / 1024 / 1024 AS [Size (MB)],
fs.io_stall_read_ms
FROM sys.dm_io_virtual_file_stats(default, default) AS fs
INNER JOIN sys.master_files f ON fs.database_id = f.database_id AND fs.file_id = f.file_id
INNER JOIN sys.databases d ON d.database_id = fs.database_id;
This query only gives you the totals. You'd have to run it at some interval and record the results in a temp table with a time stamp. You could then query this table to get your min/max/avg as needed. The sliding time window would just be a function of how much data you keep in that table and what time period you query.
此查询仅为您提供总计。您必须以某个间隔运行它并将结果记录在带有时间戳的临时表中。然后，您可以根据需要查询此表以获取最小值/最大值/平均值。滑动时间窗口只是您在该表中保留的数据量以及查询的时间段的函数。
#2
2
The problem you are going to have is that SQL doesn't necessarily track the level of detail you are looking to get per file. You are probably going to have to use Performance monitor as well. You will have to use a combination approach looking at both performance monitor for details on I/O on the disk over the course of time. As well as the aforementioned SQL monitoring techniques to see get a more complete complete picture. I hope that helps.
您将遇到的问题是SQL不一定跟踪您希望获得的每个文件的详细程度。您可能还必须使用性能监视器。您将不得不使用组合方法查看性能监视器，以了解磁盘上I / O的详细信息。以及前面提到的SQL监控技术，可以看到更完整的图片。我希望有所帮助。
#3
2
You can use a few scripts to pull the metrics. I have enebled the Data Collection/Data Collection server built in to SQL Server 8. It collects the metrics from multiple instances and stores them in a mssql server you designate as the collector. The reports provided for each instance are adequate for most purposes. I am sure there are 3rd party tools that go beyond the capabilities of the data collector as it just reports on performance/disk usage/and query statistics without performance hints.
您可以使用一些脚本来提取指标。我已经使SQL Server 8内置的数据收集/数据收集服务器变得困难。它从多个实例收集指标并将它们存储在您指定为收集器的mssql服务器中。为每个实例提供的报告足以满足大多数目的。我确信有第三方工具超出了数据收集器的功能，因为它只是在没有性能提示的情况下报告性能/磁盘使用情况/查询统计信息。
This gives you a data file growth projection and growth event summary for both logs and data files, however, I do not know if it will give you the metrics you are looking for per file or filegroup:)
这为日志和数据文件提供了数据文件增长预测和增长事件摘要，但是，我不知道它是否会为您提供每个文件或文件组所需的指标:)
NOTE: If you use the data collection warehouse you should consider rebuilding indexes periodically as the size grows. It collects approx. 20 MB/day of data in my senario.
注意：如果使用数据收集仓库，则应考虑随着大小的增加定期重建索引。它收集约。我的senario中每天20 MB的数据。
#4
2
See the following for collecting per file wait statistics http://msdn.microsoft.com/en-us/library/ms187309(v=sql.105).aspx
有关每个文件的收集请参阅以下内容等待统计信息http://msdn.microsoft.com/en-us/library/ms187309(v=sql.105).aspx
#1
4
Below is the query that SSMS's Activie Monitor uses. They label the io_stall field as total wait time. You could add the fs.io_stall_read_ms and fs.io_stall_write_ms fields to get the read/write specific numbers.
以下是SSMS的Activie Monitor使用的查询。它们将io_stall字段标记为总等待时间。您可以添加fs.io_stall_read_ms和fs.io_stall_write_ms字段以获取读/写特定数字。
SELECT
d.name AS [Database],
f.physical_name AS [File],
(fs.num_of_bytes_read / 1024.0 / 1024.0) [Total MB Read],
(fs.num_of_bytes_written / 1024.0 / 1024.0) AS [Total MB Written],
(fs.num_of_reads + fs.num_of_writes) AS [Total I/O Count],
fs.io_stall AS [Total I/O Wait Time (ms)],
fs.size_on_disk_bytes / 1024 / 1024 AS [Size (MB)],
fs.io_stall_read_ms
FROM sys.dm_io_virtual_file_stats(default, default) AS fs
INNER JOIN sys.master_files f ON fs.database_id = f.database_id AND fs.file_id = f.file_id
INNER JOIN sys.databases d ON d.database_id = fs.database_id;
This query only gives you the totals. You'd have to run it at some interval and record the results in a temp table with a time stamp. You could then query this table to get your min/max/avg as needed. The sliding time window would just be a function of how much data you keep in that table and what time period you query.
此查询仅为您提供总计。您必须以某个间隔运行它并将结果记录在带有时间戳的临时表中。然后，您可以根据需要查询此表以获取最小值/最大值/平均值。滑动时间窗口只是您在该表中保留的数据量以及查询的时间段的函数。
#2
2
The problem you are going to have is that SQL doesn't necessarily track the level of detail you are looking to get per file. You are probably going to have to use Performance monitor as well. You will have to use a combination approach looking at both performance monitor for details on I/O on the disk over the course of time. As well as the aforementioned SQL monitoring techniques to see get a more complete complete picture. I hope that helps.
您将遇到的问题是SQL不一定跟踪您希望获得的每个文件的详细程度。您可能还必须使用性能监视器。您将不得不使用组合方法查看性能监视器，以了解磁盘上I / O的详细信息。以及前面提到的SQL监控技术，可以看到更完整的图片。我希望有所帮助。
#3
2
You can use a few scripts to pull the metrics. I have enebled the Data Collection/Data Collection server built in to SQL Server 8. It collects the metrics from multiple instances and stores them in a mssql server you designate as the collector. The reports provided for each instance are adequate for most purposes. I am sure there are 3rd party tools that go beyond the capabilities of the data collector as it just reports on performance/disk usage/and query statistics without performance hints.
您可以使用一些脚本来提取指标。我已经使SQL Server 8内置的数据收集/数据收集服务器变得困难。它从多个实例收集指标并将它们存储在您指定为收集器的mssql服务器中。为每个实例提供的报告足以满足大多数目的。我确信有第三方工具超出了数据收集器的功能，因为它只是在没有性能提示的情况下报告性能/磁盘使用情况/查询统计信息。
This gives you a data file growth projection and growth event summary for both logs and data files, however, I do not know if it will give you the metrics you are looking for per file or filegroup:)
这为日志和数据文件提供了数据文件增长预测和增长事件摘要，但是，我不知道它是否会为您提供每个文件或文件组所需的指标:)
NOTE: If you use the data collection warehouse you should consider rebuilding indexes periodically as the size grows. It collects approx. 20 MB/day of data in my senario.
注意：如果使用数据收集仓库，则应考虑随着大小的增加定期重建索引。它收集约。我的senario中每天20 MB的数据。
#4
2
See the following for collecting per file wait statistics http://msdn.microsoft.com/en-us/library/ms187309(v=sql.105).aspx
有关每个文件的收集请参阅以下内容等待统计信息http://msdn.microsoft.com/en-us/library/ms187309(v=sql.105).aspx