Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Poor database design Expand / Collapse
Author
Message
Posted Friday, April 1, 2011 4:09 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:35 PM
Points: 259, Visits: 1,086
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.
Post #1087728
Posted Monday, April 4, 2011 1:24 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 7:51 AM
Points: 825, Visits: 2,659
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
Post #1087921
Posted Monday, April 4, 2011 1:26 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 7:51 AM
Points: 825, Visits: 2,659
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
Post #1087922
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse