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 Trace 1 > General Tab 2 > Events Tab 3 > Data Columns Tab 4 > Filters Tab 6 > Running the Trace 7 > Other Things 8 > 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 > * Start Time, End Time > o Start time is included by default, but including End > time makes it possible to spot queries that were backed up behind > another query (they all have the same end time, for example) > > If you need to know who or what is running the queries then you might > want to include these columns: > * Application Name, Host name, Login name, NT User name > o Not all fields will be filled in, but between these you > can get a good idea of what user/computer and program (or CF vs. Java > vs. .Net vs. Access vs. SQL scheduled task) is generating the query > * Database ID > o I've never found Database name to be set, but Database > ID always is, so I often include this column just so I can get the ID of > the database I want to filter on, and then I remove it. > > You can define "groups" of columns by putting the data columns in the > special "Groups" section. > * One or more data columns define the grouping > * Each trace statement is sorted into the proper group > o SQL 7 and SQL 2005 show groups in a nice > expanding/collapsing tree-view style format, but SQL 2000 does not (even > though help mentions it). I miss this feature. > > > > -----Original Message----- > From: Rick Root [mailto:rick.r...@webworksllc.com] > Sent: Friday, February 06, 2009 12:39 PM > To: cf-talk > Subject: Re: JVM Tuning and Garbage Collection > > > Eventually I'm going to want to solve these spikes.. I think SQL > Profiler might help me figure stuff out but also having Cacti installed > and monitoring network and disk and cpu activity more accurately than > the task manager might help me debug some things to. > > So, my server is experience fairly rhthmic request activity spikes where > for 10 seconds or so, fusion reactor shows a whole bunch of active > requests... while I'm normally cruising along at 5-10 requests per > second, I'll suddenly get 20 active requests and FR will queue new > requests. > > So far I've been unable to figure out what's going on. > > But one interesting thing I just noted while comparing my request > activity, jdbc activity, cpu and memory graphs.... > > I had one of these spikes at 11:55 today... my active requests spiked to > 20 for about 20 seconds, while competed requests dropped down to 0 per > second for a good 10 seconds. > > at the same time, JDBC activity dropped to 1 active request and 0 > completed requests per second for about 15 seconds. The normal is 30-50 > completed JDBC queries per second. > > the memory graph shows nothing unusual. > > The CPU graph shows the overall and instance cpu dropping to almost > nothing. > > Which strikes me as a little odd.. I mean, let's say there is some > application.cfm code that uses an exclusive named lock, and it includes > a query that hits the database. If that query, for some reason, ran for > 15 seconds (god forbid), I could understand this happening.. all the > other requests would have to sit and wait for the lock to be released, > and it woudl explain why only one query was running > > Unless it *IS* the database, in which case the lock would hang up on all > three instances at the same time > > But the thing is... it's rhythmic... but not so rhythmic it can be > attributed to some kind of scheduled task on the database server... > but it is relative to system activity.. ie, the busier the site is, the > more often these spikes occur. > > So I'm thinking.. MAYBE SQL Server is doing something that's causing it > to slow down for 15 seconds .. > > but it's not going along with any kind of database maintenance, > scheduled tasks or scheduled backups, etc... > > OTHER Than my already intended plans of using SQL Profiler to do some > more SQL monitoring... and cacti to monitor the servers via SNMP ... > are there any other suggestions? > > Rick > > > >
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319001 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
