Skip to main content

HOWTO: SQL Server: Reporting on performance

Some resources to draw info on SQL Server memory usage, locks, fragmentation, etc. This is my favorite SQL Server tuning site: http://www.sql-server-performance.com/

SQL Server Perf Mon

SQLServer: Access Methods - Page Splits/sec: Number of page splits occurring as the result of index pages overflowing.
SQLServer: Databases - Data File Size (KB)
SQLServer: Databases - Log File Size (KB)
SQLServer: Databases - Log File Used Size (KB)
SQLServer: General Statistics - User Connections: Number of users connected to the system.
SQLServer: Locks - Average Wait Time (ms): The average amount of wait time (milliseconds) for each lock request that resulted in a wait.
SQLServer: Locks - Number of Deadlocks/sec: Number of lock requests that resulted in a deadlock.
SQLServer: Memory Manager - Total Server Memory (KB): Total amount of dynamic memory the server is currently consuming
Many, many others…

Queries

View Blocks
SELECT spid, blocked, waittype, getdate() AS 'Time'
FROM master..sysprocesses (nolock)
WHERE blocked <> 0

View a whole lot of info on processes
SELECT *
FROM master..sysprocesses (nolock)

View process info sorted by memory usage
SELECT *
FROM master..sysprocesses (nolock)
ORDER BY memusage DESC

How to monitor SQL Server 2000 blocking


Stored Procedures (system)
Sp_lock
Sp_who
Sp_who2
Sp_monitor
sp_spaceused

Comments

Post a Comment

Popular posts from this blog

Starting a New Blog with my Cousin

On 9/15/2006, my cousin (he's really a 2nd cousin many times removed...I'm not convinced we're actually related ;-) John Ingrisano and I will launch a new blog targeted to the small business owner. He'll provide most of the content and I will provide the technical and marketing expertise. Ok, ok, starting a blog isn't rocket science but we're aiming to [eventually] derive some sort of profit from this venture, so I plan on doing this right. The two blogs I currently maintain live on the free Blogger.com and LiveJournal.com. I'll be covering the adventure here on Effective Thoughts--providing insight as to what worked and what didn't. To start off, our first activities are: Choose a name and a theme Find a host Help John learn the ins and outs of blogging Implement the site: look/feel, about/bio, 1st content, announcements, etc. Understand marketing best practices To this end, here is some research I compiled: Finding a host http://en...
6 comments
Read more

Switching the Parents to Ubuntu...?

I spent a half hour or so recently on the phone walking my Mom through a technical issue. Tentatively, I diagnosed her issue as a hard drive failure. She brought it over on her last visit and sure enough, the Dell XPS 450 from circa 1999 sounds like a bad coin-operated laundry at full capacity. I was aghast to discover she's running Windows 98. Ugh. Also, her recovery disk is just that--for recovery. I don't believe I'll be able to re-install Win98 on a new hard drive. That, coupled with the end of Microsoft (and Dell) support for Win98, got me thinking about Linux. (and she's not intense about her computing needs...and she doesn't want to spend much money...) I've been reading good things about switching one's parents to Ubuntu. Any thoughts out there?
4 comments
Read more