February 14, 2017 at 11:52 am
One of my clients has requested to find any options to reduce the size of some of the tables in this 500 GB database that are huge in size, and causes issues with read performance. My client would be migrating to SQL 2016 soon. Is there an approach or a way to have it reduced and increase the read performance as well. Client system is on SQL 2008 R2 Standard Edition.
February 14, 2017 at 1:16 pm
ffarouqi - Tuesday, February 14, 2017 11:52 AMOne of my clients has requested to find any options to reduce the size of some of the tables in this 500 GB database that are huge in size, and causes issues with read performance. My client would be migrating to SQL 2016 soon. Is there an approach or a way to have it reduced and increase the read performance as well. Client system is on SQL 2008 R2 Standard Edition.
Yes there is but if it's your client, you're the one making the money for this stuff. You should already know this. 😉 You should also already know what the issues are with "read performance" and know that it's usually not the amount of data that's causing the problem. It's the code using the data.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2017 at 3:49 pm
SQL 2016 SP1 added table compression (along with In-memory OLTP, columnstore indexes and a whole pile of other things) to Standard Edition, so that's an option. Row compression is often beneficial for performance as well as space, but test first. Page compression gets better space reduction but can degrade performance.
Performance problems are best fixed with well-written queries and indexes that support the workload though, not space reductions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2017 at 4:00 pm
Gail Shaw - I believe this is one is more geared towards performance on a bigger table having millions of records rather than space constraint. As a matter of fact the client is more interested in performance and not space reduction. I would definitely look into those options as suggested. However, do you think table with millions and billions of records should be evaluated by the business and try to archive or purge data which is not needed into a staging or an archive table before implementing any of the above said solution.
February 14, 2017 at 4:05 pm
ffarouqi - Tuesday, February 14, 2017 3:59 PMAs a matter of fact the client is more interested in performance and not space reduction.
Then you need to look at your indexes and queries, that's where the performance improvement will come from.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2017 at 4:11 pm
GilaMonster - Tuesday, February 14, 2017 4:05 PMffarouqi - Tuesday, February 14, 2017 3:59 PMAs a matter of fact the client is more interested in performance and not space reduction.Then you need to look at your indexes and queries, that's where the performance improvement will come from.
I agree on that part, but it would be helpful if you could also shed some light on whether it would be a good idea to purge and archive a lot of crap data that is not needed by the business...what do you suggest?
February 14, 2017 at 4:53 pm
ffarouqi - Tuesday, February 14, 2017 4:11 PMGilaMonster - Tuesday, February 14, 2017 4:05 PMffarouqi - Tuesday, February 14, 2017 3:59 PMAs a matter of fact the client is more interested in performance and not space reduction.Then you need to look at your indexes and queries, that's where the performance improvement will come from.
I agree on that part, but it would be helpful if you could also shed some light on whether it would be a good idea to purge and archive a lot of crap data that is not needed by the business...what do you suggest?
That needs to be worked out by you and your client. If they, for example, have 10 years of audit table or old invoice/invoice detail information (for example) and no legal requirement to keep the data for that long, I'd copy the legacy months to a temporary new archive database, back it up to tape (2 different tapes, just to make sure) and then begin the process of purging the data that was archived. If the data archived is substantial, then you'll need a plan for how to shrink the MDF file to reduce its size without the necessary rebuilds of indexes causing unnecessary growth. That can be done fairly easily by creating a sacrificial file group to rebuild the index on and then back to the primary file group.
If you can't archive much legacy data (again, WORM tables like Audit tables and old transaction tables of one sort or another), then a plan to partition and make the legacy months READ_ONLY so you don't have to maintain indexes nor repeatedly backup lots of data that has not and will not every change again.
In either case, Gail is correct and, as I previously stated, the size of tables really doesn't matter insofar as performance goes. It's going to take thoughtful indexes and properly written code to use them to really get performance back on it's feet. For example, cutting out half of the legacy data might make the code run twice as fast if you're lucky... fixing the code could make it run 60, 100, or (in some cases) even a 1000 times faster.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2017 at 10:41 pm
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply