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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With