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.

  • My client is on SQL 2008 R2 Standard Edition – Feivel Feb 14 '17 at 18:43
  • 1
    Huge in size...due to amount of data? Char columns too wide? Too many columns/denormalized? Maybe read performance is a simple index issue? – Kevin3NF Feb 14 '17 at 18:51
  • if the tables are heaps and data has been deleted from them, the space is not necessarily released back to SQL Server to use again. – Jonathan Fite Feb 14 '17 at 18:54

Here's my answers in the order that I'd do them:

1. Remove tables you no longer need. Go into SSMS, Object Explorer, click on the tables folder for your database, and then open Object Explorer Details by clicking View, Object Explorer Details. From there, you can right-click on columns to show data size and index size, and you can sort by either one. I find that often, folks left backup tables they didn't know about. SSMS 2016 shown

2. Remove unused indexes. Use sp_BlitzIndex in the First Responder Kit (disclaimer: I'm one of the authors) to remove nonclustered indexes that aren't being used. Bonus points for removing duplicate and near-duplicate indexes.

3. Check the server's bottlenecks. From the First Responder Kit again, run sp_BlitzFirst @SinceStartup = 1 to get your wait stats since startup. If the top wait is PAGEIOLATCH, that means reading data pages from the data file. In that case, try maxing out your memory. 2008R2 Standard Edition takes 64GB just for the buffer pool, and can use more memory than that for other purposes. Make sure your server has at least 96GB RAM. (I'm not saying it's always a good idea to throw hardware at the problem, but my desktop has 64GB of RAM, and the rest of these changes are going to be more and more disruptive.)

If your top wait stat is anything other than PAGEIOLATCH, then focus on the top wait type instead, because reducing the database size may not help performance.

4. Implement desperately needed indexes. When your database won't fit in RAM, the index designs become much more important. You'll see the list of desperately needed missing indexes from sp_BlitzIndex in the prior step, but also consider running sp_BlitzCache @SortOrder = 'reads' (again, from our First Responder Kit) to identify the queries doing the most logical reads. Often, their plans will have missing indexes right there, and again, you can implement those to reduce the amount of data you need to read from disk.

5. Double-check storage speeds. In an earlier step, I recommended running sp_BlitzFirst @SinceStartup = 1. Now that you've pruned indexes, tuned queries, and right-sized RAM, it's time to circle back to that same command again - only this time, instead of looking at your wait stats, look at the PHYSICAL READS section. That identifies which data files you've been reading from, and what their average latency is. If your average read latency is over, say, 100ms, it's time to start asking questions about storage performance.

  • Thanks! Brent. Is there a way we can leverage any feature or think of deleting or purging records in order to reduce size and maybe later on when we move to SQL 2016 SP1 we can compress the data for read performance. – Feivel Feb 14 '17 at 18:55
  • I'd focus on the things I mentioned in the answer. – Brent Ozar Feb 14 '17 at 18:58
  • Thanks! Brent. I will follow your suggestions and hopefully, things should improve. Although,a little off topic, but is there a better or an easier way to migrate a 500 Gb database with least amount of downtime etc. – Feivel Feb 14 '17 at 19:01
  • Sure, for migrations, try database mirroring or log shipping. – Brent Ozar Feb 14 '17 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.