April 12, 2011 at 11:48 am
today i noticed one of the databases I'm using has multiple files in the same filegroup, all in the same spindle.
is there any benefit in this?
SQL Server does some sort of Round Robin distributing data?
If so, is it per table, per row, per insert statement or what?
Can i know exactly on which file is a table, or a record for that matter without DBCC PAGE?
Is there a simple way to migrate all data into a single file?
If there are already blog posts covering this, please direct me to then, I couldn't find any.
--
Thiago Dantas
@DantHimself
April 12, 2011 at 12:24 pm
You're asking for more control than is possible through SQL Server. DBCC is how you determine where data other than tables are stored. Tables storage is defined when you create the table, or it goes to the default. You can move them as needed between files and file groups. Having multiple files in a filegroup on a single drive can offer a very small performance improvement based on some old tests I ran, but we're talking extremely small. In general to realize substantive performance gains you need to set up multiple drives.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 12, 2011 at 12:25 pm
Hi dant,
today i noticed one of the databases I'm using has multiple files in the same filegroup, all in the same spindle.
is there any benefit in this?
There is no performance benefit in relation to i/o performance. But because of using separate threads for each datafile it can use the cpu's in a better way. You have only a aministering benefit if this database is too big for a disk you can locate all datafiles on separate disks. So you don't need to have one big disk and if you need a performance booster you can also stripe the data files on separate disks.
SQL Server does some sort of Round Robin distributing data?
Yes, it is using Round Robin on row level to distribute data.
Can i know exactly on which file is a table, or a record for that matter without DBCC PAGE?
I think you can find out which page is on which datafile a system view, but I don't have a SQL Server Management Studio available now.
I hope it helps. 🙂
Greets
Patrick Fiedler
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
www.fiedler-sql-consulting.de
[/font]
April 12, 2011 at 12:34 pm
If you have multiple files on the same filegroup - your table will be spread across those files. You can see this happening when you rebuild a Clustered index or when you do a mass load on the table. The round-robin nature will spread the data out.
As for performance - it is quite possible to see an IO improvement even when the data files are all on the same spindle. Best recommendation though is to spread it out across spindles. Too many files though has little benefit. Though the benefit may be minuscule when all files are on the same disk, it is possible. To really find out for your environment, you should test several configs.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 12, 2011 at 12:41 pm
Thanks everyone, I think I got the point.
Now the only thing I'm still curious is to find the actual row location, and a way to migrate all of into into a single file.
--
Thiago Dantas
@DantHimself
April 12, 2011 at 12:46 pm
you can migrate all of into a single file by creating a new filegroup with a single file in it. Rebuild your clustered index on the table and redirect the rebuild to the new filegroup.
Alternatively, you could also do a shrinkfile and use the emptyfile option. That will empty that particular file and move the data to other files in the same filegroup.
Both options should be done during an approved maintenance window.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 12, 2011 at 12:47 pm
ok, i got the first part, finding where it is
ex:
select B.* from table cross apply sys.fn_PhysLocCracker(%%physloc%%) B
edit:
Thanks, I am working with a copy of the production database to generate some pretty heavy adhoc reports and since I'm the single user there's no problem doing this right now. The problem is, the machine I'm using seems a century old, so when it must have seemed a good idea to have 16 data files on production, for a dual core with 3 physical disks surely doesn't.
Shrinking seems the way to go...ugh
--
Thiago Dantas
@DantHimself
April 12, 2011 at 12:49 pm
dant12 (4/12/2011)
ok, i got the first part, finding where it isex:
select B.* from table cross apply sys.fn_PhysLocCracker(%%physloc%%) B
Excellent nugget.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply