Solved

How important is SQL Profiler when debugging database application?

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now