Database is freezing under high workload

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Database is freezing under high workload

Author

Topic

kakuke
Starting Member

1 Post

Posted - 2009-06-19 : 05:02:09

Hello all.

Can someone please help me with the problem that is driving me crazy.

I have lots of connections (~100) from a VB6 application to ms sql 2005 standard edition. Sometimes (when lots of the connections are active) the sql server just stops processing other queries. There is also a website using the same database and it stops getting data from the database. The sql server is showing 0 - 10% cpu usage when this happens so it must be a configuration problem?

I had the same problem before when the application was using ms sql 2005 desktop engine and I saw in the error log the error about exceeding 8 concurrent connections so I decided to upgrade to standard edition but the problem hasn't gone away, now I just don't get the error.

Thanks for any replies!

SQLRatankalwa
Starting Member

42 Posts

Posted - 2009-06-22 : 08:46:55

1. Make sure that the connections are closed after each operation.
2. Make use of Connection pooling.
3. Increase the memory on your server.

The 2 options above will help you to fix this problem.

Ratan Kalwa
SQL Server Professionals User Group

http://www.revalsys.com

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-06-22 : 10:44:10

I've seen something similar when the server runs out of memory. You might need to throw some more RAM at the machine.

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources