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