March 1, 2011 at 6:35 am
We have pretty high average read IO wait times on the main client database and extremely high average write IO wait times on tempdb. This is all according to the performance dashboard IO stats report. ( Client DB: average read IO wait ~300milliseconds Tempdb average write IO wait ~7,000 milliseconds per performance dashboard )
I think most of this is skewing of averages by weekend maintenance and one nightly job that spikes tempdb. Using our Quest Spotlight tool I don't see anything like this during the business day in real time.
I think the Netapp rep is just looking at latency inside the SAN, which is typically under 50 milliseconds. We believe that aside from what the Netapp does internally and the effectiveness of the HBA network cards between sql cluster and SAN, multiple "IO channels" to the data files is a big plus. We just recently spread our 8, ten GB tempdb files out over 8 luns, but that high average is still there.
Systems is now looking at turning off MPIO ( multi-path IO ) to determine the source of lots of network errors reported between sql and Netapp.
March 1, 2011 at 7:43 am
Generally speaking, multiple data files is only an advantage if each one has a truly separate I/O channel. With most SAN set-ups, you won't actually get that, so there's no benefit. On local or SCSI drives, one per drive gives actual I/O benefits, but not on SAN/NAS storage.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 1, 2011 at 7:59 am
Actually now I'm hearing that MPIO is not on. Systems is still looking into all of that. Each of the data and log files shown below is on it's own lun. The "split_data" paths are mount points in the K:\data folder. Tempdb has something similar. Are you saying there is no advantage to this arrangement?
K:\data\OurDBName.mdf
L:\LOG\OurDBName_LOG.LDF
K:\SPLIT_DATA\CM_Data1\CM_DataFile_01.ndf
K:\SPLIT_DATA\CM_Data2\CM_DataFile_02.ndf
K:\SPLIT_DATA\CM_Data3\CM_DataFile_03.ndf
K:\SPLIT_DATA\CM_Data4\CM_DataFile_04.ndf
K:\SPLIT_DATA\CM_Index1\CM_IdxFile_04.ndf
K:\SPLIT_DATA\CM_Index2\CM_IdxFile_03.ndf
K:\SPLIT_DATA\CM_Index3\CM_IdxFile_02.ndf
K:\SPLIT_DATA\CM_Index4\CM_IdxFile_01.ndf
K:\SPLIT_DATA\CM_Data1\CM_LogFile_01.ndf
K:\SPLIT_DATA\CM_Data2\CM_LogFile_02.ndf
K:\SPLIT_DATA\CM_Data3\CM_LogFile_03.ndf
K:\SPLIT_DATA\CM_Data4\CM_LogFile_04.ndf
K:\SPLIT_DATA\CM_Index1\CM_LogIdx_04.ndf
K:\SPLIT_DATA\CM_Index2\CM_LogIdx_03.ndf
K:\SPLIT_DATA\CM_Index3\CM_LogIdx_02.ndf
K:\SPLIT_DATA\CM_Index4\CM_LogIdx_01.ndf
March 1, 2011 at 8:40 am
There generally isn't on a properly configured SAN. I can't say "there's no benefit", because there can be, depending on the SAN, the configuration, the I/O controller, et al, but most of the time, there isn't.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 1, 2011 at 12:17 pm
We have a sql 2005 Enterprise 64-bit active/passive cluster running on Windows 2008 Server 64-bit connected to a Netapp. There may only be one HBA network card in active use at the moment. There is 128GB of memory but sql is limited to about 82GB at the moment. I can't really speak to the rest of the plumbing.
March 1, 2011 at 1:28 pm
Indianrock (3/1/2011)
Actually now I'm hearing that MPIO is not on. Systems is still looking into all of that. Each of the data and log files shown below is on it's own lun. The "split_data" paths are mount points in the K:\data folder. Tempdb has something similar. Are you saying there is no advantage to this arrangement?K:\data\OurDBName.mdf
L:\LOG\OurDBName_LOG.LDF
K:\SPLIT_DATA\CM_Data1\CM_DataFile_01.ndf
K:\SPLIT_DATA\CM_Data2\CM_DataFile_02.ndf
K:\SPLIT_DATA\CM_Data3\CM_DataFile_03.ndf
K:\SPLIT_DATA\CM_Data4\CM_DataFile_04.ndf
K:\SPLIT_DATA\CM_Index1\CM_IdxFile_04.ndf
K:\SPLIT_DATA\CM_Index2\CM_IdxFile_03.ndf
K:\SPLIT_DATA\CM_Index3\CM_IdxFile_02.ndf
K:\SPLIT_DATA\CM_Index4\CM_IdxFile_01.ndf
K:\SPLIT_DATA\CM_Data1\CM_LogFile_01.ndf
K:\SPLIT_DATA\CM_Data2\CM_LogFile_02.ndf
K:\SPLIT_DATA\CM_Data3\CM_LogFile_03.ndf
K:\SPLIT_DATA\CM_Data4\CM_LogFile_04.ndf
K:\SPLIT_DATA\CM_Index1\CM_LogIdx_04.ndf
K:\SPLIT_DATA\CM_Index2\CM_LogIdx_03.ndf
K:\SPLIT_DATA\CM_Index3\CM_LogIdx_02.ndf
K:\SPLIT_DATA\CM_Index4\CM_LogIdx_01.ndf
If everything is on "K", where would the benefit come from ?
In my old shop, we had a SAN, and could configure the LUNs "vertically" to be on specific arrays, so that LUN_1 was on disk1, 2, 3, while LUN_2 was on disk 4,5,6 ..... so we could spread the files over different specific physical disks.
On my current system, the LUNs are "horizontal" so that LUN1 takes a slice of all drives, as does LUN2, so there's no real benefit to splitting things up since they are all spread across all drives.
March 1, 2011 at 1:37 pm
Even the "vertical" split is only advantageous if your bottleneck is actual disk access, and not the connection to the SAN or anything in between the disks and the connection.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 1, 2011 at 1:40 pm
Each of those mdf, ldf, ndf files it's on it's own lun on the Netapp. But aside from "disk busyness" it's what we've referred to as the separated "IO channels" that are at the heart of whether this has benefit or not. How does sql server access each file?
So the question is whether sql benefits from being able to send requests to disk over multiple paths. True, there are only so many network cards. So does sql benefit from sending one request to K:\data and another to a completely separate disk lun via a mount point set below K:\split_data and another to tempdb on other luns in another mount point below K:\split_data ?
March 1, 2011 at 1:47 pm
homebrew01 (3/1/2011)
On my current system, the LUNs are "horizontal" so that LUN1 takes a slice of all drives, as does LUN2, so there's no real benefit to splitting things up since they are all spread across all drives.
Being that this is NetApp I'm guessing the same thing is true in this configuration as that configuration is how they are able to get their performance. There are some other nuances to their configurations which we be good to check on with the systems folks. I would be sure to find out what else is sharing the controller for the trays that your database disks reside on. An underpowered controller can make your world a bit messy and it may not be the database activity that is causing the latencies you are seeing.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 1, 2011 at 2:01 pm
I could be wrong, but to my knowledge, SQL uses the OS to manage that level of I/O. If you tell it all the files are on the same drive letter, it won't know or care how they are physically arranged.
Where you get an advantage from multiple data files is when you can isolate concurrent heavy I/O on different filegroups. This usually means either separate tables or separate partitions on a large table. If, for example, you need to update a row in one table, and have a trigger log that in other table (possibly even another database), and the I/O subsystem knows that those are not on the same platter, it can potentially take advantage of channels to each platter and do them concurrently instead of sequentially. This depends a lot on how your hardware is set up, and is most often of trivial benefit, if at all, on a properly set up SAN, which will already take advantage of physically separated platters without you having to tell it what to do.
That built-in optimization is the main thing you're paying for with an enterprise level SAN. Otherwise, you'd just set up a file server or NAS with RAID 5 and get "the same thing".
In the case that you manually optimize, what you're doing is optimizing for Right Now, and hoping that the load on each file stays comparable for the life of the system. If you leave it up to the SAN, it can re-optimize on the fly as load needs change. So, you may get a small benefit from it for the short-term, but probably won't, and you will probably create more work for yourself and the SAN admin later.
On multiple log files, SQL uses them in a round-robbin fashion, meaning it will switch to file2 when file1 is full up (essentially). So there's not a speed gain from that, regardless of your set up. (Log writes need to be sequential anyway, to maintain ACIDity in the database, so that makes sense.)
On multiple tempdb files, there are few benefits from that with modern versions of SQL Server in most hardware environments. It's one of those, "try it with your hardware and your environment and your database use and see if it actually benefits you" kind of things. Search online for "SQL Server Myths" and you'll see that one in there with data on how and why.
All of these are general data (except the log file thing). Try things on your system and see if you can get a little extra out of it, if you need to. But keep in mind that time invested in that is time that might be better spent on code optimization (which is usually the most certain way to get better performance out of a database), or on a myriad of other things.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 1, 2011 at 2:09 pm
I have to disagree slightly with the rest of the comments about not seeing an advantage from multiple files (even if they are on the same LUN). I would say that there is a configuration issue somewhere if you are not seeing some sort of gain, whether it be in the SAN setup or if it be in how the data files were setup.
Paul Randal did a series of benchmarking tests along these same lines. The number of files does have a threshold and you should do heavy testing before deploying something like this to production expecting there to be an immediate gain. Sometimes the gain may only be minimal. I have seen performance benefits from multiple files - even on the same LUN. My observations (based on our setup) was an improvement by about 10% in performance. This should only be one factor and is NOT the magic cure-all for performance woes.
You can see Pauls results here.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 1, 2011 at 2:12 pm
Indianrock (3/1/2011)
Actually now I'm hearing that MPIO is not on. Systems is still looking into all of that. Each of the data and log files shown below is on it's own lun. The "split_data" paths are mount points in the K:\data folder. Tempdb has something similar. Are you saying there is no advantage to this arrangement?K:\data\OurDBName.mdf
L:\LOG\OurDBName_LOG.LDF
K:\SPLIT_DATA\CM_Data1\CM_DataFile_01.ndf
K:\SPLIT_DATA\CM_Data2\CM_DataFile_02.ndf
K:\SPLIT_DATA\CM_Data3\CM_DataFile_03.ndf
K:\SPLIT_DATA\CM_Data4\CM_DataFile_04.ndf
K:\SPLIT_DATA\CM_Index1\CM_IdxFile_04.ndf
K:\SPLIT_DATA\CM_Index2\CM_IdxFile_03.ndf
K:\SPLIT_DATA\CM_Index3\CM_IdxFile_02.ndf
K:\SPLIT_DATA\CM_Index4\CM_IdxFile_01.ndf
K:\SPLIT_DATA\CM_Data1\CM_LogFile_01.ndf
K:\SPLIT_DATA\CM_Data2\CM_LogFile_02.ndf
K:\SPLIT_DATA\CM_Data3\CM_LogFile_03.ndf
K:\SPLIT_DATA\CM_Data4\CM_LogFile_04.ndf
K:\SPLIT_DATA\CM_Index1\CM_LogIdx_04.ndf
K:\SPLIT_DATA\CM_Index2\CM_LogIdx_03.ndf
K:\SPLIT_DATA\CM_Index3\CM_LogIdx_02.ndf
K:\SPLIT_DATA\CM_Index4\CM_LogIdx_01.ndf
Is this representing two separate databases?
Have you run any benchmarking tools such as SQLIO or IOSIM?
What is your allocation unit size (8k or 64k or something else)?
Is your partition properly offset?
How is the volume fragmentation?
Have you checked the growth sizes of your data and log files?
What is the VLF fragmentation looking like?
What is the RAID config of your netapp (RAID DP or something else)?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 1, 2011 at 2:22 pm
CirquedeSQLeil (3/1/2011)
Indianrock (3/1/2011)
Actually now I'm hearing that MPIO is not on. Systems is still looking into all of that. Each of the data and log files shown below is on it's own lun. The "split_data" paths are mount points in the K:\data folder. Tempdb has something similar. Are you saying there is no advantage to this arrangement?K:\data\OurDBName.mdf
L:\LOG\OurDBName_LOG.LDF
K:\SPLIT_DATA\CM_Data1\CM_DataFile_01.ndf
K:\SPLIT_DATA\CM_Data2\CM_DataFile_02.ndf
K:\SPLIT_DATA\CM_Data3\CM_DataFile_03.ndf
K:\SPLIT_DATA\CM_Data4\CM_DataFile_04.ndf
K:\SPLIT_DATA\CM_Index1\CM_IdxFile_04.ndf
K:\SPLIT_DATA\CM_Index2\CM_IdxFile_03.ndf
K:\SPLIT_DATA\CM_Index3\CM_IdxFile_02.ndf
K:\SPLIT_DATA\CM_Index4\CM_IdxFile_01.ndf
K:\SPLIT_DATA\CM_Data1\CM_LogFile_01.ndf
K:\SPLIT_DATA\CM_Data2\CM_LogFile_02.ndf
K:\SPLIT_DATA\CM_Data3\CM_LogFile_03.ndf
K:\SPLIT_DATA\CM_Data4\CM_LogFile_04.ndf
K:\SPLIT_DATA\CM_Index1\CM_LogIdx_04.ndf
K:\SPLIT_DATA\CM_Index2\CM_LogIdx_03.ndf
K:\SPLIT_DATA\CM_Index3\CM_LogIdx_02.ndf
K:\SPLIT_DATA\CM_Index4\CM_LogIdx_01.ndf
Is this representing two separate databases? Just one database
Have you run any benchmarking tools such as SQLIO or IOSIM? No, but we use Quest Spotlight and Quest Performance Analysis for SQL Server along with other tools such as the performance dashboard reports.
What is your allocation unit size (8k or 64k or something else)? Systems handles the Netapp
Is your partition properly offset? Can't answer that one.
How is the volume fragmentation? I've read this isn't pertinent on a SAN like a netapp.
Have you checked the growth sizes of your data and log files? We bumped them up to 1 or 2GB generally quite a while back. I couldn't convince anyone to turn on instant file initialization -- again, they thought it irrelevant to a SAN
What is the VLF fragmentation looking like? I shrunk/rebuilt the single log file quite a while back until it was pretty good. The file is now 135GB on it's own 350GB lun.
What is the RAID config of your netapp (RAID DP or something else)? Raid 6 I'm told
March 1, 2011 at 2:24 pm
That is correct for NetApp.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 1, 2011 at 2:26 pm
nevermind, I see that the answers are inline in the quote.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply