asked
SQL Server Tool showing performance
I am note familiar with SQL Server administration and I am looking for a tool that is showing the current IO/RAM/CPU activity for a particular SQL Server Instance. What I am trying to find is how a particular optimization deployed on all databases in particular instance will improve the IO/RAM/CPU activity. For, example, I want to create about 15 new indexes. I have already tested them on particular database and I am sure that they are appropriate covering indexes used in queries constantly (thousands and even millions of times each day). That's why I believe the IO activity will be improved, but need to prove that as well. I am looking for a free tools mostly (something integrated in the SQL Server Management Studio will be great). I am sorry for the lame question, but I am doing mostly programming, not administrating.
You have a number of options available through Windows and SQL Server. You can capture Performance Monitor metrics that will show you disk and cpu queues as well as page requests, among a million other things. You can also use dynamic management views within SQL Server to see things like wait statistics through sys.dm_os_wait_stats, or query times through sys.dm_exec_query_stats. You can also capture query performance metrics using extended events. All of those are free and built right into SQL Server and the Windows Operating system.
You might also find some useful information in the SQL Server Activity Monitor. Right click on your server name in SSMS and select it from the pop-up menu items![alt text][1] [1]: /storage/temp/1588-activitymonitor.png
Based on the answers I am going to use the following query: SELECT DB_NAME(FS.[database_id]) AS [Database] ,IIF(FS.[file_id] = 2, 'LOG', 'DATA') AS [File Type] ,UPPER(SUBSTRING(MF.[physical_name], 1, 2)) AS [DISK] ,FS.[num_of_reads] AS [number of reads] ,FS.[num_of_bytes_read] / (1024.0 * 1024.0) AS [number of read bytes (MB)] ,FS.[io_stall_read_ms] / (1000 * 60) AS [wait time for read (MM)] ,FS.[num_of_writes] AS [number of writes] ,FS.[num_of_bytes_written] / (1024.0 * 1024.0) AS [number of written bytes (MB)] ,FS.[io_stall_write_ms] / (1000 * 60) AS [wait time for write (MM)] ,FS.[io_stall_read_ms] / FS.[num_of_reads] AS [read latency] ,FS.[io_stall_write_ms] / FS.[num_of_writes] AS [write latency] ,(FS.[num_of_bytes_read] / FS.[num_of_reads]) / (1024.0 * 1024.0) AS [AVG (MB) per read] ,(FS.[num_of_bytes_written] / FS.[num_of_writes]) / (1024.0 * 1024.0) AS [Avg (MB) per write] FROM [sys].[dm_io_virtual_file_stats] (DB_ID(), NULL) FS INNER JOIN [sys].[master_files] MF ON FS.[database_id] = MF.[database_id] AND FS.[file_id] = MF.[file_id]
17 People are following this question.
