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.
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