ASP.NET/SQL 2008 Performance issue

Question

We've developed a system with a search screen that looks a little something like this:

_{(source: nsourceservices.com)}

As you can see, there is some fairly serious search functionality. You can use any combination of statuses, channels, languages, campaign types, and then narrow it down by name and so on as well.

Then, once you've searched and the leads pop up at the bottom, you can sort the headers.

The query uses ROWNUM to do a paging scheme, so we only return something like 70 rows at a time.

The Problem

Even though we're only returning 70 rows, an awful lot of IO and sorting is going on. This makes sense of course.

This has always caused some minor spikes to the Disk Queue. It started slowing down more when we hit 3 million leads, and now that we're getting closer to 5, the Disk Queue pegs for up to a second or two straight sometimes.

That would actually still be workable, but this system has another area with a time-sensitive process, lets say for simplicity that it's a web service, that needs to serve up responses very quickly or it will cause a timeout on the other end. The Disk Queue spikes are causing that part to bog down, which is causing timeouts downstream. The end result is actually dropped phone calls in our automated VoiceXML-based IVR, and that's very bad for us.

What We've Tried

We've tried:

• Maintenance tasks that reduce the number of leads in the system to the bare minimum.
• Added the obvious indexes to help.
• Ran the index tuning wizard in profiler and applied most of its suggestions. One of them was going to more or less reproduce the entire table inside an index so I tweaked it by hand to do a bit less than that.
• Added more RAM to the server. It was a little low but now it always has something like 8 gigs idle, and the SQL server is configured to use no more than 8 gigs, however it never uses more than 2 or 3. I found that odd. Why isn't it just putting the whole table in RAM? It's only 5 million leads and there's plenty of room.
• Poured over query execution plans. I can see that at this point the indexes seem to be mostly doing their job -- about 90% of the work is happening during the sorting stage.
• Considered partitioning the Leads table out to a different physical drive, but we don't have the resources for that, and it seems like it shouldn't be necessary.

In Closing...

Part of me feels like the server should be able to handle this. Five million records is not so many given the power of that server, which is a decent quad core with 16 gigs of ram. However, I can see how the sorting part is causing millions of rows to be touched just to return a handful.

So what have you done in situations like this? My instinct is that we should maybe slash some functionality, but if there's a way to keep this intact that will save me a war with the business unit.

Thanks in advance!

Answer 1

Database bottlenecks can frequently be improved by improving your SQL queries. Without knowing what those look like, consider creating an operational data store or a data warehouse that you populate on a scheduled basis.

Sometimes flattening out your complex relational databases is the way to go. It can make queries run significantly faster, and make it a lot easier to optimize your queries, since the model is very flat. That may also make it easier to determine if you need to scale your database server up or out. A capacity and growth analysis may help to make that call.

Transactional/highly normalized databases are not usually as scalable as an ODS or data warehouse.

Edit: Your ORM may have optimizations as well that it may support, that may be worth looking into, rather than just looking into how to optimize the queries that it's sending to your database. Perhaps bypassing your ORM altogether for the reports could be one way to have full control over your queries in order to gain better performance.

Answer 2

Consider how your ORM is creating the queries. If you're having poor search performance perhaps you could try using stored procedures to return your results and, if necessary, multiple stored procedures specifically tailored to which search criteria are in use.

Answer 3

1. determine which ad-hoc queries will most likely be run or limit the search criteria with stored procedures.. can you summarize data?.. treat this
   app like a data warehouse.
2. create indexes on each column involved in the search to avoid table scans.
3. create fragments on expressions.
4. periodically reorg the data and update statistics as more leads are loaded.
5. put the temporary files created by queries (result sets) in ramdisk.
6. consider migrating to a high-performance RDBMS engine like Informix OnLine.
7. Initiate another thread to start displaying N rows from the result set while the query
   continues to execute.