Is there a SQL Server profiler similar to Java/.Net profilers?

Question

I love the way I can profile a Java/.Net app to find performance bottlenecks or memory problems. For example, it's very easy to find a performance bottleneck looking at the call tree with execution times and invocation counts per method. In SQL Server, I have stored procedures that call other stored procedures that depend on views, which is similar to Java/.Net methods calling other methods. So it seems the same kind of profiler would be very helpful here. However, I looked far and wide and could not find one. Is anyone aware of such tools, either for SQL Server or any other DBMS?

Update: Thanks fro your replies around SQL Server Profiler, but this tool is very limited. Take a look at the screenshot.

Answer 1

You could use Sql Profiler - which covers the profiling aspect, but I tend to think of it more as a logging tool. For diagnosing performance, you should probably just be looking at the query plan.

Answer 2

In addition to SQL Server Profiler, as mentioned in a comment from @Galwegian, also check out your execution plan when you run a query.

http://www.sql-server-performance.com/tips/query_execution_plan_analysis_p1.aspx
http://en.wikipedia.org/wiki/Query_plan

Answer 3

Another whole thread about the SQL Server profiler:

http://stackoverflow.com/questions/14717/identifying-mssql-performance-problems

I understand what you are talking about, but typically, database optimization takes place at a finer grained level. If the database activity is driven from a client, you should be able to use the existing client profiler to get the total time on each step and then address the low hanging fruit (whether in the database or not).

When you need to profile a particular database step in detail, you can use profiler and a trace.

Typically, the database access has a certain granularity which is addressed on an individual basis and database activity is not linear with all kinds of user access going on, whereas a program profiler is typically profiling a linear path of code.

Answer 4

There's the sql server profiler, but despite it's name, it doesn't do what you want, by the sound of your question. It'll show you a detailed view of all the calls going on in the database. It's Better for troubleshooting the app as a whole, not just one sproc at a time

Sounds like you need to view the execution plan of your queries/spocs in query analyzer and that will give you something akin to the data you are looking for.

Answer 5

As mentioned, SQL Server Profiler, which is great for checking what parameters you're program is passing to SQL etc. It won't show you an execution tree though if that's what you need. For that, all I can think of is to use Show Plan to see what exactly is executed at run-time. E.g. if you're calling an sp that calls a view, Profiler will only show you that the sp was executed and what params were passed in. Also, the Windows Performance Monitor has extensive run-time performance metrics specific to SQL Server. You can run it on the server, or connect remotely.

Answer 6

To find performance bottlenecks, you can use the Database Engine Tuning Advisor (found in Tools menu of SQL Server Management Studio. It provides suggestions for optimizing your queries and offers to optimize them for you automatically (e.x. create the appropriate indexes, etc.).

Answer 7

Check out SQL Nexus Tool. This has some good reports on identifying bottlenecks. SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues. It loads and analyzes performance data collected by SQLDiag and PSSDiag. It can dramatically reduce the amount of time you spend manually analyzing data.

In one of the Inside SQL 2005 books (maybe T-SQL Querying), there was a cool technique in which the author dumps the SQL profiler output to a table or excel file and applies a pivot to get the output in a similar format as your screenshot.

I have not seen any built-in SQL tools which gives you that kind of analysis. Another useful post.

Answer 8

As mentioned by several replies the SQL Profiler will show what you're asking for. What you'll have to be sure to do is to turn on the events SP:StmtCompleted, which is in the Stored Procedures group, and if you want the query plans as well turn on Showplan XML Statistics Profile, which is in the Performance group. The XML plan last one gives you a graphical description and shows the actual rows processed by each step in the plan.

If the profiler is slowing your app down, filter it as much as possible and consider going to a server side trace.

HTH Andy