5

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.

Improve this question
4
  • 5
    If indexing cannot make your queries faster, partitioning rarely can.
    – usr
    Jul 24, 2014 at 19:50
  • It is not clear that you have exhausted all your indexing options. It sounds like you want to partition by year because you are querying by year. So just index by year. Wouldn't that work?
    – usr
    Jul 24, 2014 at 19:52
  • A lot depends on what the query's execution plan looks like. If the DBA/developer can look at where index scans are occurring vs. seeks, either the T-SQL or indexing strategy pertaining to that section can be modified to improve the performance. I've had comparable performance issues where the difference between a seek and a scan was because data types were mismatched or because statistics on the columns being queried were out of date, both relatively quick to fix.
    – MattyZDBA
    Jul 24, 2014 at 19:59
  • I certainly admit that my lack of hardcore DBA skills could reveal that I am possibly missing a better index or something similar. I do have an index on the time sheet date (DateTime). I guess I need to bone up on learning more performance optimizations and understanding the execution plan better? But where would I start? There are many many queries that are hitting this database. Thx
    – cbmeeks
    Jul 24, 2014 at 20:01

4 Answers 4

Reset to default
9

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?

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:

  1. Increase the memory allocation to SQL Server. You said the server is dedicated but have only half the RAM allocated. 32GB server, allocate 28GB.
  2. Upgrade the server memory to [size of database] + 4GB.
  3. Post the problem queries on dba.se.
  4. Engage an expert.
Improve this answer
2
  • Especially the last point is a good idea before expending 50k$.
    – usr
    Jul 24, 2014 at 21:06
  • Thank you. We're going to do exactly that. We're bumping the RAM up to 96GB and going to devote 64GB to SQL Server. Which I believe is the max we can devote to 2008 Standard. We're in the talks of even upgrading the entire machine with RAID SSD and maybe, just maybe Enterprise. We get pretty good lease deals so the way it works around here the lease payments may not be that much more than Standard edition.
    – cbmeeks
    Jul 25, 2014 at 15:31
1

At 14 million rows in your largest table and 16GB of RAM devoted to SQL Server it sounds like your entire DB almost fits in memory. If that is true the problem is likely to be quadratic (or worse) behaviour in your queries, which can only be truly fixed by rewriting the worst offenders.

That said, I would try one or both of:

  1. Adding some SSD RAM to the server devoted to the indices of the largest tables; and
  2. Adding more disks (not more disk space, more actual disks) in order to have more heads and axles servicing the massive table scans.

I believe these are more likely to give you incremental performance improvements than partitioning the data tables.

Improve this answer
2
  • If all data is in memory more disks will not change anything.
    – usr
    Jul 24, 2014 at 20:10
  • @usr: Unless memory-thrashing is occurring, which is almost the only way a fairly modest database like this generates such bad performance. (The real culprit is badly written SQL, but the embedded RBAR induces the thrashing.) Your point merely emphasizes that all performance tuning should be of specific, measured, functional requests for which the bottleneck (Disk, memory or CPU) has been identified.
    – Pieter Geerkens
    Jul 24, 2014 at 20:13
1

If you don't have a DBA chances are the server hasn't been configured according to simple but critical best practices. You can't really make a definitive diagnosis until you've done that.

Engage and expert to assess what needs getting done. Then either make the changes or have them do it.

Improve this answer
0

Are your indexes & tables maintained recently and are you statistics up-to-date? Fragmentation and wrong statistics can cause big problems.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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