Profiler and Trace Tips

While investigating performance issues as a DBA I am often asked to profile a SQL system and provide the trace files to the requestor ASAP. The requestor could be a developer, tester or 3rd party vendor or the information could be useful to the DBA. I need a secure, consistent, reliable and quick way to deliver on the request without going through the hassle of starting a new Profiler session and configuring all the properties for a new trace. The trace files generated need to be clearly labeled and in a location where the requestor can access them. Also, I would like the option on some servers of having a profiler trace running 24x7 in the background. I could then access and read these trace files to investigate issues after the fact - muc

I am a SQL Server user and in my day-to-day work I use SQL Profiler for various purposes. Sometimes I run profiler to trace slow running queries and tune them appropriately; sometimes I run a trace to know details about failed Logins in addition to those reported in SQL Server error log. There are other scenarios such as tracing missing statistics on the tables, finding deadlock information or viewing execution plans of the queries being run and so on. Every time I start a new trace, I repeat the same steps, such as choosing certain events only and filtering events related a particular database and / or monitor a particular SQL Login and then make the necessary analysis. Choosing these properties each time I start a new trace is tedious and

I have been running SQL Server 2005 server side traces to address some SQL Server 2005 performance issues. I have noticed an unusual trace session that is running. I know I have not been running this trace and know I have been diligent about running only a single trace to not impact performance. So where did this trace session coming from?

I often see questions online where someone wants to find out who started a trace, when tempdb last had an autogrow event, or when the last full backup for master occurred. Usually we have to tell them that they *might* be able to get this information. Since these and other events are captured by the default trace, but the default trace only keeps five 20MB rollover files by default. This means that the event you are after may no longer be there, depending on how long ago it was and how busy your server happens to be. Unfortunately, people often need to find this information well after the fact, and in a lot of these cases the data is no longer available.

You want to schedule a trace, but SQL Profiler does not have a built-in scheduling option. So how do you use SQL Agent to schedule a SQL Profiler trace?

When troubleshooting a SQL Server performance problem, one of the tools to use is Profiler. This tool allows you to collect metrics on statements that are run on your SQL Server for analysis and troubleshooting. The problem with using Profiler is that it is a client tool and unless it is run on the server itself the connection may be lost and your trace stops. One alternative to using Profiler is to run a Server Side Trace.

You're starting to work with or troubleshoot an application that doesn't have documentation. You need to find all the database calls from the application to the database during the execution of a feature within the application. You know Profiler will help trace transactions, but you see so many transactions your head spins. You need to only trace the transactions created by you during testing. In this tip I walk through how to setup filtering to minimize the amount of transactions that are captured.

I have heard of the default trace in SQL Server 2005 and 2008. I know it's on by default, but I don't know how to use it. What can I do with the default trace?

In a previous tip we looked at the steps to Create a Trace Template in Profiler. In this tip we will look at a few more tips such as creating a trace template from an existing trace file and saving a trace file to a SQL Server table.

I often see questions online where someone wants to find out who started a trace, when tempdb last had an autogrow event, or when the last full backup for master occurred. Usually we have to tell them that they *might* be able to get this information. Since these and other events are captured by the default trace, but the default trace only keeps five 20MB rollover files by default. This means that the event you are after may no longer be there, depending on how long ago it was and how busy your server happens to be. Unfortunately, people often need to find this information well after the fact, and in a lot of these cases the data is no longer available.

When troubleshooting a SQL Server performance problem, one of the tools to use is Profiler. This tool allows you to collect metrics on statements that are run on your SQL Server for analysis and troubleshooting. The problem with using Profiler is that it is a client tool and unless it is run on the server itself the connection may be lost and your trace stops. One alternative to using Profiler is to run a Server Side Trace.

I have been running SQL Server 2005 server side traces to address some SQL Server 2005 performance issues. I have noticed an unusual trace session that is running. I know I have not been running this trace and know I have been diligent about running only a single trace to not impact performance. So where did this trace session coming from?

You want to schedule a trace, but SQL Profiler does not have a built-in scheduling option. So how do you use SQL Agent to schedule a SQL Profiler trace?

In a previous tip we looked at the steps to Create a Trace Template in Profiler. In this tip we will look at a few more tips such as creating a trace template from an existing trace file and saving a trace file to a SQL Server table.

While investigating performance issues as a DBA I am often asked to profile a SQL system and provide the trace files to the requestor ASAP. The requestor could be a developer, tester or 3rd party vendor or the information could be useful to the DBA. I need a secure, consistent, reliable and quick way to deliver on the request without going through the hassle of starting a new Profiler session and configuring all the properties for a new trace. The trace files generated need to be clearly labeled and in a location where the requestor can access them. Also, I would like the option on some servers of having a profiler trace running 24x7 in the background. I could then access and read these trace files to investigate issues after the fact - muc

I have heard of the default trace in SQL Server 2005 and 2008. I know it's on by default, but I don't know how to use it. What can I do with the default trace?

You're starting to work with or troubleshoot an application that doesn't have documentation. You need to find all the database calls from the application to the database during the execution of a feature within the application. You know Profiler will help trace transactions, but you see so many transactions your head spins. You need to only trace the transactions created by you during testing. In this tip I walk through how to setup filtering to minimize the amount of transactions that are captured.

I am a SQL Server user and in my day-to-day work I use SQL Profiler for various purposes. Sometimes I run profiler to trace slow running queries and tune them appropriately; sometimes I run a trace to know details about failed Logins in addition to those reported in SQL Server error log. There are other scenarios such as tracing missing statistics on the tables, finding deadlock information or viewing execution plans of the queries being run and so on. Every time I start a new trace, I repeat the same steps, such as choosing certain events only and filtering events related a particular database and / or monitor a particular SQL Login and then make the necessary analysis. Choosing these properties each time I start a new trace is tedious and

I often see questions online where someone wants to find out who started a trace, when tempdb last had an autogrow event, or when the last full backup for master occurred. Usually we have to tell them that they *might* be able to get this information. Since these and other events are captured by the default trace, but the default trace only keeps five 20MB rollover files by default. This means that the event you are after may no longer be there, depending on how long ago it was and how busy your server happens to be. Unfortunately, people often need to find this information well after the fact, and in a lot of these cases the data is no longer available.

In a previous tip we looked at the steps to Create a Trace Template in Profiler. In this tip we will look at a few more tips such as creating a trace template from an existing trace file and saving a trace file to a SQL Server table.

You're starting to work with or troubleshoot an application that doesn't have documentation. You need to find all the database calls from the application to the database during the execution of a feature within the application. You know Profiler will help trace transactions, but you see so many transactions your head spins. You need to only trace the transactions created by you during testing. In this tip I walk through how to setup filtering to minimize the amount of transactions that are captured.

I am a SQL Server user and in my day-to-day work I use SQL Profiler for various purposes. Sometimes I run profiler to trace slow running queries and tune them appropriately; sometimes I run a trace to know details about failed Logins in addition to those reported in SQL Server error log. There are other scenarios such as tracing missing statistics on the tables, finding deadlock information or viewing execution plans of the queries being run and so on. Every time I start a new trace, I repeat the same steps, such as choosing certain events only and filtering events related a particular database and / or monitor a particular SQL Login and then make the necessary analysis. Choosing these properties each time I start a new trace is tedious and

While investigating performance issues as a DBA I am often asked to profile a SQL system and provide the trace files to the requestor ASAP. The requestor could be a developer, tester or 3rd party vendor or the information could be useful to the DBA. I need a secure, consistent, reliable and quick way to deliver on the request without going through the hassle of starting a new Profiler session and configuring all the properties for a new trace. The trace files generated need to be clearly labeled and in a location where the requestor can access them. Also, I would like the option on some servers of having a profiler trace running 24x7 in the background. I could then access and read these trace files to investigate issues after the fact - muc

I have heard of the default trace in SQL Server 2005 and 2008. I know it's on by default, but I don't know how to use it. What can I do with the default trace?

You want to schedule a trace, but SQL Profiler does not have a built-in scheduling option. So how do you use SQL Agent to schedule a SQL Profiler trace?

I have been running SQL Server 2005 server side traces to address some SQL Server 2005 performance issues. I have noticed an unusual trace session that is running. I know I have not been running this trace and know I have been diligent about running only a single trace to not impact performance. So where did this trace session coming from?

When troubleshooting a SQL Server performance problem, one of the tools to use is Profiler. This tool allows you to collect metrics on statements that are run on your SQL Server for analysis and troubleshooting. The problem with using Profiler is that it is a client tool and unless it is run on the server itself the connection may be lost and your trace stops. One alternative to using Profiler is to run a Server Side Trace.