RE: JVM Tuning and Garbage Collection (Using SQL Profiler)
Hi I've seen a similar regular burp in SQL server performance every fifteen minutes or so on our servers too. I thought it might be related to maintaining the transaction log but I was never able to prove it. I have some notes on using SQL Profiler that I wrote for an internal information sharing session; here it is (below), pasted from Word, so sorry about the formatting. The general tips are probably worth reading if you haven't using SQL Profiler before. Thanks Mark Intro to SQL Profiler 11/1/06 Basics of SQL Profiler 1 Creating a Trace1 General Tab 2 Events Tab 3 Data Columns Tab4 Filters Tab 6 Running the Trace 7 Other Things8 Basics of SQL Profiler SQL Server Profiler sees all SQL Server queries running on the SQL Server. This allows you to see exactly what T-SQL statements are submitted to the server and how the server accesses the database to return result sets. Using SQL Server Profiler, you can do the following: * Create a trace that is based on a reusable template * Watch the trace results as the trace runs * Store the trace results in a table * Start, stop, pause, and modify the trace results as necessary * Replay the trace results * Monitor only the events in which you are interested With the SQL profile you can see the SQL being generated by any program, even ones you don't have source code for or are not running in a debugger. Simply remembering that you don't have to open a program in a debugger, set a breakpoint, and then get the program to execute a routine just to see the SQL it would produce can save you some time, plus you can watch it all happen on a live database. A properly set-up trace will let you find slow queries and determine if the query itself is slow or if the problem is some other query running at the same time. Profiling does add overhead to the SQL Server, so don't go crazy with events to monitor or leave a trace running for a very long time. (I have had traces running on the live site overnight or over a weekend to find a problem that was crashing the site. You do what you have to do.) Creating a Trace SQL Profiler is installed with the rest of the SQL Tools, and you'll find it in the Microsoft SQL Server program group. Here's a really quick way to get a trace going: * Start the Profiler * Do File/New/Trace * Pick the SQL Server to connect to * Next you'll see the Trace Properties dialog. Just hit the Run button * You are now watching live queries running on the target SQL Server! You'll want to learn how to use the Trace Properties settings to make it easier to find what you are looking for, so I'll describe each tab in the Trace Properties dialog. General Tab The general tab lets you pick/change the SQL Server to connect to and the trace template to use. Trace templates are just the saved settings from the Trace Properties dialog. There are several standard ones and you can make your own. (I have some saved on the live site for different debugging scenarios.) * Setting a stop time might be useful if you want to capture a lot of data but not too much data. You can also use it in conjunction with a Start Time filter to capture, say, all events between 2 am and 3 am. * I've never used the save to table option, but it does what it says... saving all traced rows to a database table so you can look at them later. Events Tab An event is an action generated on a SQL Server: * Login connections, failures, and disconnections. * Transact-SQL SELECT, INSERT, UPDATE, and DELETE statements. * Remote procedure call (RPC) batch status. * The start or end of a stored procedure. * The start or end of statements within stored procedures. * The start or end of an SQL batch. * An error written to the SQL Server error log. * A lock acquired or released on a database object. * An opened cursor. * Security permission checks. * Performance events include execution plans All of the data generated by an event is displayed in the trace in a single row. I find that the default events (shown below) are usually more than I need, so I often remove the Security Audit and Sessions events, leaving just the two Completed events. Data Columns Tab This is where you pick the information to display for each event in the trace. The default columns (partially show below) are not too bad, but I usually need to change them. Selecting the right data columns to display can take some trial and error. General tips: * You'll always want Text Data... that's the SQL statement itself. * You can't remove the Event Class column, so I often move it to the end of the list with the Up/Down buttons If you are interested in performance then I suggest including these columns: * Duration, CPU, Reads, Writes o These numeric fields are key to spotting slow queries *
Re: JVM Tuning and Garbage Collection (Using SQL Profiler)
Mark, that's awesome, thanks! On Fri, Feb 6, 2009 at 2:52 PM, Gaulin, Mark mgau...@globalspec.com wrote: Hi I've seen a similar regular burp in SQL server performance every fifteen minutes or so on our servers too. I thought it might be related to maintaining the transaction log but I was never able to prove it. I have some notes on using SQL Profiler that I wrote for an internal information sharing session; here it is (below), pasted from Word, so sorry about the formatting. The general tips are probably worth reading if you haven't using SQL Profiler before. Thanks Mark Intro to SQL Profiler 11/1/06 Basics of SQL Profiler 1 Creating a Trace1 General Tab 2 Events Tab 3 Data Columns Tab4 Filters Tab 6 Running the Trace 7 Other Things8 Basics of SQL Profiler SQL Server Profiler sees all SQL Server queries running on the SQL Server. This allows you to see exactly what T-SQL statements are submitted to the server and how the server accesses the database to return result sets. Using SQL Server Profiler, you can do the following: * Create a trace that is based on a reusable template * Watch the trace results as the trace runs * Store the trace results in a table * Start, stop, pause, and modify the trace results as necessary * Replay the trace results * Monitor only the events in which you are interested With the SQL profile you can see the SQL being generated by any program, even ones you don't have source code for or are not running in a debugger. Simply remembering that you don't have to open a program in a debugger, set a breakpoint, and then get the program to execute a routine just to see the SQL it would produce can save you some time, plus you can watch it all happen on a live database. A properly set-up trace will let you find slow queries and determine if the query itself is slow or if the problem is some other query running at the same time. Profiling does add overhead to the SQL Server, so don't go crazy with events to monitor or leave a trace running for a very long time. (I have had traces running on the live site overnight or over a weekend to find a problem that was crashing the site. You do what you have to do.) Creating a Trace SQL Profiler is installed with the rest of the SQL Tools, and you'll find it in the Microsoft SQL Server program group. Here's a really quick way to get a trace going: * Start the Profiler * Do File/New/Trace * Pick the SQL Server to connect to * Next you'll see the Trace Properties dialog. Just hit the Run button * You are now watching live queries running on the target SQL Server! You'll want to learn how to use the Trace Properties settings to make it easier to find what you are looking for, so I'll describe each tab in the Trace Properties dialog. General Tab The general tab lets you pick/change the SQL Server to connect to and the trace template to use. Trace templates are just the saved settings from the Trace Properties dialog. There are several standard ones and you can make your own. (I have some saved on the live site for different debugging scenarios.) * Setting a stop time might be useful if you want to capture a lot of data but not too much data. You can also use it in conjunction with a Start Time filter to capture, say, all events between 2 am and 3 am. * I've never used the save to table option, but it does what it says... saving all traced rows to a database table so you can look at them later. Events Tab An event is an action generated on a SQL Server: * Login connections, failures, and disconnections. * Transact-SQL SELECT, INSERT, UPDATE, and DELETE statements. * Remote procedure call (RPC) batch status. * The start or end of a stored procedure. * The start or end of statements within stored procedures. * The start or end of an SQL batch. * An error written to the SQL Server error log. * A lock acquired or released on a database object. * An opened cursor. * Security permission checks. * Performance events include execution plans All of the data generated by an event is displayed in the trace in a single row. I find that the default events (shown below) are usually more than I need, so I often remove the Security Audit and Sessions events, leaving just the two Completed events. Data Columns Tab This is where you pick the information to display for each event in the trace. The default columns (partially show below) are not too bad, but I usually need to change them. Selecting the right data columns to display can take some trial and error. General tips: * You'll always want Text Data... that's the SQL statement itself. * You can't remove the Event Class column, so I often move it to the end of the list with the Up/Down
