Poor database design
-
Ninja's_RGR'us (3/30/2011)
... and Gail is the one I would call to save my life with a problem like this. Can't ask for a better consultant.Good luck!
+1
-
Sean Pearce (3/30/2011)
GilaMonster (3/30/2011)
I suspect that you may need to get a specialist in (on-site) for a couple of days. I can recommend some good people here. Sounds like it's maybe too big/complex a problem for the forums.Thanks for the offer, but we are in South Africa. 🙂
I will figure this one out though. Will wait for it to raise its head again.
BWAHAHAHAHA, talk about luck for you Sean!
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Craig Farrell (3/30/2011)
Sean Pearce (3/30/2011)
GilaMonster (3/30/2011)
I suspect that you may need to get a specialist in (on-site) for a couple of days. I can recommend some good people here. Sounds like it's maybe too big/complex a problem for the forums.Thanks for the offer, but we are in South Africa. 🙂
I will figure this one out though. Will wait for it to raise its head again.
BWAHAHAHAHA, talk about luck for you Sean!
Doesn't look like I'll be getting involved there, so....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I'd agree with views and indexed views as an option to restructure underlying data.
I'd also note that on the "buy truly excessive hardware" side, there are commodity 4U servers that can be bought with 1TB of RAM, Fusionio ioDrive PCIe SSD's, and well over 1TB of SATA/SAS SSD's as well (at that level, you'd be looking at controller bottlenecks).
If nothing else, sticker shock from some of that hardware may... encourage... management to allow more redesign of the database and/or the software itself.
-
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.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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.
-
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.
-
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.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply