Hi, I've been taking some stats from one of our production servers that we've not long taken on from another company. Installed version is SQL Server 2000 SP2(!) so I've been taking some stats to find out how it's used and what sort of load it's under.
The contents of my normal toolbox won't work (Brent Ozar's sp_blitz, Adam Machanic's who_is_active for example) so I came up with this to get some I/O stats:
SET NOCOUNT ON
DECLARE @upSince DATETIME
SET @upSince = (SELECT TOP 1 crdate FROM master.sys.sysdatabases WHERE name='tempdb')
SELECT CAST(GETDATE()-@upSince AS BIGINT) AS uptime_in_days
SELECT dbs.name, vfs.FileID, vfs.NumberReads, vfs.IoStallReadMS, vfs.NumberWrites,
vfs.BytesWritten/1024 AS KB_Written, vfs.IOStallWriteMS,
vfs.BytesOnDisk/1024 AS KB_On_Disk_Now
FROM fn_virtualfilestats(null,null) vfs
LEFT JOIN master.sys.sysdatabases dbs ON vfs.DBId = dbs.dbid
WHERE name NOT IN ('master','msdb','model','tempdb')
ORDER BY name ASC
This gave me some results which, once I'd thrown them into Excel and played with them a bit, showed me that on balance the databases on this server are write-heavy.
So I looked into the disk arrays - C: for boot, D: for data files, E: for log files, physical RAID 1 arrays.
Next, I went into perfmon and looked at a few stats, but the one that stood out was an average 40ms for transfers (Avg. Disk/sec / Transfer) on D:. Good values (from MSDN) are < 10ms as excellent, 10-20ms OK, 20-40ms slow and > 40ms - big problem.
There's been no complaints so far from the users that the applications are running slow but I'd like to improve on the write performance.
My first thoughts are to move some DBs onto a separate server to ease the load. Are there any other tips you guys have come across that might alleviate the write load?
What I'm thinking is perhaps force more memory use, or restrict the I/O in some (beneficial) way?
---Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!
Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.