We have created a system having a search screen that looks a bit something similar to this:

As you can tell, there's some fairly serious search functionality. You should use any mixture of statuses, channels, languages, campaign types, after which narrow it lower by title and so forth too.

Then, once you have looked and also the leads appear at the end, you are able to sort the headers.

The query uses ROWNUM to perform a paging plan, therefore we only return something similar to 70 rows at any given time.

The Issue

Despite the fact that we are only coming back 70 rows, a great deal of IO and sorting is happening. This will make feeling of course.

It has always triggered some minor spikes towards the Disk Queue. It began slowing down lower more whenever we hit 3 million leads, now that we are getting nearer to 5, the Disk Queue pegs for approximately another or two straight sometimes.

That will really be workable, but this technique has another area having a time-sensitive process, allows say for simplicity it's an internet service, that must offer reactions very rapidly or it'll result in a timeout alternatively finish. The Disk Queue spikes are leading to that part to bog lower, that is leading to timeouts downstream. The finish outcome is really dropped telephone calls within our automated VoiceXML-based IVR, and that is very harmful to us.

What We have Attempted

We have attempted:

  • Maintenance tasks that reduce the amount of leads within the system towards the minimum.
  • Added the apparent indexes to assist.
  • Went the index tuning wizard in profiler and applied the majority of its suggestions. One of these would pretty much reproduce the whole table in a index and so i tweaked it manually to perform a bit under that.
  • Added more RAM towards the server. It had been just a little low however it has something similar to 8 gigs idle, and also the SQL server is set up to make use of a maximum of 8 gigs, nevertheless it never uses a lot more than two or three. I discovered that odd. Why is not it simply putting the entire table in RAM? It is just 5 million leads and there is lots of space.
  • Put over query execution plans. I can tell that at this time the indexes appear to become mostly doing their job -- about 90% from the jobs are happening throughout the sorting stage.
  • Considered partitioning the Leads table to another physical drive, but we not have the assets for your, also it appears enjoy it should not be necessary.

In Conclusion...

A part of me feels as though the server should have the ability to handle this. 5 million records isn't a lot of because of the energy of this server, the industry decent quad core with 16 gigs of ram. However, I can tell the way the sorting part is leading to countless rows to become touched simply to return a few.

So whoever else completed in situations such as this? My instinct is the fact that we ought to maybe slash some functionality, but when there's a method to bare this intact which will save us a war using the business unit.

Thanks ahead of time!

Database bottlenecks could be enhanced by enhancing your SQL queries. Not understanding what individuals seem like, consider creating an operational data store or perhaps a data warehouse that you simply populate on the scheduled basis.

Sometimes flattening your complex relational databases is what you want. It will make queries run considerably faster, and turn it into a lot simpler to optimize your queries, because the model is extremely flat. That could also allow it to be simpler to find out if you want to scale your database server up or out. A capacity and growth analysis might help to make that call.

Transactional/highly stabilized databases aren't usually as scalable being an ODS or data warehouse.

Edit: Your ORM might have optimizations too that it could support, that might be worth considering, as opposed to just considering how you can optimize the queries it's delivering for your database. Possibly skipping your ORM altogether for that reviews might be one method to have full treatments for your queries to be able to gain better performance.

  1. pick which ad-hoc queries will in all probability be run or limit the search criteria with saved methods.. are you able to summarize data?.. treat this
    application just like a data warehouse.
  2. create indexes on each column active in the search to prevent table scans.
  3. create fragments on expressions.
  4. periodically reorg the information increase statistics weight loss leads are loaded.
  5. place the temporary files produced by queries (result sets) in ramdisk.
  6. consider moving to some high-performance RDBMS engine like Informix OnLine.