We're using a SQL Server 2008 database. This database provides data for two main applications:

A website to display a Viewer.

A routing application that needs to be highly responsive !

Is it possible to give different priorities to those two applications and what would be all the possibilities available to us ? I'm afraid that our Viewer website, through a bug or anything else would slow down our routing application.

I want to protect the routing application in order to keep it highly responsive, what are the solutions I could use?

1 Answer
1

Andy M, to achieve this level of tunning in SQL, you must use the Resource Governor. It can be a problem, since it's available only in the (expensives) Datacenter & Enterprise editions.

If you can't afford this editions, there's a few things you can consider:

GENERAL

Split your database in 2: one for the routing app, another for the viewer app. This way, you can tune them separately, to obtain the best performance;

Consider acquiring HDs to your server (SSD, if you can afford), and split the data/index files between them, to increase I/O (thus the responsiveness);

ROUTING APP

If you need a high performance just in querying data, you can pre-process this data, leaving it in its final modeling. This approach can boost your query performance in orders of magnitude;

If you have to process information for this requests, but not all of this processing is relevant for the output, try to buffer the processing: analyze what data processing can be post-poned, and just write the data to some dummy tables (maybe in another DB), and process them in a different time.