November 18, 2018 at 5:19 am
Hi All,
We have a database about 500 GB , we thought of splitting the data files on different physical hard disks to improve performance.
The steps we did were:
1. Shrink the entire database.
2.Create a new file group on a different physical hard disk.
3.Restrict file size for original data file.
4.For the new file we set autogrowth to true.
Since we did that we had a performance issue with one of the tables specially with inserts and updates, we kept rebuilding the indexes on that table many times during the day, after rebuild things go back to normal but after some time the performance degrades again.
My first thought is that data and index pages are scattered between the 2 files as opposed to one file in the older setup but not sure if that's the reason or not.
So please give suggestions on how to detect the reason for this issue and how to solve it.
Thank you
Nader
November 18, 2018 at 7:28 am
nadersam - Sunday, November 18, 2018 5:19 AMHi All,We have a database about 500 GB , we thought of splitting the data files on different physical hard disks to improve performance.
The steps we did were:
1. Shrink the entire database.
2.Create a new file group on a different physical hard disk.
3.Restrict file size for original data file.
4.For the new file we set autogrowth to true.Since we did that we had a performance issue with one of the tables specially with inserts and updates, we kept rebuilding the indexes on that table many times during the day, after rebuild things go back to normal but after some time the performance degrades again.
My first thought is that data and index pages are scattered between the 2 files as opposed to one file in the older setup but not sure if that's the reason or not.
So please give suggestions on how to detect the reason for this issue and how to solve it.
Thank you
Nader
When you shrink a database you will fragment the tables and indexes. You should rebuild all the indexes on the table you are having issues with.
November 18, 2018 at 7:53 am
Jonathan AC Roberts - Sunday, November 18, 2018 7:28 AMnadersam - Sunday, November 18, 2018 5:19 AMHi All,We have a database about 500 GB , we thought of splitting the data files on different physical hard disks to improve performance.
The steps we did were:
1. Shrink the entire database.
2.Create a new file group on a different physical hard disk.
3.Restrict file size for original data file.
4.For the new file we set autogrowth to true.Since we did that we had a performance issue with one of the tables specially with inserts and updates, we kept rebuilding the indexes on that table many times during the day, after rebuild things go back to normal but after some time the performance degrades again.
My first thought is that data and index pages are scattered between the 2 files as opposed to one file in the older setup but not sure if that's the reason or not.
So please give suggestions on how to detect the reason for this issue and how to solve it.
Thank you
NaderWhen you shrink a database you will fragment the tables and indexes. You should rebuild all the indexes on the table you are having issues with.
After shrinking data files of this size, reviving performance is like reviving a frog out of the blender:exclamation:
😎
November 18, 2018 at 8:11 am
I do think you took the wrong approach there unless you didn't tell us of further steps that would normally be required.
But by your list of steps I do not believe any further steps were taken.
When there is proof that adding a new filegroup is what will sort out the performance what is normally done is to move some of the required tables onto the new filegroup and only then, and if absolutely required, shrink the original data file - may not be required at all and should be avoided.
AND one or both of
And your code/indexes is already tuned to the maximum - many times heavy IO is due to bad coding alone and this should be addressed in the first instance.
If you did indeed do due diligence and have identified the above then moving some tables to another file would be a possible solution.
The way to do it then would be
Note that if the IO issue was due to max load on the controller the new disk MUST be on another controller or it will not help in any way.
so at the moment your performance issues are probably related to the shrink step.
And I do hope that you have indeed created a new filegroup, not just added a new file to the existing filegroup - this again may not be the best option
November 18, 2018 at 9:07 am
Eirikur Eiriksson - Sunday, November 18, 2018 7:53 AMJonathan AC Roberts - Sunday, November 18, 2018 7:28 AMnadersam - Sunday, November 18, 2018 5:19 AMHi All,We have a database about 500 GB , we thought of splitting the data files on different physical hard disks to improve performance.
The steps we did were:
1. Shrink the entire database.
2.Create a new file group on a different physical hard disk.
3.Restrict file size for original data file.
4.For the new file we set autogrowth to true.Since we did that we had a performance issue with one of the tables specially with inserts and updates, we kept rebuilding the indexes on that table many times during the day, after rebuild things go back to normal but after some time the performance degrades again.
My first thought is that data and index pages are scattered between the 2 files as opposed to one file in the older setup but not sure if that's the reason or not.
So please give suggestions on how to detect the reason for this issue and how to solve it.
Thank you
NaderWhen you shrink a database you will fragment the tables and indexes. You should rebuild all the indexes on the table you are having issues with.
After shrinking data files of this size, reviving performance is like reviving a frog out of the blender:exclamation:
😎
All our databases are on SAN drives and performance doesn't seem to be affected much when files are shrunk. I'm guessing the OP has his files on some other type of drive.
November 18, 2018 at 11:34 am
Jonathan AC Roberts - Sunday, November 18, 2018 9:07 AMEirikur Eiriksson - Sunday, November 18, 2018 7:53 AMJonathan AC Roberts - Sunday, November 18, 2018 7:28 AMnadersam - Sunday, November 18, 2018 5:19 AMHi All,We have a database about 500 GB , we thought of splitting the data files on different physical hard disks to improve performance.
The steps we did were:
1. Shrink the entire database.
2.Create a new file group on a different physical hard disk.
3.Restrict file size for original data file.
4.For the new file we set autogrowth to true.Since we did that we had a performance issue with one of the tables specially with inserts and updates, we kept rebuilding the indexes on that table many times during the day, after rebuild things go back to normal but after some time the performance degrades again.
My first thought is that data and index pages are scattered between the 2 files as opposed to one file in the older setup but not sure if that's the reason or not.
So please give suggestions on how to detect the reason for this issue and how to solve it.
Thank you
NaderWhen you shrink a database you will fragment the tables and indexes. You should rebuild all the indexes on the table you are having issues with.
After shrinking data files of this size, reviving performance is like reviving a frog out of the blender:exclamation:
😎All our databases are on SAN drives and performance doesn't seem to be affected much when files are shrunk. I'm guessing the OP has his files on some other type of drive.
Logical fragmentation matters very little once the data is loaded into memory. Getting it into memory is a whole 'nuther story".
As you know, doing a shrink file doesn't just cause a lot of fragmentation. It causes the absolute worst type of fragmentation there is. That type of fragmentation (to those indexes on which it does occur) in referred to as "Index Inversion". This is where the physical order of the pages in the file are exactly in the reverse order of the logical order. Even with SSDs (like what we have at work), it can still have a huge impact on performance because the "Read Aheads" necessary to get the data into memory (if not already there) is limited to usually just one page at a time.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 19, 2018 at 1:27 am
Jonathan AC Roberts - Sunday, November 18, 2018 7:28 AMnadersam - Sunday, November 18, 2018 5:19 AMHi All,We have a database about 500 GB , we thought of splitting the data files on different physical hard disks to improve performance.
The steps we did were:
1. Shrink the entire database.
2.Create a new file group on a different physical hard disk.
3.Restrict file size for original data file.
4.For the new file we set autogrowth to true.Since we did that we had a performance issue with one of the tables specially with inserts and updates, we kept rebuilding the indexes on that table many times during the day, after rebuild things go back to normal but after some time the performance degrades again.
My first thought is that data and index pages are scattered between the 2 files as opposed to one file in the older setup but not sure if that's the reason or not.
So please give suggestions on how to detect the reason for this issue and how to solve it.
Thank you
NaderWhen you shrink a database you will fragment the tables and indexes. You should rebuild all the indexes on the table you are having issues with.
Thank for your reply , yes we did a full index rebuild after the shrink , sorry for forgetting to mention that, but still we got the performance issue specially with one of the tables.
November 19, 2018 at 1:31 am
Thank you for all replies above, from what i understand the performance issue is related to shrinking the database which we will avoid.
Now after we did the mentioned step above should we separate some table with high IO to the new filegroup?
Thanks again.
Nader
November 19, 2018 at 2:38 am
nadersam - Monday, November 19, 2018 1:31 AMThank you for all replies above, from what i understand the performance issue is related to shrinking the database which we will avoid.
Now after we did the mentioned step above should we separate some table with high IO to the new filegroup?
Thanks again.
Nader
Yes, try putting tables that are frequently joined together into separate filegroups. Another strategy I've seen is to have tables (including clustered indexes) on one filegroup and non-clustered indexes on another. If you're on a SAN, bear in mind that even by doing this you may not be getting physical separation. Speak to your storage administrator if you're in any doubt about that.
John
November 19, 2018 at 2:55 am
John Mitchell-245523 - Monday, November 19, 2018 2:38 AMnadersam - Monday, November 19, 2018 1:31 AMThank you for all replies above, from what i understand the performance issue is related to shrinking the database which we will avoid.
Now after we did the mentioned step above should we separate some table with high IO to the new filegroup?
Thanks again.
NaderYes, try putting tables that are frequently joined together into separate filegroups. Another strategy I've seen is to have tables (including clustered indexes) on one filegroup and non-clustered indexes on another. If you're on a SAN, bear in mind that even by doing this you may not be getting physical separation. Speak to your storage administrator if you're in any doubt about that.
John
Thank you i will keep that in mind.
November 19, 2018 at 6:46 am
nadersam - Monday, November 19, 2018 1:31 AMThank you for all replies above, from what i understand the performance issue is related to shrinking the database which we will avoid.
Now after we did the mentioned step above should we separate some table with high IO to the new filegroup?
Thanks again.
Nader
No. You haven't identified WHY the table suffers "High I/O". You first need to identify the cause and then fix it. 90% of the time it will be poorly written code and 5% of the time it will be a problem with an index.
Also, when you split the file, are you positive that you moved it to a disk that had the same physical performance features?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply