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.
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.
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.