SQL Server troubleshooting – first steps in troubleshooting performance of someTSQL statements

Goal

Road map of steps that I can use to troubleshoot the performance of a TSQL statement or a SP and keep track of the things discovered on the way for future reference

My way of achieving it

Using scripts that I gathered or taken from what other people shared over time over the internet and by scripts that I created.

first steps troubleshootingV1

1. The script can be found in this book, SQL Server DMVs in Action by Ian W. Stirk right under — Listing 3.6 Identifying indexes used by a given routine
2. The script can be downloaded from here
3. The script can be downloaded from here
4. The script can be downloaded from here
5. The script can be downloaded from here
6. Check the execution plan of the routine in XML format and inside search after PARAMETERCOMPILEDVALUE. Check if the values from there are identical with the values that the application is using or the job.

In most of the cases the index optimization or updating the statistics will help us and maybe will solve the issue but in other cases as the post stated the above steps are just the beginning of troubleshooting.

SQL Server Instance Information – physical location, volume name, disk name and number, disk information

Goal

Obtaining information about the SQL Server physical location where the databases are and displaying for those locations the volume name, disk name and disk information

My way of achieving it

Using SSMS, xp_cmdshell and DISKPART.

In order to not use detail keyword in the script when constructing the xp_cmdshell and diskpart command I decided to us the varbinary representation of it in the script.
SELECT CONVERT(VARBINARY(MAX), ‘detail’) AS [VARBINARY], ‘varbinary representation of DETAIL keyword’ AS Info
SELECT CONVERT(VARCHAR(MAX), 0x64657461696C)AS [VARCHAR], ‘varchar representation of 0x64657461696C’ AS Info

VARBINARY – 0x64657461696C – varbinary representation of DETAIL keyword
VARCHAR – detail – varchar representation of 0x64657461696C

Before any comments about the script I want to say how important is to verify the script before execution and to run it first only on a test server or virtual machine and to run it on other systems only after you create an encrypted version of it.

Sometimes when

– we have to increase a disk where SQL Server databases are, we need to provide information about that disk to other team in order for them to increase the disk

– we have to find more information about a disk from SAN team in case we have some performance issues and those issues are tracked back to I/O latency

– we want to just display the information of disk, volumes, type, size, free in only one window from inside SSMS

The idea of having something like this came to live after I was searching how to map disks and volumes in powershell and I found the script posted by Adam Conkle ( Thanks for the script ) , here, https://gallery.technet.microsoft.com/DiskPartexe-Powershell-0f7a1bab. I realized that I can use the same approach that he uses to do the same but from inside SQL Server.

The script that I am using to obtain the output below can be downloaded from the link provided at the end of this post.

mapvtodandsqll

The problem is that diskpart requires administrators privileges which means that the SQL Service account must be a member of local administrators group on the server in order for the script to function or the implementation of a proxy account that has administrative privileges at OS level. More than that is advisable to have the script created as a SP and encrypt it in order to be sure that no one will replace those keywords, detail disk or detail volume with other ones like delete which can compromise or in other words to destroy the server. I have tried to replace the keywords to its varbinary representation because I considered the script much safer than having the script using the DETAIL keyword.

The script can be downloaded from here

SQL Server cluster – checking if SQL Server clustered instance is running on preferred node

Goal

Obtaining information about the SQL Server clustered instances and if they are running on the preferred node

My way of achieving it

Using SSMS, xp_cmdshell and WMIC. When we have to check and see if our clustered instances are running on the preferred node we can use different approaches and each approach has its own benefits in comparison with the other approaches. In my case because I am used to work more with SQL Server, the usage of SSMS, xp_cmdshell and WMIC is more appealing than everything else and I chose it to find that information.

The script is using:

– 1 WMIC query that uses MSCluster_ResourceGroupToResource – used to find all the groups that are related to SQL Server clustered instances

– 1 WMIC query that uses MSCluster_ResourceGroupToPreferredNode – used to find the preferred node for all the groups

The output returned by the first query is used to generate a result set of group names and the name of the SQL Server clustered instance that is found in each group.

GroupName  InstanceName
SQL11 SQL11\I11

The output above is used to actually generate the script that we will have to ran in SSMS after enabling the SQLCMD mode. Below is an example of the generated script.

:CONNECT APPDKBA1162

DECLARE @preferrednodes TABLE (line nvarchar(500))
DECLARE @InstanceInformation TABLE (InstanceName nvarchar(100), RunsON nvarchar(50), IsPreferredNode nvarchar(3), Comments nvarchar(max))
DECLARE @groupsandinstance TABLE (GroupName nvarchar(500), InstanceName nvarchar(100))
DECLARE @GroupName nvarchar(50)

BEGIN TRY

INSERT INTO @groupsandinstance VALUES (‘SQL Server (MSSQLSERVER)’,’APPDKBA1162′)
INSERT INTO @groupsandinstance VALUES (‘SQL11′,’SQL11\I11’)

SELECT @GroupName = GroupName FROM @groupsandinstance WHERE InstanceName = @@SERVERNAME
INSERT INTO @preferrednodes EXEC xp_cmdshell ‘wmic /namespace:\\root\mscluster path MSCluster_ResourceGroupToPreferredNode’
UPDATE @preferrednodes SET line = REPLACE(line,CHAR(13),”)

IF (SELECT COUNT(line) FROM @preferrednodes WHERE line like ‘%.Name%’ AND line like ‘%’+@GroupName+’%’) > 1
INSERT INTO @InstanceInformation
SELECT CONVERT(nvarchar(50),@@SERVERNAME) AS InstanceName, CONVERT(nvarchar(50),SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)),
(SELECT TOP 1 CASE WHEN CHARINDEX(CONVERT(nvarchar(100),SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)), line) > 0 THEN ‘YES’ else ‘NO’ END FROM @preferrednodes WHERE line like ‘%.Name%’ AND line like ‘%’+@GroupName+’%’ ),”
ELSE
INSERT INTO @InstanceInformation
SELECT CONVERT(nvarchar(50),@@SERVERNAME) AS InstanceName, CONVERT(nvarchar(50),SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)),
(SELECT CASE WHEN CHARINDEX(CONVERT(nvarchar(100),SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)), line) > 0 THEN ‘YES’ else ‘NO’ END FROM @preferrednodes WHERE line like ‘%.Name%’ AND line like ‘%’+@GroupName+’%’ ),”

SELECT * FROM @InstanceInformation

END TRY
BEGIN CATCH
INSERT INTO @InstanceInformation SELECT CONVERT(nvarchar(50),@@SERVERNAME) AS InstanceName, CONVERT(nvarchar(50),SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)) AS RunsON,” AS IsPreferredNode,(select ERROR_MESSAGE()) AS Comments
SELECT * FROM @InstanceInformation
END CATCH

GO

:CONNECT SQL11\I11

……….

Below you can see the output of the script

iropn_1

The script can be downloaded from here

SQL Server troubleshooting – what happened with my instance

Goal

Obtaining information about what happened during my last restart of SQL Server instance and if this was a normal restart or an unexpected restart

My way of achieving it

Using SSMS, xp_cmdshell and Wevtutil  we can achieve the goal and we can overcome some of the challenges we face in case we are after the same information using some of the other approaches. I have chosen to not use  SQL Server Error logs because of the

– cycling of the logs during each SQL server instance restart or by using the sp_cycle_errorlog in our environments

– information inside the logs doesn’t contain information outside of SQL Server

I want to mention that the execution time of the script is influenced by the information captured and kept in the windows application logs and the reason for which I decided to display by default 1 sql server restart times.

In case of a clustered instance we have to provide the credentials that will be used to ran the Wevutil remotely and another mention as important as first one is that the script is working only for 2 nodes cluster and in case we would like to run it on a clustered instance that has more than 2 nodes then we need to tweak the script a little bit.

The output of the script is shown below

irmi

We can use the information to check what happened during last restart and see what types of errors , critical, warnings appeared in the last 60 minutes before the last restart in the system and application log.

The script can be downloaded from here

SQL Server troubleshooting – what happened with my database

Goal

Obtaining in the same SSMS window, information about why a database might have been unavailable or having its status changed.

My way of achieving it

Almost all of the monitoring deployed solutions these days raise tickets every time a database becomes unavailable and from the DBA operational point of view this means that we must connect and check what happened with the database and take the required actions. Of course that depending of the monitoring implementation some tickets will be closed automatically if during the second check the database becomes online but this doesn’t mean that we don’t need to try to see why the database status changed. Although we can do our investigation using GUI tools that SQL Server provides this approach has some limitations that the script used here tries to overcome. Below are some of the limitations:

– the account we use to connect to the server where SQL Server instance is running might not have rights to access the location where default trace files are stored which will make almost impossible the usage of SQL Server Profiler

– filtering or searching of SQL Server Error log files using SQL Server Log File Viewer was not designed to search or filter after multiple strings in the same time which makes the filtering or searching of the logs after string a and after string b impossible.

Because of this and other limitations I turned my attention and I tried to find other ways of searching and filtering SQL Server Error Log files and default trace and display the required information in only one window.

The output of the script in some cases will provide us enough information to see what happened while in other cases might give us only the name of the logins that were performing activities during that time.

dbstatus

The main benefit of this approach is that we can have in one window the information pertaining to that database from SQL Server Error log files and the default trace.

The script can be downloaded from here

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