WARNING: This may be very obvious to most of you. However, several people that I have encountered did not realize this could be done. Therefore, I decided to share this.
For a DBA the SQL Server Profiler is a must have tool in his/her arsenal of items when trying to solve problems. Recently, I gave a presentation on the Profiler at SQL Saturday in Baton Rouge, LA. The topic was “An Introduction to the SQL Server Profiler”. There were approximately 40 attendees and of those in attendance about 95% were developers. There were a few DBAs in the audience.
I started the talk by discussing concepts pertaining to profiler terminology, trace events, trace categories, etc… Then I moved on to filtering traces. I discussed a couple of the obvious trace filters such as: SPID, database, and duration. Then as expected one of the attendees asked, “Can you filter a trace by text within a query”. Not exactly like that, but once I finished my probing that is what it summed up to be. One of the DBAs in the audience, to my surprise, immediately said, “You can’t do that”. Hmmmm, I thought.
In my experiences as a DBA, especially at my current place of employment, I have become very proficient in using the profiler. The primary Operational Database that I am responsible for uses mostly Dynamic SQL, which makes my life difficult (sometimes). Therefore, I cannot use the Stored Procedure events and filter on the Object Name or the ObjectID.
I digress. I said to the DBA of little faith, “sure you can”. I opened the Profiler and created a trace that only contained the SQL:BatchCompleted event from the TSQL trace category. I only chose the TextData column from the list of available columns, and of course the SPID must be displayed.
Then I clicked the Column Filters Button and added %select *% and %select*% to the TextData filter under the LIKE choice. The attendee wanted to find all the queries that included a select *. Notice that I added two variations of the request. I did this because it is syntactically correct to issue either statement, and I wanted to ensure that all variations of the request were captured by the trace. Like this:
Notice that I added the %, which is a wild card character for T-SQL, to the beginning and end of each filter. As expected, it behaved just as it does when used in a T-SQL query. After running a couple of queries that contained both filters the results where:
As you can see the trace captured both of the statements. Surprisingly, not one person in attendance realized that the SQL Profiler was capable of performing such a filter. As always please provide your comments, thoughts, criticisms and ideas regarding this.
Talk to you soon
Patrick LeBlanc, Founder TSQLScripts.com
SQLDownSouth
No comments:
Post a Comment