question

gotqn avatar image
gotqn 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.
administrationtoolsclient-toolsthird-party-tools
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

First of all, there are no lame questions :) Second of all, start with the built-in Windows Performance Monitor to see if that would be enough for you. It's the easiest solution, I think, for you.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
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

activitymonitor.png (16.3 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

gotqn avatar image
gotqn answered
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]
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.