4

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

Improve this question

4 Answers 4

Reset to default
6

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.
Improve this answer
3
  • 2
    If you're trying to escalate and still having trouble with the vendor I recommend a device called a 'governance stick'. The best ones are made from hickory and the web site of a well known manufacturer can be found here. ;-}
    – ConcernedOfTunbridgeWells
    Commented Nov 30, 2011 at 14:21
  • 1
    Great reply. The vendor acknowledges text book step #1 and #2 and after step #3 I reached step #4 long ago. That being said, I've thrown more memory at it since some disk IO was going on (to no ultimate effect). I've experimented with indexes (between adding new ones targeting those slow summary tasks and simply rebuilding existing indexes) and I've not had any luck. Still working on understanding all the root causes. (Honestly, I'm new to this DBA stuff. Software engineering background.) I'll let you know what else I can turn up - plan guides look fun.
    – Kaganar
    Commented Nov 30, 2011 at 19:14
  • If they are a Microsoft Partner, then you can get Microsoft to put pressure on them to fix their app. I don't recall what the program is called, but hopefully someone else does.
    – Eric Humphrey - lotsahelp
    Commented Dec 7, 2011 at 17:42
3

Is there some sort of single-process mode for SQL server that makes locking records unnecessary?

You may want to look at "Read Committed Snapshot" isolation level. You can set it at the database level. It's a type of 'read-committed' isolation but it does not take a "S" lock on records while reading data.

You can enable it by setting READ_COMMITTED_SNAPSHOT at the database level. For more info on isolation levels, see this post by Sunil Agarwal.

Improve this answer
3
  • 1
    +1 for picking up on that part of the question (I missed it) and for suggesting RCSI but your description of how it works is wrong. Also, switching it on without understanding the impact it can have is very dangerous.
    – Mark Storey-Smith
    Commented Nov 29, 2011 at 23:45
  • This is an interesting suggestion. Unfortunately, from what I've been able to tell RCSI doesn't help the read-only scenario most of our slowest application functions perform. Given the across-the-board poor performance poor indices make more sense.
    – Kaganar
    Commented Nov 30, 2011 at 17:41
  • 1
    Mark, Thanks for pointing out the issue with my description. I had accidently forgotten to put the "NOT" in the sentence " does not take a S lock". You are also right about the implications - specially on tempDB space.
    – Dharmendar Kumar 'DK'
    Commented Nov 30, 2011 at 18:26
2

Throwing hardware at the problem can be a very workable solution for third party applications. Sadly that can end up simply masking problems. They will show up again later as the system has grown.

This is where index tuning becomes very important.

Also you'll want to look at pinning execution plans to force specific execution plans without modifying the query.

Improve this answer
2

In addition to other answers...

If SQL Server is CPU bound then it often means poor indexing. You can identity missing indexes using dmvs, such as the scripts below.

This should remove pressure from CPU.

Otherwise, throw hardware at it. If you can fix CPU usage with indexes, then look at as much RAM as you can stuff in the server so it runs from memory. Then disks: RAID 10 not RAID 5 especially for transaction logs. And 64 bit?

SELECT
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
  'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
  + ' ON ' + mid.statement
  + ' (' + ISNULL (mid.equality_columns,'')
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
    + ISNULL (mid.inequality_columns, '')
  + ')'
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
Improve this answer
1
  • Excellent code, helped me weed out that the indices are actually set up pretty well.
    – Kaganar
    Commented Nov 30, 2011 at 19:15

Your Answer

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

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