Solved

How important is SQL Profiler when debugging database application?

Posted on 2011-02-26
3
239 Views
Last Modified: 2012-05-11
I have used it on several projects at other companies and find it to be a required debugging tool in some development shops. As a contractor, when I first learned of it, I was looked down upon for not having used it before.

I have heard some DBA's say it's not important at all during debugging, that the old trial and error method of debugging works fine.

What do you think?

We are new all to Entity Framework and I have argued since the start of the project we needed to see the exact SQL being created by EF4 so we could help remove the mystery by the magical tool :)

We have not gotten SQL Profiler running and still have as much mystery as when the project began.

I hope to hear some opinions on the use of SQL Profiler to read the auto generated SQL that's being presented to the database.

newbieweb
0
Comment
Question by:newbieweb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 17

Accepted Solution

by:
dbaSQL earned 250 total points
ID: 34989805
When used properly, I think the Profiler can be a wonderful tool.  It is not without overhead, though, and you've really got to understand how to translate what you are reading in the varied outputs.  Here are a couple of good references:

http://www.techrepublic.com/article/step-by-step-an-introduction-to-sql-server-profiler/5054787
http://www.sqlfingers.com/p/performance-tuning.html

You can use the Profiler to debug or analyze your statements, and procedures.  Isolate the long running queries, and see where all of the time is being spent, capture all of the activity in very heavy load times, or even just capture who is running what.  

Again, if used properly, the Profiler is great.  But it has taken me a very long time to be able to say that.  'Mastering SQL Server Profiler' by Brad McGehee is a very good read.
http://www.simple-talk.com/books/sql-books/mastering-sql-server-profiler/
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 250 total points
ID: 34989969
I use the profiler on a daily basis in my job.

The end-user calls in and says I'm getting this error. I then run the profiler while trying to reproduce error. I then get the Exec [ThisSP] with all the strings particular to the company.

I then can just plug that back into the query window in SSMS and run it again. Then if I get the error I can then break down the SP to find out what is causing the error.

Sometimes it is the programming (e.g. a function with a varchar(40) getting 42 characters) or it might be a setup issue (e.g. the payer isn't setup to be billed that way).

It is all in the level of detail that you need. Running it constantly though just is a drag on performance. You can do blackbox traces but I would do it on a very limited basis. It would be directed at databases and or the table level.

Another option is table level triggers that write data before and after. But again that is overhead.

0
 

Author Closing Comment

by:newbieweb
ID: 34990099
Thanks!
0

Featured Post

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question