Solved

How important is SQL Profiler when debugging database application?

Posted on 2011-02-26
3
238 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

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