SQL Server profile is misleading when you look at it under the All Programs listing. It states that SQL Server Profile is part of the, “Performance Tools” set. OK, yeah it really is primarily a performance tool for DBAs. There’s something else that it does though that is a pretty handy friend when either writing reports on third party databases or trouble shooting issues with application not functioning.
Let’s say a user comes to you and asks for a report “just” like that one in “this” screen. They just want a summarized column or maybe that one column that they can’t get at in the applications reporting tools. What do you do? You start looking at the table designs and SELECT *ing the living hell out of your database server to the point you are actually the one user in which you try so hard to prevent from causing issues. What are you doing man???
OK, what does SQL Server Profiler do? It does exactly what you need to determine the location and design of the database.schema.tables behind the application for starters. Think about the fact that if you had a user that you just couldn’t figure out why they take the server to its knees at 8:03 PM nightly. You open profiler about 10 minutes before, log the batches to a table or files and then query it up after the server is back to life once the user did the mystical, “I don’t do anything” magic once again. In that result set you find the user, the time and the query that you need to help you along or give suggestive criteria for other means to the task being completed.
Now think about that same scenario but in the aspect of you writing a query on a database you know little about. User wants it to look “just” like “this” screen! Start profiler and use the filters in profiler to your advantage then. Say I’m a DBA helping the user (isn’t that fun?), The user I log in under is MeDBA_CanDOAnything and you already know that the screen in the application is working off at the least, customers table. First thing is open profiler and hit the trace properties button. Go to the events selection and select the ApplicationName filter. Throw the application name in there and then go to LoginName and put in MeDBA_CanDOAnything. Now go to TextData and put your typical wildcard phrase of %customers%. Hit ok and then run that thing!
Open the application now and go to the screen where you run what is it that is “just” like what “they” need. Watch the query fly by in profiler once you do that. Stop profiler and find the row that has some relevant value to the screen you executed. Something around customer order number or customer number etc… If you saved these to a table, just write a select to find the rows. There you have it! Now if you’re lucky, adding the column to this is already there and in the tables in the query already written for you. If you not, then all I can say is 99.9% of your work was just done for you so I’d still be pretty damn happy.
WARNINGS! It’s my duty as a DBA to tell you these points.
- DO NOT run profiler on a production system during heavy load periods. Profiler is a resource intensive tool and WILL cause performance issues on its own. If you use the save to table option, then watch your log size if in full recovery.
- Profiler has intersting security on it. Bascially you need ALTER TRACE but you also need to be sure you can execute everything you read in (kind of). You can see this here with the example that is better than I could give. If you are not sure or get errors, ask your DBA very nicely if he or she would be so kind (as all us DBAs are) to run it for you when they say they are going to execute the screen in the application
- If you Google this tip, you will see it about a zillion times. I’m just refreshing it for everyone sense my SQL developer actually didn’t know what the hell I was talking about when I said, “just use profiler, duh?!?”
