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

Rollback a Ooops in TFS with TFPT Rollback

Rhut roe, Raggie. You just checked in a merge operation affecting 100's of files in TFS against the wrong branch. Ooops. Well, you can simply roll it back, right? Select the folder in Source Control Explorer and...hey, where's the Rollback? Rollback isn't supported in TFS natively. However, it is supported within the Power Tools leveraging the command-line TFPT.exe utility. It's fairly straightforward to revert back to a previous version--with one caveot. First, download and install the Team Foundation Power Tools 2008 on your workstation. Before proceeding, let's create a workspace dedicated to the rollback. To "true up" the workspace, the rollback operation will peform a Get Latest for every file in your current workspace. This can consume hours (and many GB) with a broad workspace mapping. To work around this, I create a temporary workspace targeted at just the area of source I need to roll back. So let's drill down on our scenario... I'm worki...

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?

VSTS Tester Demo Follow-ups

Last week, I delivered a VSTS 2008 Tester Edition demo to a prospective client. Following up on a few questions to which I didn’t know the answer: Q. Can I use Subversion with TFS? A. I get this question all the time from developers. It’s a perfectly valid question. The answer is no…but yes…sort of. The version control repository (and all data) must remain SQL Server. Yes, it’s proprietary. Further, if you plan to use TFS in your software development environment, but choose not to leverage it for version control, it severely limits the usefulness of the information elicited from TFS (because you’re not feeding in the crucial VC data). If you’re not leveraging VC in TFS, you’re probably not leveraging Team Build either. That said, while a fully-integrated TFS for ALM and SCM is the ideal, there’s a compelling argument to leverage TFS as a repository for requirements, scenarios, test cases, functional and load testing as well as defect tracking. TFS is an excellent repository to s...

Effective Thoughts

Search This Blog