Alleviating heavy writes?

  • 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??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • 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.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • 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??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply