As part of my ongoing testing of the performance impact of multiple
files vs. a single file. (Here is the link, comments would be
appreciated http://linktrim.com/yq) I have come across some
inconsistencies in how "proportional fill" works. According to books
online
"Filegroups use a proportional fill strategy across all the files
within each filegroup. As data is written to the filegroup, Microsoft=AE
SQL Server=99 2000 writes an amount proportional to the free space in
the file to each file within the filegroup, rather than writing all the
data to the first file until full, and then writing to the next file.
For example, if file f1 has 100 megabytes (MB) free and file f2 has 200
MB free, one extent is allocated from file f1, two extents from file
f2, and so on. This way both files become full at about the same time,
and simple striping is achieved."
Based on some of my testing this is not what is occurring. I created 3
files in one filegroup for my DB. The first number is the file size,
the second number is the used size after the data load.
1=2E Test one
File1 - 3000 - 520
File2 - 2000 - 520
File3 - 2000 - 520
2=2E Test two
File1 - 3000 - 293
File2 - 2000 - 293
File3 - 3000 - 293
3=2E Test three
File1 - 4000 - 416
File2 - 2000 - 232
File3 - 2000 - 232
As you can see in test one and two the used size is the same across
files even thought the 3 data files are not the same size. Not until I
make one file twice as large as the others did I sea difference in
used space.
Can anyone explain this behavior?
BertWere all the files completely empty? What size is that in? Is it MB's,
GBs, KBs etc?
--
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1107271506.112735.198080@.f14g2000cwb.googlegroups.com...
As part of my ongoing testing of the performance impact of multiple
files vs. a single file. (Here is the link, comments would be
appreciated http://linktrim.com/yq) I have come across some
inconsistencies in how "proportional fill" works. According to books
online
"Filegroups use a proportional fill strategy across all the files
within each filegroup. As data is written to the filegroup, Microsoft®
SQL ServerT 2000 writes an amount proportional to the free space in
the file to each file within the filegroup, rather than writing all the
data to the first file until full, and then writing to the next file.
For example, if file f1 has 100 megabytes (MB) free and file f2 has 200
MB free, one extent is allocated from file f1, two extents from file
f2, and so on. This way both files become full at about the same time,
and simple striping is achieved."
Based on some of my testing this is not what is occurring. I created 3
files in one filegroup for my DB. The first number is the file size,
the second number is the used size after the data load.
1. Test one
File1 - 3000 - 520
File2 - 2000 - 520
File3 - 2000 - 520
2. Test two
File1 - 3000 - 293
File2 - 2000 - 293
File3 - 3000 - 293
3. Test three
File1 - 4000 - 416
File2 - 2000 - 232
File3 - 2000 - 232
As you can see in test one and two the used size is the same across
files even thought the 3 data files are not the same size. Not until I
make one file twice as large as the others did I sea difference in
used space.
Can anyone explain this behavior?
Bert|||Hey Andrew thanks for taking the time to respond. Yes the files were
completely empty, and the size is in MB. Here is the DDL for the
table.
CREATE DATABASE [testdb]
ON (NAME = N'testdb_Data',
FILENAME = N'e:\sqldata\mssql\data\testdb_Data.MDF' ,
SIZE = 3000, FILEGROWTH = 10%), (NAME = N'testdb_Data1',
FILENAME = N'e:\sqldata\mssql\data\testdb_Data1.NDF' ,
SIZE = 2000, FILEGROWTH = 10%), (NAME = N'testdb_Data2',
FILENAME = N'e:\sqldata\mssql\data\testdb_Data2.NDF' ,
SIZE = 3000, FILEGROWTH = 10%) LOG ON (NAME = N'testdb_Log',
FILENAME = N'g:\sqldata\mssql\data\testdb_Log.LDF' ,
SIZE = 500, FILEGROWTH = 10%)
I reran the tests a second time as I was wondering if checking the
batch size for my bulk insert would have an affect. I ran the test
twice once without setting the batch size and a second time of setting
the batch size of 10000. Both tests resulted in the data files being
filled equally, even though the second file is smaller than the others.|||OK this is a guess but makes a lot of sense when you think about it. The
results you had are listed below. Since the files are filled at the extent
level it can't split an extent and put half in one file and half in another.
So in your tests until you get differences in free space that are exact
multiples of each other it will round down. So with a file size of 2000
(file2) and 3000 (file3) it would allocate one extent to file 2 and one to
file 3. When you get double the free space as in File1 and file 2 it can
then allocate in a 2 to 1 fashion. I believe the algorithm has to be very
simple to keep it efficient. That and the fact it allocates in extents lend
to the behavior you see.
1. Test one
File1 - 3000 - 520
File2 - 2000 - 520
File3 - 2000 - 520
2. Test two
File1 - 3000 - 293
File2 - 2000 - 293
File3 - 3000 - 293
3. Test three
File1 - 4000 - 416
File2 - 2000 - 232
File3 - 2000 - 232
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1107287428.961190.60820@.z14g2000cwz.googlegroups.com...
> Hey Andrew thanks for taking the time to respond. Yes the files were
> completely empty, and the size is in MB. Here is the DDL for the
> table.
> CREATE DATABASE [testdb]
> ON (NAME = N'testdb_Data',
> FILENAME = N'e:\sqldata\mssql\data\testdb_Data.MDF' ,
> SIZE = 3000, FILEGROWTH = 10%), (NAME = N'testdb_Data1',
> FILENAME = N'e:\sqldata\mssql\data\testdb_Data1.NDF' ,
> SIZE = 2000, FILEGROWTH = 10%), (NAME = N'testdb_Data2',
> FILENAME = N'e:\sqldata\mssql\data\testdb_Data2.NDF' ,
> SIZE = 3000, FILEGROWTH = 10%) LOG ON (NAME = N'testdb_Log',
> FILENAME = N'g:\sqldata\mssql\data\testdb_Log.LDF' ,
> SIZE = 500, FILEGROWTH = 10%)
> I reran the tests a second time as I was wondering if checking the
> batch size for my bulk insert would have an affect. I ran the test
> twice once without setting the batch size and a second time of setting
> the batch size of 10000. Both tests resulted in the data files being
> filled equally, even though the second file is smaller than the others.
>|||This is correct. In test 1, if you keep adding data, when the remaining free
space in file 1 versus file 2 become a ratio of 2:1, then we allocate 2
pages in file 1 for every page in file 2.
Please also note that when file becomes very large or when there are a lot
of concurrent transactions, we use some performance improvement technique
with a side effect that the free space calculation is not 100% accurate.
--
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.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eHQruWNCFHA.2072@.TK2MSFTNGP10.phx.gbl...
> OK this is a guess but makes a lot of sense when you think about it. The
> results you had are listed below. Since the files are filled at the
> extent level it can't split an extent and put half in one file and half in
> another. So in your tests until you get differences in free space that are
> exact multiples of each other it will round down. So with a file size of
> 2000 (file2) and 3000 (file3) it would allocate one extent to file 2 and
> one to file 3. When you get double the free space as in File1 and file 2
> it can then allocate in a 2 to 1 fashion. I believe the algorithm has to
> be very simple to keep it efficient. That and the fact it allocates in
> extents lend to the behavior you see.
> 1. Test one
> File1 - 3000 - 520
> File2 - 2000 - 520
> File3 - 2000 - 520
> 2. Test two
> File1 - 3000 - 293
> File2 - 2000 - 293
> File3 - 3000 - 293
> 3. Test three
> File1 - 4000 - 416
> File2 - 2000 - 232
> File3 - 2000 - 232
>
> --
> Andrew J. Kelly SQL MVP
>
> "bert" <bertcord@.gmail.com> wrote in message
> news:1107287428.961190.60820@.z14g2000cwz.googlegroups.com...
>> Hey Andrew thanks for taking the time to respond. Yes the files were
>> completely empty, and the size is in MB. Here is the DDL for the
>> table.
>> CREATE DATABASE [testdb]
>> ON (NAME = N'testdb_Data',
>> FILENAME = N'e:\sqldata\mssql\data\testdb_Data.MDF' ,
>> SIZE = 3000, FILEGROWTH = 10%), (NAME = N'testdb_Data1',
>> FILENAME = N'e:\sqldata\mssql\data\testdb_Data1.NDF' ,
>> SIZE = 2000, FILEGROWTH = 10%), (NAME = N'testdb_Data2',
>> FILENAME = N'e:\sqldata\mssql\data\testdb_Data2.NDF' ,
>> SIZE = 3000, FILEGROWTH = 10%) LOG ON (NAME = N'testdb_Log',
>> FILENAME = N'g:\sqldata\mssql\data\testdb_Log.LDF' ,
>> SIZE = 500, FILEGROWTH = 10%)
>> I reran the tests a second time as I was wondering if checking the
>> batch size for my bulk insert would have an affect. I ran the test
>> twice once without setting the batch size and a second time of setting
>> the batch size of 10000. Both tests resulted in the data files being
>> filled equally, even though the second file is smaller than the others.
>|||Ok I see so basically it will write equal pages to the files until the
free space is at a 2:1 ration. I wasn't thinking about it on an extent
by extent basis. I was thinking that the end result could be
calculated as such.
(FileSize/TotalFileGroupSizeFreeSpace) * TotalDataToLoad
So in this example
File1 - 3000 - 293
File2 - 2000 - 293
File3 - 3000 - 293
Results should be
File1 - 3000 - 329.625
File2 - 2000 - 219.75
File3 - 3000 - 329.625
Wei maybe you can answer this... as data is allocated at what level is
it determined what file should be written to? IS it at the extent
level? When using multiple files are teh extents grouped into larger
chunks based on file?
bert|||Bert,
There are no cases that I know of where you would break apart an extent.
Each extent always has 8 pages allocated to it (whether or not they all
include data). An extent may have pages from multiple objects (Mixed
Extent) but this is rare and only used when the object has less than 8
pages. You can read and write at the page level but the page always lives
on an extent. So if you are writing to a file and the current extent is full
it will create a new extent in the proper file and begin writing to it. It
will not write half an extent here and half there so to speak.
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1107362088.012050.183690@.l41g2000cwc.googlegroups.com...
> Ok I see so basically it will write equal pages to the files until the
> free space is at a 2:1 ration. I wasn't thinking about it on an extent
> by extent basis. I was thinking that the end result could be
> calculated as such.
> (FileSize/TotalFileGroupSizeFreeSpace) * TotalDataToLoad
> So in this example
> File1 - 3000 - 293
> File2 - 2000 - 293
> File3 - 3000 - 293
> Results should be
> File1 - 3000 - 329.625
> File2 - 2000 - 219.75
> File3 - 3000 - 329.625
> Wei maybe you can answer this... as data is allocated at what level is
> it determined what file should be written to? IS it at the extent
> level? When using multiple files are teh extents grouped into larger
> chunks based on file?
> bert
>|||I was wondering if it does the opposite...not smaller than an extent
but if the proportional fill ever allocates in groups of extents. For
example under load will it write 4 extents to file1 and then 4 extents
to file2 or does it always go extent by extent switching between files.
I am trying to figure out the best configuration for my new server. I
have a database that is about 3TB in size. We currently load about
300-500 million records a month into several tables and want to make
sure I set up the files and file groups correctly.
My new server has 2 PERC4 cards with 2 channels each. Each channel has
a RAID10 array that are each 1TB in size, this gives me four drives
with 1TB each F,G on one channel and H,I on another. I have other
drives for tempdb and tlog that are mirrors.
We are using partitioned tables for data with new tables created each
month.
The strategy I am leaning towards is as follows.
When new tables are created create 2 new file groups. For this month
they would be FebuaryData and FeburaryIndex, create one file in each
filegroup
F:\ FebuaryData
H:\ FeburaryIndex
Next month I would create
G:\MarchData
I:\MarchIndex
What I am trying to determine if there is any advantage to creating
multiple files within each file group, for example.
F:\ FebuaryData-File1
H:\ FeburaryIndex-File1
G:\ FebuaryData-File2
I:\ FebuaryData-File2
F:\ FebuaryData1
H:\ FeburaryIndex1
G:\ FebuaryData2
I:\ FeburaryIndex2
The current database has multiple files located on the same drives. I
have already determined that this does not give better performance.
Any input would be appreciated
Thanks
Bert|||I suspect that it is possible they can allocate more than one extent at a
time per file when it makes sense. Wei Xiao said something to the effect
that they do make adjustments in certain circumstances. Is this going to
be a DW or a large OLTP system? Will you be doing more seeks or range
scans? I wouldn't worry as much about getting the data in as working with
it afterwards. While you may find that one technique is slightly faster
than others for loading the data it is usually a small percentage of the
overall time spent in a system. So one thing you need to decide is whether
the load time or the read time is more important. Then figure out how the
data will be read. If you do a lot of queries that scan ranges of indexes
it will most like benefit from separating the data from the indexes onto
different arrays. If you do a lot of scans (partially or totally) it may be
better to create 4 files for the filegroup and place one on each of the Raid
10's. Of coarse there are no hard and fast rules that you can ccount on in
this situation. It really depends a lot on how you use the data and most of
the time you can only find the optimal solution by trial and error. That
usually takes more time and resources than people want to give but that is
the way it goes. How long do you plan on keeping the data? Is it a yearly
process in which you drop one month out and bring in the next one? Having a
filegroup for each month in that case may not be too bad of an idea. It
allows you to backup at the filegroup level and that may help if the older
months don't have update activity. Again without knowing exactly how you
will use the data it's a guess but why only create 1 file per file group?
If you have 4 drives why not 2 files per FG and place 1 file on each drive.
Two for the indexes and 2 for the data.
--
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1107403443.038720.258700@.c13g2000cwb.googlegroups.com...
>I was wondering if it does the opposite...not smaller than an extent
> but if the proportional fill ever allocates in groups of extents. For
> example under load will it write 4 extents to file1 and then 4 extents
> to file2 or does it always go extent by extent switching between files.
> I am trying to figure out the best configuration for my new server. I
> have a database that is about 3TB in size. We currently load about
> 300-500 million records a month into several tables and want to make
> sure I set up the files and file groups correctly.
> My new server has 2 PERC4 cards with 2 channels each. Each channel has
> a RAID10 array that are each 1TB in size, this gives me four drives
> with 1TB each F,G on one channel and H,I on another. I have other
> drives for tempdb and tlog that are mirrors.
> We are using partitioned tables for data with new tables created each
> month.
> The strategy I am leaning towards is as follows.
> When new tables are created create 2 new file groups. For this month
> they would be FebuaryData and FeburaryIndex, create one file in each
> filegroup
> F:\ FebuaryData
> H:\ FeburaryIndex
> Next month I would create
> G:\MarchData
> I:\MarchIndex
> What I am trying to determine if there is any advantage to creating
> multiple files within each file group, for example.
> F:\ FebuaryData-File1
> H:\ FeburaryIndex-File1
> G:\ FebuaryData-File2
> I:\ FebuaryData-File2
> F:\ FebuaryData1
> H:\ FeburaryIndex1
> G:\ FebuaryData2
> I:\ FeburaryIndex2
> The current database has multiple files located on the same drives. I
> have already determined that this does not give better performance.
> Any input would be appreciated
> Thanks
> Bert
>|||>>I suspect that it is possible they can allocate more than one extent
at a
>>time per file when it makes sense.
I guess the main thing I wanted to know is when they would fill at
differnet rates. IT makes it import to plan ahead as I dont want to
run in to a situation where I have the files spread out but they are
not being written to at the same rate. Would make it hard to plan for
space.
>>Is this going to be a DW or a large OLTP system?
OLTP, the system will do real time processing of firewall and ids event
data. A set of queries will always be runnign processing the data and
looking for attackers.
>>How long do you plan on keeping the data? Is it a yearly
>>process in which you drop one month out and bring in the next one?
yep that is what we are doing. This is actually one of 4 servers that
will be loading and processing the same data. We are going to set up
the other servers to act as our "backup" and they will use differnet
indexing stratagies. This is why I am spending so much time trying to
make sure I make the right file layout choice.
>>why only create 1 file per file group
Some of our queries will span several months. With 4 drives I was
thinking I could have month1 on adrive Index 1 on another and then do
the same for month 2. After thinking about this some more and looking
at some of the query patterns I have determined that most queries
target data in the last month so this layout would not be ideal.
Ok last question for you ;) ... I have decided on using 2 FileGroups
for now. LargeTables and LargeTablesIndex do I ...
A. Each FileGroup gets one file on each array for a total of eight
files
Pro - Extra Files distribute teh IO across all disk
Con - Index IO could impact Table IO and vs versa
F:\ LargeTables - File1, LargeTablesIndex -File1
H:\ LargeTables - File2, LargeTablesIndex -File1
G:\ LargeTables - File3, LargeTablesIndex -File1
I:\ LargeTables - File4, LargeTablesIndex -File1
B. Each Filegroup has 2 dedicated arrays
Pro - EAch filegroup has dedicated drives
Con - Not fully utilizing resources
F:\ LargeTables - File1
H:\ LargeTables - File2
G:\ LargeTablesIndex -File1
I:\ LargeTablesIndex -File1
I think I am leaning towards A... I am also going to perform some more
tests to back up this theory. I am almost done writing a multi
threading utility that can be used to load test the DB once this is
done it will make my testing much easier|||>>I suspect that it is possible they can allocate more than one extent
at a
>>time per file when it makes sense.
I guess the main thing I wanted to know is when they would fill at
differnet rates. IT makes it import to plan ahead as I dont want to
run in to a situation where I have the files spread out but they are
not being written to at the same rate. Would make it hard to plan for
space.
>>Is this going to be a DW or a large OLTP system?
OLTP, the system will do real time processing of firewall and ids event
data. A set of queries will always be runnign processing the data and
looking for attackers.
>>How long do you plan on keeping the data? Is it a yearly
>>process in which you drop one month out and bring in the next one?
yep that is what we are doing. This is actually one of 4 servers that
will be loading and processing the same data. We are going to set up
the other servers to act as our "backup" and they will use differnet
indexing stratagies. This is why I am spending so much time trying to
make sure I make the right file layout choice.
>>why only create 1 file per file group
Some of our queries will span several months. With 4 drives I was
thinking I could have month1 on adrive Index 1 on another and then do
the same for month 2. After thinking about this some more and looking
at some of the query patterns I have determined that most queries
target data in the last month so this layout would not be ideal.
Ok last question for you ;) ... I have decided on using 2 FileGroups
for now. LargeTables and LargeTablesIndex do I ...
A. Each FileGroup gets one file on each array for a total of eight
files
Pro - Extra Files distribute teh IO across all disk
Con - Index IO could impact Table IO and vs versa
F:\ LargeTables - File1, LargeTablesIndex -File1
H:\ LargeTables - File2, LargeTablesIndex -File1
G:\ LargeTables - File3, LargeTablesIndex -File1
I:\ LargeTables - File4, LargeTablesIndex -File1
B. Each Filegroup has 2 dedicated arrays
Pro - EAch filegroup has dedicated drives
Con - Not fully utilizing resources
F:\ LargeTables - File1
H:\ LargeTables - File2
G:\ LargeTablesIndex -File1
I:\ LargeTablesIndex -File1
I think I am leaning towards A... I am also going to perform some more
tests to back up this theory. I am almost done writing a multi
threading utility that can be used to load test the DB once this is
done it will make my testing much easier|||>>I suspect that it is possible they can allocate more than one extent
at a
>>time per file when it makes sense.
I guess the main thing I wanted to know is when they would fill at
differnet rates. IT makes it import to plan ahead as I dont want to
run in to a situation where I have the files spread out but they are
not being written to at the same rate. Would make it hard to plan for
space.
>>Is this going to be a DW or a large OLTP system?
OLTP, the system will do real time processing of firewall and ids event
data. A set of queries will always be runnign processing the data and
looking for attackers.
>>How long do you plan on keeping the data? Is it a yearly
>>process in which you drop one month out and bring in the next one?
yep that is what we are doing. This is actually one of 4 servers that
will be loading and processing the same data. We are going to set up
the other servers to act as our "backup" and they will use differnet
indexing stratagies. This is why I am spending so much time trying to
make sure I make the right file layout choice.
>>why only create 1 file per file group
Some of our queries will span several months. With 4 drives I was
thinking I could have month1 on adrive Index 1 on another and then do
the same for month 2. After thinking about this some more and looking
at some of the query patterns I have determined that most queries
target data in the last month so this layout would not be ideal.
Ok last question for you ;) ... I have decided on using 2 FileGroups
for now. LargeTables and LargeTablesIndex do I ...
A. Each FileGroup gets one file on each array for a total of eight
files
Pro - Extra Files distribute teh IO across all disk
Con - Index IO could impact Table IO and vs versa
F:\ LargeTables - File1, LargeTablesIndex -File1
H:\ LargeTables - File2, LargeTablesIndex -File1
G:\ LargeTables - File3, LargeTablesIndex -File1
I:\ LargeTables - File4, LargeTablesIndex -File1
B. Each Filegroup has 2 dedicated arrays
Pro - EAch filegroup has dedicated drives
Con - Not fully utilizing resources
F:\ LargeTables - File1
H:\ LargeTables - File2
G:\ LargeTablesIndex -File1
I:\ LargeTablesIndex -File1
I think I am leaning towards A... I am also going to perform some more
tests to back up this theory. I am almost done writing a multi
threading utility that can be used to load test the DB once this is
done it will make my testing much easier|||>>I suspect that it is possible they can allocate more than one extent
at a
>>time per file when it makes sense.
I guess the main thing I wanted to know is when they would fill at
differnet rates. IT makes it import to plan ahead as I dont want to
run in to a situation where I have the files spread out but they are
not being written to at the same rate. Would make it hard to plan for
space.
>>Is this going to be a DW or a large OLTP system?
OLTP, the system will do real time processing of firewall and ids event
data. A set of queries will always be runnign processing the data and
looking for attackers.
>>How long do you plan on keeping the data? Is it a yearly
>>process in which you drop one month out and bring in the next one?
yep that is what we are doing. This is actually one of 4 servers that
will be loading and processing the same data. We are going to set up
the other servers to act as our "backup" and they will use differnet
indexing stratagies. This is why I am spending so much time trying to
make sure I make the right file layout choice.
>>why only create 1 file per file group
Some of our queries will span several months. With 4 drives I was
thinking I could have month1 on adrive Index 1 on another and then do
the same for month 2. After thinking about this some more and looking
at some of the query patterns I have determined that most queries
target data in the last month so this layout would not be ideal.
Ok last question for you ;) ... I have decided on using 2 FileGroups
for now. LargeTables and LargeTablesIndex do I ...
A. Each FileGroup gets one file on each array for a total of eight
files
Pro - Extra Files distribute teh IO across all disk
Con - Index IO could impact Table IO and vs versa
F:\ LargeTables - File1, LargeTablesIndex -File1
H:\ LargeTables - File2, LargeTablesIndex -File1
G:\ LargeTables - File3, LargeTablesIndex -File1
I:\ LargeTables - File4, LargeTablesIndex -File1
B. Each Filegroup has 2 dedicated arrays
Pro - EAch filegroup has dedicated drives
Con - Not fully utilizing resources
F:\ LargeTables - File1
H:\ LargeTables - File2
G:\ LargeTablesIndex -File1
I:\ LargeTablesIndex -File1
I think I am leaning towards A... I am also going to perform some more
tests to back up this theory. I am almost done writing a multi
threading utility that can be used to load test the DB once this is
done it will make my testing much easier|||> Ok last question for you ;) ... I have decided on using 2 FileGroups
> for now. LargeTables and LargeTablesIndex do I ...
> A. Each FileGroup gets one file on each array for a total of eight
> files
> Pro - Extra Files distribute teh IO across all disk
> Con - Index IO could impact Table IO and vs versa
> F:\ LargeTables - File1, LargeTablesIndex -File1
> H:\ LargeTables - File2, LargeTablesIndex -File1
> G:\ LargeTables - File3, LargeTablesIndex -File1
> I:\ LargeTables - File4, LargeTablesIndex -File1
> B. Each Filegroup has 2 dedicated arrays
> Pro - EAch filegroup has dedicated drives
> Con - Not fully utilizing resources
> F:\ LargeTables - File1
> H:\ LargeTables - File2
> G:\ LargeTablesIndex -File1
> I:\ LargeTablesIndex -File1
>
Since you said this is going to be an OLTP you should be doing more seeks
than scans. That means relatively small amount of pages being read from
each file. So I would spread the files across all the array's as in choice
A. These are RAID 10's and the activity should be such that I am not sure
you would get as much benefit from dedicating two drives for indexes alone.
Your key is to make sure you have enough ram to mitigate the I/O anyway.
You are spending a lot of money on drives so make sure you give the memory
it's due as well. Have you considered using 64 bit at all? I am not saying
that it is the way to go, only that you should weigh it's pros and cons as
well. If you are doing lots of computations and need support for large
memory it can be a winner.
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1107492155.518141.278920@.o13g2000cwo.googlegroups.com...|||>>Since you said this is going to be an OLTP you should be doing more
seeks
>>than scans.
Still lots of scnas... I guess I woudl call this a hybrid database.
Ever 24 hours about 30-50 millions rows are loaded. Most queries are
scanning this data.
>>You are spending a lot of money on drives so make sure you give the
memory
>>it's due as well.
We are starting with 8GB...hopefully this is enough.
>>Have you considered using 64 bit at all?
I would love to ;)...heck we are still running windows 2000... Other
reasons I am putting so much effort into measuring performance is so I
have a baseline to compare to. Once I get this server set up I think I
will finally have some time to start my WIn2003 and Yukon testing. I
finished my .Net app today...I call it the DBJammer. Very simple for
now but it creates two thread pools...one pool runs bulk inserts 2
threads at A time and the other pool fires of queries against the data
about 50 threads at a time. Hopefully I can use this to help get some
good metrics. I have heard that an upgrade to windows 2003 gives a
measurable performance gain as well.|||Absolutely you should use Win2003. It makes no sense to build a new app and
use Win2000. 2003 has many benefits to aide in performance across the
board.
--
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1107580908.417955.239800@.g14g2000cwa.googlegroups.com...
>>Since you said this is going to be an OLTP you should be doing more
> seeks
>>than scans.
> Still lots of scnas... I guess I woudl call this a hybrid database.
> Ever 24 hours about 30-50 millions rows are loaded. Most queries are
> scanning this data.
>>You are spending a lot of money on drives so make sure you give the
> memory
>>it's due as well.
> We are starting with 8GB...hopefully this is enough.
>>Have you considered using 64 bit at all?
> I would love to ;)...heck we are still running windows 2000... Other
> reasons I am putting so much effort into measuring performance is so I
> have a baseline to compare to. Once I get this server set up I think I
> will finally have some time to start my WIn2003 and Yukon testing. I
> finished my .Net app today...I call it the DBJammer. Very simple for
> now but it creates two thread pools...one pool runs bulk inserts 2
> threads at A time and the other pool fires of queries against the data
> about 50 threads at a time. Hopefully I can use this to help get some
> good metrics. I have heard that an upgrade to windows 2003 gives a
> measurable performance gain as well.
>|||I agree... unfortunatley I am only the DBA..And it's not my decision.
I can't just say hey lets use windows2003 because it's better.
However I am going to run a set of tests on identical hardware, and
then I can say hey lets use windows 2003 because in these test it
performned this much faster. I think it is kind of crazy to spend all
of that money on hardware and use windows 2000.
Bert
Showing posts with label single. Show all posts
Showing posts with label single. Show all posts
Friday, March 30, 2012
proportional fill using multiple files
As part of my ongoing testing of the performance impact of multiple
files vs. a single file. (Here is the link, comments would be
appreciated http://linktrim.com/yq) I have come across some
inconsistencies in how "proportional fill" works. According to books
online
"Filegroups use a proportional fill strategy across all the files
within each filegroup. As data is written to the filegroup, Microsoft=AE
SQL Server=99 2000 writes an amount proportional to the free space in
the file to each file within the filegroup, rather than writing all the
data to the first file until full, and then writing to the next file.
For example, if file f1 has 100 megabytes (MB) free and file f2 has 200
MB free, one extent is allocated from file f1, two extents from file
f2, and so on. This way both files become full at about the same time,
and simple striping is achieved."
Based on some of my testing this is not what is occurring. I created 3
files in one filegroup for my DB. The first number is the file size,
the second number is the used size after the data load.
1=2E Test one
File1 - 3000 - 520
File2 - 2000 - 520
File3 - 2000 - 520
2=2E Test two
File1 - 3000 - 293
File2 - 2000 - 293
File3 - 3000 - 293
3=2E Test three
File1 - 4000 - 416
File2 - 2000 - 232
File3 - 2000 - 232
As you can see in test one and two the used size is the same across
files even thought the 3 data files are not the same size. Not until I
make one file twice as large as the others did I sea difference in
used space.
Can anyone explain this behavior?
BertWere all the files completely empty? What size is that in? Is it MB's,
GBs, KBs etc?
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1107271506.112735.198080@.f14g2000cwb.googlegroups.com...
As part of my ongoing testing of the performance impact of multiple
files vs. a single file. (Here is the link, comments would be
appreciated http://linktrim.com/yq) I have come across some
inconsistencies in how "proportional fill" works. According to books
online
"Filegroups use a proportional fill strategy across all the files
within each filegroup. As data is written to the filegroup, Microsoft
SQL ServerT 2000 writes an amount proportional to the free space in
the file to each file within the filegroup, rather than writing all the
data to the first file until full, and then writing to the next file.
For example, if file f1 has 100 megabytes (MB) free and file f2 has 200
MB free, one extent is allocated from file f1, two extents from file
f2, and so on. This way both files become full at about the same time,
and simple striping is achieved."
Based on some of my testing this is not what is occurring. I created 3
files in one filegroup for my DB. The first number is the file size,
the second number is the used size after the data load.
1. Test one
File1 - 3000 - 520
File2 - 2000 - 520
File3 - 2000 - 520
2. Test two
File1 - 3000 - 293
File2 - 2000 - 293
File3 - 3000 - 293
3. Test three
File1 - 4000 - 416
File2 - 2000 - 232
File3 - 2000 - 232
As you can see in test one and two the used size is the same across
files even thought the 3 data files are not the same size. Not until I
make one file twice as large as the others did I sea difference in
used space.
Can anyone explain this behavior?
Bert|||Hey Andrew thanks for taking the time to respond. Yes the files were
completely empty, and the size is in MB. Here is the DDL for the
table.
CREATE DATABASE [testdb]
ON (NAME = N'testdb_Data',
FILENAME = N'e:\sqldata\mssql\data\testdb_Data.MDF' ,
SIZE = 3000, FILEGROWTH = 10%), (NAME = N'testdb_Data1',
FILENAME = N'e:\sqldata\mssql\data\testdb_Data1.NDF' ,
SIZE = 2000, FILEGROWTH = 10%), (NAME = N'testdb_Data2',
FILENAME = N'e:\sqldata\mssql\data\testdb_Data2.NDF' ,
SIZE = 3000, FILEGROWTH = 10%) LOG ON (NAME = N'testdb_Log',
FILENAME = N'g:\sqldata\mssql\data\testdb_Log.LDF' ,
SIZE = 500, FILEGROWTH = 10%)
I reran the tests a second time as I was wondering if checking the
batch size for my bulk insert would have an affect. I ran the test
twice once without setting the batch size and a second time of setting
the batch size of 10000. Both tests resulted in the data files being
filled equally, even though the second file is smaller than the others.|||OK this is a guess but makes a lot of sense when you think about it. The
results you had are listed below. Since the files are filled at the extent
level it can't split an extent and put half in one file and half in another.
So in your tests until you get differences in free space that are exact
multiples of each other it will round down. So with a file size of 2000
(file2) and 3000 (file3) it would allocate one extent to file 2 and one to
file 3. When you get double the free space as in File1 and file 2 it can
then allocate in a 2 to 1 fashion. I believe the algorithm has to be very
simple to keep it efficient. That and the fact it allocates in extents lend
to the behavior you see.
1. Test one
File1 - 3000 - 520
File2 - 2000 - 520
File3 - 2000 - 520
2. Test two
File1 - 3000 - 293
File2 - 2000 - 293
File3 - 3000 - 293
3. Test three
File1 - 4000 - 416
File2 - 2000 - 232
File3 - 2000 - 232
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1107287428.961190.60820@.z14g2000cwz.googlegroups.com...
> Hey Andrew thanks for taking the time to respond. Yes the files were
> completely empty, and the size is in MB. Here is the DDL for the
> table.
> CREATE DATABASE [testdb]
> ON (NAME = N'testdb_Data',
> FILENAME = N'e:\sqldata\mssql\data\testdb_Data.MDF' ,
> SIZE = 3000, FILEGROWTH = 10%), (NAME = N'testdb_Data1',
> FILENAME = N'e:\sqldata\mssql\data\testdb_Data1.NDF' ,
> SIZE = 2000, FILEGROWTH = 10%), (NAME = N'testdb_Data2',
> FILENAME = N'e:\sqldata\mssql\data\testdb_Data2.NDF' ,
> SIZE = 3000, FILEGROWTH = 10%) LOG ON (NAME = N'testdb_Log',
> FILENAME = N'g:\sqldata\mssql\data\testdb_Log.LDF' ,
> SIZE = 500, FILEGROWTH = 10%)
> I reran the tests a second time as I was wondering if checking the
> batch size for my bulk insert would have an affect. I ran the test
> twice once without setting the batch size and a second time of setting
> the batch size of 10000. Both tests resulted in the data files being
> filled equally, even though the second file is smaller than the others.
>|||This is correct. In test 1, if you keep adding data, when the remaining free
space in file 1 versus file 2 become a ratio of 2:1, then we allocate 2
pages in file 1 for every page in file 2.
Please also note that when file becomes very large or when there are a lot
of concurrent transactions, we use some performance improvement technique
with a side effect that the free space calculation is not 100% accurate.
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.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eHQruWNCFHA.2072@.TK2MSFTNGP10.phx.gbl...
> OK this is a guess but makes a lot of sense when you think about it. The
> results you had are listed below. Since the files are filled at the
> extent level it can't split an extent and put half in one file and half in
> another. So in your tests until you get differences in free space that are
> exact multiples of each other it will round down. So with a file size of
> 2000 (file2) and 3000 (file3) it would allocate one extent to file 2 and
> one to file 3. When you get double the free space as in File1 and file 2
> it can then allocate in a 2 to 1 fashion. I believe the algorithm has to
> be very simple to keep it efficient. That and the fact it allocates in
> extents lend to the behavior you see.
> 1. Test one
> File1 - 3000 - 520
> File2 - 2000 - 520
> File3 - 2000 - 520
> 2. Test two
> File1 - 3000 - 293
> File2 - 2000 - 293
> File3 - 3000 - 293
> 3. Test three
> File1 - 4000 - 416
> File2 - 2000 - 232
> File3 - 2000 - 232
>
> --
> Andrew J. Kelly SQL MVP
>
> "bert" <bertcord@.gmail.com> wrote in message
> news:1107287428.961190.60820@.z14g2000cwz.googlegroups.com...
>|||Ok I see so basically it will write equal pages to the files until the
free space is at a 2:1 ration. I wasn't thinking about it on an extent
by extent basis. I was thinking that the end result could be
calculated as such.
(FileSize/TotalFileGroupSizeFreeSpace) * TotalDataToLoad
So in this example
File1 - 3000 - 293
File2 - 2000 - 293
File3 - 3000 - 293
Results should be
File1 - 3000 - 329.625
File2 - 2000 - 219.75
File3 - 3000 - 329.625
Wei maybe you can answer this... as data is allocated at what level is
it determined what file should be written to? IS it at the extent
level? When using multiple files are teh extents grouped into larger
chunks based on file?
bert|||Bert,
There are no cases that I know of where you would break apart an extent.
Each extent always has 8 pages allocated to it (whether or not they all
include data). An extent may have pages from multiple objects (Mixed
Extent) but this is rare and only used when the object has less than 8
pages. You can read and write at the page level but the page always lives
on an extent. So if you are writing to a file and the current extent is full
it will create a new extent in the proper file and begin writing to it. It
will not write half an extent here and half there so to speak.
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1107362088.012050.183690@.l41g2000cwc.googlegroups.com...
> Ok I see so basically it will write equal pages to the files until the
> free space is at a 2:1 ration. I wasn't thinking about it on an extent
> by extent basis. I was thinking that the end result could be
> calculated as such.
> (FileSize/TotalFileGroupSizeFreeSpace) * TotalDataToLoad
> So in this example
> File1 - 3000 - 293
> File2 - 2000 - 293
> File3 - 3000 - 293
> Results should be
> File1 - 3000 - 329.625
> File2 - 2000 - 219.75
> File3 - 3000 - 329.625
> Wei maybe you can answer this... as data is allocated at what level is
> it determined what file should be written to? IS it at the extent
> level? When using multiple files are teh extents grouped into larger
> chunks based on file?
> bert
>|||I was wondering if it does the opposite...not smaller than an extent
but if the proportional fill ever allocates in groups of extents. For
example under load will it write 4 extents to file1 and then 4 extents
to file2 or does it always go extent by extent switching between files.
I am trying to figure out the best configuration for my new server. I
have a database that is about 3TB in size. We currently load about
300-500 million records a month into several tables and want to make
sure I set up the files and file groups correctly.
My new server has 2 PERC4 cards with 2 channels each. Each channel has
a RAID10 array that are each 1TB in size, this gives me four drives
with 1TB each F,G on one channel and H,I on another. I have other
drives for tempdb and tlog that are mirrors.
We are using partitioned tables for data with new tables created each
month.
The strategy I am leaning towards is as follows.
When new tables are created create 2 new file groups. For this month
they would be FebuaryData and FeburaryIndex, create one file in each
filegroup
F:\ FebuaryData
H:\ FeburaryIndex
Next month I would create
G:\MarchData
I:\MarchIndex
What I am trying to determine if there is any advantage to creating
multiple files within each file group, for example.
F:\ FebuaryData-File1
H:\ FeburaryIndex-File1
G:\ FebuaryData-File2
I:\ FebuaryData-File2
F:\ FebuaryData1
H:\ FeburaryIndex1
G:\ FebuaryData2
I:\ FeburaryIndex2
The current database has multiple files located on the same drives. I
have already determined that this does not give better performance.
Any input would be appreciated
Thanks
Bert|||I suspect that it is possible they can allocate more than one extent at a
time per file when it makes sense. Wei Xiao said something to the effect
that they do make adjustments in certain circumstances. Is this going to
be a DW or a large OLTP system? Will you be doing more seeks or range
scans? I wouldn't worry as much about getting the data in as working with
it afterwards. While you may find that one technique is slightly faster
than others for loading the data it is usually a small percentage of the
overall time spent in a system. So one thing you need to decide is whether
the load time or the read time is more important. Then figure out how the
data will be read. If you do a lot of queries that scan ranges of indexes
it will most like benefit from separating the data from the indexes onto
different arrays. If you do a lot of scans (partially or totally) it may be
better to create 4 files for the filegroup and place one on each of the Raid
10's. Of coarse there are no hard and fast rules that you can ccount on in
this situation. It really depends a lot on how you use the data and most of
the time you can only find the optimal solution by trial and error. That
usually takes more time and resources than people want to give but that is
the way it goes. How long do you plan on keeping the data? Is it a yearly
process in which you drop one month out and bring in the next one? Having a
filegroup for each month in that case may not be too bad of an idea. It
allows you to backup at the filegroup level and that may help if the older
months don't have update activity. Again without knowing exactly how you
will use the data it's a guess but why only create 1 file per file group?
If you have 4 drives why not 2 files per FG and place 1 file on each drive.
Two for the indexes and 2 for the data.
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1107403443.038720.258700@.c13g2000cwb.googlegroups.com...
>I was wondering if it does the opposite...not smaller than an extent
> but if the proportional fill ever allocates in groups of extents. For
> example under load will it write 4 extents to file1 and then 4 extents
> to file2 or does it always go extent by extent switching between files.
> I am trying to figure out the best configuration for my new server. I
> have a database that is about 3TB in size. We currently load about
> 300-500 million records a month into several tables and want to make
> sure I set up the files and file groups correctly.
> My new server has 2 PERC4 cards with 2 channels each. Each channel has
> a RAID10 array that are each 1TB in size, this gives me four drives
> with 1TB each F,G on one channel and H,I on another. I have other
> drives for tempdb and tlog that are mirrors.
> We are using partitioned tables for data with new tables created each
> month.
> The strategy I am leaning towards is as follows.
> When new tables are created create 2 new file groups. For this month
> they would be FebuaryData and FeburaryIndex, create one file in each
> filegroup
> F:\ FebuaryData
> H:\ FeburaryIndex
> Next month I would create
> G:\MarchData
> I:\MarchIndex
> What I am trying to determine if there is any advantage to creating
> multiple files within each file group, for example.
> F:\ FebuaryData-File1
> H:\ FeburaryIndex-File1
> G:\ FebuaryData-File2
> I:\ FebuaryData-File2
> F:\ FebuaryData1
> H:\ FeburaryIndex1
> G:\ FebuaryData2
> I:\ FeburaryIndex2
> The current database has multiple files located on the same drives. I
> have already determined that this does not give better performance.
> Any input would be appreciated
> Thanks
> Bert
>|||>>I suspect that it is possible they can allocate more than one extent
at a[vbcol=seagreen]
I guess the main thing I wanted to know is when they would fill at
differnet rates. IT makes it import to plan ahead as I dont want to
run in to a situation where I have the files spread out but they are
not being written to at the same rate. Would make it hard to plan for
space.
[vbcol=seagreen]
OLTP, the system will do real time processing of firewall and ids event
data. A set of queries will always be runnign processing the data and
looking for attackers.
[vbcol=seagreen]
yep that is what we are doing. This is actually one of 4 servers that
will be loading and processing the same data. We are going to set up
the other servers to act as our "backup" and they will use differnet
indexing stratagies. This is why I am spending so much time trying to
make sure I make the right file layout choice.
[vbcol=seagreen]
Some of our queries will span several months. With 4 drives I was
thinking I could have month1 on adrive Index 1 on another and then do
the same for month 2. After thinking about this some more and looking
at some of the query patterns I have determined that most queries
target data in the last month so this layout would not be ideal.
Ok last question for you ;) ... I have decided on using 2 FileGroups
for now. LargeTables and LargeTablesIndex do I ...
A. Each FileGroup gets one file on each array for a total of eight
files
Pro - Extra Files distribute teh IO across all disk
Con - Index IO could impact Table IO and vs versa
F:\ LargeTables - File1, LargeTablesIndex -File1
H:\ LargeTables - File2, LargeTablesIndex -File1
G:\ LargeTables - File3, LargeTablesIndex -File1
I:\ LargeTables - File4, LargeTablesIndex -File1
B. Each Filegroup has 2 dedicated arrays
Pro - EAch filegroup has dedicated drives
Con - Not fully utilizing resources
F:\ LargeTables - File1
H:\ LargeTables - File2
G:\ LargeTablesIndex -File1
I:\ LargeTablesIndex -File1
I think I am leaning towards A... I am also going to perform some more
tests to back up this theory. I am almost done writing a multi
threading utility that can be used to load test the DB once this is
done it will make my testing much easier
files vs. a single file. (Here is the link, comments would be
appreciated http://linktrim.com/yq) I have come across some
inconsistencies in how "proportional fill" works. According to books
online
"Filegroups use a proportional fill strategy across all the files
within each filegroup. As data is written to the filegroup, Microsoft=AE
SQL Server=99 2000 writes an amount proportional to the free space in
the file to each file within the filegroup, rather than writing all the
data to the first file until full, and then writing to the next file.
For example, if file f1 has 100 megabytes (MB) free and file f2 has 200
MB free, one extent is allocated from file f1, two extents from file
f2, and so on. This way both files become full at about the same time,
and simple striping is achieved."
Based on some of my testing this is not what is occurring. I created 3
files in one filegroup for my DB. The first number is the file size,
the second number is the used size after the data load.
1=2E Test one
File1 - 3000 - 520
File2 - 2000 - 520
File3 - 2000 - 520
2=2E Test two
File1 - 3000 - 293
File2 - 2000 - 293
File3 - 3000 - 293
3=2E Test three
File1 - 4000 - 416
File2 - 2000 - 232
File3 - 2000 - 232
As you can see in test one and two the used size is the same across
files even thought the 3 data files are not the same size. Not until I
make one file twice as large as the others did I sea difference in
used space.
Can anyone explain this behavior?
BertWere all the files completely empty? What size is that in? Is it MB's,
GBs, KBs etc?
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1107271506.112735.198080@.f14g2000cwb.googlegroups.com...
As part of my ongoing testing of the performance impact of multiple
files vs. a single file. (Here is the link, comments would be
appreciated http://linktrim.com/yq) I have come across some
inconsistencies in how "proportional fill" works. According to books
online
"Filegroups use a proportional fill strategy across all the files
within each filegroup. As data is written to the filegroup, Microsoft
SQL ServerT 2000 writes an amount proportional to the free space in
the file to each file within the filegroup, rather than writing all the
data to the first file until full, and then writing to the next file.
For example, if file f1 has 100 megabytes (MB) free and file f2 has 200
MB free, one extent is allocated from file f1, two extents from file
f2, and so on. This way both files become full at about the same time,
and simple striping is achieved."
Based on some of my testing this is not what is occurring. I created 3
files in one filegroup for my DB. The first number is the file size,
the second number is the used size after the data load.
1. Test one
File1 - 3000 - 520
File2 - 2000 - 520
File3 - 2000 - 520
2. Test two
File1 - 3000 - 293
File2 - 2000 - 293
File3 - 3000 - 293
3. Test three
File1 - 4000 - 416
File2 - 2000 - 232
File3 - 2000 - 232
As you can see in test one and two the used size is the same across
files even thought the 3 data files are not the same size. Not until I
make one file twice as large as the others did I sea difference in
used space.
Can anyone explain this behavior?
Bert|||Hey Andrew thanks for taking the time to respond. Yes the files were
completely empty, and the size is in MB. Here is the DDL for the
table.
CREATE DATABASE [testdb]
ON (NAME = N'testdb_Data',
FILENAME = N'e:\sqldata\mssql\data\testdb_Data.MDF' ,
SIZE = 3000, FILEGROWTH = 10%), (NAME = N'testdb_Data1',
FILENAME = N'e:\sqldata\mssql\data\testdb_Data1.NDF' ,
SIZE = 2000, FILEGROWTH = 10%), (NAME = N'testdb_Data2',
FILENAME = N'e:\sqldata\mssql\data\testdb_Data2.NDF' ,
SIZE = 3000, FILEGROWTH = 10%) LOG ON (NAME = N'testdb_Log',
FILENAME = N'g:\sqldata\mssql\data\testdb_Log.LDF' ,
SIZE = 500, FILEGROWTH = 10%)
I reran the tests a second time as I was wondering if checking the
batch size for my bulk insert would have an affect. I ran the test
twice once without setting the batch size and a second time of setting
the batch size of 10000. Both tests resulted in the data files being
filled equally, even though the second file is smaller than the others.|||OK this is a guess but makes a lot of sense when you think about it. The
results you had are listed below. Since the files are filled at the extent
level it can't split an extent and put half in one file and half in another.
So in your tests until you get differences in free space that are exact
multiples of each other it will round down. So with a file size of 2000
(file2) and 3000 (file3) it would allocate one extent to file 2 and one to
file 3. When you get double the free space as in File1 and file 2 it can
then allocate in a 2 to 1 fashion. I believe the algorithm has to be very
simple to keep it efficient. That and the fact it allocates in extents lend
to the behavior you see.
1. Test one
File1 - 3000 - 520
File2 - 2000 - 520
File3 - 2000 - 520
2. Test two
File1 - 3000 - 293
File2 - 2000 - 293
File3 - 3000 - 293
3. Test three
File1 - 4000 - 416
File2 - 2000 - 232
File3 - 2000 - 232
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1107287428.961190.60820@.z14g2000cwz.googlegroups.com...
> Hey Andrew thanks for taking the time to respond. Yes the files were
> completely empty, and the size is in MB. Here is the DDL for the
> table.
> CREATE DATABASE [testdb]
> ON (NAME = N'testdb_Data',
> FILENAME = N'e:\sqldata\mssql\data\testdb_Data.MDF' ,
> SIZE = 3000, FILEGROWTH = 10%), (NAME = N'testdb_Data1',
> FILENAME = N'e:\sqldata\mssql\data\testdb_Data1.NDF' ,
> SIZE = 2000, FILEGROWTH = 10%), (NAME = N'testdb_Data2',
> FILENAME = N'e:\sqldata\mssql\data\testdb_Data2.NDF' ,
> SIZE = 3000, FILEGROWTH = 10%) LOG ON (NAME = N'testdb_Log',
> FILENAME = N'g:\sqldata\mssql\data\testdb_Log.LDF' ,
> SIZE = 500, FILEGROWTH = 10%)
> I reran the tests a second time as I was wondering if checking the
> batch size for my bulk insert would have an affect. I ran the test
> twice once without setting the batch size and a second time of setting
> the batch size of 10000. Both tests resulted in the data files being
> filled equally, even though the second file is smaller than the others.
>|||This is correct. In test 1, if you keep adding data, when the remaining free
space in file 1 versus file 2 become a ratio of 2:1, then we allocate 2
pages in file 1 for every page in file 2.
Please also note that when file becomes very large or when there are a lot
of concurrent transactions, we use some performance improvement technique
with a side effect that the free space calculation is not 100% accurate.
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.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eHQruWNCFHA.2072@.TK2MSFTNGP10.phx.gbl...
> OK this is a guess but makes a lot of sense when you think about it. The
> results you had are listed below. Since the files are filled at the
> extent level it can't split an extent and put half in one file and half in
> another. So in your tests until you get differences in free space that are
> exact multiples of each other it will round down. So with a file size of
> 2000 (file2) and 3000 (file3) it would allocate one extent to file 2 and
> one to file 3. When you get double the free space as in File1 and file 2
> it can then allocate in a 2 to 1 fashion. I believe the algorithm has to
> be very simple to keep it efficient. That and the fact it allocates in
> extents lend to the behavior you see.
> 1. Test one
> File1 - 3000 - 520
> File2 - 2000 - 520
> File3 - 2000 - 520
> 2. Test two
> File1 - 3000 - 293
> File2 - 2000 - 293
> File3 - 3000 - 293
> 3. Test three
> File1 - 4000 - 416
> File2 - 2000 - 232
> File3 - 2000 - 232
>
> --
> Andrew J. Kelly SQL MVP
>
> "bert" <bertcord@.gmail.com> wrote in message
> news:1107287428.961190.60820@.z14g2000cwz.googlegroups.com...
>|||Ok I see so basically it will write equal pages to the files until the
free space is at a 2:1 ration. I wasn't thinking about it on an extent
by extent basis. I was thinking that the end result could be
calculated as such.
(FileSize/TotalFileGroupSizeFreeSpace) * TotalDataToLoad
So in this example
File1 - 3000 - 293
File2 - 2000 - 293
File3 - 3000 - 293
Results should be
File1 - 3000 - 329.625
File2 - 2000 - 219.75
File3 - 3000 - 329.625
Wei maybe you can answer this... as data is allocated at what level is
it determined what file should be written to? IS it at the extent
level? When using multiple files are teh extents grouped into larger
chunks based on file?
bert|||Bert,
There are no cases that I know of where you would break apart an extent.
Each extent always has 8 pages allocated to it (whether or not they all
include data). An extent may have pages from multiple objects (Mixed
Extent) but this is rare and only used when the object has less than 8
pages. You can read and write at the page level but the page always lives
on an extent. So if you are writing to a file and the current extent is full
it will create a new extent in the proper file and begin writing to it. It
will not write half an extent here and half there so to speak.
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1107362088.012050.183690@.l41g2000cwc.googlegroups.com...
> Ok I see so basically it will write equal pages to the files until the
> free space is at a 2:1 ration. I wasn't thinking about it on an extent
> by extent basis. I was thinking that the end result could be
> calculated as such.
> (FileSize/TotalFileGroupSizeFreeSpace) * TotalDataToLoad
> So in this example
> File1 - 3000 - 293
> File2 - 2000 - 293
> File3 - 3000 - 293
> Results should be
> File1 - 3000 - 329.625
> File2 - 2000 - 219.75
> File3 - 3000 - 329.625
> Wei maybe you can answer this... as data is allocated at what level is
> it determined what file should be written to? IS it at the extent
> level? When using multiple files are teh extents grouped into larger
> chunks based on file?
> bert
>|||I was wondering if it does the opposite...not smaller than an extent
but if the proportional fill ever allocates in groups of extents. For
example under load will it write 4 extents to file1 and then 4 extents
to file2 or does it always go extent by extent switching between files.
I am trying to figure out the best configuration for my new server. I
have a database that is about 3TB in size. We currently load about
300-500 million records a month into several tables and want to make
sure I set up the files and file groups correctly.
My new server has 2 PERC4 cards with 2 channels each. Each channel has
a RAID10 array that are each 1TB in size, this gives me four drives
with 1TB each F,G on one channel and H,I on another. I have other
drives for tempdb and tlog that are mirrors.
We are using partitioned tables for data with new tables created each
month.
The strategy I am leaning towards is as follows.
When new tables are created create 2 new file groups. For this month
they would be FebuaryData and FeburaryIndex, create one file in each
filegroup
F:\ FebuaryData
H:\ FeburaryIndex
Next month I would create
G:\MarchData
I:\MarchIndex
What I am trying to determine if there is any advantage to creating
multiple files within each file group, for example.
F:\ FebuaryData-File1
H:\ FeburaryIndex-File1
G:\ FebuaryData-File2
I:\ FebuaryData-File2
F:\ FebuaryData1
H:\ FeburaryIndex1
G:\ FebuaryData2
I:\ FeburaryIndex2
The current database has multiple files located on the same drives. I
have already determined that this does not give better performance.
Any input would be appreciated
Thanks
Bert|||I suspect that it is possible they can allocate more than one extent at a
time per file when it makes sense. Wei Xiao said something to the effect
that they do make adjustments in certain circumstances. Is this going to
be a DW or a large OLTP system? Will you be doing more seeks or range
scans? I wouldn't worry as much about getting the data in as working with
it afterwards. While you may find that one technique is slightly faster
than others for loading the data it is usually a small percentage of the
overall time spent in a system. So one thing you need to decide is whether
the load time or the read time is more important. Then figure out how the
data will be read. If you do a lot of queries that scan ranges of indexes
it will most like benefit from separating the data from the indexes onto
different arrays. If you do a lot of scans (partially or totally) it may be
better to create 4 files for the filegroup and place one on each of the Raid
10's. Of coarse there are no hard and fast rules that you can ccount on in
this situation. It really depends a lot on how you use the data and most of
the time you can only find the optimal solution by trial and error. That
usually takes more time and resources than people want to give but that is
the way it goes. How long do you plan on keeping the data? Is it a yearly
process in which you drop one month out and bring in the next one? Having a
filegroup for each month in that case may not be too bad of an idea. It
allows you to backup at the filegroup level and that may help if the older
months don't have update activity. Again without knowing exactly how you
will use the data it's a guess but why only create 1 file per file group?
If you have 4 drives why not 2 files per FG and place 1 file on each drive.
Two for the indexes and 2 for the data.
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1107403443.038720.258700@.c13g2000cwb.googlegroups.com...
>I was wondering if it does the opposite...not smaller than an extent
> but if the proportional fill ever allocates in groups of extents. For
> example under load will it write 4 extents to file1 and then 4 extents
> to file2 or does it always go extent by extent switching between files.
> I am trying to figure out the best configuration for my new server. I
> have a database that is about 3TB in size. We currently load about
> 300-500 million records a month into several tables and want to make
> sure I set up the files and file groups correctly.
> My new server has 2 PERC4 cards with 2 channels each. Each channel has
> a RAID10 array that are each 1TB in size, this gives me four drives
> with 1TB each F,G on one channel and H,I on another. I have other
> drives for tempdb and tlog that are mirrors.
> We are using partitioned tables for data with new tables created each
> month.
> The strategy I am leaning towards is as follows.
> When new tables are created create 2 new file groups. For this month
> they would be FebuaryData and FeburaryIndex, create one file in each
> filegroup
> F:\ FebuaryData
> H:\ FeburaryIndex
> Next month I would create
> G:\MarchData
> I:\MarchIndex
> What I am trying to determine if there is any advantage to creating
> multiple files within each file group, for example.
> F:\ FebuaryData-File1
> H:\ FeburaryIndex-File1
> G:\ FebuaryData-File2
> I:\ FebuaryData-File2
> F:\ FebuaryData1
> H:\ FeburaryIndex1
> G:\ FebuaryData2
> I:\ FeburaryIndex2
> The current database has multiple files located on the same drives. I
> have already determined that this does not give better performance.
> Any input would be appreciated
> Thanks
> Bert
>|||>>I suspect that it is possible they can allocate more than one extent
at a[vbcol=seagreen]
I guess the main thing I wanted to know is when they would fill at
differnet rates. IT makes it import to plan ahead as I dont want to
run in to a situation where I have the files spread out but they are
not being written to at the same rate. Would make it hard to plan for
space.
[vbcol=seagreen]
OLTP, the system will do real time processing of firewall and ids event
data. A set of queries will always be runnign processing the data and
looking for attackers.
[vbcol=seagreen]
yep that is what we are doing. This is actually one of 4 servers that
will be loading and processing the same data. We are going to set up
the other servers to act as our "backup" and they will use differnet
indexing stratagies. This is why I am spending so much time trying to
make sure I make the right file layout choice.
[vbcol=seagreen]
Some of our queries will span several months. With 4 drives I was
thinking I could have month1 on adrive Index 1 on another and then do
the same for month 2. After thinking about this some more and looking
at some of the query patterns I have determined that most queries
target data in the last month so this layout would not be ideal.
Ok last question for you ;) ... I have decided on using 2 FileGroups
for now. LargeTables and LargeTablesIndex do I ...
A. Each FileGroup gets one file on each array for a total of eight
files
Pro - Extra Files distribute teh IO across all disk
Con - Index IO could impact Table IO and vs versa
F:\ LargeTables - File1, LargeTablesIndex -File1
H:\ LargeTables - File2, LargeTablesIndex -File1
G:\ LargeTables - File3, LargeTablesIndex -File1
I:\ LargeTables - File4, LargeTablesIndex -File1
B. Each Filegroup has 2 dedicated arrays
Pro - EAch filegroup has dedicated drives
Con - Not fully utilizing resources
F:\ LargeTables - File1
H:\ LargeTables - File2
G:\ LargeTablesIndex -File1
I:\ LargeTablesIndex -File1
I think I am leaning towards A... I am also going to perform some more
tests to back up this theory. I am almost done writing a multi
threading utility that can be used to load test the DB once this is
done it will make my testing much easier
proportional fill using multiple files
As part of my ongoing testing of the performance impact of multiple
files vs. a single file. (Here is the link, comments would be
appreciated http://linktrim.com/yq) I have come across some
inconsistencies in how "proportional fill" works. According to books
online
"Filegroups use a proportional fill strategy across all the files
within each filegroup. As data is written to the filegroup, Microsoft=AE
SQL Server=99 2000 writes an amount proportional to the free space in
the file to each file within the filegroup, rather than writing all the
data to the first file until full, and then writing to the next file.
For example, if file f1 has 100 megabytes (MB) free and file f2 has 200
MB free, one extent is allocated from file f1, two extents from file
f2, and so on. This way both files become full at about the same time,
and simple striping is achieved."
Based on some of my testing this is not what is occurring. I created 3
files in one filegroup for my DB. The first number is the file size,
the second number is the used size after the data load.
1=2E Test one
File1 - 3000 - 520
File2 - 2000 - 520
File3 - 2000 - 520
2=2E Test two
File1 - 3000 - 293
File2 - 2000 - 293
File3 - 3000 - 293
3=2E Test three
File1 - 4000 - 416
File2 - 2000 - 232
File3 - 2000 - 232
As you can see in test one and two the used size is the same across
files even thought the 3 data files are not the same size. Not until I
make one file twice as large as the others did I sea difference in
used space.
Can anyone explain this behavior?
Bert
Were all the files completely empty? What size is that in? Is it MB's,
GBs, KBs etc?
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1107271506.112735.198080@.f14g2000cwb.googlegr oups.com...
As part of my ongoing testing of the performance impact of multiple
files vs. a single file. (Here is the link, comments would be
appreciated http://linktrim.com/yq) I have come across some
inconsistencies in how "proportional fill" works. According to books
online
"Filegroups use a proportional fill strategy across all the files
within each filegroup. As data is written to the filegroup, Microsoft
SQL ServerT 2000 writes an amount proportional to the free space in
the file to each file within the filegroup, rather than writing all the
data to the first file until full, and then writing to the next file.
For example, if file f1 has 100 megabytes (MB) free and file f2 has 200
MB free, one extent is allocated from file f1, two extents from file
f2, and so on. This way both files become full at about the same time,
and simple striping is achieved."
Based on some of my testing this is not what is occurring. I created 3
files in one filegroup for my DB. The first number is the file size,
the second number is the used size after the data load.
1. Test one
File1 - 3000 - 520
File2 - 2000 - 520
File3 - 2000 - 520
2. Test two
File1 - 3000 - 293
File2 - 2000 - 293
File3 - 3000 - 293
3. Test three
File1 - 4000 - 416
File2 - 2000 - 232
File3 - 2000 - 232
As you can see in test one and two the used size is the same across
files even thought the 3 data files are not the same size. Not until I
make one file twice as large as the others did I sea difference in
used space.
Can anyone explain this behavior?
Bert
|||Hey Andrew thanks for taking the time to respond. Yes the files were
completely empty, and the size is in MB. Here is the DDL for the
table.
CREATE DATABASE [testdb]
ON (NAME = N'testdb_Data',
FILENAME = N'e:\sqldata\mssql\data\testdb_Data.MDF' ,
SIZE = 3000, FILEGROWTH = 10%), (NAME = N'testdb_Data1',
FILENAME = N'e:\sqldata\mssql\data\testdb_Data1.NDF' ,
SIZE = 2000, FILEGROWTH = 10%), (NAME = N'testdb_Data2',
FILENAME = N'e:\sqldata\mssql\data\testdb_Data2.NDF' ,
SIZE = 3000, FILEGROWTH = 10%) LOG ON (NAME = N'testdb_Log',
FILENAME = N'g:\sqldata\mssql\data\testdb_Log.LDF' ,
SIZE = 500, FILEGROWTH = 10%)
I reran the tests a second time as I was wondering if checking the
batch size for my bulk insert would have an affect. I ran the test
twice once without setting the batch size and a second time of setting
the batch size of 10000. Both tests resulted in the data files being
filled equally, even though the second file is smaller than the others.
|||OK this is a guess but makes a lot of sense when you think about it. The
results you had are listed below. Since the files are filled at the extent
level it can't split an extent and put half in one file and half in another.
So in your tests until you get differences in free space that are exact
multiples of each other it will round down. So with a file size of 2000
(file2) and 3000 (file3) it would allocate one extent to file 2 and one to
file 3. When you get double the free space as in File1 and file 2 it can
then allocate in a 2 to 1 fashion. I believe the algorithm has to be very
simple to keep it efficient. That and the fact it allocates in extents lend
to the behavior you see.
1. Test one
File1 - 3000 - 520
File2 - 2000 - 520
File3 - 2000 - 520
2. Test two
File1 - 3000 - 293
File2 - 2000 - 293
File3 - 3000 - 293
3. Test three
File1 - 4000 - 416
File2 - 2000 - 232
File3 - 2000 - 232
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1107287428.961190.60820@.z14g2000cwz.googlegro ups.com...
> Hey Andrew thanks for taking the time to respond. Yes the files were
> completely empty, and the size is in MB. Here is the DDL for the
> table.
> CREATE DATABASE [testdb]
> ON (NAME = N'testdb_Data',
> FILENAME = N'e:\sqldata\mssql\data\testdb_Data.MDF' ,
> SIZE = 3000, FILEGROWTH = 10%), (NAME = N'testdb_Data1',
> FILENAME = N'e:\sqldata\mssql\data\testdb_Data1.NDF' ,
> SIZE = 2000, FILEGROWTH = 10%), (NAME = N'testdb_Data2',
> FILENAME = N'e:\sqldata\mssql\data\testdb_Data2.NDF' ,
> SIZE = 3000, FILEGROWTH = 10%) LOG ON (NAME = N'testdb_Log',
> FILENAME = N'g:\sqldata\mssql\data\testdb_Log.LDF' ,
> SIZE = 500, FILEGROWTH = 10%)
> I reran the tests a second time as I was wondering if checking the
> batch size for my bulk insert would have an affect. I ran the test
> twice once without setting the batch size and a second time of setting
> the batch size of 10000. Both tests resulted in the data files being
> filled equally, even though the second file is smaller than the others.
>
|||This is correct. In test 1, if you keep adding data, when the remaining free
space in file 1 versus file 2 become a ratio of 2:1, then we allocate 2
pages in file 1 for every page in file 2.
Please also note that when file becomes very large or when there are a lot
of concurrent transactions, we use some performance improvement technique
with a side effect that the free space calculation is not 100% accurate.
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.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eHQruWNCFHA.2072@.TK2MSFTNGP10.phx.gbl...
> OK this is a guess but makes a lot of sense when you think about it. The
> results you had are listed below. Since the files are filled at the
> extent level it can't split an extent and put half in one file and half in
> another. So in your tests until you get differences in free space that are
> exact multiples of each other it will round down. So with a file size of
> 2000 (file2) and 3000 (file3) it would allocate one extent to file 2 and
> one to file 3. When you get double the free space as in File1 and file 2
> it can then allocate in a 2 to 1 fashion. I believe the algorithm has to
> be very simple to keep it efficient. That and the fact it allocates in
> extents lend to the behavior you see.
> 1. Test one
> File1 - 3000 - 520
> File2 - 2000 - 520
> File3 - 2000 - 520
> 2. Test two
> File1 - 3000 - 293
> File2 - 2000 - 293
> File3 - 3000 - 293
> 3. Test three
> File1 - 4000 - 416
> File2 - 2000 - 232
> File3 - 2000 - 232
>
> --
> Andrew J. Kelly SQL MVP
>
> "bert" <bertcord@.gmail.com> wrote in message
> news:1107287428.961190.60820@.z14g2000cwz.googlegro ups.com...
>
|||Ok I see so basically it will write equal pages to the files until the
free space is at a 2:1 ration. I wasn't thinking about it on an extent
by extent basis. I was thinking that the end result could be
calculated as such.
(FileSize/TotalFileGroupSizeFreeSpace) * TotalDataToLoad
So in this example
File1 - 3000 - 293
File2 - 2000 - 293
File3 - 3000 - 293
Results should be
File1 - 3000 - 329.625
File2 - 2000 - 219.75
File3 - 3000 - 329.625
Wei maybe you can answer this... as data is allocated at what level is
it determined what file should be written to? IS it at the extent
level? When using multiple files are teh extents grouped into larger
chunks based on file?
bert
|||Bert,
There are no cases that I know of where you would break apart an extent.
Each extent always has 8 pages allocated to it (whether or not they all
include data). An extent may have pages from multiple objects (Mixed
Extent) but this is rare and only used when the object has less than 8
pages. You can read and write at the page level but the page always lives
on an extent. So if you are writing to a file and the current extent is full
it will create a new extent in the proper file and begin writing to it. It
will not write half an extent here and half there so to speak.
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1107362088.012050.183690@.l41g2000cwc.googlegr oups.com...
> Ok I see so basically it will write equal pages to the files until the
> free space is at a 2:1 ration. I wasn't thinking about it on an extent
> by extent basis. I was thinking that the end result could be
> calculated as such.
> (FileSize/TotalFileGroupSizeFreeSpace) * TotalDataToLoad
> So in this example
> File1 - 3000 - 293
> File2 - 2000 - 293
> File3 - 3000 - 293
> Results should be
> File1 - 3000 - 329.625
> File2 - 2000 - 219.75
> File3 - 3000 - 329.625
> Wei maybe you can answer this... as data is allocated at what level is
> it determined what file should be written to? IS it at the extent
> level? When using multiple files are teh extents grouped into larger
> chunks based on file?
> bert
>
|||I was wondering if it does the opposite...not smaller than an extent
but if the proportional fill ever allocates in groups of extents. For
example under load will it write 4 extents to file1 and then 4 extents
to file2 or does it always go extent by extent switching between files.
I am trying to figure out the best configuration for my new server. I
have a database that is about 3TB in size. We currently load about
300-500 million records a month into several tables and want to make
sure I set up the files and file groups correctly.
My new server has 2 PERC4 cards with 2 channels each. Each channel has
a RAID10 array that are each 1TB in size, this gives me four drives
with 1TB each F,G on one channel and H,I on another. I have other
drives for tempdb and tlog that are mirrors.
We are using partitioned tables for data with new tables created each
month.
The strategy I am leaning towards is as follows.
When new tables are created create 2 new file groups. For this month
they would be FebuaryData and FeburaryIndex, create one file in each
filegroup
F:\ FebuaryData
H:\ FeburaryIndex
Next month I would create
G:\MarchData
I:\MarchIndex
What I am trying to determine if there is any advantage to creating
multiple files within each file group, for example.
F:\ FebuaryData-File1
H:\ FeburaryIndex-File1
G:\ FebuaryData-File2
I:\ FebuaryData-File2
F:\ FebuaryData1
H:\ FeburaryIndex1
G:\ FebuaryData2
I:\ FeburaryIndex2
The current database has multiple files located on the same drives. I
have already determined that this does not give better performance.
Any input would be appreciated
Thanks
Bert
|||I suspect that it is possible they can allocate more than one extent at a
time per file when it makes sense. Wei Xiao said something to the effect
that they do make adjustments in certain circumstances. Is this going to
be a DW or a large OLTP system? Will you be doing more seeks or range
scans? I wouldn't worry as much about getting the data in as working with
it afterwards. While you may find that one technique is slightly faster
than others for loading the data it is usually a small percentage of the
overall time spent in a system. So one thing you need to decide is whether
the load time or the read time is more important. Then figure out how the
data will be read. If you do a lot of queries that scan ranges of indexes
it will most like benefit from separating the data from the indexes onto
different arrays. If you do a lot of scans (partially or totally) it may be
better to create 4 files for the filegroup and place one on each of the Raid
10's. Of coarse there are no hard and fast rules that you can ccount on in
this situation. It really depends a lot on how you use the data and most of
the time you can only find the optimal solution by trial and error. That
usually takes more time and resources than people want to give but that is
the way it goes. How long do you plan on keeping the data? Is it a yearly
process in which you drop one month out and bring in the next one? Having a
filegroup for each month in that case may not be too bad of an idea. It
allows you to backup at the filegroup level and that may help if the older
months don't have update activity. Again without knowing exactly how you
will use the data it's a guess but why only create 1 file per file group?
If you have 4 drives why not 2 files per FG and place 1 file on each drive.
Two for the indexes and 2 for the data.
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1107403443.038720.258700@.c13g2000cwb.googlegr oups.com...
>I was wondering if it does the opposite...not smaller than an extent
> but if the proportional fill ever allocates in groups of extents. For
> example under load will it write 4 extents to file1 and then 4 extents
> to file2 or does it always go extent by extent switching between files.
> I am trying to figure out the best configuration for my new server. I
> have a database that is about 3TB in size. We currently load about
> 300-500 million records a month into several tables and want to make
> sure I set up the files and file groups correctly.
> My new server has 2 PERC4 cards with 2 channels each. Each channel has
> a RAID10 array that are each 1TB in size, this gives me four drives
> with 1TB each F,G on one channel and H,I on another. I have other
> drives for tempdb and tlog that are mirrors.
> We are using partitioned tables for data with new tables created each
> month.
> The strategy I am leaning towards is as follows.
> When new tables are created create 2 new file groups. For this month
> they would be FebuaryData and FeburaryIndex, create one file in each
> filegroup
> F:\ FebuaryData
> H:\ FeburaryIndex
> Next month I would create
> G:\MarchData
> I:\MarchIndex
> What I am trying to determine if there is any advantage to creating
> multiple files within each file group, for example.
> F:\ FebuaryData-File1
> H:\ FeburaryIndex-File1
> G:\ FebuaryData-File2
> I:\ FebuaryData-File2
> F:\ FebuaryData1
> H:\ FeburaryIndex1
> G:\ FebuaryData2
> I:\ FeburaryIndex2
> The current database has multiple files located on the same drives. I
> have already determined that this does not give better performance.
> Any input would be appreciated
> Thanks
> Bert
>
|||>>I suspect that it is possible they can allocate more than one extent
at a[vbcol=seagreen]
I guess the main thing I wanted to know is when they would fill at
differnet rates. IT makes it import to plan ahead as I dont want to
run in to a situation where I have the files spread out but they are
not being written to at the same rate. Would make it hard to plan for
space.
[vbcol=seagreen]
OLTP, the system will do real time processing of firewall and ids event
data. A set of queries will always be runnign processing the data and
looking for attackers.
[vbcol=seagreen]
yep that is what we are doing. This is actually one of 4 servers that
will be loading and processing the same data. We are going to set up
the other servers to act as our "backup" and they will use differnet
indexing stratagies. This is why I am spending so much time trying to
make sure I make the right file layout choice.
[vbcol=seagreen]
Some of our queries will span several months. With 4 drives I was
thinking I could have month1 on adrive Index 1 on another and then do
the same for month 2. After thinking about this some more and looking
at some of the query patterns I have determined that most queries
target data in the last month so this layout would not be ideal.
Ok last question for you ;) ... I have decided on using 2 FileGroups
for now. LargeTables and LargeTablesIndex do I ...
A. Each FileGroup gets one file on each array for a total of eight
files
Pro - Extra Files distribute teh IO across all disk
Con - Index IO could impact Table IO and vs versa
F:\ LargeTables - File1, LargeTablesIndex -File1
H:\ LargeTables - File2, LargeTablesIndex -File1
G:\ LargeTables - File3, LargeTablesIndex -File1
I:\ LargeTables - File4, LargeTablesIndex -File1
B. Each Filegroup has 2 dedicated arrays
Pro - EAch filegroup has dedicated drives
Con - Not fully utilizing resources
F:\ LargeTables - File1
H:\ LargeTables - File2
G:\ LargeTablesIndex -File1
I:\ LargeTablesIndex -File1
I think I am leaning towards A... I am also going to perform some more
tests to back up this theory. I am almost done writing a multi
threading utility that can be used to load test the DB once this is
done it will make my testing much easier
files vs. a single file. (Here is the link, comments would be
appreciated http://linktrim.com/yq) I have come across some
inconsistencies in how "proportional fill" works. According to books
online
"Filegroups use a proportional fill strategy across all the files
within each filegroup. As data is written to the filegroup, Microsoft=AE
SQL Server=99 2000 writes an amount proportional to the free space in
the file to each file within the filegroup, rather than writing all the
data to the first file until full, and then writing to the next file.
For example, if file f1 has 100 megabytes (MB) free and file f2 has 200
MB free, one extent is allocated from file f1, two extents from file
f2, and so on. This way both files become full at about the same time,
and simple striping is achieved."
Based on some of my testing this is not what is occurring. I created 3
files in one filegroup for my DB. The first number is the file size,
the second number is the used size after the data load.
1=2E Test one
File1 - 3000 - 520
File2 - 2000 - 520
File3 - 2000 - 520
2=2E Test two
File1 - 3000 - 293
File2 - 2000 - 293
File3 - 3000 - 293
3=2E Test three
File1 - 4000 - 416
File2 - 2000 - 232
File3 - 2000 - 232
As you can see in test one and two the used size is the same across
files even thought the 3 data files are not the same size. Not until I
make one file twice as large as the others did I sea difference in
used space.
Can anyone explain this behavior?
Bert
Were all the files completely empty? What size is that in? Is it MB's,
GBs, KBs etc?
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1107271506.112735.198080@.f14g2000cwb.googlegr oups.com...
As part of my ongoing testing of the performance impact of multiple
files vs. a single file. (Here is the link, comments would be
appreciated http://linktrim.com/yq) I have come across some
inconsistencies in how "proportional fill" works. According to books
online
"Filegroups use a proportional fill strategy across all the files
within each filegroup. As data is written to the filegroup, Microsoft
SQL ServerT 2000 writes an amount proportional to the free space in
the file to each file within the filegroup, rather than writing all the
data to the first file until full, and then writing to the next file.
For example, if file f1 has 100 megabytes (MB) free and file f2 has 200
MB free, one extent is allocated from file f1, two extents from file
f2, and so on. This way both files become full at about the same time,
and simple striping is achieved."
Based on some of my testing this is not what is occurring. I created 3
files in one filegroup for my DB. The first number is the file size,
the second number is the used size after the data load.
1. Test one
File1 - 3000 - 520
File2 - 2000 - 520
File3 - 2000 - 520
2. Test two
File1 - 3000 - 293
File2 - 2000 - 293
File3 - 3000 - 293
3. Test three
File1 - 4000 - 416
File2 - 2000 - 232
File3 - 2000 - 232
As you can see in test one and two the used size is the same across
files even thought the 3 data files are not the same size. Not until I
make one file twice as large as the others did I sea difference in
used space.
Can anyone explain this behavior?
Bert
|||Hey Andrew thanks for taking the time to respond. Yes the files were
completely empty, and the size is in MB. Here is the DDL for the
table.
CREATE DATABASE [testdb]
ON (NAME = N'testdb_Data',
FILENAME = N'e:\sqldata\mssql\data\testdb_Data.MDF' ,
SIZE = 3000, FILEGROWTH = 10%), (NAME = N'testdb_Data1',
FILENAME = N'e:\sqldata\mssql\data\testdb_Data1.NDF' ,
SIZE = 2000, FILEGROWTH = 10%), (NAME = N'testdb_Data2',
FILENAME = N'e:\sqldata\mssql\data\testdb_Data2.NDF' ,
SIZE = 3000, FILEGROWTH = 10%) LOG ON (NAME = N'testdb_Log',
FILENAME = N'g:\sqldata\mssql\data\testdb_Log.LDF' ,
SIZE = 500, FILEGROWTH = 10%)
I reran the tests a second time as I was wondering if checking the
batch size for my bulk insert would have an affect. I ran the test
twice once without setting the batch size and a second time of setting
the batch size of 10000. Both tests resulted in the data files being
filled equally, even though the second file is smaller than the others.
|||OK this is a guess but makes a lot of sense when you think about it. The
results you had are listed below. Since the files are filled at the extent
level it can't split an extent and put half in one file and half in another.
So in your tests until you get differences in free space that are exact
multiples of each other it will round down. So with a file size of 2000
(file2) and 3000 (file3) it would allocate one extent to file 2 and one to
file 3. When you get double the free space as in File1 and file 2 it can
then allocate in a 2 to 1 fashion. I believe the algorithm has to be very
simple to keep it efficient. That and the fact it allocates in extents lend
to the behavior you see.
1. Test one
File1 - 3000 - 520
File2 - 2000 - 520
File3 - 2000 - 520
2. Test two
File1 - 3000 - 293
File2 - 2000 - 293
File3 - 3000 - 293
3. Test three
File1 - 4000 - 416
File2 - 2000 - 232
File3 - 2000 - 232
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1107287428.961190.60820@.z14g2000cwz.googlegro ups.com...
> Hey Andrew thanks for taking the time to respond. Yes the files were
> completely empty, and the size is in MB. Here is the DDL for the
> table.
> CREATE DATABASE [testdb]
> ON (NAME = N'testdb_Data',
> FILENAME = N'e:\sqldata\mssql\data\testdb_Data.MDF' ,
> SIZE = 3000, FILEGROWTH = 10%), (NAME = N'testdb_Data1',
> FILENAME = N'e:\sqldata\mssql\data\testdb_Data1.NDF' ,
> SIZE = 2000, FILEGROWTH = 10%), (NAME = N'testdb_Data2',
> FILENAME = N'e:\sqldata\mssql\data\testdb_Data2.NDF' ,
> SIZE = 3000, FILEGROWTH = 10%) LOG ON (NAME = N'testdb_Log',
> FILENAME = N'g:\sqldata\mssql\data\testdb_Log.LDF' ,
> SIZE = 500, FILEGROWTH = 10%)
> I reran the tests a second time as I was wondering if checking the
> batch size for my bulk insert would have an affect. I ran the test
> twice once without setting the batch size and a second time of setting
> the batch size of 10000. Both tests resulted in the data files being
> filled equally, even though the second file is smaller than the others.
>
|||This is correct. In test 1, if you keep adding data, when the remaining free
space in file 1 versus file 2 become a ratio of 2:1, then we allocate 2
pages in file 1 for every page in file 2.
Please also note that when file becomes very large or when there are a lot
of concurrent transactions, we use some performance improvement technique
with a side effect that the free space calculation is not 100% accurate.
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.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eHQruWNCFHA.2072@.TK2MSFTNGP10.phx.gbl...
> OK this is a guess but makes a lot of sense when you think about it. The
> results you had are listed below. Since the files are filled at the
> extent level it can't split an extent and put half in one file and half in
> another. So in your tests until you get differences in free space that are
> exact multiples of each other it will round down. So with a file size of
> 2000 (file2) and 3000 (file3) it would allocate one extent to file 2 and
> one to file 3. When you get double the free space as in File1 and file 2
> it can then allocate in a 2 to 1 fashion. I believe the algorithm has to
> be very simple to keep it efficient. That and the fact it allocates in
> extents lend to the behavior you see.
> 1. Test one
> File1 - 3000 - 520
> File2 - 2000 - 520
> File3 - 2000 - 520
> 2. Test two
> File1 - 3000 - 293
> File2 - 2000 - 293
> File3 - 3000 - 293
> 3. Test three
> File1 - 4000 - 416
> File2 - 2000 - 232
> File3 - 2000 - 232
>
> --
> Andrew J. Kelly SQL MVP
>
> "bert" <bertcord@.gmail.com> wrote in message
> news:1107287428.961190.60820@.z14g2000cwz.googlegro ups.com...
>
|||Ok I see so basically it will write equal pages to the files until the
free space is at a 2:1 ration. I wasn't thinking about it on an extent
by extent basis. I was thinking that the end result could be
calculated as such.
(FileSize/TotalFileGroupSizeFreeSpace) * TotalDataToLoad
So in this example
File1 - 3000 - 293
File2 - 2000 - 293
File3 - 3000 - 293
Results should be
File1 - 3000 - 329.625
File2 - 2000 - 219.75
File3 - 3000 - 329.625
Wei maybe you can answer this... as data is allocated at what level is
it determined what file should be written to? IS it at the extent
level? When using multiple files are teh extents grouped into larger
chunks based on file?
bert
|||Bert,
There are no cases that I know of where you would break apart an extent.
Each extent always has 8 pages allocated to it (whether or not they all
include data). An extent may have pages from multiple objects (Mixed
Extent) but this is rare and only used when the object has less than 8
pages. You can read and write at the page level but the page always lives
on an extent. So if you are writing to a file and the current extent is full
it will create a new extent in the proper file and begin writing to it. It
will not write half an extent here and half there so to speak.
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1107362088.012050.183690@.l41g2000cwc.googlegr oups.com...
> Ok I see so basically it will write equal pages to the files until the
> free space is at a 2:1 ration. I wasn't thinking about it on an extent
> by extent basis. I was thinking that the end result could be
> calculated as such.
> (FileSize/TotalFileGroupSizeFreeSpace) * TotalDataToLoad
> So in this example
> File1 - 3000 - 293
> File2 - 2000 - 293
> File3 - 3000 - 293
> Results should be
> File1 - 3000 - 329.625
> File2 - 2000 - 219.75
> File3 - 3000 - 329.625
> Wei maybe you can answer this... as data is allocated at what level is
> it determined what file should be written to? IS it at the extent
> level? When using multiple files are teh extents grouped into larger
> chunks based on file?
> bert
>
|||I was wondering if it does the opposite...not smaller than an extent
but if the proportional fill ever allocates in groups of extents. For
example under load will it write 4 extents to file1 and then 4 extents
to file2 or does it always go extent by extent switching between files.
I am trying to figure out the best configuration for my new server. I
have a database that is about 3TB in size. We currently load about
300-500 million records a month into several tables and want to make
sure I set up the files and file groups correctly.
My new server has 2 PERC4 cards with 2 channels each. Each channel has
a RAID10 array that are each 1TB in size, this gives me four drives
with 1TB each F,G on one channel and H,I on another. I have other
drives for tempdb and tlog that are mirrors.
We are using partitioned tables for data with new tables created each
month.
The strategy I am leaning towards is as follows.
When new tables are created create 2 new file groups. For this month
they would be FebuaryData and FeburaryIndex, create one file in each
filegroup
F:\ FebuaryData
H:\ FeburaryIndex
Next month I would create
G:\MarchData
I:\MarchIndex
What I am trying to determine if there is any advantage to creating
multiple files within each file group, for example.
F:\ FebuaryData-File1
H:\ FeburaryIndex-File1
G:\ FebuaryData-File2
I:\ FebuaryData-File2
F:\ FebuaryData1
H:\ FeburaryIndex1
G:\ FebuaryData2
I:\ FeburaryIndex2
The current database has multiple files located on the same drives. I
have already determined that this does not give better performance.
Any input would be appreciated
Thanks
Bert
|||I suspect that it is possible they can allocate more than one extent at a
time per file when it makes sense. Wei Xiao said something to the effect
that they do make adjustments in certain circumstances. Is this going to
be a DW or a large OLTP system? Will you be doing more seeks or range
scans? I wouldn't worry as much about getting the data in as working with
it afterwards. While you may find that one technique is slightly faster
than others for loading the data it is usually a small percentage of the
overall time spent in a system. So one thing you need to decide is whether
the load time or the read time is more important. Then figure out how the
data will be read. If you do a lot of queries that scan ranges of indexes
it will most like benefit from separating the data from the indexes onto
different arrays. If you do a lot of scans (partially or totally) it may be
better to create 4 files for the filegroup and place one on each of the Raid
10's. Of coarse there are no hard and fast rules that you can ccount on in
this situation. It really depends a lot on how you use the data and most of
the time you can only find the optimal solution by trial and error. That
usually takes more time and resources than people want to give but that is
the way it goes. How long do you plan on keeping the data? Is it a yearly
process in which you drop one month out and bring in the next one? Having a
filegroup for each month in that case may not be too bad of an idea. It
allows you to backup at the filegroup level and that may help if the older
months don't have update activity. Again without knowing exactly how you
will use the data it's a guess but why only create 1 file per file group?
If you have 4 drives why not 2 files per FG and place 1 file on each drive.
Two for the indexes and 2 for the data.
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1107403443.038720.258700@.c13g2000cwb.googlegr oups.com...
>I was wondering if it does the opposite...not smaller than an extent
> but if the proportional fill ever allocates in groups of extents. For
> example under load will it write 4 extents to file1 and then 4 extents
> to file2 or does it always go extent by extent switching between files.
> I am trying to figure out the best configuration for my new server. I
> have a database that is about 3TB in size. We currently load about
> 300-500 million records a month into several tables and want to make
> sure I set up the files and file groups correctly.
> My new server has 2 PERC4 cards with 2 channels each. Each channel has
> a RAID10 array that are each 1TB in size, this gives me four drives
> with 1TB each F,G on one channel and H,I on another. I have other
> drives for tempdb and tlog that are mirrors.
> We are using partitioned tables for data with new tables created each
> month.
> The strategy I am leaning towards is as follows.
> When new tables are created create 2 new file groups. For this month
> they would be FebuaryData and FeburaryIndex, create one file in each
> filegroup
> F:\ FebuaryData
> H:\ FeburaryIndex
> Next month I would create
> G:\MarchData
> I:\MarchIndex
> What I am trying to determine if there is any advantage to creating
> multiple files within each file group, for example.
> F:\ FebuaryData-File1
> H:\ FeburaryIndex-File1
> G:\ FebuaryData-File2
> I:\ FebuaryData-File2
> F:\ FebuaryData1
> H:\ FeburaryIndex1
> G:\ FebuaryData2
> I:\ FeburaryIndex2
> The current database has multiple files located on the same drives. I
> have already determined that this does not give better performance.
> Any input would be appreciated
> Thanks
> Bert
>
|||>>I suspect that it is possible they can allocate more than one extent
at a[vbcol=seagreen]
I guess the main thing I wanted to know is when they would fill at
differnet rates. IT makes it import to plan ahead as I dont want to
run in to a situation where I have the files spread out but they are
not being written to at the same rate. Would make it hard to plan for
space.
[vbcol=seagreen]
OLTP, the system will do real time processing of firewall and ids event
data. A set of queries will always be runnign processing the data and
looking for attackers.
[vbcol=seagreen]
yep that is what we are doing. This is actually one of 4 servers that
will be loading and processing the same data. We are going to set up
the other servers to act as our "backup" and they will use differnet
indexing stratagies. This is why I am spending so much time trying to
make sure I make the right file layout choice.
[vbcol=seagreen]
Some of our queries will span several months. With 4 drives I was
thinking I could have month1 on adrive Index 1 on another and then do
the same for month 2. After thinking about this some more and looking
at some of the query patterns I have determined that most queries
target data in the last month so this layout would not be ideal.
Ok last question for you ;) ... I have decided on using 2 FileGroups
for now. LargeTables and LargeTablesIndex do I ...
A. Each FileGroup gets one file on each array for a total of eight
files
Pro - Extra Files distribute teh IO across all disk
Con - Index IO could impact Table IO and vs versa
F:\ LargeTables - File1, LargeTablesIndex -File1
H:\ LargeTables - File2, LargeTablesIndex -File1
G:\ LargeTables - File3, LargeTablesIndex -File1
I:\ LargeTables - File4, LargeTablesIndex -File1
B. Each Filegroup has 2 dedicated arrays
Pro - EAch filegroup has dedicated drives
Con - Not fully utilizing resources
F:\ LargeTables - File1
H:\ LargeTables - File2
G:\ LargeTablesIndex -File1
I:\ LargeTablesIndex -File1
I think I am leaning towards A... I am also going to perform some more
tests to back up this theory. I am almost done writing a multi
threading utility that can be used to load test the DB once this is
done it will make my testing much easier
Wednesday, March 28, 2012
Property Promotion with multi-level XML data type
How would you extract data from an XML datatype column when it has multiple levels? I've done this with single levels using CROSS APPLY and the nodes method, but can't seem to grab data from the intermediate levels.
Example:
<Level1>
<Level2>xyz</Level2>
<Level2>xyz</Level2>
<Level3>abc</Level3>
<Level3>abc</Level3>
<Level3>abc</Level3>
<Level2>xyz</Level2>
<Level2>xyz</Level2>
</Level1>
For the root level, I use the xml.value() function, and for the details I use the cross apply xml.nodes() function typically, but if I use the xpath in the xml.nodes, I have to use the path to the lowest level (level3) to grab those values, but can't seem to grab the changing values at the intermediate levels (level2).
Would this involve multiple cross-apply instances?
I don't understand your example, why are those Level3 elements indented further to the right than the Level2 elements? They are both children of the root element Level1.
And it is not obvious what you want to extract.
|||Sorry if this wasn't clear. The XML is supposed to represent a multiple-level heirarchy and the indentation indicates a parent-child relationship.
So level 1 can be Order, for example. At this level there can be several elements that define the Order (ID, Cust #, etc). Level 2 would be Order Type, like Internal, External, Global, Domestic (each order always has these 4 types associated with it). Level 3 would be details relevant to that order detail item, like part #, quantity, etc. (each Order has 4 Order Types, of which have multiple line-items within each order type).
So I need to return a table of data based on this single order. The results returned would have these fields:
Order ID (repeated for every order detail)
Cust # (repeated for every order detail)
OrderType (repeated for every order type/order detail)
Part # (unique per order detail/type)
Qty (unique per order detail/type)
Analogous to joining three relational tables (Order details -> Order Type -> Order) and getting the combined results of all three.
In SQL xquery, I use the .value function to get the Order ID and Cust# because they are at the root level of the XML field. I use the CROSS APPLY .nodes method to get to the lowest level of detail (Part# and Qty) and this produces the correct data. But for the intermediate level (Order Type) I can't seem to get to it.
If it still isn't clear, I'll send some actual XML.
Thanks
Kory
|||Consider posting the XML and the query you have, then we can work from there to improve it.
|||A coworker of mine helped me to figure this out:
I was doing this:
Before
With Namespaces(....)
select
t.EventDateTime
,t.OrderId
,ref.value('(//gns:OrderType/text())[1]','varchar(max)') OrderType
,ref.value('(ns:Product/text())[1]','varchar(max)') Product
,ref.value('(ns:Qty/text())[1]','varchar(max)') Qty
FROM
Orders t
CROSS APPLY
xmlfld.nodes ('//ns:OrderDetailItem') AS R(ref)
And we changed it to this:
After
With Namespaces(....)
select
t.EventDateTime
,t.OrderId
,ref.value('(http://gns:OrderType/text())[1]','varchar(max)') OrderType
,ref.value('(//gns:OrderType/text())[1]','varchar(max)') OrderType
,ref.value('(ns:Product/text())[1]','varchar(max)') Product
,ref.value('(ns:Qty/text())[1]','varchar(max)') Qty
FROM
Orders t
CROSS APPLY
xmlfld.nodes ('//ns:OrderDetailItem') AS R(ref)
So the only change was to the xpath from //gns
I really don't understand why this worked, but the first way just promoted the first occurance of the value, but the second method promoted the value when it changed.
-Kory
Property IsPrimaryFile is not available for DataFile
Hi all,
I posted this following difficulty in
microsoft.public.sqlserver.programming forum.But could not get a
single answer.So I posted this here also.
I have restored a database in SQL SERVER 2005.After restoring the
database, I can make query on that particular database.But problem is
when I right click on the database name(to open the properties etc.),
I am getting the following error ....
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
--
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
--
Property IsPrimaryFile is not available for DataFile
'[FINSIEBPRD01_Data]'. This property may not exist for this object,
or
may not be retrievable due to insufficient access rights.
(Microsoft.SqlServer.Smo)
As per the suggestion of Andrew J. Kelly SQL MVP, I have changed the
compatibility level to 90, but still I am getting the problem.
Please help.
Regards,
MukutThis may sound silly, but double check the logical file names in sql 2005
after the restore. I just had this exact same problem, and after lots of
head-scratching noticed that SOMEHOW the logical filenames were different
after the restore to sql 2005. In my case I had 'jobsafe_dataa' and
'jobsafe_logg' instead of 'jobsafe_data' and 'jobsafe_log'. I simply used
the following to rename the logical files back to what they were, and it
works fine thereafter.
ALTER DATABASE pubs
MODIFY FILE (NAME = pubs, NEWNAME = pubs_data)
Let me know if that helps
Michael
"Mukut" wrote:
> Hi all,
> I posted this following difficulty in
> microsoft.public.sqlserver.programming forum.But could not get a
> single answer.So I posted this here also.
> I have restored a database in SQL SERVER 2005.After restoring the
> database, I can make query on that particular database.But problem is
> when I right click on the database name(to open the properties etc.),
> I am getting the following error ....
> TITLE: Microsoft SQL Server Management Studio
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (SqlMgmt)
> --
> Property IsPrimaryFile is not available for DataFile
> '[FINSIEBPRD01_Data]'. This property may not exist for this object,
> or
> may not be retrievable due to insufficient access rights.
> (Microsoft.SqlServer.Smo)
> As per the suggestion of Andrew J. Kelly SQL MVP, I have changed the
> compatibility level to 90, but still I am getting the problem.
> Please help.
>
> Regards,
> Mukut
>sql
I posted this following difficulty in
microsoft.public.sqlserver.programming forum.But could not get a
single answer.So I posted this here also.
I have restored a database in SQL SERVER 2005.After restoring the
database, I can make query on that particular database.But problem is
when I right click on the database name(to open the properties etc.),
I am getting the following error ....
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
--
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
--
Property IsPrimaryFile is not available for DataFile
'[FINSIEBPRD01_Data]'. This property may not exist for this object,
or
may not be retrievable due to insufficient access rights.
(Microsoft.SqlServer.Smo)
As per the suggestion of Andrew J. Kelly SQL MVP, I have changed the
compatibility level to 90, but still I am getting the problem.
Please help.
Regards,
MukutThis may sound silly, but double check the logical file names in sql 2005
after the restore. I just had this exact same problem, and after lots of
head-scratching noticed that SOMEHOW the logical filenames were different
after the restore to sql 2005. In my case I had 'jobsafe_dataa' and
'jobsafe_logg' instead of 'jobsafe_data' and 'jobsafe_log'. I simply used
the following to rename the logical files back to what they were, and it
works fine thereafter.
ALTER DATABASE pubs
MODIFY FILE (NAME = pubs, NEWNAME = pubs_data)
Let me know if that helps
Michael
"Mukut" wrote:
> Hi all,
> I posted this following difficulty in
> microsoft.public.sqlserver.programming forum.But could not get a
> single answer.So I posted this here also.
> I have restored a database in SQL SERVER 2005.After restoring the
> database, I can make query on that particular database.But problem is
> when I right click on the database name(to open the properties etc.),
> I am getting the following error ....
> TITLE: Microsoft SQL Server Management Studio
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (SqlMgmt)
> --
> Property IsPrimaryFile is not available for DataFile
> '[FINSIEBPRD01_Data]'. This property may not exist for this object,
> or
> may not be retrievable due to insufficient access rights.
> (Microsoft.SqlServer.Smo)
> As per the suggestion of Andrew J. Kelly SQL MVP, I have changed the
> compatibility level to 90, but still I am getting the problem.
> Please help.
>
> Regards,
> Mukut
>sql
Subscribe to:
Posts (Atom)