Slow performance from Huge table

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)

Slow performance from Huge table

Author

Topic

jbrown7232
Starting Member

22 Posts

Posted - 2010-05-13 : 11:20:50

Hello,

I have a table that has 3 columns and the last column is TransactionNO.

This table has over 10 Million rows.

When the table is read/written to I get locks and blocks ocassionaly

It is properly indexed.

What can I do to increase the performance and avoid locks and blocks?

Thanks

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-05-13 : 11:36:19

1. partitioning
2. archiving
3. new hardware -disks, cpu, memory
4. examine other database activity to see ifthe problem is elsewhere
5. upgrade network cards in case the problem is there.
6. use Stored Procedures to beefit from cache (and security features)

my reply is generic, because your starting description is too vague.

please be more specific especially in terms of current performance benchmarks and state what is acceptable/desirable given the predicted/actual server load.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-13 : 11:56:33

quote:
Originally posted by jbrown7232

Hello,

I have a table that has 3 columns and the last column is TransactionNO.

Is that a key column? How selective is it? Why is it important that you mention this column?

quote:

This table has over 10 Million rows.

When the table is read/written to I get locks and blocks ocassionaly

What kind of reading writing? single entries -- updating lots of rows at once? etc. etc.

quote:

It is properly indexed.

Are you sure? Are you sure that the queries causing the blocking are actually using your 'proper' indexes?

quote:

What can I do to increase the performance and avoid locks and blocks?

Need specifics. This is a very general question.

Some general things to look for first:

Find the queries / updates that are causing the blocking. Analyse their query plans / check them for poor code

Are reads blocking writes and visa versa? if so you may see a benefit if you use the ISOLATION LEVEL READ_COMMITTED_SNAPSHOT

Apart from that then what AndrewMurphy said. I'm guessing though that there will be a bad query/index/design at the root of this

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-05-13 : 12:07:58

If the Blocking is caused by Reads then READ_COMMITTED_SNAPSHOT may solve the problem.

Make sure you aren't using / aren't tempted to use! NOLOCK hints anywhere (that's often the first port-of-call for DEVs, and they rarely understand the potentially disastrous side effects.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources