Statistics Profile Output Formatting
Statistics profile output is an important tool when it comes to troubleshooting query plan issues. When enabled, it returns a textual representation of the query plan with a lot of detail about cost and cardinality estimates as well as actual counts.
When working in SQL Server management Studio (SSMS), it is advised to enable grid mode to output the results of statistics profile. This presents the results in a neatly formatted way. It also allows to easily copy and past the result in Excel for further manipulation (hiding columns, highlighting individual cells, adding columns with additional calculations, …). But what if grid output mode is not available? E.g., when running SQL Trace with an event to produce statistics profile output for each query, the result is always presented as flat text. For even mildly complex queries this output is hard to decipher.
The attached perl script helps to solve this formatting problem. It takes the flat text output of statistics profile and parses it to identify individual columns. It then generates tab delimited output which can be directly consumed by Excel.
Usage is straightforward: the script assumes one individual statistics profile in a regular text file (ANSI text only please, UNICODE output confuses the script). Just run
perl statspro.pl <input file> <output file>
E.g., lets assume I have my statistics profile output in a file called test.txt I’ll run
perl statspro.pl test.txt test.xls
The resulting xls file can be directly viewed with Excel.
Note that I’m not a perl wizard and there might be a more elegant/efficient way to achieve the same means. The script is provided “as is”. You are more than welcome to use and/or enhance the script.
Peter Zabback