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
没有评论:
发表评论