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 Tuesday, March 29, 2011 7:03 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: Thursday, December 18, 2014 1:37 AM
Points: 998, Visits: 3,089
Not sure where to post this and I'm not sure if I am going to gain anything by posting this. At the very least I could warn other developers out there.

I have a very badly designed database. It has grown out of control and there is very little I can do about it.

I cannot change the structure due to the application design. I'm also struggling to design an archive system, also due to the application design. The system is approaching a terrabyte and the more data that is loaded the slower the system becomes due to inneficient design.

Our immediate problem is this. Sometimes when we do a large load of data the system grinds to a halt until we rebuild the database. The batch requests per second is much lower than usual, as well the very little IO, however the CPU is suffering. Our only solution is a rebuild. After shutting down application servers, we BCP the data out, drop the table, create the table, BCP the data back in, add the indexes. This is the only method we have had success with when this anomaly happens.




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1085483
Posted Tuesday, March 29, 2011 7:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
What other solutions have you tried?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1085499
Posted Tuesday, March 29, 2011 7:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:26 AM
Points: 14,205, Visits: 28,536
Have you look at locks and wait states to understand why it's doing this? You should be able to at least narrow down the problem.

App code or not (and I assume we're not talking 3rd party here), if things are this bad, that you have to take the productoin system off-line, I don't know of many businesses that couldn't find a bit of time for some refactoring. It sounds like you need it.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1085536
Posted Tuesday, March 29, 2011 7:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
Are those tables heaps or clusters?
What's logical fragmentation? forwarding pointers? avg page space used? at the point that you need to do a 'rebuild'?

What version of SQL are you using? What edition?

p.s. How experienced are your DBAs?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1085545
Posted Tuesday, March 29, 2011 8:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 12,962, Visits: 32,506
I'm thinking when he does a load of data, the statistics go out of date, and "it grinds to a halt"? updating statistics right after the large load seems to be one of the first things i'd look at.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1085548
Posted Tuesday, March 29, 2011 8:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
If you can't change the design or application. then you could change the hardware... I have seen some good performance improvements from using SSDs on badly designed databases, though at over 1TB it will be expensive..

Post #1085577
Posted Tuesday, March 29, 2011 12:42 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, September 4, 2014 2:25 AM
Points: 424, Visits: 265
Sometimes when we do a large load of data the system grinds to a halt until we rebuild the database


Just a thought, do you drop the indexes prior to a large load of data and then rebuild same indexes afterwards?

It could be the updating on the indexes during the large data load that causes some (if not all) of your slowdown.


---------------------------------------------
If data can screw you, it will; never assume the data are correct.
Post #1085791
Posted Tuesday, March 29, 2011 12:45 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, September 4, 2014 2:25 AM
Points: 424, Visits: 265
Also, there may be a little bit of back-end tweaking that can be done, whilst using views to present the same logical output to the application. Not knowing the structure of the db, this is just a thought aired.

---------------------------------------------
If data can screw you, it will; never assume the data are correct.
Post #1085799
Posted Tuesday, March 29, 2011 2:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
KevinGurney (3/29/2011)
Also, there may be a little bit of back-end tweaking that can be done, whilst using views to present the same logical output to the application. Not knowing the structure of the db, this is just a thought aired.


I'd second this. You're in a lousy position. Views to imitate schema may be one of your ways out of this. Throw in indexed views and you might be looking at a possible short term solution while you puzzle out what the table level should actually look like.

Can you take us step by step through your 'large data load', including existing rowcounts, expected inclusion volume, number of tables, if you use staging tables to cleanse the data if there's any cleanup to be done, etc?

A reindexing every load isn't unheard of, but it does depend on the volume. It also depends on the 'noise' area in the table. Most huge tables have a very small area that's being heavily updated with the rest of it just being there for reference, if at all. I've seen as low as 2% fragmentation on a huge table be enough to take the processing speed of a system down.

At the time, I didn't have easy partitioning available. However, if you can isolate this to being a portion of your issue (which is what it sounds like to me offhand), it may be a possibility. The biggest reason to do this is reindexing will be much faster in the area(s) of the partition that don't involve the remaining large volume.



- Craig Farrell

Never 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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1085870
Posted Wednesday, March 30, 2011 3:47 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: Thursday, December 18, 2014 1:37 AM
Points: 998, Visits: 3,089
Hi, and thanks for all the replies. Yesterday was a long day and we finally got the rebuilds complete last night. I'm not happy at all with the solution, as it is unheard of to take a production system offline to rebuild tables in order to get the system working again. I'm also unhappy that we cannot quite pinpoint the underlying problem.

Allow me to answer your questions:

What other solutions have you tried?

We have tried "ALTER INDEX WITH REORGANIZE", "ALTER INDEX WITH REBUILD" without success.

Have you look at locks and wait states to understand why it's doing this? You should be able to at least narrow down the problem.

The excessive wait stats were all SOS_SCHEDULER_YIELD. At the time there were very few requests and very little IO, but the CPU was running between 85% and 100%.

App code or not (and I assume we're not talking 3rd party here), if things are this bad, that you have to take the production system offline, I don't know of many businesses that couldn't find a bit of time for some refactoring. It sounds like you need it.

3rd party (and 4th party) stuff. This software has a data layer that builds SQL on the fly. It creates temporary stored procedures (CREATE PROCEDURE #aabb001 @P1, @P2 AS .....) then it executes these with the necessary parameters. The customer does not want us to create stored procedures as they do not want to be vendor dependant. It is a nightmare to maintain and optimise.

Are those tables heaps or clusters?

Clusters

What's logical fragmentation?

I couldn't take a reading, but file growth is set at 1Gb per file. They are on a dedicated disk array on a SAN.

forwarding pointers? avg page space used? at the point that you need to do a 'rebuild'?

Not sure. I only checked index fragmentation which was 99% on one of the tables, but an index rebuild did not help much.

What version of SQL are you using? What edition?

SQL Server 2008 R2 (RTM) - 10.50.1600.1

p.s. How experienced are your DBAs?

My boss was working with me on the issue and he knows his SQL very well. Been around the block a few times. My DBA skills are ok but I'm primarily a developer.

I'm thinking when he does a load of data, the statistics go out of date, and "it grinds to a halt"? updating statistics right after the large load seems to be one of the first things i'd look at.

I did not think of statistics as Auto Create Stats and Auto Update Stats are both on.

If you can't change the design or application. then you could change the hardware... I have seen some good performance improvements from using SSDs on badly designed databases, though at over 1TB it will be expensive..

Too expensive for this client, besides the disks were completely underutilised yesterday.

Just a thought, do you drop the indexes prior to a large load of data and then rebuild same indexes afterwards?

Not at all.

Also, there may be a little bit of back-end tweaking that can be done, whilst using views to present the same logical output to the application. Not knowing the structure of the db, this is just a thought aired.

We currently have a partitioning proposal with the client as well as an archiving proposal. The archiving is difficult due to the applications data layer, but we want to implement distributed views with insert and update triggers.

Can you take us step by step through your 'large data load', including existing rowcounts, expected inclusion volume, number of tables, if you use staging tables to cleanse the data if there's any cleanup to be done, etc?

The first time this happened, in December, the client did an annual run that updated each and every member on the system. The update actually created a new version of each so there was a large number of new rows in a large number of tables, although I cannot give you numbers. Monday nights update was very different. We needed to add new properties to all the members. The strange thing was that my script to add the new properties was run two weeks ago with no ill effect. My script added the properties with a default value of NULL. The script added about 250,000 rows to 5 tables, so over a million new rows. On Monday evening their application, via the data layer, performed an update in a loop to set the value to a valid string. They manage to update 91,000 rows in about 10 hours before stopping the process. This is when the system croaked. Don't ask me why they decided to perform an update like this, it is very typical of the incompetence we have to deal with on a daily basis. I have since informed them that a SQL script could perform the update in a set in a matter of minutes, but that is besides the point.




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1086065
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse