August 23, 2007 at 4:14 am
Hi all, sorry in advance for all the extra background information, this is as much a rant as a request for help!!
I've got an internal client (for dire want of a more polite phrase) who wants a profiler trace run against a db this weekend. The "problem" he has is one of query performance from his app, although I've demonstrated that the query returns 1600 in one second FLAT in my remote QA, he still wants the trace. I've asked him THREE times when the query runs; he won't say.
So, I've got to run a Profiler trace ALL weekend!
What I'd like to know is:
1) How do I set up the job/SP etc to schedule the trace?
2) What events/data columns/filters are the minimum needed to prove whether SQL is the culprit? (I need to include as little data as needed so the file doesn't blow up!)
3) If I save to a .trc file, is this the most space-efficient filetype?
Thanks,
Jaybee
August 23, 2007 at 5:00 am
Jaybee
(1) Check out the system stored procedures that start with sp_trace.
(2) Use Profiler to have a look through the list of columns to see which will provide useful information. I should imagine that EventClass, Writes, Reads, EndTime, Duration, LoginName, CPU would be a good place to start. Even more important is to filter effectively - so exclude activity in other databases, exclude the login that runs your backup jobs, and so on. Try it out for an hour before you set it going for the weekend!
(3) I don't know. But you will also wish to consider ease of analysis. If it's going to return a lot of data, and you use a .trc file, you will only be able to use Profiler to look through it. If you save to a database table, you can query it with T-SQL.
Good luck
John
August 23, 2007 at 6:33 am
You can use profiler to create the script file. Open profiler, set all the options you want, including end time, then go to the file menu and select the option to script the trace.
Put that script into a job step and schedule as you like.
Rather save as a trc file than into a db table, especially a table on th same db as you're profiling. It's the methos with the lowest impact on the server that it's running on. You can save the trace file to a table later for analysis
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2007 at 12:14 pm
Along the lines of your 'rant' portion: IF this is an adhoc query, and IF you want to pay your client for the amount of time and effort they are willing to spend with you, working with you to determine exactly how to diagnose his/her supposed problem, why not run the profiler all weekend, send him/her the results, and say here's what you asked for.
When they respond that it's not very useful, just smile and say 'exactly. That's why I asked for specifics.'
Consider it user training. And be prepared to teach them how to use an editor and search. FORCE them to spend time they don't have to clean up a non-existent mess. Even if it's only 10 minutes, then they will have had an object lesson in what you do, behind the scenes, so (s)he doesn't have to spend that time.
Remember to speak softly and smile often (when appropriate). You're not being superior - you've done this enough times to know what it takes and your client hasn't a clue. Either that, or (faint possibility) this is a political setup to terminate you, in which case, your 'client' may actually be doing you a favor. If this is how they fire people, do you really want to be associated with them?
Such a harsh note to end on - but the upside is that they may end up with a better appreciation of collaboration and the kind of work you have to go through to meet 'client' requests.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply