Administering a SQL Database Infrastructure — Question 151

You manage a SQL Server 2014 instance that contains a database named DB1.
Users report that some queries to DB1 take longer than expected.
Although most queries run in less than one second, some queries take up to 20 seconds to run. You need to view all of the performance statistics for each database file.
Which method should you use?

Answer options

• A. Query the sys.dm_os_tasks dynamic management view.
• B. Query the sys.dm_os_performance_counters dynamic management view.
• C. Query the sys.dm_io_virtual_file_stats dynamic management function.
• D. Examine the Data File I/O pane in Activity Monitor.

Correct answer: C

Explanation

The correct answer is C because querying the sys.dm_io_virtual_file_stats dynamic management function provides specific I/O statistics for each database file, which is essential for identifying performance issues. The other options either provide general task information, performance counters, or I/O metrics that do not directly relate to individual database file performance.