SQL Server Profiler
From SQLServerPedia
|
See Also: Main_Page - Performance Tuning
SQL Server Profiler: What It DoesSQL Server performance problems can be caused by a number of different reasons, including insufficient hardware, poor database design, ineffective indexes, poorly written Transact-SQL code, etc. SQL Server Profiler can help you determine the root cause of the problem by identifying troublesome queries and stored procedures. Examining the components of the poorly performing queries is the key to figuring out what is causing the problem and coming up with the solution. Getting the Most Bang for your BuckMost applications are composed of numerous stored procedures and ad-hoc queries. Examining each of these can be a daunting task. Instead, you should concentrate your attention on a few code modules that are:
It is essential to optimize the queries that are executed most often because that's where you get the most return on your investment. If you can work out a 20% improvement on a query that is executed 10000 times daily, you may improve the entire system by 20% or more. This is especially true because queries that are executed repeatedly tend to slow down when executed simultaneously by multiple users. On the other hand, optimizing a task that runs once a week might not yield any noticeable improvements of the overall system. The slowest code modules present a great opportunity since there is a large potential for improvement. Optimizing a stored procedure to complete within one second instead of 2 minutes will yield much higher dividends than shaving 200 milliseconds from a routine that currently takes 1.5 seconds. How Profiler Helps You Find Slow QueriesIn order to know which routines are executed most often and which ones take the longest, you must have a way of spying on statements executed by the users. This is where Profiler comes in handy - it lets you trace the events that occur within SQL Server and provides information that assists you in analyzing those events. In addition to recording SQL statements, Profiler can show you the duration of each command, CPU time used, number of reads and writes performed, types of locks acquired and more. Tracing every single event that occurs on your server can be disastrous; Profiler might quickly generate more data than you can digest. That is why Profiler allows you to choose which events you wish to monitor and even carve out specific portions of those events by applying filters. To get you started, SQL Server Profiler comes with pre-defined trace filters (sometimes referred to as templates) that setup useful traces for you. Once you record SQL statements with Profiler you can save the trace and even replay it at a later time on the same or different server. You can also use SQL Profiler to debug queries and stored procedures and audit security events. Correlating Profiler Data with Performance Monitor DataIf you have SQL Profiler data that you have saved and performance monitor data that you have saved, you can import the performance monitor data into SQL Profiler so that you can see the Profile trace and the Performance monitor data and see where they line up. After you load the saved SQL Profiler trace from a table or file click on File > Import Performance Data to select the Performance Monitor data that you want to import. Then select the counters to view and you'll have the data. If you are using a very large Performance Monitor data set this will take quite a while to complete. When recording performance data use smaller files to prevent this from being a problem. SQLServerPedia Articles on Profiler
|