[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

How important is SQL Profiler when debugging database application?

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

650 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