SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Poor database design


Poor database design

Author
Message
Gail Wanabee
Gail Wanabee
Right there with Babe
Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)

Group: General Forum Members
Points: 756 Visits: 1339
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.
Sean Pearce
Sean Pearce
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1804 Visits: 3432
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.


This makes a lot of sense, and sounds exactly like the situation I am in currently, however we cannot archive data. Their system relies on all data being present. We are currently proposing a system whereby we have archive data seperated from current data with views mimicing the current tables on top and triggers on the views to control inserts and updates. I hope the client approves this because this system will not last for very long at current growth rates.



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Sean Pearce
Sean Pearce
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1804 Visits: 3432
GilaMonster (3/31/2011)
Sean, next time it happens, if you're allowed to investigate, feel free to post specific questions and we'll see what we can help with.

Thanks, I will definitely do that.

FYI, we ran the full data load on the QA server on Friday and the process ran almost 10 times faster and caused no issues.



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search