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:318994
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
