Tutorial :What are the best ways of using the SQL Profiler



Question:

I am a .NET developer with average SQL skills. I am working on a web app that is 'database heavy'. I have been using the profiler to debug queries and procs. Is there a way to use this tool to look at performance of a query / procedure.

How do I make the most of the SQL Profiler?

I am using SQL Server 2008.


Solution:1

There are no hard and fast rules per say as it depends on the type of database system your a working with.

As a general starting point with SQL Server performance tuning you will find the following reference to be very useful indeed. It contains a variety of considerations and instructions.

http://www.brentozar.com/sql-server-performance-tuning/

Also take a look at the following article, "Identifying Performance issues using SQL Server Profiler"

http://vyaskn.tripod.com/analyzing_profiler_output.htm

If you need additional assistance just drop me a line.


Solution:2

I am not familiar with sql serer 2008, but in sql server 2005 your best bet is the "Display Execution Plan" feature. I will let you know what parts of your query are taking up the most time. Typically adding indexes will help immensely and this tool will help you identify where they are most needed.


Solution:3

Its a wonderful and powerful tool. But be careful and don't do the following"

1) Don't Capture Everything. You can have so many events/objects that you won't be able to find anything. You can set filters by app/user/database.

2) Don't run profiler on the production machine.

3) Don't save the trace info on a production database. Save it to a file that is stored separately from production files.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »