Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Alleviating heavy writes? Expand / Collapse
Author
Message
Posted Monday, May 28, 2012 6:51 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 22, 2013 9:13 AM
Points: 496, Visits: 601
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?

Thanks


---

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.
Post #1307282
Posted Monday, May 28, 2012 9:00 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
One thing I'd recommend is not to exclude the system databases from these stats. I guess you could exclude [model], but the other three all have configurations/cases where they could be heavily used.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1307363
Posted Tuesday, May 29, 2012 3:29 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 22, 2013 9:13 AM
Points: 496, Visits: 601
Very true - I've not even thought to check where tempdb is located(!) I'll include the system DBs - thanks.

---

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.
Post #1307623
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse