SQL Server troubleshooting using performance counters

Goal

Sometimes when I need to troubleshoot one local or remote SQL Server instance I need to have information from inside SQL Server instance but also information outside of  it in order to start my investigation or take some conclusions. Most of the time this is a very time consuming operation and not always straight forward hence I the need to have something that I can re-utilize over and over again in these situations.

My way of achieving it

After searching and seeing what other people are doing when it comes to this I decided that I can combine some of the ideas found and put my own ideas in one script that I can use to have access to performance counters outside of  SQL Server but obtainable from SQL Server. I already mentioned in another post and I would like to mention it again that every script that I will post might have flaws or shortcomings and should be perceived as such. More than that the scripts can be considered the result of collective effort of different people  from the internet since I am taking and using what they were sharing over the internet.

The output of the script provides this type of information but the script can be modified to return the kind of information that you would like to have and use.  For me is important when troubleshooting a SQL Server instance to know:

– the processor utilization

– available memory

– disk utilization for the drives where sql server has files

– network utilization

Counter    Value
“\\WIN-666BDQE0KVL\Memory\Commit Limit”    4292546560
“\\WIN-666BDQE0KVL\Memory\Available MBytes”    1511
“\\WIN-666BDQE0KVL\Network Interface(Intel[R] PRO_1000 MT Network Connection)\Output Queue Length”    0
“\\WIN-666BDQE0KVL\Network Interface(isatap.{F5634C4F-D7A9-4921-924B-C112B6BC5377})\Output Queue Length”    0
“\\WIN-666BDQE0KVL\Network Interface(Local Area Connection* 11)\Output Queue Length”    0
“\\WIN-666BDQE0KVL\Network Interface(Intel[R] PRO_1000 MT Network Connection)\Bytes Total/sec”    0
“\\WIN-666BDQE0KVL\Network Interface(isatap.{F5634C4F-D7A9-4921-924B-C112B6BC5377})\Bytes Total/sec”    0
“\\WIN-666BDQE0KVL\Network Interface(Local Area Connection* 11)\Bytes Total/sec”    0
“\\WIN-666BDQE0KVL\Processor(_Total)\% User Time”    -1
“\\WIN-666BDQE0KVL\Processor(_Total)\% Privileged Time”    0
“\\WIN-666BDQE0KVL\Processor(_Total)\% Processor Time”    0
“\\WIN-666BDQE0KVL\LogicalDisk(C:)\Current Disk Queue Length”    0
“\\WIN-666BDQE0KVL\LogicalDisk(G:)\Current Disk Queue Length”    0
“\\WIN-666BDQE0KVL\Process(_Total)\Page File Bytes”    2915581952
“\\WIN-666BDQE0KVL\Process(sqlservr#3)\Page File Bytes”    434040832
“\\WIN-666BDQE0KVL\Process(sqlservr#3)\% User Time”    0
“\\WIN-666BDQE0KVL\Process(sqlservr#3)\% Privileged Time”    0
“\\WIN-666BDQE0KVL\Process(sqlservr#3)\% Processor Time”    0

typeperf

I will not explain here how the above output should be used since the goal of the post was only to provide a method to obtain performance counters outside of  SQL Server

The script can be downloaded from here

Interpreting sp_WhoIsActive stored procedure output for beginers

I will start my first post by apologizing in advance for any mistakes that most probably I will do but I hope that I will learn and educate myself on the way.

The idea behind this post came to me after I first heard about the stored procedure that Adam Machanic wrote and after I saw how useful was when troubleshooting or seeing what is happening to the SQL Server. Because depending on our role in the company and our day to day activities,  I realized that I need to have something that will refresh the meaning of the output till this output will become a second nature for me.  This was my attempt to take some of the information that he already made available in his blog ( http://sqlblog.com/blogs/adam_machanic/archive/2011/04/01/a-month-of-monitoring-part-1-of-30-a-brief-history-of-monitoring.aspx ) and put them in a format that in the beginning was easier for me to understand and communicate it to other colleagues. As with everything that I will post here this was my attempt and of course it has some flaws, some shortcomings but overall I believe it makes some sense for someone that is learning the output of this stored procedure and how to use it in the beginning. In order to use it we have to follow these steps

1. Create the stored procedure using the latest version of the SP from here http://sqlblog.com/files/default.aspx

2. Create a powershell script using the code provided at the end of this post.

3. Run the stored procedure with the parameters that you want but using this output column list. This is a prerequisite because the last columns depending on the parameters received by the SP will contain more than one line and the script cannot parse it correctly. We want to have those columns at the end in order to select all the columns expect those ones.

exec sp_whoisactive
@output_column_list = ‘[session_id][dd hh:mm:ss.mss][dd hh:mm:ss.mss (avg)][physical_io][reads][physical_reads][writes][tempdb_allocations][tempdb_current][CPU][context_switches][used_memory]
[physical_io_delta][reads_delta][physical_reads_delta][writes_delta][tempdb_allocations_delta][tempdb_current_delta][CPU_delta][context_switches_delta][used_memory_delta][tasks]
[status][wait_info][locks][tran_start_time][tran_log_writes][open_tran_count][blocking_session_id][blocked_session_count][percent_complete][host_name][login_name][database_name]
[program_name]start_time][login_time][request_id][collection_time][additional_info][sql_text][sql_command]’

4. Run the powershell script but not using the ISE. We will be prompted to select the command that you ran in SSMS and press enter after you made it available in the clipboard

PS C:\Users\Administrator\Downloads> .\sp_whoisactive10august2015.ps1
Select the command that you ran in SSMS and copy it in order to be available in the clipboard (Ctrl+C).
Press Enter to continue …:

5. After pressing enter we will have to provide the output of the command  by providing also the column names. Usually I am selecting only one row and all the column headers except the additional_info, sql_text and sql_command since these as we mentioned before, sometimes, have more lines and are not parsed right by the script

6. After pressing enter again the output is parsed and it provides more information about the columns and what those means

Below is a picture with step 4, 5 and 6 and because the script is not displaying the query the first line seems to be out of context since we are not pasting the query that is captured in the columns (sql_text or sql_command).

sp_whoisactive

The script can be downloaded from here