SQL Server Profiling

Considering that Gibson Pickups are fresh on my mind, I’d like to delve deeper into SQL Server Profiling. While the Profiler provides immensely helpful data, and while I love parameterized SQL (for all the reasons that it keeps me and the rest of the world safer), the combination of the two of these provide rather inconvenient means for debugging poorly performing queries.

I have a query which performs quite poorly on one server, yet very quickly on another. It has 212 arguments. Also, it’s not any old “IN” statement where I can throw in a comma delimited list of items, there’s around 212 “IN” statements, each of which take a single argument…so simply rewriting this query correctly by hand isn’t very easy.

SQL Server Profiler does a good job of recording this query, but it’s completely broken up into the raw SQL and the parameters being passed, just like Java passed along.

Next, to profile…when using SQL Server’s functionality to display the Execution Plan (with the raw SQL and parameters that were provided through the Profiler) it tells us pretty much nothing since it’s all executed under the guise of a stored procedure.

Thanks for the help!

To combat this I wrote some quick and dirty Python code to throw this script into a legit SQL query.

https://github.com/jasonmckim/convert-sp_executesql

We can then take the output from the execution of that Python code and check out the actual execution plan of the query, giving some actual sensible information to further debug and figure out what the real problem-of-the-day is today.