crainlee2 (4/1/2011)

---

Without knowing all of the details of your situation and the problems you are having, the one thing that stands out is that the larger the database grows, the worse the situation gets.

What follows is a short story about fixing the symptoms, not the problems. But, given the situation I was in (unable to fix the underlying problems), fixing the symptoms was the only solution:

____________

Two years ago, I was faced with a similar situation: a terrible database design purchased from a third party (along with their proprietary software) that violated every rule of normalization, inadequate and inappropriate indexing, adhoc queries written by lay employees that took the server down as it choked on correlated subqueries and "select * from table" type queries.

In short, the production environment could not be controlled by the DBA. So, I analyzed the stress on the system and found that the CPU, RAM, and the disk subsystem were all stressed to capacity. I wrote a justification for a more powerful server based on this data, and the company purchased a much more powerful server.

This bought some time but it did not fix the underlying problems of table scans, adhoc queries for which there were no supporting indexes, all of which were being aggravated by an ever-growing database.

In this environment, there was only one solution that would take pressure off of the server (and me, the DBA). I identified the largest tables with the highest levels of access; there were 4. Then, I proposed to management to reduce the size of the tables by one day's worth of data every night by archiving the oldest day of data in each table. We set windows of data retention for each table (90 days, 120 days, etc.). I created corresponding data archive tables (with nothing but a clustered index and a non-clustered index on the pertinent datetime column), then wrote and deployed the nightly exectuted SQL Agent jobs to archive the oldest day's data from the 4 primary tables to the 4 archive tables.

To make a long story short, this solved almost all of the performance problems on the server. Full table scans on short tables? No problem. Adhoc SQL statements referencing unindexed columns? No problem.

If a database's most highly accessed tables are small enough, a lot of performance problems disappear.

I hope this information is of use to you.

LC

P.S. Additionally, I wrote data retrieval queries to, upon execution, restored data from the archive tables to their corresponding primary tables, when needed by management. Those restored records are automatically re-archived every night by the data archiving jobs run by SQL Agent.