The Profiler is a development and diagnostic tool that logs the activities that occur in your database in real time and analyzes the information for performance issues.
Use the Profiler to help identify such issues as:
Use the Profiler to collect information about all database activity (comprehensive profiling) or to collect a subset of executing SQL statements (targeted profiling). Both options collect statistics about the database server load and automatically analyze your workload to identify or rule out issues that could be affecting your application's performance. Always start your performance investigation by reviewing this workload analysis.
Comprehensive profiling collects the most information about your workload. It collects all activity that occurs in your database, including information about connections, SQL statements, events, console messages, web server messages, and internal database server operations. Comprehensive profiling provides you with the most visibility into the activities occurring in your database. In contrast, targeted profiling collects only information about the database server load and the SQL statements that fulfill your specified criteria.
Because so much information is collected during a comprehensive profiling session, the performance of the database being monitored is affected. In contrast, targeted profiling collects information about only the SQL statements that fulfill specific criteria. Because targeted profiling collects less information, performance is minimally affected. You can run a targeted profiling session for an extended period of time on a database in a production environment.
Comprehensive profiling is the only option that provides features for profiling stored SQL objects and reviewing blocked objects and connections. Its analysis of the workload is more extensive because it has additional types of information to consider. However, targeted profiling allows you to create the collection criteria for the SQL statements to collect.
Targeted profiling is often a good choice when you are concerned about the performance of your database, especially if you are profiling a database while it is running in a production environment. However, when the information collected by targeted profiling is not sufficient, use comprehensive profiling and limit the length of the profiling sessions. Comprehensive profiling is the more powerful option because it collects information about all activity occurring in the database and it provides more features such as the ability to profile stored SQL objects.
Choose targeted profiling if you are mainly interested in information about your database server load and in the Profiler's analysis of your application. Targeted profiling is a great choice when you know the characteristics of the SQL statements that are likely to be affecting performance. It can also be a good choice when you have a complex application that generates a lot of database activity, because it reduces the amount of information that you need to filter to identify the information that is of interest to you.
Comprehensive profiling is useful when you are investigating a performance problem that has a number of potential causes. Use comprehensive profiling when you are developing your application to validate your designs.