Sql-server – SQL Server Optimization/Configuration for Inflexible Application

configurationoptimizationsql server

I'm responsible for overseeing an application which uses SQL Server as its backend storage solution. Unfortunately, the application has no concept of how a RDBMS should be utilized and seems to use it mainly like a NoSQL solution. What the application needs most is fetching and storing of single records and doesn't use any foreign keys for data integrity whatsoever.

As expected, this brilliant design leads to rather poor performance because throwing a RDBMS at a problem which merely requires a hash-on-disk style scheme is like using a cannon on a mosquito. But alas, I'm not able to modify the software as it is third party.

I've seen many tips that are happy to tell me I should use more efficient queries, but this simply isn't an option. SQL Server seems to be CPU bound mainly, and most of the data also seems to be cached in memory. Is there a way I can optimize/configure the way SQL deals with requests from this application? (The entire instance of SQL Server is available exclusively to the application.) Alternatively, is there another solution that behaves like SQL Server but acts like NoSQL in speed for simple queries? Is there some sort of single-process mode for SQL server that makes locking records unnecessary?

Any off-the-wall ideas I should give a shot?

Update:
So after a lot of mucking about with traces laced with auto-generated cursor preps, execs, fetches, and so forth, it looks like most of the time in the particular application feature of concern is wasted on populating tables for which only the first row is actually fetched. This actually appears to be a common problem the way the application was written.

Since the select statements behind the cursors are ordering the data, the cursors cannot effectively use a dynamic plan. Ultimately, the data the application is trying to get at is the next record where field A = document_id and the next field B value that's larger than the previously sought field B value. This is, of course, a typical application of cursors — if only the application was using cursors the typical way.

I'm looking to contact the vendor again because this is… bad. I figure they're more likely to pop in an easy two-line already-existing fix. As such, I'm looking to generate a more efficient version of their query (still using cursors, with the same client-side semantics of 'give me the next record, k?'). I'm not exactly sure how to formulate such a query in TSQL since everything I've given a shot results in a scan, even with indexes I'd consider useful. (Really, only one b-tree search should be required, but somehow I just can't seem to get there.)

Best Answer

We've all been there. Those that haven't are going to run into this situation one day!

There is a text book approach to dealing with third party horror software:

  1. Prove there is a problem. Identify and document crazy queries and data access patterns.
  2. Approach the vendor with your evidence of a problem. If you can show that a re-write of a query shows improvements are possible, it gets harder for the vendor to ignore your concerns.
  3. If your getting no response from your contact at the vendor, escalate. If you can't make enough noise to be heard, maybe your CTO can.
  4. If you've reached step 4 without any success, you're likely to be sacrificing follicles or noticing the telling first signs of grey. This is where a voodoo doll of the third party development lead can ease the stresses of the day. A dartboard featuring the company logo is an acceptable alternative.

Back in the real world, what practical approaches do we have:

  • Kill it with iron. It always pains me to say it but throwing hardware at the problem can be the fastest, cheapest, lowest risk solution at times.
  • Understand the root causes. Run the same analysis as you would for a system that was fully under your control. Identify the most frequent queries, those with highest CPU, highest reads, highest duration, look at tempdb usage, analyse the plan cache (single use ad-hoc queries crop up often in these scenarios).
  • Indexing. The Database Tuning Advisor (DTA) is a useful tool for analysing workloads you have little control over. In particular, it can spot candidates for indexed views and missing multi-column statistics which may not be obvious.
  • Plan guides. If you spot a query that you can re-work the execution plan for, plan guides give you a way of forcing that plan without modifying the source query.
Related Question