Yeah that's what I said...what are they talking about. I am in the
process of doing some research on multiple files vs. multiple
filgroups. I have seen it discussed a bunch on these groups but I need
to perform some tests to back it up. During my research I came across
the following from the "SQL Server 2000 Operations Guide"
"Note on Disk Queue Length
This counter no longer yields meaningful results in interpreting disk
I/O problems with SQL Server 2000. This is because the SQL Server
engines dynamically manage disk I/O, which is why the Max Async I/O
setting no longer exists. A simplified explanation is that SQL Server
issues an order for disk I/O, but does not wait for that process to
complete before issuing another request. When the I/O request has
completed its work, the database engine is notified and processes the
results. SQL Server monitors the throughput on these requests, and will
manage the amount of I/O that the disks can effectively handle."
You can see it here
http://www.microsoft.com/technet/pro...n/sqlops6.mspx
can anyone make any sense of this.
"SQL Server monitors the throughput on these requests, and will manage
the amount of I/O that the disks can effectively handle."
ok what does this mean? it will mange the amount of IO...ok how.
Has anyone seen this, or has an explanation?
Thanks
Bert
This means SQL Server 2000 tries not to issue 200 IO per second if your IO
system can only handle 100 IO per second. So normally you would not see
average disk queue length to be more than 1. However sometimes disk queue
length can be high (more than 1) in some heavily loaded systems. If that
happens then you know you have an IO bottleneck. The % disk busy is also a
good indication and it should be used in conjunction with average disk queue
length.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
<bertcord@.gmail.com> wrote in message
news:1106861683.038716.139100@.c13g2000cwb.googlegr oups.com...
> Yeah that's what I said...what are they talking about. I am in the
> process of doing some research on multiple files vs. multiple
> filgroups. I have seen it discussed a bunch on these groups but I need
> to perform some tests to back it up. During my research I came across
> the following from the "SQL Server 2000 Operations Guide"
> "Note on Disk Queue Length
> This counter no longer yields meaningful results in interpreting disk
> I/O problems with SQL Server 2000. This is because the SQL Server
> engines dynamically manage disk I/O, which is why the Max Async I/O
> setting no longer exists. A simplified explanation is that SQL Server
> issues an order for disk I/O, but does not wait for that process to
> complete before issuing another request. When the I/O request has
> completed its work, the database engine is notified and processes the
> results. SQL Server monitors the throughput on these requests, and will
> manage the amount of I/O that the disks can effectively handle."
> You can see it here
> http://www.microsoft.com/technet/pro...n/sqlops6.mspx
> can anyone make any sense of this.
> "SQL Server monitors the throughput on these requests, and will manage
> the amount of I/O that the disks can effectively handle."
> ok what does this mean? it will mange the amount of IO...ok how.
> Has anyone seen this, or has an explanation?
> Thanks
> Bert
>
|||Disk queue length isn't particularly interesting because SQL Server throws a
lot of IOs at the disk and then goes to do other work. For instance,
read-ahead is a good example: if the server has a reasonable idea that it
will need several pages from disk in order to complete a scan, it will issue
the IOs for those pages before it actually needs them, with the expectation
that when we actually do need the pages, the IOs will have completed and we
won't have to wait. Here's the executive summary, I guess: by design, SQL
Server loads up the disk subsystem with IO requests so that we don't have to
wait for the pages when we need them. That causes IO queue lengths to
increase since there are typically always outstanding IOs posted by the
server. In some applications this might indicate a bottleneck, but SQL
Server is smart about it and uses asynchronous IO to its benefit.
Perhaps the following article (just recently posted) might illuminate the
subject a bit:
http://www.microsoft.com/technet/pro...IObasics.mspx.
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
<bertcord@.gmail.com> wrote in message
news:1106861683.038716.139100@.c13g2000cwb.googlegr oups.com...
> Yeah that's what I said...what are they talking about. I am in the
> process of doing some research on multiple files vs. multiple
> filgroups. I have seen it discussed a bunch on these groups but I need
> to perform some tests to back it up. During my research I came across
> the following from the "SQL Server 2000 Operations Guide"
> "Note on Disk Queue Length
> This counter no longer yields meaningful results in interpreting disk
> I/O problems with SQL Server 2000. This is because the SQL Server
> engines dynamically manage disk I/O, which is why the Max Async I/O
> setting no longer exists. A simplified explanation is that SQL Server
> issues an order for disk I/O, but does not wait for that process to
> complete before issuing another request. When the I/O request has
> completed its work, the database engine is notified and processes the
> results. SQL Server monitors the throughput on these requests, and will
> manage the amount of I/O that the disks can effectively handle."
> You can see it here
> http://www.microsoft.com/technet/pro...n/sqlops6.mspx
> can anyone make any sense of this.
> "SQL Server monitors the throughput on these requests, and will manage
> the amount of I/O that the disks can effectively handle."
> ok what does this mean? it will mange the amount of IO...ok how.
> Has anyone seen this, or has an explanation?
> Thanks
> Bert
>
2012年3月27日星期二
Disk Queue Length counters not meaningful in SQL 2000?
Yeah that's what I said...what are they talking about. I am in the
process of doing some research on multiple files vs. multiple
filgroups. I have seen it discussed a bunch on these groups but I need
to perform some tests to back it up. During my research I came across
the following from the "SQL Server 2000 Operations Guide"
"Note on Disk Queue Length
This counter no longer yields meaningful results in interpreting disk
I/O problems with SQL Server 2000. This is because the SQL Server
engines dynamically manage disk I/O, which is why the Max Async I/O
setting no longer exists. A simplified explanation is that SQL Server
issues an order for disk I/O, but does not wait for that process to
complete before issuing another request. When the I/O request has
completed its work, the database engine is notified and processes the
results. SQL Server monitors the throughput on these requests, and will
manage the amount of I/O that the disks can effectively handle."
You can see it here
http://www.microsoft.com/technet/pr...in/sqlops6.mspx
can anyone make any sense of this.
"SQL Server monitors the throughput on these requests, and will manage
the amount of I/O that the disks can effectively handle."
ok what does this mean? it will mange the amount of IO...ok how.
Has anyone seen this, or has an explanation?
Thanks
BertThis means SQL Server 2000 tries not to issue 200 IO per second if your IO
system can only handle 100 IO per second. So normally you would not see
average disk queue length to be more than 1. However sometimes disk queue
length can be high (more than 1) in some heavily loaded systems. If that
happens then you know you have an IO bottleneck. The % disk busy is also a
good indication and it should be used in conjunction with average disk queue
length.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
<bertcord@.gmail.com> wrote in message
news:1106861683.038716.139100@.c13g2000cwb.googlegroups.com...
> Yeah that's what I said...what are they talking about. I am in the
> process of doing some research on multiple files vs. multiple
> filgroups. I have seen it discussed a bunch on these groups but I need
> to perform some tests to back it up. During my research I came across
> the following from the "SQL Server 2000 Operations Guide"
> "Note on Disk Queue Length
> This counter no longer yields meaningful results in interpreting disk
> I/O problems with SQL Server 2000. This is because the SQL Server
> engines dynamically manage disk I/O, which is why the Max Async I/O
> setting no longer exists. A simplified explanation is that SQL Server
> issues an order for disk I/O, but does not wait for that process to
> complete before issuing another request. When the I/O request has
> completed its work, the database engine is notified and processes the
> results. SQL Server monitors the throughput on these requests, and will
> manage the amount of I/O that the disks can effectively handle."
> You can see it here
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx[/url
]
> can anyone make any sense of this.
> "SQL Server monitors the throughput on these requests, and will manage
> the amount of I/O that the disks can effectively handle."
> ok what does this mean? it will mange the amount of IO...ok how.
> Has anyone seen this, or has an explanation?
> Thanks
> Bert
>|||Disk queue length isn't particularly interesting because SQL Server throws a
lot of IOs at the disk and then goes to do other work. For instance,
read-ahead is a good example: if the server has a reasonable idea that it
will need several pages from disk in order to complete a scan, it will issue
the IOs for those pages before it actually needs them, with the expectation
that when we actually do need the pages, the IOs will have completed and we
won't have to wait. Here's the executive summary, I guess: by design, SQL
Server loads up the disk subsystem with IO requests so that we don't have to
wait for the pages when we need them. That causes IO queue lengths to
increase since there are typically always outstanding IOs posted by the
server. In some applications this might indicate a bottleneck, but SQL
Server is smart about it and uses asynchronous IO to its benefit.
Perhaps the following article (just recently posted) might illuminate the
subject a bit:
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx.[/
url]
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
<bertcord@.gmail.com> wrote in message
news:1106861683.038716.139100@.c13g2000cwb.googlegroups.com...
> Yeah that's what I said...what are they talking about. I am in the
> process of doing some research on multiple files vs. multiple
> filgroups. I have seen it discussed a bunch on these groups but I need
> to perform some tests to back it up. During my research I came across
> the following from the "SQL Server 2000 Operations Guide"
> "Note on Disk Queue Length
> This counter no longer yields meaningful results in interpreting disk
> I/O problems with SQL Server 2000. This is because the SQL Server
> engines dynamically manage disk I/O, which is why the Max Async I/O
> setting no longer exists. A simplified explanation is that SQL Server
> issues an order for disk I/O, but does not wait for that process to
> complete before issuing another request. When the I/O request has
> completed its work, the database engine is notified and processes the
> results. SQL Server monitors the throughput on these requests, and will
> manage the amount of I/O that the disks can effectively handle."
> You can see it here
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx[/url
]
> can anyone make any sense of this.
> "SQL Server monitors the throughput on these requests, and will manage
> the amount of I/O that the disks can effectively handle."
> ok what does this mean? it will mange the amount of IO...ok how.
> Has anyone seen this, or has an explanation?
> Thanks
> Bert
>|||Hello,
I am also interested in disk queue lengths reported by System Monitor when r
unning SQL Server 2000.
I have tempdb on a dedicated (mirrored) disk. I am seeing average disk queue
lengths of up to 8000 for a 5 minute sampling period on this disk. This see
ms to contradict Wei's point that SQL Server 2000 throttle's disk accesses w
hilst corroborating Ryan's that it loads the system up with requests.
However, as I am seeing response times of above a ten seconds on this disk ,
I still think I have a disk bottleneck. As I already have a fast disk syste
m I believe the application needs rewriting to reduce its use of tempdb.
I would be grateful for any further comment or insight on my particular prob
lem.
Thanks,
Paul
process of doing some research on multiple files vs. multiple
filgroups. I have seen it discussed a bunch on these groups but I need
to perform some tests to back it up. During my research I came across
the following from the "SQL Server 2000 Operations Guide"
"Note on Disk Queue Length
This counter no longer yields meaningful results in interpreting disk
I/O problems with SQL Server 2000. This is because the SQL Server
engines dynamically manage disk I/O, which is why the Max Async I/O
setting no longer exists. A simplified explanation is that SQL Server
issues an order for disk I/O, but does not wait for that process to
complete before issuing another request. When the I/O request has
completed its work, the database engine is notified and processes the
results. SQL Server monitors the throughput on these requests, and will
manage the amount of I/O that the disks can effectively handle."
You can see it here
http://www.microsoft.com/technet/pr...in/sqlops6.mspx
can anyone make any sense of this.
"SQL Server monitors the throughput on these requests, and will manage
the amount of I/O that the disks can effectively handle."
ok what does this mean? it will mange the amount of IO...ok how.
Has anyone seen this, or has an explanation?
Thanks
BertThis means SQL Server 2000 tries not to issue 200 IO per second if your IO
system can only handle 100 IO per second. So normally you would not see
average disk queue length to be more than 1. However sometimes disk queue
length can be high (more than 1) in some heavily loaded systems. If that
happens then you know you have an IO bottleneck. The % disk busy is also a
good indication and it should be used in conjunction with average disk queue
length.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
<bertcord@.gmail.com> wrote in message
news:1106861683.038716.139100@.c13g2000cwb.googlegroups.com...
> Yeah that's what I said...what are they talking about. I am in the
> process of doing some research on multiple files vs. multiple
> filgroups. I have seen it discussed a bunch on these groups but I need
> to perform some tests to back it up. During my research I came across
> the following from the "SQL Server 2000 Operations Guide"
> "Note on Disk Queue Length
> This counter no longer yields meaningful results in interpreting disk
> I/O problems with SQL Server 2000. This is because the SQL Server
> engines dynamically manage disk I/O, which is why the Max Async I/O
> setting no longer exists. A simplified explanation is that SQL Server
> issues an order for disk I/O, but does not wait for that process to
> complete before issuing another request. When the I/O request has
> completed its work, the database engine is notified and processes the
> results. SQL Server monitors the throughput on these requests, and will
> manage the amount of I/O that the disks can effectively handle."
> You can see it here
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx[/url
]
> can anyone make any sense of this.
> "SQL Server monitors the throughput on these requests, and will manage
> the amount of I/O that the disks can effectively handle."
> ok what does this mean? it will mange the amount of IO...ok how.
> Has anyone seen this, or has an explanation?
> Thanks
> Bert
>|||Disk queue length isn't particularly interesting because SQL Server throws a
lot of IOs at the disk and then goes to do other work. For instance,
read-ahead is a good example: if the server has a reasonable idea that it
will need several pages from disk in order to complete a scan, it will issue
the IOs for those pages before it actually needs them, with the expectation
that when we actually do need the pages, the IOs will have completed and we
won't have to wait. Here's the executive summary, I guess: by design, SQL
Server loads up the disk subsystem with IO requests so that we don't have to
wait for the pages when we need them. That causes IO queue lengths to
increase since there are typically always outstanding IOs posted by the
server. In some applications this might indicate a bottleneck, but SQL
Server is smart about it and uses asynchronous IO to its benefit.
Perhaps the following article (just recently posted) might illuminate the
subject a bit:
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx.[/
url]
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
<bertcord@.gmail.com> wrote in message
news:1106861683.038716.139100@.c13g2000cwb.googlegroups.com...
> Yeah that's what I said...what are they talking about. I am in the
> process of doing some research on multiple files vs. multiple
> filgroups. I have seen it discussed a bunch on these groups but I need
> to perform some tests to back it up. During my research I came across
> the following from the "SQL Server 2000 Operations Guide"
> "Note on Disk Queue Length
> This counter no longer yields meaningful results in interpreting disk
> I/O problems with SQL Server 2000. This is because the SQL Server
> engines dynamically manage disk I/O, which is why the Max Async I/O
> setting no longer exists. A simplified explanation is that SQL Server
> issues an order for disk I/O, but does not wait for that process to
> complete before issuing another request. When the I/O request has
> completed its work, the database engine is notified and processes the
> results. SQL Server monitors the throughput on these requests, and will
> manage the amount of I/O that the disks can effectively handle."
> You can see it here
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx[/url
]
> can anyone make any sense of this.
> "SQL Server monitors the throughput on these requests, and will manage
> the amount of I/O that the disks can effectively handle."
> ok what does this mean? it will mange the amount of IO...ok how.
> Has anyone seen this, or has an explanation?
> Thanks
> Bert
>|||Hello,
I am also interested in disk queue lengths reported by System Monitor when r
unning SQL Server 2000.
I have tempdb on a dedicated (mirrored) disk. I am seeing average disk queue
lengths of up to 8000 for a 5 minute sampling period on this disk. This see
ms to contradict Wei's point that SQL Server 2000 throttle's disk accesses w
hilst corroborating Ryan's that it loads the system up with requests.
However, as I am seeing response times of above a ten seconds on this disk ,
I still think I have a disk bottleneck. As I already have a fast disk syste
m I believe the application needs rewriting to reduce its use of tempdb.
I would be grateful for any further comment or insight on my particular prob
lem.
Thanks,
Paul
Disk Queue Length counters not meaningful in SQL 2000?
Yeah that's what I said...what are they talking about. I am in the
process of doing some research on multiple files vs. multiple
filgroups. I have seen it discussed a bunch on these groups but I need
to perform some tests to back it up. During my research I came across
the following from the "SQL Server 2000 Operations Guide"
"Note on Disk Queue Length
This counter no longer yields meaningful results in interpreting disk
I/O problems with SQL Server 2000. This is because the SQL Server
engines dynamically manage disk I/O, which is why the Max Async I/O
setting no longer exists. A simplified explanation is that SQL Server
issues an order for disk I/O, but does not wait for that process to
complete before issuing another request. When the I/O request has
completed its work, the database engine is notified and processes the
results. SQL Server monitors the throughput on these requests, and will
manage the amount of I/O that the disks can effectively handle."
You can see it here
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx
can anyone make any sense of this.
"SQL Server monitors the throughput on these requests, and will manage
the amount of I/O that the disks can effectively handle."
ok what does this mean? it will mange the amount of IO...ok how.
Has anyone seen this, or has an explanation?
Thanks
BertDisk queue length isn't particularly interesting because SQL Server throws a
lot of IOs at the disk and then goes to do other work. For instance,
read-ahead is a good example: if the server has a reasonable idea that it
will need several pages from disk in order to complete a scan, it will issue
the IOs for those pages before it actually needs them, with the expectation
that when we actually do need the pages, the IOs will have completed and we
won't have to wait. Here's the executive summary, I guess: by design, SQL
Server loads up the disk subsystem with IO requests so that we don't have to
wait for the pages when we need them. That causes IO queue lengths to
increase since there are typically always outstanding IOs posted by the
server. In some applications this might indicate a bottleneck, but SQL
Server is smart about it and uses asynchronous IO to its benefit.
Perhaps the following article (just recently posted) might illuminate the
subject a bit:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx.
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
<bertcord@.gmail.com> wrote in message
news:1106861683.038716.139100@.c13g2000cwb.googlegroups.com...
> Yeah that's what I said...what are they talking about. I am in the
> process of doing some research on multiple files vs. multiple
> filgroups. I have seen it discussed a bunch on these groups but I need
> to perform some tests to back it up. During my research I came across
> the following from the "SQL Server 2000 Operations Guide"
> "Note on Disk Queue Length
> This counter no longer yields meaningful results in interpreting disk
> I/O problems with SQL Server 2000. This is because the SQL Server
> engines dynamically manage disk I/O, which is why the Max Async I/O
> setting no longer exists. A simplified explanation is that SQL Server
> issues an order for disk I/O, but does not wait for that process to
> complete before issuing another request. When the I/O request has
> completed its work, the database engine is notified and processes the
> results. SQL Server monitors the throughput on these requests, and will
> manage the amount of I/O that the disks can effectively handle."
> You can see it here
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx
> can anyone make any sense of this.
> "SQL Server monitors the throughput on these requests, and will manage
> the amount of I/O that the disks can effectively handle."
> ok what does this mean? it will mange the amount of IO...ok how.
> Has anyone seen this, or has an explanation?
> Thanks
> Bert
>|||This means SQL Server 2000 tries not to issue 200 IO per second if your IO
system can only handle 100 IO per second. So normally you would not see
average disk queue length to be more than 1. However sometimes disk queue
length can be high (more than 1) in some heavily loaded systems. If that
happens then you know you have an IO bottleneck. The % disk busy is also a
good indication and it should be used in conjunction with average disk queue
length.
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
<bertcord@.gmail.com> wrote in message
news:1106861683.038716.139100@.c13g2000cwb.googlegroups.com...
> Yeah that's what I said...what are they talking about. I am in the
> process of doing some research on multiple files vs. multiple
> filgroups. I have seen it discussed a bunch on these groups but I need
> to perform some tests to back it up. During my research I came across
> the following from the "SQL Server 2000 Operations Guide"
> "Note on Disk Queue Length
> This counter no longer yields meaningful results in interpreting disk
> I/O problems with SQL Server 2000. This is because the SQL Server
> engines dynamically manage disk I/O, which is why the Max Async I/O
> setting no longer exists. A simplified explanation is that SQL Server
> issues an order for disk I/O, but does not wait for that process to
> complete before issuing another request. When the I/O request has
> completed its work, the database engine is notified and processes the
> results. SQL Server monitors the throughput on these requests, and will
> manage the amount of I/O that the disks can effectively handle."
> You can see it here
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx
> can anyone make any sense of this.
> "SQL Server monitors the throughput on these requests, and will manage
> the amount of I/O that the disks can effectively handle."
> ok what does this mean? it will mange the amount of IO...ok how.
> Has anyone seen this, or has an explanation?
> Thanks
> Bert
>|||Thanks for responding. I read the article today and it was very good.
I am however interested in how SQL manages IO when working with
multiple files in a single filegroup.
For example how Flushing Pages to disk is affected when a database has
multiple files. My assumption is that it is the same except that the
process performing the write (lazy write, Checkpoint or Eager write)
would pass off the write requests to the thread that is working on the
specific file. Based on my test results writing to multiple files was
always a tiny bit slower if the disk subsystem was not under heavy
load.
I am aware that here is some confusion about how SQL server issue
threads for reads. Some people feel that having more files is the only
way SQL will spawn more threads. Andrew Kelly pointed out in the
following Google threads this is not correct for SQL2000.
http://linktrim.com/x2
http://linktrim.com/x3
For writes I confirmed that SQL does write to all files in parallel. I
used fn_virtualfilestats to test. I had a Query Analyzer windows open
and I held down the F5 key so it was constantly refreshing, I could see
that the NumberReads column for all the files where incrementing on
each refresh. I do admit that my method was not 100% scientific but it
was the only way I could think of to confirm this.
The specific things I want to test are.
1. Do multiple files on the same drive perform better than a single
file
2. Do multiple files spread on separate drives perform better than a
single file.
My test server configuration is as follows
Dell 6650, 4 Xeon 1595, 5.2 GB of Ram
Disk configuration as follows
ArrayGroup0 - Channel1 E: RAID5 - 3 Drives
ArrayGroup1 - Channel0 F: RAID5 - 7 Drives
ArrayGroup1 - Channel1 J: RAID5 - 7 Drives
ArrayGroup2 - Channel0 G: RAID10 - 12 Drives
Drives E,F, and J only have data files for the test databases. Drive G
contains the tempDB log files for the test databases and all BULK
INSERT files.
I created 3 separate databases all having a single fileGroup, the file
configuration is as follows.
1. BulkInOut1 - One data File on the E drive.
2. BulkInOut2 - Three datafiles, one each on E, F and J
3. BulkInOut3 - Three datafiles all on the E drive.
Test1 - Bulk inserting 11,000,000 into an empty table with no indexes.
I ran each test 5 times and here are the results averaged in seconds.
BulkInOut1 - 165 (One File)
BulkInOut2 - 173 (3 files sep drives)
BulkInOut3 - 172 (3 files same drive)
Raw Results
BulkInOut1 - 164,162,169,163,165
BulkInOut2 - 169,175,184,166,169
BulkInOut3 - 172,170,168,180,171
Test2 - Create indexes on table from test 1. Create Clustered and two
non-clustered.
BulkInOut1 - 127 (One File)
BulkInOut2 - 98 (3 files sep drives)
BulkInOut3 - 132 (3 files same drive)
Raw Results
BulkInOut1 - 126,129,126
BulkInOut2 - 101,98,96
BulkInOut3 - 135,132,130
Based on these two test I am make the following conclusions.
1. Multiple files on the same drive is always slower do not do it.
2. Having multiple files spread onto multiple drives can perform
better. What happened in my test cases is that test one hardly
stressed the disk-subsystem. Therefore the multiple disks performed
slower. This I assume is due to the overhead of context switching. It
test two IO demands increased and disk queuing occurred on BulkInOut3
(2 files same drive), this caused it to be the slowest.
Next week I am going to perform some more tests with a better simulated
load. I am going to write an app that opens multiple connections and
inserts and selects data.
Any comments on my results or methodology would be appreciated. IF any
one else has performance data on file IO test I would love to see them
Thanks for you time
Bert Corderman|||I have completed a few more tests and here are the results. As a recap
my drive config is as follows
ArrayGroup0 - Channel1 E: RAID5 - 3 Drives
ArrayGroup1 - Channel0 F: RAID5 - 7 Drives
ArrayGroup1 - Channel1 J: RAID5 - 7 Drives
ArrayGroup2 - Channel0 G: RAID10 - 12 Drives
Test DB's
1. BulkInOut1 - One data File on the E drive.
2. BulkInOut2 - Three data files, one each on E, F and J
3. BulkInOut3 - Three data files all on the E drive.
Creation of Clustered Indexes only
BulkInOut1 - 73 (One File)
BulkInOut2 - 48 (3 files sep drives)
BulkInOut3 - 78 (3 files same drive)
As another test I wanted to see the performance of creating a separate
Filegroup for the non-clustered indexes. I tested a few configuration.
For these tests I used the database with one data file on E
(BulkInOut1)
The first test was a baseline
1. Non-Clustered Indexes Created on Primary filegroup - 66
The following tests added the Index filegroup with different file
placement.
2. 1 File on J = 62
3. 2 Files on J = 62
4. 1 File on J, one on F = 60
5. 5 Files on J = 65
Again my tests show that having multiple files in the same FileGroup
does not perform any better than having only one file, and in some
cases will even perform slower.
Any suggestions or input would be appreciated
Thanks
Bert|||I don't know, Bert. You've seemed to have done the most methodical and
exhaustive test that I've seen reported to in this forum.
If you've seen better, or at least equal performance, using single file
filegroups, Occam's Razor would say to go that route... KISS principle,
right?
Also, did you try reformatting the drives using 64 KB clusters? We have
experience quite a bit of throughput increase using this cluster allocation.
If you can afford it, use RAID 10 for everything. However, you should be
able to get buy with RAID 5 for data files, RAID 10 for log files, and RAID
1 for the tempdb, which should be on a dedicated set of disks.
Also, having more active spindles per database file is more important than
files themselves. If you can keep each processor running I/O against each
spindle, then whenever I/O requests come in, the more efficient they will
be. Second, having as many channels per parallel request will also increase
your throughput.
Check out this link for some Case Studies on this topic. Granted, the cover
a broad range of installation issues, but all of them have disk and file
layout strategies. I found the EMC CLARiiON whitepaper extremely
interesting. They introduce a term called "Plaiding," which is a
combination of RAID striping at the hardware level and multiple SQL Server
file placements at the software level, similar to a plaid pattern. This way
you maximize spindle usage per request.
There is A LOT of information on this site. Take your time to check out
each and every one. I have and it has provided quite a bit of information
to consider.
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
See what you think.
Sincerely,
Anthony Thomas
"bert" <bertcord@.gmail.com> wrote in message
news:1107304742.349333.272980@.z14g2000cwz.googlegroups.com...
I have completed a few more tests and here are the results. As a recap
my drive config is as follows
ArrayGroup0 - Channel1 E: RAID5 - 3 Drives
ArrayGroup1 - Channel0 F: RAID5 - 7 Drives
ArrayGroup1 - Channel1 J: RAID5 - 7 Drives
ArrayGroup2 - Channel0 G: RAID10 - 12 Drives
Test DB's
1. BulkInOut1 - One data File on the E drive.
2. BulkInOut2 - Three data files, one each on E, F and J
3. BulkInOut3 - Three data files all on the E drive.
Creation of Clustered Indexes only
BulkInOut1 - 73 (One File)
BulkInOut2 - 48 (3 files sep drives)
BulkInOut3 - 78 (3 files same drive)
As another test I wanted to see the performance of creating a separate
Filegroup for the non-clustered indexes. I tested a few configuration.
For these tests I used the database with one data file on E
(BulkInOut1)
The first test was a baseline
1. Non-Clustered Indexes Created on Primary filegroup - 66
The following tests added the Index filegroup with different file
placement.
2. 1 File on J = 62
3. 2 Files on J = 62
4. 1 File on J, one on F = 60
5. 5 Files on J = 65
Again my tests show that having multiple files in the same FileGroup
does not perform any better than having only one file, and in some
cases will even perform slower.
Any suggestions or input would be appreciated
Thanks
Bert|||Thanks for the input Anthony...
Yes KISS is what I want to do...but again if the difference is that
much greater sometimes it just isn't simple any more.
>>Also, did you try reformatting the drives using 64 KB clusters?
No I have not tried this yet...this is actually the first I have heard
of this but will definitely look into it. They actually talk of using
64kb clusters in the Rosetta Genomics 10-Terabyte Human Genome Database
white paper. (From the other link you mentioned) I had actually
thought of this but I did not know it was possible to format a drive
like this. For example sitting on my XP machine at home when I go to
format a drive the only options I have are 512, 1024, 2048 and 4096
bytes... I will have to look at this some more.
>>Also, having more active spindles per database file is more important
than files themselves.
I agree 110%. I actually performed a bunch more tests than what I
posted here. Having multiple files spread to different arrays only
increased performance if there was enough IO to saturate the IO of one
drive\controller. IF the IO wasn't enough the performance was
actually slower. The KISS philosophy leads me to recommend only
creating multiple files per file group if the IO generated can saturate
a single drive. Before adding more files I would recommend first
placing heavily accessed tables onto a new file group onto a separate
drive. But again I would like to do more research in this area. For
example lets say you have a big database with only 2 tables (simple
example) and you have 2 drives. Would you a) create one file group
with a file on each drive or b) two file groups with a file on each
drive?
>>They introduce a term called "Plaiding," which is a combination of
RAID striping at the hardware level and multiple SQL Server file
placements at the >>software level, similar to a plaid pattern.
Hum this sound very interesting. Unfortunately the link from the
Microsoft website no longer works. I tried searching the EMC site for
the article by searching for the article title and for the term plaid
and Plaiding... no dice... you don't happen to have the article
saved anywhere do you? I most cases I would prefer to let the hardware
perform the striping...but I would love to read the article
Thanks
Bert|||For the 64 KB allocation units (disk clusters), as well as many other useful
operational tips, check out the Microsoft SQL Server 2000 Operations Manual.
Be aware, however, that once you go above 4 KB clusters, disk compression
and defragmentation are no longer available through the native Windows
tools.
As far as your question concerning the two big tables, the answer depends on
the usage, as always. If you were to join the two tables frequently, I
would place each table within a sperate, single file, filegroup, one placed
on each drive in order to get simultaneous read-ahead reads for the JOIN
operation. However, an option not provided, with only two drives, I'd want
to maximize spindle usage; so, I'd stripe the two dirves together, at the
hardware level, of course, and then one file, one filegroup against the
stripe set.
As far as the "plaiding" article is concerned, yes, I noticed EMC moved the
site. I have the .pdf file and will forward it to you when I get a chance.
When I "Googled" the net, I did come across an article between Dell and
Oracle that explained similar features.
Also, know that if you have DAS, it would make no sense to "plaid" because
you would have total control over the layout of the disks and could
manipulate partitions for logs and data files and control the striping;
however, in a SAN situation, you oftentimes do not have this luxary. In
this scenario, "plaiding" can not only potentially increase the spindle
usage, but also give back to the DBA some measure of I/O performance
control, especially given the DBA is in the prime position to determine the
"changing" environmental charactersitics of the DBMS that no Storage
Administrator could possibly invision.
Sincerely,
Anthony Thomas
"bert" <bertcord@.gmail.com> wrote in message
news:1109015102.338558.43420@.l41g2000cwc.googlegroups.com...
Thanks for the input Anthony...
Yes KISS is what I want to do...but again if the difference is that
much greater sometimes it just isn't simple any more.
>>Also, did you try reformatting the drives using 64 KB clusters?
No I have not tried this yet...this is actually the first I have heard
of this but will definitely look into it. They actually talk of using
64kb clusters in the Rosetta Genomics 10-Terabyte Human Genome Database
white paper. (From the other link you mentioned) I had actually
thought of this but I did not know it was possible to format a drive
like this. For example sitting on my XP machine at home when I go to
format a drive the only options I have are 512, 1024, 2048 and 4096
bytes... I will have to look at this some more.
>>Also, having more active spindles per database file is more important
than files themselves.
I agree 110%. I actually performed a bunch more tests than what I
posted here. Having multiple files spread to different arrays only
increased performance if there was enough IO to saturate the IO of one
drive\controller. IF the IO wasn't enough the performance was
actually slower. The KISS philosophy leads me to recommend only
creating multiple files per file group if the IO generated can saturate
a single drive. Before adding more files I would recommend first
placing heavily accessed tables onto a new file group onto a separate
drive. But again I would like to do more research in this area. For
example lets say you have a big database with only 2 tables (simple
example) and you have 2 drives. Would you a) create one file group
with a file on each drive or b) two file groups with a file on each
drive?
>>They introduce a term called "Plaiding," which is a combination of
RAID striping at the hardware level and multiple SQL Server file
placements at the >>software level, similar to a plaid pattern.
Hum this sound very interesting. Unfortunately the link from the
Microsoft website no longer works. I tried searching the EMC site for
the article by searching for the article title and for the term plaid
and Plaiding... no dice... you don't happen to have the article
saved anywhere do you? I most cases I would prefer to let the hardware
perform the striping...but I would love to read the article
Thanks
Bert|||I am still digging over all the white papers on the MS site...that is a
great link thanks
>>However, an option not provided, with only two drives, I'd want to
maximize spindle usage; so, I'd stripe the two >>drives together, at
the hardware level, of course, and then one file, one filegroup against
the stripe set.
I guess my question about the 1 tables and two drives was a bit too
simplistic. Maybe I can ask the question in a different way as I am
curios as to what your conclusions would be.
How do you maximize spindle usage when you have created arrays as large
as the controller can support? In my simple example above lets say the
two drives are actually two arrays configured as large as possible (15
drives in RAID5 or RAID10), would you still want one filegroup with the
2 tables on one array and a second filegroup with the NonClustered
Indexes on the second filegroup? At first glance this is what I would
think also but...lets say the NonClustered indexes are very large it
is possible that when inserting data, or when joining more IO is
generated on The NonClustedIndex array. You could theoretically end up
in a situation where the NONClustered array is becoming a bottleneck
but the TableArray is fine
Wouldn't it make more sense to just create one filegroup with a single
file on each array and place all tables and indexes on this single
filegroup. Let that hardware handle the striping?
It becomes even much more complicated when you add multiple tables to
the file groups.
The best reason I can think of for using multiple user defined
filegroups, is if you are creating the FileGroup for a single object,
and you know that the data is going to accessed sequentially.
>>I have the .pdf file and will forward it to you when I get a chance.
Thanks that would be great
>>Also, know that if you have DAS,...
I am not sure what you are referring to when you say DAS, what does it
stand for?
Bert|||I would keep it simple. Put your system and user databases' data files on
one RAID 5 or RAID 10 volume. Put you system and user databases' log files
on one RAID 10 volume. Put your TempDB data file on one RAID 1 volume and
the TempDB log file on another RAID 1 volume.
You could then go with, for all of user databases only, for the data file(s)
only, leave the system tables and objects in the PRIMARY FILEGROUP with one
file on one disk. Put the clustered indexes in one file in a SECONDARY or
TABLE FILEGROUP on one disk. Then, finally, put all the non-clustered
indexes in one file in a TIRTIARY or INDEX FILEGROUP.
Whenever you perform CRUD operations, ALL INDEXES WILL BE UPDATED, both
clustered and non-clustered indexes. Only the UPDATE operations could
possibly be directed at non-clustered index(es) only. If the clustered
index is augmented or modified, so will the non-clustered indexes. Also, if
you have a mix of large and small Read operations, large queries would force
scans, which would use the clustered index, small queries would be directed
at the non-clustered indexes or an index seek of the clustered index.
It only makes sense to use "plaiding," multiple files per filegroup, when
you have limited control over the SAN. Another potential use would be in a
consolidated environment where a single SS instance could be hosting
numerous application databases. We have one where we host nearly 100 vended
applicaiton databases. "Plaiding" helps us further reduce potential SAN
hotspots from the pseudo-random drive usage.
Also keep in mind, that for vended databases, you will have little control
over the use of FILEGROUPs and object placement on those file groups. The
vendors tend to through everything into a single file PRIMARY FILEGROUP and
you would have to modify their script to get the objects created elsewhere.
"Plaiding" isolates the file layouts from the database object definitions.
I also sent you that .pdf whitepaper by private communication.
Sincerely,
Anthony Thomas
"bert" <bertcord@.gmail.com> wrote in message
news:1109115612.962063.112300@.c13g2000cwb.googlegroups.com...
I am still digging over all the white papers on the MS site...that is a
great link thanks
>>However, an option not provided, with only two drives, I'd want to
maximize spindle usage; so, I'd stripe the two >>drives together, at
the hardware level, of course, and then one file, one filegroup against
the stripe set.
I guess my question about the 1 tables and two drives was a bit too
simplistic. Maybe I can ask the question in a different way as I am
curios as to what your conclusions would be.
How do you maximize spindle usage when you have created arrays as large
as the controller can support? In my simple example above lets say the
two drives are actually two arrays configured as large as possible (15
drives in RAID5 or RAID10), would you still want one filegroup with the
2 tables on one array and a second filegroup with the NonClustered
Indexes on the second filegroup? At first glance this is what I would
think also but...lets say the NonClustered indexes are very large it
is possible that when inserting data, or when joining more IO is
generated on The NonClustedIndex array. You could theoretically end up
in a situation where the NONClustered array is becoming a bottleneck
but the TableArray is fine
Wouldn't it make more sense to just create one filegroup with a single
file on each array and place all tables and indexes on this single
filegroup. Let that hardware handle the striping?
It becomes even much more complicated when you add multiple tables to
the file groups.
The best reason I can think of for using multiple user defined
filegroups, is if you are creating the FileGroup for a single object,
and you know that the data is going to accessed sequentially.
>>I have the .pdf file and will forward it to you when I get a chance.
Thanks that would be great
>>Also, know that if you have DAS,...
I am not sure what you are referring to when you say DAS, what does it
stand for?
Bertsql
process of doing some research on multiple files vs. multiple
filgroups. I have seen it discussed a bunch on these groups but I need
to perform some tests to back it up. During my research I came across
the following from the "SQL Server 2000 Operations Guide"
"Note on Disk Queue Length
This counter no longer yields meaningful results in interpreting disk
I/O problems with SQL Server 2000. This is because the SQL Server
engines dynamically manage disk I/O, which is why the Max Async I/O
setting no longer exists. A simplified explanation is that SQL Server
issues an order for disk I/O, but does not wait for that process to
complete before issuing another request. When the I/O request has
completed its work, the database engine is notified and processes the
results. SQL Server monitors the throughput on these requests, and will
manage the amount of I/O that the disks can effectively handle."
You can see it here
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx
can anyone make any sense of this.
"SQL Server monitors the throughput on these requests, and will manage
the amount of I/O that the disks can effectively handle."
ok what does this mean? it will mange the amount of IO...ok how.
Has anyone seen this, or has an explanation?
Thanks
BertDisk queue length isn't particularly interesting because SQL Server throws a
lot of IOs at the disk and then goes to do other work. For instance,
read-ahead is a good example: if the server has a reasonable idea that it
will need several pages from disk in order to complete a scan, it will issue
the IOs for those pages before it actually needs them, with the expectation
that when we actually do need the pages, the IOs will have completed and we
won't have to wait. Here's the executive summary, I guess: by design, SQL
Server loads up the disk subsystem with IO requests so that we don't have to
wait for the pages when we need them. That causes IO queue lengths to
increase since there are typically always outstanding IOs posted by the
server. In some applications this might indicate a bottleneck, but SQL
Server is smart about it and uses asynchronous IO to its benefit.
Perhaps the following article (just recently posted) might illuminate the
subject a bit:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx.
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
<bertcord@.gmail.com> wrote in message
news:1106861683.038716.139100@.c13g2000cwb.googlegroups.com...
> Yeah that's what I said...what are they talking about. I am in the
> process of doing some research on multiple files vs. multiple
> filgroups. I have seen it discussed a bunch on these groups but I need
> to perform some tests to back it up. During my research I came across
> the following from the "SQL Server 2000 Operations Guide"
> "Note on Disk Queue Length
> This counter no longer yields meaningful results in interpreting disk
> I/O problems with SQL Server 2000. This is because the SQL Server
> engines dynamically manage disk I/O, which is why the Max Async I/O
> setting no longer exists. A simplified explanation is that SQL Server
> issues an order for disk I/O, but does not wait for that process to
> complete before issuing another request. When the I/O request has
> completed its work, the database engine is notified and processes the
> results. SQL Server monitors the throughput on these requests, and will
> manage the amount of I/O that the disks can effectively handle."
> You can see it here
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx
> can anyone make any sense of this.
> "SQL Server monitors the throughput on these requests, and will manage
> the amount of I/O that the disks can effectively handle."
> ok what does this mean? it will mange the amount of IO...ok how.
> Has anyone seen this, or has an explanation?
> Thanks
> Bert
>|||This means SQL Server 2000 tries not to issue 200 IO per second if your IO
system can only handle 100 IO per second. So normally you would not see
average disk queue length to be more than 1. However sometimes disk queue
length can be high (more than 1) in some heavily loaded systems. If that
happens then you know you have an IO bottleneck. The % disk busy is also a
good indication and it should be used in conjunction with average disk queue
length.
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
<bertcord@.gmail.com> wrote in message
news:1106861683.038716.139100@.c13g2000cwb.googlegroups.com...
> Yeah that's what I said...what are they talking about. I am in the
> process of doing some research on multiple files vs. multiple
> filgroups. I have seen it discussed a bunch on these groups but I need
> to perform some tests to back it up. During my research I came across
> the following from the "SQL Server 2000 Operations Guide"
> "Note on Disk Queue Length
> This counter no longer yields meaningful results in interpreting disk
> I/O problems with SQL Server 2000. This is because the SQL Server
> engines dynamically manage disk I/O, which is why the Max Async I/O
> setting no longer exists. A simplified explanation is that SQL Server
> issues an order for disk I/O, but does not wait for that process to
> complete before issuing another request. When the I/O request has
> completed its work, the database engine is notified and processes the
> results. SQL Server monitors the throughput on these requests, and will
> manage the amount of I/O that the disks can effectively handle."
> You can see it here
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx
> can anyone make any sense of this.
> "SQL Server monitors the throughput on these requests, and will manage
> the amount of I/O that the disks can effectively handle."
> ok what does this mean? it will mange the amount of IO...ok how.
> Has anyone seen this, or has an explanation?
> Thanks
> Bert
>|||Thanks for responding. I read the article today and it was very good.
I am however interested in how SQL manages IO when working with
multiple files in a single filegroup.
For example how Flushing Pages to disk is affected when a database has
multiple files. My assumption is that it is the same except that the
process performing the write (lazy write, Checkpoint or Eager write)
would pass off the write requests to the thread that is working on the
specific file. Based on my test results writing to multiple files was
always a tiny bit slower if the disk subsystem was not under heavy
load.
I am aware that here is some confusion about how SQL server issue
threads for reads. Some people feel that having more files is the only
way SQL will spawn more threads. Andrew Kelly pointed out in the
following Google threads this is not correct for SQL2000.
http://linktrim.com/x2
http://linktrim.com/x3
For writes I confirmed that SQL does write to all files in parallel. I
used fn_virtualfilestats to test. I had a Query Analyzer windows open
and I held down the F5 key so it was constantly refreshing, I could see
that the NumberReads column for all the files where incrementing on
each refresh. I do admit that my method was not 100% scientific but it
was the only way I could think of to confirm this.
The specific things I want to test are.
1. Do multiple files on the same drive perform better than a single
file
2. Do multiple files spread on separate drives perform better than a
single file.
My test server configuration is as follows
Dell 6650, 4 Xeon 1595, 5.2 GB of Ram
Disk configuration as follows
ArrayGroup0 - Channel1 E: RAID5 - 3 Drives
ArrayGroup1 - Channel0 F: RAID5 - 7 Drives
ArrayGroup1 - Channel1 J: RAID5 - 7 Drives
ArrayGroup2 - Channel0 G: RAID10 - 12 Drives
Drives E,F, and J only have data files for the test databases. Drive G
contains the tempDB log files for the test databases and all BULK
INSERT files.
I created 3 separate databases all having a single fileGroup, the file
configuration is as follows.
1. BulkInOut1 - One data File on the E drive.
2. BulkInOut2 - Three datafiles, one each on E, F and J
3. BulkInOut3 - Three datafiles all on the E drive.
Test1 - Bulk inserting 11,000,000 into an empty table with no indexes.
I ran each test 5 times and here are the results averaged in seconds.
BulkInOut1 - 165 (One File)
BulkInOut2 - 173 (3 files sep drives)
BulkInOut3 - 172 (3 files same drive)
Raw Results
BulkInOut1 - 164,162,169,163,165
BulkInOut2 - 169,175,184,166,169
BulkInOut3 - 172,170,168,180,171
Test2 - Create indexes on table from test 1. Create Clustered and two
non-clustered.
BulkInOut1 - 127 (One File)
BulkInOut2 - 98 (3 files sep drives)
BulkInOut3 - 132 (3 files same drive)
Raw Results
BulkInOut1 - 126,129,126
BulkInOut2 - 101,98,96
BulkInOut3 - 135,132,130
Based on these two test I am make the following conclusions.
1. Multiple files on the same drive is always slower do not do it.
2. Having multiple files spread onto multiple drives can perform
better. What happened in my test cases is that test one hardly
stressed the disk-subsystem. Therefore the multiple disks performed
slower. This I assume is due to the overhead of context switching. It
test two IO demands increased and disk queuing occurred on BulkInOut3
(2 files same drive), this caused it to be the slowest.
Next week I am going to perform some more tests with a better simulated
load. I am going to write an app that opens multiple connections and
inserts and selects data.
Any comments on my results or methodology would be appreciated. IF any
one else has performance data on file IO test I would love to see them
Thanks for you time
Bert Corderman|||I have completed a few more tests and here are the results. As a recap
my drive config is as follows
ArrayGroup0 - Channel1 E: RAID5 - 3 Drives
ArrayGroup1 - Channel0 F: RAID5 - 7 Drives
ArrayGroup1 - Channel1 J: RAID5 - 7 Drives
ArrayGroup2 - Channel0 G: RAID10 - 12 Drives
Test DB's
1. BulkInOut1 - One data File on the E drive.
2. BulkInOut2 - Three data files, one each on E, F and J
3. BulkInOut3 - Three data files all on the E drive.
Creation of Clustered Indexes only
BulkInOut1 - 73 (One File)
BulkInOut2 - 48 (3 files sep drives)
BulkInOut3 - 78 (3 files same drive)
As another test I wanted to see the performance of creating a separate
Filegroup for the non-clustered indexes. I tested a few configuration.
For these tests I used the database with one data file on E
(BulkInOut1)
The first test was a baseline
1. Non-Clustered Indexes Created on Primary filegroup - 66
The following tests added the Index filegroup with different file
placement.
2. 1 File on J = 62
3. 2 Files on J = 62
4. 1 File on J, one on F = 60
5. 5 Files on J = 65
Again my tests show that having multiple files in the same FileGroup
does not perform any better than having only one file, and in some
cases will even perform slower.
Any suggestions or input would be appreciated
Thanks
Bert|||I don't know, Bert. You've seemed to have done the most methodical and
exhaustive test that I've seen reported to in this forum.
If you've seen better, or at least equal performance, using single file
filegroups, Occam's Razor would say to go that route... KISS principle,
right?
Also, did you try reformatting the drives using 64 KB clusters? We have
experience quite a bit of throughput increase using this cluster allocation.
If you can afford it, use RAID 10 for everything. However, you should be
able to get buy with RAID 5 for data files, RAID 10 for log files, and RAID
1 for the tempdb, which should be on a dedicated set of disks.
Also, having more active spindles per database file is more important than
files themselves. If you can keep each processor running I/O against each
spindle, then whenever I/O requests come in, the more efficient they will
be. Second, having as many channels per parallel request will also increase
your throughput.
Check out this link for some Case Studies on this topic. Granted, the cover
a broad range of installation issues, but all of them have disk and file
layout strategies. I found the EMC CLARiiON whitepaper extremely
interesting. They introduce a term called "Plaiding," which is a
combination of RAID striping at the hardware level and multiple SQL Server
file placements at the software level, similar to a plaid pattern. This way
you maximize spindle usage per request.
There is A LOT of information on this site. Take your time to check out
each and every one. I have and it has provided quite a bit of information
to consider.
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
See what you think.
Sincerely,
Anthony Thomas
"bert" <bertcord@.gmail.com> wrote in message
news:1107304742.349333.272980@.z14g2000cwz.googlegroups.com...
I have completed a few more tests and here are the results. As a recap
my drive config is as follows
ArrayGroup0 - Channel1 E: RAID5 - 3 Drives
ArrayGroup1 - Channel0 F: RAID5 - 7 Drives
ArrayGroup1 - Channel1 J: RAID5 - 7 Drives
ArrayGroup2 - Channel0 G: RAID10 - 12 Drives
Test DB's
1. BulkInOut1 - One data File on the E drive.
2. BulkInOut2 - Three data files, one each on E, F and J
3. BulkInOut3 - Three data files all on the E drive.
Creation of Clustered Indexes only
BulkInOut1 - 73 (One File)
BulkInOut2 - 48 (3 files sep drives)
BulkInOut3 - 78 (3 files same drive)
As another test I wanted to see the performance of creating a separate
Filegroup for the non-clustered indexes. I tested a few configuration.
For these tests I used the database with one data file on E
(BulkInOut1)
The first test was a baseline
1. Non-Clustered Indexes Created on Primary filegroup - 66
The following tests added the Index filegroup with different file
placement.
2. 1 File on J = 62
3. 2 Files on J = 62
4. 1 File on J, one on F = 60
5. 5 Files on J = 65
Again my tests show that having multiple files in the same FileGroup
does not perform any better than having only one file, and in some
cases will even perform slower.
Any suggestions or input would be appreciated
Thanks
Bert|||Thanks for the input Anthony...
Yes KISS is what I want to do...but again if the difference is that
much greater sometimes it just isn't simple any more.
>>Also, did you try reformatting the drives using 64 KB clusters?
No I have not tried this yet...this is actually the first I have heard
of this but will definitely look into it. They actually talk of using
64kb clusters in the Rosetta Genomics 10-Terabyte Human Genome Database
white paper. (From the other link you mentioned) I had actually
thought of this but I did not know it was possible to format a drive
like this. For example sitting on my XP machine at home when I go to
format a drive the only options I have are 512, 1024, 2048 and 4096
bytes... I will have to look at this some more.
>>Also, having more active spindles per database file is more important
than files themselves.
I agree 110%. I actually performed a bunch more tests than what I
posted here. Having multiple files spread to different arrays only
increased performance if there was enough IO to saturate the IO of one
drive\controller. IF the IO wasn't enough the performance was
actually slower. The KISS philosophy leads me to recommend only
creating multiple files per file group if the IO generated can saturate
a single drive. Before adding more files I would recommend first
placing heavily accessed tables onto a new file group onto a separate
drive. But again I would like to do more research in this area. For
example lets say you have a big database with only 2 tables (simple
example) and you have 2 drives. Would you a) create one file group
with a file on each drive or b) two file groups with a file on each
drive?
>>They introduce a term called "Plaiding," which is a combination of
RAID striping at the hardware level and multiple SQL Server file
placements at the >>software level, similar to a plaid pattern.
Hum this sound very interesting. Unfortunately the link from the
Microsoft website no longer works. I tried searching the EMC site for
the article by searching for the article title and for the term plaid
and Plaiding... no dice... you don't happen to have the article
saved anywhere do you? I most cases I would prefer to let the hardware
perform the striping...but I would love to read the article
Thanks
Bert|||For the 64 KB allocation units (disk clusters), as well as many other useful
operational tips, check out the Microsoft SQL Server 2000 Operations Manual.
Be aware, however, that once you go above 4 KB clusters, disk compression
and defragmentation are no longer available through the native Windows
tools.
As far as your question concerning the two big tables, the answer depends on
the usage, as always. If you were to join the two tables frequently, I
would place each table within a sperate, single file, filegroup, one placed
on each drive in order to get simultaneous read-ahead reads for the JOIN
operation. However, an option not provided, with only two drives, I'd want
to maximize spindle usage; so, I'd stripe the two dirves together, at the
hardware level, of course, and then one file, one filegroup against the
stripe set.
As far as the "plaiding" article is concerned, yes, I noticed EMC moved the
site. I have the .pdf file and will forward it to you when I get a chance.
When I "Googled" the net, I did come across an article between Dell and
Oracle that explained similar features.
Also, know that if you have DAS, it would make no sense to "plaid" because
you would have total control over the layout of the disks and could
manipulate partitions for logs and data files and control the striping;
however, in a SAN situation, you oftentimes do not have this luxary. In
this scenario, "plaiding" can not only potentially increase the spindle
usage, but also give back to the DBA some measure of I/O performance
control, especially given the DBA is in the prime position to determine the
"changing" environmental charactersitics of the DBMS that no Storage
Administrator could possibly invision.
Sincerely,
Anthony Thomas
"bert" <bertcord@.gmail.com> wrote in message
news:1109015102.338558.43420@.l41g2000cwc.googlegroups.com...
Thanks for the input Anthony...
Yes KISS is what I want to do...but again if the difference is that
much greater sometimes it just isn't simple any more.
>>Also, did you try reformatting the drives using 64 KB clusters?
No I have not tried this yet...this is actually the first I have heard
of this but will definitely look into it. They actually talk of using
64kb clusters in the Rosetta Genomics 10-Terabyte Human Genome Database
white paper. (From the other link you mentioned) I had actually
thought of this but I did not know it was possible to format a drive
like this. For example sitting on my XP machine at home when I go to
format a drive the only options I have are 512, 1024, 2048 and 4096
bytes... I will have to look at this some more.
>>Also, having more active spindles per database file is more important
than files themselves.
I agree 110%. I actually performed a bunch more tests than what I
posted here. Having multiple files spread to different arrays only
increased performance if there was enough IO to saturate the IO of one
drive\controller. IF the IO wasn't enough the performance was
actually slower. The KISS philosophy leads me to recommend only
creating multiple files per file group if the IO generated can saturate
a single drive. Before adding more files I would recommend first
placing heavily accessed tables onto a new file group onto a separate
drive. But again I would like to do more research in this area. For
example lets say you have a big database with only 2 tables (simple
example) and you have 2 drives. Would you a) create one file group
with a file on each drive or b) two file groups with a file on each
drive?
>>They introduce a term called "Plaiding," which is a combination of
RAID striping at the hardware level and multiple SQL Server file
placements at the >>software level, similar to a plaid pattern.
Hum this sound very interesting. Unfortunately the link from the
Microsoft website no longer works. I tried searching the EMC site for
the article by searching for the article title and for the term plaid
and Plaiding... no dice... you don't happen to have the article
saved anywhere do you? I most cases I would prefer to let the hardware
perform the striping...but I would love to read the article
Thanks
Bert|||I am still digging over all the white papers on the MS site...that is a
great link thanks
>>However, an option not provided, with only two drives, I'd want to
maximize spindle usage; so, I'd stripe the two >>drives together, at
the hardware level, of course, and then one file, one filegroup against
the stripe set.
I guess my question about the 1 tables and two drives was a bit too
simplistic. Maybe I can ask the question in a different way as I am
curios as to what your conclusions would be.
How do you maximize spindle usage when you have created arrays as large
as the controller can support? In my simple example above lets say the
two drives are actually two arrays configured as large as possible (15
drives in RAID5 or RAID10), would you still want one filegroup with the
2 tables on one array and a second filegroup with the NonClustered
Indexes on the second filegroup? At first glance this is what I would
think also but...lets say the NonClustered indexes are very large it
is possible that when inserting data, or when joining more IO is
generated on The NonClustedIndex array. You could theoretically end up
in a situation where the NONClustered array is becoming a bottleneck
but the TableArray is fine
Wouldn't it make more sense to just create one filegroup with a single
file on each array and place all tables and indexes on this single
filegroup. Let that hardware handle the striping?
It becomes even much more complicated when you add multiple tables to
the file groups.
The best reason I can think of for using multiple user defined
filegroups, is if you are creating the FileGroup for a single object,
and you know that the data is going to accessed sequentially.
>>I have the .pdf file and will forward it to you when I get a chance.
Thanks that would be great
>>Also, know that if you have DAS,...
I am not sure what you are referring to when you say DAS, what does it
stand for?
Bert|||I would keep it simple. Put your system and user databases' data files on
one RAID 5 or RAID 10 volume. Put you system and user databases' log files
on one RAID 10 volume. Put your TempDB data file on one RAID 1 volume and
the TempDB log file on another RAID 1 volume.
You could then go with, for all of user databases only, for the data file(s)
only, leave the system tables and objects in the PRIMARY FILEGROUP with one
file on one disk. Put the clustered indexes in one file in a SECONDARY or
TABLE FILEGROUP on one disk. Then, finally, put all the non-clustered
indexes in one file in a TIRTIARY or INDEX FILEGROUP.
Whenever you perform CRUD operations, ALL INDEXES WILL BE UPDATED, both
clustered and non-clustered indexes. Only the UPDATE operations could
possibly be directed at non-clustered index(es) only. If the clustered
index is augmented or modified, so will the non-clustered indexes. Also, if
you have a mix of large and small Read operations, large queries would force
scans, which would use the clustered index, small queries would be directed
at the non-clustered indexes or an index seek of the clustered index.
It only makes sense to use "plaiding," multiple files per filegroup, when
you have limited control over the SAN. Another potential use would be in a
consolidated environment where a single SS instance could be hosting
numerous application databases. We have one where we host nearly 100 vended
applicaiton databases. "Plaiding" helps us further reduce potential SAN
hotspots from the pseudo-random drive usage.
Also keep in mind, that for vended databases, you will have little control
over the use of FILEGROUPs and object placement on those file groups. The
vendors tend to through everything into a single file PRIMARY FILEGROUP and
you would have to modify their script to get the objects created elsewhere.
"Plaiding" isolates the file layouts from the database object definitions.
I also sent you that .pdf whitepaper by private communication.
Sincerely,
Anthony Thomas
"bert" <bertcord@.gmail.com> wrote in message
news:1109115612.962063.112300@.c13g2000cwb.googlegroups.com...
I am still digging over all the white papers on the MS site...that is a
great link thanks
>>However, an option not provided, with only two drives, I'd want to
maximize spindle usage; so, I'd stripe the two >>drives together, at
the hardware level, of course, and then one file, one filegroup against
the stripe set.
I guess my question about the 1 tables and two drives was a bit too
simplistic. Maybe I can ask the question in a different way as I am
curios as to what your conclusions would be.
How do you maximize spindle usage when you have created arrays as large
as the controller can support? In my simple example above lets say the
two drives are actually two arrays configured as large as possible (15
drives in RAID5 or RAID10), would you still want one filegroup with the
2 tables on one array and a second filegroup with the NonClustered
Indexes on the second filegroup? At first glance this is what I would
think also but...lets say the NonClustered indexes are very large it
is possible that when inserting data, or when joining more IO is
generated on The NonClustedIndex array. You could theoretically end up
in a situation where the NONClustered array is becoming a bottleneck
but the TableArray is fine
Wouldn't it make more sense to just create one filegroup with a single
file on each array and place all tables and indexes on this single
filegroup. Let that hardware handle the striping?
It becomes even much more complicated when you add multiple tables to
the file groups.
The best reason I can think of for using multiple user defined
filegroups, is if you are creating the FileGroup for a single object,
and you know that the data is going to accessed sequentially.
>>I have the .pdf file and will forward it to you when I get a chance.
Thanks that would be great
>>Also, know that if you have DAS,...
I am not sure what you are referring to when you say DAS, what does it
stand for?
Bertsql
Disk Queue Length always high, even with no requests...
I was working on my server this weekend trying to figure out why I'm
having the problems some of my users are reporting.
After I got done running a dbcc dbreindex, I noticed my average disk
queue length (even over several hours) was a solid 4.337. The perfmon
would show disk IO in large spikes (into the 400s) several times per
polling intraval (1:40 according to perfmon).
This is a bit odd to me, as the system does nothing but serve the SQL
database. Nobody but myself was attached to it.
So, I grabbed process explorer from sysinternals. I was watching the
disk i/o in that, and according to it, the program causing the disk i/o
spikes is csrss.exe (required by windows).
This machine is not connected to the outside world, just to our lan.
However I guess it's possible a virus made it's way to the machine.
But I'm not entirely sure that's the problem.
At any rate. What can I do to get rid of that high of a disk i/o? I've
monitored it before and never seen a steady 4.337 (steady, unwavering)
I/O reading.. especially with nobody on, let alone 30 users.Ahh, system specs just in case
2GB ECC RAM (Tossing in 2 more GB for the sake of it).
RAID1: 160GB Mirror Array
AMD X2 4400+ Toledo Core.
All this machine does is serve up a 7GB MSSQL db to about 17 machines.
However, most of these users are not active throughout the day.
I realize the mirror array is not the best way to do it. But we are
more concerned with data safety than speed. (for the most part).
I'm just trying to narrow down why my average disk queue length is
currently in the 4's with nobody on.|||Alright,
We can nix this topic. I came in this morning and the averages were
under 1. It must have been in a grow op or something when it was in
the 4's constantly.
having the problems some of my users are reporting.
After I got done running a dbcc dbreindex, I noticed my average disk
queue length (even over several hours) was a solid 4.337. The perfmon
would show disk IO in large spikes (into the 400s) several times per
polling intraval (1:40 according to perfmon).
This is a bit odd to me, as the system does nothing but serve the SQL
database. Nobody but myself was attached to it.
So, I grabbed process explorer from sysinternals. I was watching the
disk i/o in that, and according to it, the program causing the disk i/o
spikes is csrss.exe (required by windows).
This machine is not connected to the outside world, just to our lan.
However I guess it's possible a virus made it's way to the machine.
But I'm not entirely sure that's the problem.
At any rate. What can I do to get rid of that high of a disk i/o? I've
monitored it before and never seen a steady 4.337 (steady, unwavering)
I/O reading.. especially with nobody on, let alone 30 users.Ahh, system specs just in case
2GB ECC RAM (Tossing in 2 more GB for the sake of it).
RAID1: 160GB Mirror Array
AMD X2 4400+ Toledo Core.
All this machine does is serve up a 7GB MSSQL db to about 17 machines.
However, most of these users are not active throughout the day.
I realize the mirror array is not the best way to do it. But we are
more concerned with data safety than speed. (for the most part).
I'm just trying to narrow down why my average disk queue length is
currently in the 4's with nobody on.|||Alright,
We can nix this topic. I came in this morning and the averages were
under 1. It must have been in a grow op or something when it was in
the 4's constantly.
Disk Queue Length always high, even with no requests...
I was working on my server this weekend trying to figure out why I'm
having the problems some of my users are reporting.
After I got done running a dbcc dbreindex, I noticed my average disk
queue length (even over several hours) was a solid 4.337. The perfmon
would show disk IO in large spikes (into the 400s) several times per
polling intraval (1:40 according to perfmon).
This is a bit odd to me, as the system does nothing but serve the SQL
database. Nobody but myself was attached to it.
So, I grabbed process explorer from sysinternals. I was watching the
disk i/o in that, and according to it, the program causing the disk i/o
spikes is csrss.exe (required by windows).
This machine is not connected to the outside world, just to our lan.
However I guess it's possible a virus made it's way to the machine.
But I'm not entirely sure that's the problem.
At any rate. What can I do to get rid of that high of a disk i/o? I've
monitored it before and never seen a steady 4.337 (steady, unwavering)
I/O reading.. especially with nobody on, let alone 30 users.Ahh, system specs just in case
2GB ECC RAM (Tossing in 2 more GB for the sake of it).
RAID1: 160GB Mirror Array
AMD X2 4400+ Toledo Core.
All this machine does is serve up a 7GB MSSQL db to about 17 machines.
However, most of these users are not active throughout the day.
I realize the mirror array is not the best way to do it. But we are
more concerned with data safety than speed. (for the most part).
I'm just trying to narrow down why my average disk queue length is
currently in the 4's with nobody on.|||Alright,
We can nix this topic. I came in this morning and the averages were
under 1. It must have been in a grow op or something when it was in
the 4's constantly.
having the problems some of my users are reporting.
After I got done running a dbcc dbreindex, I noticed my average disk
queue length (even over several hours) was a solid 4.337. The perfmon
would show disk IO in large spikes (into the 400s) several times per
polling intraval (1:40 according to perfmon).
This is a bit odd to me, as the system does nothing but serve the SQL
database. Nobody but myself was attached to it.
So, I grabbed process explorer from sysinternals. I was watching the
disk i/o in that, and according to it, the program causing the disk i/o
spikes is csrss.exe (required by windows).
This machine is not connected to the outside world, just to our lan.
However I guess it's possible a virus made it's way to the machine.
But I'm not entirely sure that's the problem.
At any rate. What can I do to get rid of that high of a disk i/o? I've
monitored it before and never seen a steady 4.337 (steady, unwavering)
I/O reading.. especially with nobody on, let alone 30 users.Ahh, system specs just in case
2GB ECC RAM (Tossing in 2 more GB for the sake of it).
RAID1: 160GB Mirror Array
AMD X2 4400+ Toledo Core.
All this machine does is serve up a 7GB MSSQL db to about 17 machines.
However, most of these users are not active throughout the day.
I realize the mirror array is not the best way to do it. But we are
more concerned with data safety than speed. (for the most part).
I'm just trying to narrow down why my average disk queue length is
currently in the 4's with nobody on.|||Alright,
We can nix this topic. I came in this morning and the averages were
under 1. It must have been in a grow op or something when it was in
the 4's constantly.
2012年3月22日星期四
Disk controller vs another disk
Hi,
Since I added a couple databases and a lot of users to my SQL server, I
notice that my disk queue lenght is now too high and I need to add disk(s) t
o
increase performance. My server is configured in a clustered environment and
the data is on a SAN. The SAN use RAID1 for my partition and the controller
of the disk has a 128mb cache. I have 125 databases on this server for a
total of 80GO of data.
I had a discussion with someone in the ''hardware team'' and he said that
if we change the controller card for a card that will have 1GB of cache, it
will be better than add another disk in term of performance. I searched on
the web and in some newsgroups and I found nothing that suggest this kind of
thing. What is your opinion ?"TheOne" <TheOne@.discussions.microsoft.com> wrote in message
news:692914A2-F957-4E00-809E-DDB3BFFC878B@.microsoft.com...
> Hi,
> Since I added a couple databases and a lot of users to my SQL server, I
> notice that my disk queue lenght is now too high and I need to add disk(s)
> to
> increase performance. My server is configured in a clustered environment
> and
> the data is on a SAN. The SAN use RAID1 for my partition and the
> controller
> of the disk has a 128mb cache. I have 125 databases on this server for a
> total of 80GO of data.
> I had a discussion with someone in the ''hardware team'' and he said that
> if we change the controller card for a card that will have 1GB of cache,
> it
> will be better than add another disk in term of performance. I searched on
> the web and in some newsgroups and I found nothing that suggest this kind
> of
> thing. What is your opinion ?
Impossible to tell without testing. He's right that the remedy for for IO
contention and disk queuing is as likely to be adding memory as adding
disks. The reason is that better caching will reduce the number of IO's
needed by the workload. But adding cache between the server and disk by
adding it on the controller is generally vastly more expensive than simply
adding more memory to the server.
David|||TheOne,
Additional cache is good, but as always ... "your results may vary". It is
very difficult to estimate cacheing performance. Why not do both? Add a new
controller and see if it makes a difference. If not, add a disk drives to
the SAN.
-- Bill
"TheOne" <TheOne@.discussions.microsoft.com> wrote in message
news:692914A2-F957-4E00-809E-DDB3BFFC878B@.microsoft.com...
> Hi,
> Since I added a couple databases and a lot of users to my SQL server, I
> notice that my disk queue lenght is now too high and I need to add disk(s)
> to
> increase performance. My server is configured in a clustered environment
> and
> the data is on a SAN. The SAN use RAID1 for my partition and the
> controller
> of the disk has a 128mb cache. I have 125 databases on this server for a
> total of 80GO of data.
> I had a discussion with someone in the ''hardware team'' and he said that
> if we change the controller card for a card that will have 1GB of cache,
> it
> will be better than add another disk in term of performance. I searched on
> the web and in some newsgroups and I found nothing that suggest this kind
> of
> thing. What is your opinion ?
Since I added a couple databases and a lot of users to my SQL server, I
notice that my disk queue lenght is now too high and I need to add disk(s) t
o
increase performance. My server is configured in a clustered environment and
the data is on a SAN. The SAN use RAID1 for my partition and the controller
of the disk has a 128mb cache. I have 125 databases on this server for a
total of 80GO of data.
I had a discussion with someone in the ''hardware team'' and he said that
if we change the controller card for a card that will have 1GB of cache, it
will be better than add another disk in term of performance. I searched on
the web and in some newsgroups and I found nothing that suggest this kind of
thing. What is your opinion ?"TheOne" <TheOne@.discussions.microsoft.com> wrote in message
news:692914A2-F957-4E00-809E-DDB3BFFC878B@.microsoft.com...
> Hi,
> Since I added a couple databases and a lot of users to my SQL server, I
> notice that my disk queue lenght is now too high and I need to add disk(s)
> to
> increase performance. My server is configured in a clustered environment
> and
> the data is on a SAN. The SAN use RAID1 for my partition and the
> controller
> of the disk has a 128mb cache. I have 125 databases on this server for a
> total of 80GO of data.
> I had a discussion with someone in the ''hardware team'' and he said that
> if we change the controller card for a card that will have 1GB of cache,
> it
> will be better than add another disk in term of performance. I searched on
> the web and in some newsgroups and I found nothing that suggest this kind
> of
> thing. What is your opinion ?
Impossible to tell without testing. He's right that the remedy for for IO
contention and disk queuing is as likely to be adding memory as adding
disks. The reason is that better caching will reduce the number of IO's
needed by the workload. But adding cache between the server and disk by
adding it on the controller is generally vastly more expensive than simply
adding more memory to the server.
David|||TheOne,
Additional cache is good, but as always ... "your results may vary". It is
very difficult to estimate cacheing performance. Why not do both? Add a new
controller and see if it makes a difference. If not, add a disk drives to
the SAN.
-- Bill
"TheOne" <TheOne@.discussions.microsoft.com> wrote in message
news:692914A2-F957-4E00-809E-DDB3BFFC878B@.microsoft.com...
> Hi,
> Since I added a couple databases and a lot of users to my SQL server, I
> notice that my disk queue lenght is now too high and I need to add disk(s)
> to
> increase performance. My server is configured in a clustered environment
> and
> the data is on a SAN. The SAN use RAID1 for my partition and the
> controller
> of the disk has a 128mb cache. I have 125 databases on this server for a
> total of 80GO of data.
> I had a discussion with someone in the ''hardware team'' and he said that
> if we change the controller card for a card that will have 1GB of cache,
> it
> will be better than add another disk in term of performance. I searched on
> the web and in some newsgroups and I found nothing that suggest this kind
> of
> thing. What is your opinion ?
Disk controller vs another disk
Hi,
Since I added a couple databases and a lot of users to my SQL server, I
notice that my disk queue lenght is now too high and I need to add disk(s) to
increase performance. My server is configured in a clustered environment and
the data is on a SAN. The SAN use RAID1 for my partition and the controller
of the disk has a 128mb cache. I have 125 databases on this server for a
total of 80GO of data.
I had a discussion with someone in the ''hardware team'' and he said that
if we change the controller card for a card that will have 1GB of cache, it
will be better than add another disk in term of performance. I searched on
the web and in some newsgroups and I found nothing that suggest this kind of
thing. What is your opinion ?
"TheOne" <TheOne@.discussions.microsoft.com> wrote in message
news:692914A2-F957-4E00-809E-DDB3BFFC878B@.microsoft.com...
> Hi,
> Since I added a couple databases and a lot of users to my SQL server, I
> notice that my disk queue lenght is now too high and I need to add disk(s)
> to
> increase performance. My server is configured in a clustered environment
> and
> the data is on a SAN. The SAN use RAID1 for my partition and the
> controller
> of the disk has a 128mb cache. I have 125 databases on this server for a
> total of 80GO of data.
> I had a discussion with someone in the ''hardware team'' and he said that
> if we change the controller card for a card that will have 1GB of cache,
> it
> will be better than add another disk in term of performance. I searched on
> the web and in some newsgroups and I found nothing that suggest this kind
> of
> thing. What is your opinion ?
Impossible to tell without testing. He's right that the remedy for for IO
contention and disk queuing is as likely to be adding memory as adding
disks. The reason is that better caching will reduce the number of IO's
needed by the workload. But adding cache between the server and disk by
adding it on the controller is generally vastly more expensive than simply
adding more memory to the server.
David
|||TheOne,
Additional cache is good, but as always ... "your results may vary". It is
very difficult to estimate cacheing performance. Why not do both? Add a new
controller and see if it makes a difference. If not, add a disk drives to
the SAN.
-- Bill
"TheOne" <TheOne@.discussions.microsoft.com> wrote in message
news:692914A2-F957-4E00-809E-DDB3BFFC878B@.microsoft.com...
> Hi,
> Since I added a couple databases and a lot of users to my SQL server, I
> notice that my disk queue lenght is now too high and I need to add disk(s)
> to
> increase performance. My server is configured in a clustered environment
> and
> the data is on a SAN. The SAN use RAID1 for my partition and the
> controller
> of the disk has a 128mb cache. I have 125 databases on this server for a
> total of 80GO of data.
> I had a discussion with someone in the ''hardware team'' and he said that
> if we change the controller card for a card that will have 1GB of cache,
> it
> will be better than add another disk in term of performance. I searched on
> the web and in some newsgroups and I found nothing that suggest this kind
> of
> thing. What is your opinion ?
Since I added a couple databases and a lot of users to my SQL server, I
notice that my disk queue lenght is now too high and I need to add disk(s) to
increase performance. My server is configured in a clustered environment and
the data is on a SAN. The SAN use RAID1 for my partition and the controller
of the disk has a 128mb cache. I have 125 databases on this server for a
total of 80GO of data.
I had a discussion with someone in the ''hardware team'' and he said that
if we change the controller card for a card that will have 1GB of cache, it
will be better than add another disk in term of performance. I searched on
the web and in some newsgroups and I found nothing that suggest this kind of
thing. What is your opinion ?
"TheOne" <TheOne@.discussions.microsoft.com> wrote in message
news:692914A2-F957-4E00-809E-DDB3BFFC878B@.microsoft.com...
> Hi,
> Since I added a couple databases and a lot of users to my SQL server, I
> notice that my disk queue lenght is now too high and I need to add disk(s)
> to
> increase performance. My server is configured in a clustered environment
> and
> the data is on a SAN. The SAN use RAID1 for my partition and the
> controller
> of the disk has a 128mb cache. I have 125 databases on this server for a
> total of 80GO of data.
> I had a discussion with someone in the ''hardware team'' and he said that
> if we change the controller card for a card that will have 1GB of cache,
> it
> will be better than add another disk in term of performance. I searched on
> the web and in some newsgroups and I found nothing that suggest this kind
> of
> thing. What is your opinion ?
Impossible to tell without testing. He's right that the remedy for for IO
contention and disk queuing is as likely to be adding memory as adding
disks. The reason is that better caching will reduce the number of IO's
needed by the workload. But adding cache between the server and disk by
adding it on the controller is generally vastly more expensive than simply
adding more memory to the server.
David
|||TheOne,
Additional cache is good, but as always ... "your results may vary". It is
very difficult to estimate cacheing performance. Why not do both? Add a new
controller and see if it makes a difference. If not, add a disk drives to
the SAN.
-- Bill
"TheOne" <TheOne@.discussions.microsoft.com> wrote in message
news:692914A2-F957-4E00-809E-DDB3BFFC878B@.microsoft.com...
> Hi,
> Since I added a couple databases and a lot of users to my SQL server, I
> notice that my disk queue lenght is now too high and I need to add disk(s)
> to
> increase performance. My server is configured in a clustered environment
> and
> the data is on a SAN. The SAN use RAID1 for my partition and the
> controller
> of the disk has a 128mb cache. I have 125 databases on this server for a
> total of 80GO of data.
> I had a discussion with someone in the ''hardware team'' and he said that
> if we change the controller card for a card that will have 1GB of cache,
> it
> will be better than add another disk in term of performance. I searched on
> the web and in some newsgroups and I found nothing that suggest this kind
> of
> thing. What is your opinion ?
Disk controller vs another disk
Hi,
Since I added a couple databases and a lot of users to my SQL server, I
notice that my disk queue lenght is now too high and I need to add disk(s) to
increase performance. My server is configured in a clustered environment and
the data is on a SAN. The SAN use RAID1 for my partition and the controller
of the disk has a 128mb cache. I have 125 databases on this server for a
total of 80GO of data.
I had a discussion with someone in the ''hardware team'' and he said that
if we change the controller card for a card that will have 1GB of cache, it
will be better than add another disk in term of performance. I searched on
the web and in some newsgroups and I found nothing that suggest this kind of
thing. What is your opinion ?"TheOne" <TheOne@.discussions.microsoft.com> wrote in message
news:692914A2-F957-4E00-809E-DDB3BFFC878B@.microsoft.com...
> Hi,
> Since I added a couple databases and a lot of users to my SQL server, I
> notice that my disk queue lenght is now too high and I need to add disk(s)
> to
> increase performance. My server is configured in a clustered environment
> and
> the data is on a SAN. The SAN use RAID1 for my partition and the
> controller
> of the disk has a 128mb cache. I have 125 databases on this server for a
> total of 80GO of data.
> I had a discussion with someone in the ''hardware team'' and he said that
> if we change the controller card for a card that will have 1GB of cache,
> it
> will be better than add another disk in term of performance. I searched on
> the web and in some newsgroups and I found nothing that suggest this kind
> of
> thing. What is your opinion ?
Impossible to tell without testing. He's right that the remedy for for IO
contention and disk queuing is as likely to be adding memory as adding
disks. The reason is that better caching will reduce the number of IO's
needed by the workload. But adding cache between the server and disk by
adding it on the controller is generally vastly more expensive than simply
adding more memory to the server.
David|||TheOne,
Additional cache is good, but as always ... "your results may vary". It is
very difficult to estimate cacheing performance. Why not do both? Add a new
controller and see if it makes a difference. If not, add a disk drives to
the SAN.
-- Bill
"TheOne" <TheOne@.discussions.microsoft.com> wrote in message
news:692914A2-F957-4E00-809E-DDB3BFFC878B@.microsoft.com...
> Hi,
> Since I added a couple databases and a lot of users to my SQL server, I
> notice that my disk queue lenght is now too high and I need to add disk(s)
> to
> increase performance. My server is configured in a clustered environment
> and
> the data is on a SAN. The SAN use RAID1 for my partition and the
> controller
> of the disk has a 128mb cache. I have 125 databases on this server for a
> total of 80GO of data.
> I had a discussion with someone in the ''hardware team'' and he said that
> if we change the controller card for a card that will have 1GB of cache,
> it
> will be better than add another disk in term of performance. I searched on
> the web and in some newsgroups and I found nothing that suggest this kind
> of
> thing. What is your opinion ?
Since I added a couple databases and a lot of users to my SQL server, I
notice that my disk queue lenght is now too high and I need to add disk(s) to
increase performance. My server is configured in a clustered environment and
the data is on a SAN. The SAN use RAID1 for my partition and the controller
of the disk has a 128mb cache. I have 125 databases on this server for a
total of 80GO of data.
I had a discussion with someone in the ''hardware team'' and he said that
if we change the controller card for a card that will have 1GB of cache, it
will be better than add another disk in term of performance. I searched on
the web and in some newsgroups and I found nothing that suggest this kind of
thing. What is your opinion ?"TheOne" <TheOne@.discussions.microsoft.com> wrote in message
news:692914A2-F957-4E00-809E-DDB3BFFC878B@.microsoft.com...
> Hi,
> Since I added a couple databases and a lot of users to my SQL server, I
> notice that my disk queue lenght is now too high and I need to add disk(s)
> to
> increase performance. My server is configured in a clustered environment
> and
> the data is on a SAN. The SAN use RAID1 for my partition and the
> controller
> of the disk has a 128mb cache. I have 125 databases on this server for a
> total of 80GO of data.
> I had a discussion with someone in the ''hardware team'' and he said that
> if we change the controller card for a card that will have 1GB of cache,
> it
> will be better than add another disk in term of performance. I searched on
> the web and in some newsgroups and I found nothing that suggest this kind
> of
> thing. What is your opinion ?
Impossible to tell without testing. He's right that the remedy for for IO
contention and disk queuing is as likely to be adding memory as adding
disks. The reason is that better caching will reduce the number of IO's
needed by the workload. But adding cache between the server and disk by
adding it on the controller is generally vastly more expensive than simply
adding more memory to the server.
David|||TheOne,
Additional cache is good, but as always ... "your results may vary". It is
very difficult to estimate cacheing performance. Why not do both? Add a new
controller and see if it makes a difference. If not, add a disk drives to
the SAN.
-- Bill
"TheOne" <TheOne@.discussions.microsoft.com> wrote in message
news:692914A2-F957-4E00-809E-DDB3BFFC878B@.microsoft.com...
> Hi,
> Since I added a couple databases and a lot of users to my SQL server, I
> notice that my disk queue lenght is now too high and I need to add disk(s)
> to
> increase performance. My server is configured in a clustered environment
> and
> the data is on a SAN. The SAN use RAID1 for my partition and the
> controller
> of the disk has a 128mb cache. I have 125 databases on this server for a
> total of 80GO of data.
> I had a discussion with someone in the ''hardware team'' and he said that
> if we change the controller card for a card that will have 1GB of cache,
> it
> will be better than add another disk in term of performance. I searched on
> the web and in some newsgroups and I found nothing that suggest this kind
> of
> thing. What is your opinion ?
订阅：
博文 (Atom)