| Author |
Topic |
|
allnelsons
Starting Member
5 Posts |
 Posted - 2011-05-12 : 15:59:25
|
| You know how when you have multiple DB files, SQL will round-robin insert data into those files so it's spread evenly? Well, we've got 3 disk volumes with 3 disk files that have been around since the install of this server and they're all pretty much evenly loaded. However, we're adding an additional disk volume to our SQL box to add additional I/O capacity and I plan to add an additional DB file to that volume. If I just leave it as is, I'll have the 3 full DB files and this 1 empty DB file. I'd like to reorganize the data in the files so that I have 4 DB files equally full, as if the 4 files were round-robin inserted from the beginning instead of having 3 full and 1 empty. So, what's the best way to reorganize the data so it's evenly distributed among the 4 DB files given that 3 are already full? I'd like to avoid "hotspots" in the files where 3 files are busy as heck servicing requests and one is unused.I can do it this way:http://myitforum.com/cs2/blogs/jnelson/archive/2009/07/27/140199.aspx(I wrote that article)but I'm wondering if there's a better way...it takes SOOO long to do it that way. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-05-12 : 16:30:54
|
| I might be missing something but I think rebuilding the clustered index may accomplish the same thing. I know it can be used to move data to different filegroups. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-05-12 : 17:23:29
|
| Rebuilding the indexes will redistribute the data, but it may not distribute the data exactly evenly between the files.At least that's what happened when I did this last week. One file now has 44 GB of data and the other has 15 GB of data. All the tables have clustered indexes and are in the primary filegroup, but the non-clustered indexes are located in a different index filegroup. The data is almost exactly evenly distributed in the index filegroupI ran the rebuild several times and got the same results each time. I suspect that the rebuild may put a table in a single file if the file has enough room.I did the rebuilds with ONLINE = ON, SORT_IN_TEMPDB = ON (2008 Enterprise Edition). I used a maintenance plan for the rebuild, and it looks like it just does the tables in alphabetical order.CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
allnelsons
Starting Member
5 Posts |
Posted - 2011-05-13 : 09:45:47
|
| Thanks for the responses. So, since the db will be taken offline for a couple days anyway, is there a backup and restore method or a create scripts method that will guarantee it comes back identicle to the original? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-05-13 : 11:13:28
|
quote:
So, since the db will be taken offline for a couple days anyway
Nothing described here will take the database offline. If you can't do online index rebuild then a table may be locked until it completes, but the rest of the database would still be available.quote:
is there a backup and restore method or a create scripts method that will guarantee it comes back identicle to the original?
A standard backup/restore should suffice. |
|
|
|
allnelsons
Starting Member
5 Posts |
Posted - 2011-05-13 : 12:04:37
|
| Oh, no, my point wasn't that THIS will take the DB offline, it was that due to other maintenance things going on with that server, it will be taken out of production for a couple of days and thus I have more flexibility to do stuff to it/with it that I otherwise wouldn't be able to if I had to do this during production times.Standard backup and restore...that wouldn't redistribute the data, though, right? That would give me a DB with pages in the exact same spot as the original, right? Just wondering if there's a way to use GENERATE SCRIPTS or IMPORT/EXPORT to basically drop and re-add the data in an evenly distributed way. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-05-13 : 12:36:59
|
quote:
That would give me a DB with pages in the exact same spot as the original, right?
The way you originally worded that question suggested that's what you wanted. You can use the script you posted to reorganize, or rebuild the clustered index, there's not (m)any other ways to get the job done. Import/Export is also an option, but you'd have to drop or truncate the table to get the best performance. At that point, if you have the space, it's probably worth starting with a brand new database with all the filegroups laid out as you want. Only include clustered indexes during the import, then create the non-clustered indexes and keys afterwards. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
allnelsons
Starting Member
5 Posts |
Posted - 2011-05-13 : 13:37:30
|
| The DB is 400GB. There are 786 tables, 1207 indexes (clustered & nonclustered). It's a ConfigMan database (System Center Configuration Manager) db.We've got 3 disk volumes--each with 400GB--housing the current DB files. I was going to add one more identical 400GB volume and plop another DB file on it and spread the data out. But honestly, I can rearrange things however I need to. The whole point of this exercize is that we've recently outgrown our infrastructure just a little. We won't get new servers with tier1 SAN until mid next year and need to make due with the hardware we've got. If you're really interested, what I've got to work with for everything including tempdb, transaction logs, SCCM, everything, is this:PERC 6/E with 2 MD1000's (28 Disks + 2 hot swap)PERC 6/E with 2 MD1000's (28 more Disks + 2 hot swap)So, I've got 56 total spindles to use for the DB, TxLogs, Tempdb, TempDB Txlogs, the SCCM inboxes and backups.The DB has 3 volumes of 6 disks in RAID-10 currently, but I'm addinga fourth volume of 6 disks for extra I/O capacity on the DB. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
allnelsons
Starting Member
5 Posts |
Posted - 2011-05-16 : 08:30:56
|
| Wow...no kidding? There's a size limitation to the export/import process? Or is it just that the process would take so long, it's not realistic, but still technically possible? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Spreading data over multiple files