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

Question

I like the way I can profile a Java / .Net application to find performance bottlenecks or memory issues. For example, it is very easy to find a performance bottleneck looking at the call tree with the execution time and the number of calls in each method . In SQL Server, I stored procedures that call other view-specific stored procedures, similar to Java / .Net methods that call other methods. So it seems that the same profiler will be very useful here. However, I looked far and wide and could not find it. Does anyone know about such tools for both SQL Server and any other DBMS?

Update: Thanks for the answers in SQL Server Profiler, but this tool is very limited. Take a screenshot .

Answer 1

Check out the SQL Nexus Tool . It has some good bottleneck reports. SQL Nexus is a tool to help you identify the root cause of SQL Server performance problems. It downloads and analyzes performance data collected by SQLDiag and PSSDiag. This can significantly reduce the time spent manually analyzing data.

In one of the books of Inside SQL 2005 (possibly T-SQL Querying) there was a cool technique in which the author unloads the output of the SQL profiler into a table or excel file and uses a support element to get the output in the same way as your screenshot.

I have not seen the built-in SQL tools that give you such an analysis. Another useful post .

Answer 2

In addition to the SQL Server Profiler, as noted in a comment by @Galwegian, also check your execution plan when you run the query.

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

Answer 3

Another thread about SQL Server Profiler:

Identify SQL Server Performance Problems

I understand what you are talking about, but, as a rule, database optimization takes place at a more subtle level. If the database activity is initiated by the client, you should be able to use the existing client profiler to get the total time at each step, and then turn to low-hanging fruits (whether it is in the database or not).

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

As a rule, access to the database has a certain level of detail, which is addressed on an individual basis, and the activity of the database is not linear, while all types of user access continue, while the profiler usually profiles the linear path of the code.

Answer 4

As already mentioned, SQL Server Profiler, which is great for checking what parameters you pass to a SQL program, etc. It will not show you the execution tree, though, if you need it. For this, all I can think of is to use Show Plan to see what exactly is being executed at runtime. For instance. if you call sp to invoke the view, Profiler will show you that sp was executed and what parameters were passed. In addition, Windows Performance Monitor has extensive runtime performance metrics specific to SQL Server. You can run it on the server or connect remotely.

Answer 5

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

Answer 6

You can use the S ql Profiler - which covers profiling, but I tend to think of it more as a logging tool. For performance diagnostics, you probably should just take a look at the query plan .

Answer 7

There is a sql server profiler, but despite this name, it does not do what you want from the sound of your question. It will show you a detailed view of all the calls that occur in the database. This is better for troubleshooting the application as a whole, and not just one sproc at a time

It looks like you need to look at the execution plan of your queries / procedures in query analysis , and this will give you something similar to the data you are looking for.

Answer 8

As mentioned in several answers, SQL Profiler will show what you are asking for. What you will need to do is enable the SP: StmtCompleted events that are in the Stored Procedures group, and if you want query plans to also include the Showscan XML Statistics Profile, which is in the Performance group, Latest XML Plan gives you a graphic description and shows the actual lines processed by each step in the plan.

If the profiler slows down your application, filter it as much as possible and consider switching to server-side tracing.

NTN Andy