SQL Server troubleshooting – first steps in troubleshooting performance of some TSQL statements (part 3)

Goal

To obtain information about the job names that ran during that interval

My way of achieving it

By querying the history table and by displaying the information in the native format followed by the display using the rows as columns. The last way of displaying the information might have in some cases more meaning that the information that is displayed by default when we check the job history.

The output looks similar to this

JobsInfoPivot

The script can be downloaded from here

SQL Server troubleshooting – first steps in troubleshooting performance of some TSQL statements (part 2)

Goal

To obtain the nested list of TSQL statements (SELECT) from the most inner one to the outer one

My way of achieving it

Using the approach explained here for parsing parenthesis expressions.  As you will about to see I am using his unchanged code in my script together with my own additions to extract the select statements and to check for open parenthesis which are not closed. The existence of any open close parenthesis in the comments will break the cast to XML of the code that we want to troubleshoot.  That is why along the code that parses parenthesis expressions I added some logic to remove the comments. After some tests I realized that I need an workaround or better said a real solution for comments removal from the code and the solution came under the form of ScriptDom namespace which provides Microsoft SQL Server-specific Parser and Script Generator.  I left in the tsql script the logic for removing comments but if after execution of the script you still receive errors like

Msg 9400, Level 16, State 1, Line 387
XML parsing: line 227, character 8, unexpected end of input

Then please use the powershell script to clean the code  from comments and once you have it paste it to the tsql script for a successful cast to XML.

The output of the script , below is an example, shows in messages the list of statements from the most inner one to the outer one in an easy format that can be taken and executed step by step and we can see for each the execution plans and statistics and how long it takes and we can modify them to aim for some improvement. Of course that this might look like trivial for an experienced dba but I believe is a great way to troubleshoot in the beginning after none of the steps explained here solved the issue. This approach has its benefits as  other approaches have theirs and there are times when we have to use one or another.

innertooutter

The scripts can be downloaded from:

    • tsql script – has limited logic for comments removal
  • ps script to remove comments – in case we still receive errors when executing the tsql script
    • the powershell script above requires 2 dll files in order to work, Microsoft.Data.Schema.ScriptDom.dll, Microsoft.Data.Schema.ScriptDom.Sql.dll

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.