Developing Matt

My Technical Journal

Sql Server Performance 2

leave a comment »

Here are a few more takeaways from the performance class taought by Stephen Wynkoop on sql pserformance.

The Performance monitor

Start with task monitor to see if it is actually sql server that is dragging the machine.   You might get a better ballpark.

Perfmon is a performance hit so watch update frequency. You can use backspace (after clicking on a counter) to select that counter. He says to start with a baseline and add the counters you want. You can save your workspace and change/update the scaling. Things to check:

  • Page splits/second (you want the target less than 100) (under Logical Disk)
  • Buffer Cache Hit Ratio (closer to 100 is better…RAM is a good fix for this problem). (this is under sql server general statistics)
  • User Connections: good for understanding the load on the box. The more user connections the more hits on the box. (this is under sql server general statistics)
  • If you do replication, check replication based counters
  • Scan rates
  • Device throughput (check disk i/o…can be an early indication of a bad disk)
  • %processsor time (he likes to combine processor time, connections, and disk space to determine overall resource utilization on box) (under Processor)
  • %Disk time (physical disk time, but beware of SAN. It can give a bad read. A san is an abstracted layer for storage. you might not be able to use this counter) (under Logical Disk)

Another really neat trick that was new to me was to run performance monitor and profiler at the same time to get a really good feel for what is going on with the system.  EXCELLENT!

OTHER HELPFUL TOOLS

  • Sp_who, sp_who2 (who is doing what)
  • Sp_lock (shows locks)
  • Sysprocesses (what operations are going on right now)
  • sqlIO – capacity of disk subsystem (tool from Microsoft to check out disk subsystem)
Advertisements

Written by matt

November 25, 2009 at 3:12 pm

Posted in Sql Performance

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: