August 1, 2016 at 8:44 am
We’re using Microsoft SQL Server 2014 - 12.0.4213.0 (X64)
Jun 9 2015 12:06:16
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
We have a few processes that process massive amounts of data daily. When they run the Queue length on the data drive fills and we end up w/ several other processes waiting for i/o. It’s so bad that when it's happening you can’t even expand the database in sql server management studio. Has anyone else ran into this issue?
My plan was to either break the primary offenders database mdf file to multiple drives or give him his own drive and break out all the other db's onto different sets of drives.
Would setting up buffer extensions help?
Anyone have any ideas? Right now the CPU is staying level between 5 and 20 percent for the most part.
August 1, 2016 at 9:29 am
Quick question, what is the current storage configuration?
😎
August 1, 2016 at 4:34 pm
Unless you can guarantee that tables will be split across multiple separate physical spindles, splitting up an MDF file may actually slow things down even worse than they are now. Even moving the MDF to a different server on a shared SAN may not help unless separate drives come into play and, even then the drive controller will be shared.
Additionally, shifting hardware might give you a 2x or 3x improvement with no code changes (unless you go MPP and even that requires a code change) but, someone good in T-SQL could get 60X or even 1000X faster with similar reductions in resources used.
With that last thought in mind an unless you want to build a separate machine with separate physical disks, what needs to be done is that last thing, it seems, that anyone wants to do and that's to analyze the code and fix it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply