We currently have a production database that contains millions of records (14 million in the largest table).
One function of the database is to provide time sheet information so a single table could have 2 million time sheets but be joined across 5 other "detail" tables and each of those detail tables have between 500k and 14m records.
Also, we are running some legacy software that cannot be changed or replaced anytime soon. The problem we are having is that there are some queries (also legacy and cannot be changed) that unfortunately are performing massive table scans. So looking up 100 time sheets is causing millions of rows to be scanned.
I'm not a DBA by trade but I believe I have indexed everything I can possibly index with as much logic and research as I know how. Plus I believe I have setup proper re-indexing jobs, etc.
We are running SQL Server 2008 (10.0.5500.0) on a machine with 32GB RAM (16GB devoted to SQL Server). There isn't much else on the machine so most of the resources are devoted to SQL Server.
Our next plan is to possibly add more RAM to the machine and maybe bump the RAM used by SQL Server to 32GB. Our database size is around 56GB on disk.
My question is, do you think we should look into upgrading to SQL Server 2014 Enterprise so that we can partition our time sheet tables? I'm new to table partitioning but from what I understand it could be used to perhaps partition our tables by site and year? So that 14m rows would be split out by 10-20 sites and then further split by year so that queries would scan 10k-20k rows instead of 14m. Or am I just totally missing the mark here?
Any other advise would be welcomed.
Thank you.
Best Answer
No. Absolutely not. On a pocket change server of 8 cores you would be spending ~$50k and be unlikely to see any benefit.
I would suggest trying, in this order: