Hi,
I wondered in anyone can help with the following problem that i'm
experiencing, i'll try to provide as much info as possible and any
suggestions would be appreciated.
I have just started at an organsiation and there seems to be slow
performance maybe on the san on a 64bit itanium dual core machine. 4
CPUs are being showed to sql server, it also has 16gb of RAM. I'll
start with the configuration of the SAN.
After speaking to the SAN guy, rather than carve the SAN up into
different area's for san Logs/Data etc they have gone for the approach
of spreading a Vdisk across as many spindles as possible (All 145 of
them). So the area that is presented to the SQL Server according the
the SAN guys is a vraid 5 stripe made up of all 145 disks which are
all 72gb fibre-channel disks.
This storage is not just made available to sql server but also made
available to other apps as well that need storage. Having read the
manufactres best practice on setting this up there is a valid argument
for doing this.
The bandwidth from the SAN is 2Gb fibre, with each computer that uses
the SAN having 2Gb fibre cards.
Clearly, that could act as a bottle-neck. But, there's nothing that
can be done about it according to the SAN guy.
Needless to say, any changes on the SAN are pretty much going to be
out of the question as far as he's concerned but i think performance
isn't that good for the type of box they have and the SAN its attached
to.
The 2nd thing i'll explain is the setup of the database in question,
firstly whoever set it up split the database into 16 different file of
4 filegroups so the table that i'm selecting to is in one filegroup
split over 4 files and the the table selecting from is in another
filegroup made up of another 4 files. These are placed on the same
physical disk made up of the SAN LUN with 145 spindles.
Anyway when i do a select from a sales table which has various group
bys and then insert the results into a blank table with no indexes it
can take over 2hours for 200k rows which i find very slow.
When i look at the sysprocesses table i am getting various waits as
follows :-
72 4272 0 0x0042 900 PAGEIOLATCH_SH 6:9:2192094
72 4272 0 0x0069 0 SLEEP_TASK
72 4272 0 0x0000 0 SOS_SCHEDULER_YIELD
The process seams to be going inbetween a PAGEIOLATCH and
SOS_SCHEDULER_YIELD a few times per second.
Running the following to get io stalls gives the following :-
Select * from sys.dm_io_virtual_file_stats (6,7)
Select * from sys.dm_io_virtual_file_stats (6,8)
Select * from sys.dm_io_virtual_file_stats (6,9)
Select * from sys.dm_io_virtual_file_stats (6,10)
gives results like :-
6 7 1708539850 1562421 82465128448 294572225 26431 2455404544 12438340
307010565 44907495424 0x0000000000000954
It worries me that when the process is on the PAGEIOLATCH the wait
can be over 1000. Is it normal for the wait to be this long and what
would be the best way to prove one way or another if the configuration
of the san is causing this kind of performance?
Thanks for any suggestions in advance
Ian.ps. The perf mon stats we are seeing are as follows :-
Avg Disk Read/Sec are between 0.06 and 0.3 on the select partion when
the routine starts up. which i think must be a little slow.
Disk Read/Sec is around 170|||The SAN setup is not necessarily a problem. Spreading the I/Os over as many
spindles as possible is in general a good approach.
There are three possible causes: (1) your SQL query is not efficient (e.g.
bad plan), (2) you are I/O throughput limited, and (3) the performance is
killed by the I/O latency problem. Point 3 is unlikely as relevant as Point 2
in this case because you query appears to be dealing a large data set.
I'd first check if an inefficient parallel plan is being used. Assume that
your I/O subsystem can do ~200MB/sec, it shouldbe able to pull in over a
terabyte of data in 2 hours unless the query plan is such that it results in
smaller I/Os or not stressing I/O at all. A bad parallelism plan can do that
to you.
You can check several perfmon counters to get a feel whether the I/O
subsystem is saturated, in particular: Avg. Disk Bytes/Read and Avg. Disk
sec/Read. The former tells you how much throughput you are using and the
latter tells you how large is each read. To efficiently process
reporting-type of queries, the latter should be relatively large. Otherwise,
the former will not be good. Ideally, the latter should be larger than 64K
and the fomer should be close to 200MB/sec.
To be sure about what kind of throughput you can get from the drive
presented from your SAN, you should run some tests. If you have a chance, try
a simple table scan on a large and wide table to see how many MBs/sec you
achieve.
BTW, are you using a sinle 2Gb card or two load-balanced 1Gb cards?
Linchi
"ianwr" wrote:
> Hi,
> I wondered in anyone can help with the following problem that i'm
> experiencing, i'll try to provide as much info as possible and any
> suggestions would be appreciated.
> I have just started at an organsiation and there seems to be slow
> performance maybe on the san on a 64bit itanium dual core machine. 4
> CPUs are being showed to sql server, it also has 16gb of RAM. I'll
> start with the configuration of the SAN.
> After speaking to the SAN guy, rather than carve the SAN up into
> different area's for san Logs/Data etc they have gone for the approach
> of spreading a Vdisk across as many spindles as possible (All 145 of
> them). So the area that is presented to the SQL Server according the
> the SAN guys is a vraid 5 stripe made up of all 145 disks which are
> all 72gb fibre-channel disks.
> This storage is not just made available to sql server but also made
> available to other apps as well that need storage. Having read the
> manufactres best practice on setting this up there is a valid argument
> for doing this.
> The bandwidth from the SAN is 2Gb fibre, with each computer that uses
> the SAN having 2Gb fibre cards.
> Clearly, that could act as a bottle-neck. But, there's nothing that
> can be done about it according to the SAN guy.
> Needless to say, any changes on the SAN are pretty much going to be
> out of the question as far as he's concerned but i think performance
> isn't that good for the type of box they have and the SAN its attached
> to.
> The 2nd thing i'll explain is the setup of the database in question,
> firstly whoever set it up split the database into 16 different file of
> 4 filegroups so the table that i'm selecting to is in one filegroup
> split over 4 files and the the table selecting from is in another
> filegroup made up of another 4 files. These are placed on the same
> physical disk made up of the SAN LUN with 145 spindles.
> Anyway when i do a select from a sales table which has various group
> bys and then insert the results into a blank table with no indexes it
> can take over 2hours for 200k rows which i find very slow.
> When i look at the sysprocesses table i am getting various waits as
> follows :-
> 72 4272 0 0x0042 900 PAGEIOLATCH_SH 6:9:2192094
> 72 4272 0 0x0069 0 SLEEP_TASK
> 72 4272 0 0x0000 0 SOS_SCHEDULER_YIELD
> The process seams to be going inbetween a PAGEIOLATCH and
> SOS_SCHEDULER_YIELD a few times per second.
> Running the following to get io stalls gives the following :-
> Select * from sys.dm_io_virtual_file_stats (6,7)
> Select * from sys.dm_io_virtual_file_stats (6,8)
> Select * from sys.dm_io_virtual_file_stats (6,9)
> Select * from sys.dm_io_virtual_file_stats (6,10)
> gives results like :-
> 6 7 1708539850 1562421 82465128448 294572225 26431 2455404544 12438340
> 307010565 44907495424 0x0000000000000954
> It worries me that when the process is on the PAGEIOLATCH the wait
> can be over 1000. Is it normal for the wait to be this long and what
> would be the best way to prove one way or another if the configuration
> of the san is causing this kind of performance?
> Thanks for any suggestions in advance
> Ian.
>|||I think the SAN guy said it was a single 2gb card on the san and that
all servers that accessed the same san also used 2gb cards so he knows
this could be a bottleneck, the entire company uses this san for just
about all server ... i would estimate there must be about 6 MIS type
servers using this SAN 3 live and 3 test plus a number of smaller TP
type systems.
I'll run a few tests on the throughput and let you know
Ian|||Ok, just done a quick table scan on a large table and the stats were
as follows :-
avg disk bytes/sec approx 76,000 which is on 76k, not exactly the
200mb a sec you had said.
Avg disk sec/read 0.6
I take it these figures aren't too good. Any idea what i can check to
see why they look so bad?
Ian.|||stats on the tablescan are as follows :-
Avg. Disk Bytes/Read 76,000 on average which = 76k which isn't
anything near your 200mb/sec
Avg. Disk sec/Read = 0.6
Any ideas how i can narrow down exactly why the throughput is so bad?
Thanks
Ian.|||> the former will not be good. Ideally, the latter should be larger than 64K
> and the fomer should be close to 200MB/sec.
Just noticed a mistake here. What I meant to say is that the I/O block size
(i.e. Avg Disk Bytes/Read) shoud be larger than 64K for reporting queries,
and the throughput (i.e. Avg. Read Bytes/sec) should be close to ~200MB/sec
in the ideal situation (given your setup).
Linchi
"Linchi Shea" wrote:
> The SAN setup is not necessarily a problem. Spreading the I/Os over as many
> spindles as possible is in general a good approach.
> There are three possible causes: (1) your SQL query is not efficient (e.g.
> bad plan), (2) you are I/O throughput limited, and (3) the performance is
> killed by the I/O latency problem. Point 3 is unlikely as relevant as Point 2
> in this case because you query appears to be dealing a large data set.
> I'd first check if an inefficient parallel plan is being used. Assume that
> your I/O subsystem can do ~200MB/sec, it shouldbe able to pull in over a
> terabyte of data in 2 hours unless the query plan is such that it results in
> smaller I/Os or not stressing I/O at all. A bad parallelism plan can do that
> to you.
> You can check several perfmon counters to get a feel whether the I/O
> subsystem is saturated, in particular: Avg. Disk Bytes/Read and Avg. Disk
> sec/Read. The former tells you how much throughput you are using and the
> latter tells you how large is each read. To efficiently process
> reporting-type of queries, the latter should be relatively large. Otherwise,
> the former will not be good. Ideally, the latter should be larger than 64K
> and the fomer should be close to 200MB/sec.
> To be sure about what kind of throughput you can get from the drive
> presented from your SAN, you should run some tests. If you have a chance, try
> a simple table scan on a large and wide table to see how many MBs/sec you
> achieve.
> BTW, are you using a sinle 2Gb card or two load-balanced 1Gb cards?
> Linchi
> "ianwr" wrote:
> > Hi,
> >
> > I wondered in anyone can help with the following problem that i'm
> > experiencing, i'll try to provide as much info as possible and any
> > suggestions would be appreciated.
> >
> > I have just started at an organsiation and there seems to be slow
> > performance maybe on the san on a 64bit itanium dual core machine. 4
> > CPUs are being showed to sql server, it also has 16gb of RAM. I'll
> > start with the configuration of the SAN.
> >
> > After speaking to the SAN guy, rather than carve the SAN up into
> > different area's for san Logs/Data etc they have gone for the approach
> > of spreading a Vdisk across as many spindles as possible (All 145 of
> > them). So the area that is presented to the SQL Server according the
> > the SAN guys is a vraid 5 stripe made up of all 145 disks which are
> > all 72gb fibre-channel disks.
> >
> > This storage is not just made available to sql server but also made
> > available to other apps as well that need storage. Having read the
> > manufactres best practice on setting this up there is a valid argument
> > for doing this.
> >
> > The bandwidth from the SAN is 2Gb fibre, with each computer that uses
> > the SAN having 2Gb fibre cards.
> > Clearly, that could act as a bottle-neck. But, there's nothing that
> > can be done about it according to the SAN guy.
> >
> > Needless to say, any changes on the SAN are pretty much going to be
> > out of the question as far as he's concerned but i think performance
> > isn't that good for the type of box they have and the SAN its attached
> > to.
> >
> > The 2nd thing i'll explain is the setup of the database in question,
> > firstly whoever set it up split the database into 16 different file of
> > 4 filegroups so the table that i'm selecting to is in one filegroup
> > split over 4 files and the the table selecting from is in another
> > filegroup made up of another 4 files. These are placed on the same
> > physical disk made up of the SAN LUN with 145 spindles.
> >
> > Anyway when i do a select from a sales table which has various group
> > bys and then insert the results into a blank table with no indexes it
> > can take over 2hours for 200k rows which i find very slow.
> >
> > When i look at the sysprocesses table i am getting various waits as
> > follows :-
> >
> > 72 4272 0 0x0042 900 PAGEIOLATCH_SH 6:9:2192094
> > 72 4272 0 0x0069 0 SLEEP_TASK
> > 72 4272 0 0x0000 0 SOS_SCHEDULER_YIELD
> >
> > The process seams to be going inbetween a PAGEIOLATCH and
> > SOS_SCHEDULER_YIELD a few times per second.
> >
> > Running the following to get io stalls gives the following :-
> >
> > Select * from sys.dm_io_virtual_file_stats (6,7)
> > Select * from sys.dm_io_virtual_file_stats (6,8)
> > Select * from sys.dm_io_virtual_file_stats (6,9)
> > Select * from sys.dm_io_virtual_file_stats (6,10)
> >
> > gives results like :-
> >
> > 6 7 1708539850 1562421 82465128448 294572225 26431 2455404544 12438340
> > 307010565 44907495424 0x0000000000000954
> >
> > It worries me that when the process is on the PAGEIOLATCH the wait
> > can be over 1000. Is it normal for the wait to be this long and what
> > would be the best way to prove one way or another if the configuration
> > of the san is causing this kind of performance?
> >
> > Thanks for any suggestions in advance
> >
> > Ian.
> >|||Just to make sure I'm raeding the right numbers. You said 76K/sec and
76K/read. These two numbers don't jive (unless the I/O subsystem is so
horribly bad). So which is which?
Assume that it's 76K/read and the thoughput in terms of bytes/sec is also in
KB's not in MB's. A latency of 0.6 second with a I/O block size of ~76K and
such a low throughput is bad. (BTW, I didn't mean to suggest 200MB/sec for
Avg. Disk Bytes/Read. Rather, I meant to say Avg. Read Bytes/sec,, i.e. the
throughput not the I/O block size. That was a typo in my previous reply).
One way to be 100% sure whether you got a raw deal on the storage is to run
some more specific I/O benchmark tests with IOMeter or sqlio.exe since that
would take SQL Server out of the picture and just focus on the performance of
the disk I/O subsystem. If the numbers are bad from IOMeter or sqlio.exe, you
can use those numbers to confront your SAN folks, and they should not have
any excuses.
Linchi
"ianwr" wrote:
> stats on the tablescan are as follows :-
> Avg. Disk Bytes/Read 76,000 on average which = 76k which isn't
> anything near your 200mb/sec
> Avg. Disk sec/Read = 0.6
> Any ideas how i can narrow down exactly why the throughput is so bad?
> Thanks
> Ian.
>|||Thanks, will try again on monday then and see where the numbers are.
Will post back on the problem next week, but thanks for the responses
so far anyway.
Ian.|||Holy crap! 600ms disk sec/read is absolutely horrible! This number on a
properly tuned I/O subsystem sould be 2 ORDERS OF MAGNITUDE lower! The SAN
is clearly overburdened and you will never get adequate performance with
that kind of latency.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:8f28f2c6-179f-4324-8b1b-1ee378b3c157@.y43g2000hsy.googlegroups.com...
> stats on the tablescan are as follows :-
> Avg. Disk Bytes/Read 76,000 on average which = 76k which isn't
> anything near your 200mb/sec
> Avg. Disk sec/Read = 0.6
> Any ideas how i can narrow down exactly why the throughput is so bad?
> Thanks
> Ian.|||Guys,
Just a quick update on the performance, here are the performance
counters i took this morning running the table scan again :-
Disk Rad Bytes.Sec = between 11m and 33m was very choppy. so i guess
this is about 10 times too slow as it's on average about 20mb/sec
rather than the 200mb you were expecting.
Avg Disk Bytes/Read was around the 77k mark
Avg Disk Sec/Read was around the 0.7 mark but at times went upto
1.2 ... pretty shocking. Going to get sqlio on the job and see if we
can get some timing to go back to the SAN guys with.
Ian.|||One very interesting point to note, i did the same on an identical
server that is running sql 2000 rather than 2005, according to the san
guy its setup identical and the performance times were very different.
throughput was 80mb/sec rather than 20mb/sec and latency was 30ms
rather than the 600-700ms. I tried 3 or 4 times with the same query
and results were constant.
Gone back to the SAN guy and asked him to confirm everything was
identical. If he says they are will run SQLio on the server to confirm
if SQL server is the culprit or the configurations are indeed
different.
Ian,|||There is definitely something different somewhere. It could possibly be
linked to OS file level fragmentation, but I doubt that could be the sole
reason for that level of throughput/latency issues.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:61d00455-495f-428a-9fa4-4712d861933b@.x69g2000hsx.googlegroups.com...
> One very interesting point to note, i did the same on an identical
> server that is running sql 2000 rather than 2005, according to the san
> guy its setup identical and the performance times were very different.
> throughput was 80mb/sec rather than 20mb/sec and latency was 30ms
> rather than the 600-700ms. I tried 3 or 4 times with the same query
> and results were constant.
> Gone back to the SAN guy and asked him to confirm everything was
> identical. If he says they are will run SQLio on the server to confirm
> if SQL server is the culprit or the configurations are indeed
> different.
>
> Ian,|||Disk sec/read is extraordinarily high. You don't need a sqlio run to give
something to the SAN guys IMHO. 700-1200ms latency is way more than enough.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:e608dedf-961a-48cd-b53f-60df4df1d1ec@.e67g2000hsc.googlegroups.com...
> Guys,
> Just a quick update on the performance, here are the performance
> counters i took this morning running the table scan again :-
> Disk Rad Bytes.Sec = between 11m and 33m was very choppy. so i guess
> this is about 10 times too slow as it's on average about 20mb/sec
> rather than the 200mb you were expecting.
> Avg Disk Bytes/Read was around the 77k mark
> Avg Disk Sec/Read was around the 0.7 mark but at times went upto
> 1.2 ... pretty shocking. Going to get sqlio on the job and see if we
> can get some timing to go back to the SAN guys with.
> Ian.
>
>|||Will keep you posted and let you know what it was when i get to the
bottom of it.|||On the contrary, I'd strongly recommend running sqlio or IOMeter precisely
because something is fishy and you need to be able to defend your position.
If you don't run sqlio/IOMeter (or any reliable disk I/O benchmark that is
independent of SQL Server), your SAN folks can argue that the SAN config is
identical, and it must be something with SQL Server. How do you respond to
that if you only have SQL-dependent results?
Linchi
"TheSQLGuru" wrote:
> Disk sec/read is extraordinarily high. You don't need a sqlio run to give
> something to the SAN guys IMHO. 700-1200ms latency is way more than enough.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
> news:e608dedf-961a-48cd-b53f-60df4df1d1ec@.e67g2000hsc.googlegroups.com...
> > Guys,
> >
> > Just a quick update on the performance, here are the performance
> > counters i took this morning running the table scan again :-
> >
> > Disk Rad Bytes.Sec = between 11m and 33m was very choppy. so i guess
> > this is about 10 times too slow as it's on average about 20mb/sec
> > rather than the 200mb you were expecting.
> >
> > Avg Disk Bytes/Read was around the 77k mark
> >
> > Avg Disk Sec/Read was around the 0.7 mark but at times went upto
> > 1.2 ... pretty shocking. Going to get sqlio on the job and see if we
> > can get some timing to go back to the SAN guys with.
> >
> > Ian.
> >
> >
> >
>
>|||> throughput was 80mb/sec rather than 20mb/sec and latency was 30ms
> rather than the 600-700ms. I tried 3 or 4 times with the same query
> and results were constant.
For the latency of 30ms, was the I/O size around 70K as you previously
reported? make sure that when you compare 30ms with 600ms in latency, they
are doing the same kind of I/Os, especially they are issuing the same sized
I/Os.
When you have a chance to run sqlio or IOMeter, you should cover both small
sized I/Os and large sized I/Os. With small sized I/Os (e.g. ~<= 8K per read
or write), pay attention to latency.
Linchi
"ianwr" wrote:
> One very interesting point to note, i did the same on an identical
> server that is running sql 2000 rather than 2005, according to the san
> guy its setup identical and the performance times were very different.
> throughput was 80mb/sec rather than 20mb/sec and latency was 30ms
> rather than the 600-700ms. I tried 3 or 4 times with the same query
> and results were constant.
> Gone back to the SAN guy and asked him to confirm everything was
> identical. If he says they are will run SQLio on the server to confirm
> if SQL server is the culprit or the configurations are indeed
> different.
>
> Ian,
>|||Linchi,
I ran SQLio and the reports were suprising .. and glad did. On the 3
boxes we have i ran 64K block sizes and on all 3 boxes they were
reading about 140-170mb/sec and latency was 6-7ms. I ran this multiple
times with the same results.
I then went back to the sql2005 box and ran the table scan and results
were as before i.e. latency 300-400ms peaking to 2000ms and throughput
at about 20mb/sec. I ran SQLIO again on this same box and the results
were the same as previous ie. 6-7ms and 160mb/sec throughput. very
strange.
So it doesn't look like its anything to do with the san but with sql
2005 communicating with the IO Subsystem somehow. I ran the table scan
on the sql 2000 box and latency was 30ms with throughput at 80mb/sec.
So for some reason the sql2000 box seems to run much quicker than the
two 2005 boxes which are running much slower. But the san is fine on
all three.
I did think it could be fragmentation on the disks at the O/S level
but why would this just be the case on the sql2005 boxes. As there
isn't much disk space left on these disks de-fragmenting them to test
this out would be a pain.
I do know that a lot of the hardware drivers and service packs are out
of date on all these servers and maybe this is having something to do
with it. But as this organisation doesn't have a proper dev
environment they are reluctant to upgrade everything to the most
recent versions.
Do you have any other ideas on what i could try?
Thanks
ian.
