?
Solved

How important is SQL Profiler when debugging database application?

Posted on 2011-02-26
3
Medium Priority
?
240 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 1000 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 1000 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

771 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