Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hc289
Starting Member
2 Posts |
 Posted - 2010-06-15 : 04:18:42
|
| Hello all - my first post!I'll try to be as concise as possible about my issue:Layout:- I have a moderately sized database for a 'jobs' driven system i.e. a job gets created, fulfilled and 'sent to consumer' that totals around 2GB- Of that 2GB about 1.5GB is a 'history' table that contains all our old jobs (we've got lots of jobs!)Problem:- We've recently moved from an old to a new cluster with the same software (just better hardware) and we now see regular database 'freezes'. By this I mean that applications using the database execute queries that timeout (after about a minute) - these queries should execute in sub-second times, they're usually just selects or stored procedures that only do selects etc. - nothing crazy.- This occurs at all times of day, i've had it first thing in the morning, late at night etc.Attempted Solution:- We noticed there was no maintenance plan on our new cluster (apart from nightly backup) and there was on our old cluster so i've added a maintenance task to run daily that does 'check db integrity' -> 'reorganize index' -> 'update statistics' - I gather these are popular/the most useful maintenance tasks to perform. - After running these maintenance tasks we often see considerable performance improvements. We also see good improvements after just restarting the SQL server - but that's not something we really want to do on a three times a week basis!- Even with the daily maintenance plan, the problem has recurred - i'd love to figure out how to find the source so I can target a proper solution - any suggestions? hints? tips?Many thanks,HC |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-15 : 06:23:32
|
| Database automatic size extension set to percentage, rather than fixed MB size?TLog not being backed up regularly?Memory not limited for SQL Server? (should allow 1GB of memory [from memory, but needs checking] reserved per CPU on a dedicated server for the O/S - give SQL all the rest) |
 |
|
|
hc289
Starting Member
2 Posts |
Posted - 2010-06-16 : 04:00:03
|
| It appears a user is locking up the entire DB with one query, hence the sporadic nature of these issues - waiting for it to recur so we can profile! |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-06-16 : 05:02:45
|
| Are your tables properly indexed (for the type of workload your server is executing)?Do your execution plans include SCANS?Do you use Stored Procedures?Could it be that "by accident" your database was "performing" despite some structural handicaps, and this stray user is now activating a hole in your performance plan? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-16 : 05:36:25
|
| "Could it be that "by accident" your database was "performing" despite some structural handicaps, and this stray user is now activating a hole in your performance plan?"</stolen>... and added to my "bullshit baffles brains" list of cracking excuses to give to clients! |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-06-16 : 10:26:08
|
| 2 GB is not a big database at all.Give your server specs.. |
|
|
|
|
|
|
|
|
Server 'Freezes' with a large database