Index on Write only table?

Question

Index on Write only table?

Richard Paterson

Valued Member

Points: 63
More actions
March 17, 2004 at 4:34 am

#110655

Hi
I have a table that I use for user auditing with over 1 million rows. It currently has no primary key or indexes. It in only ever inserted into (1 row at a time). Occasionally I get timeouts on the inserts into this table, and following this timeouts on many other unrelated tables. Its important to note that the insert into this table is not part of a larger transaction that interacts with other tables. The only link is that they are in the same DB. The way to resolve this is to truncate the user auditing table.
So 2 questions.
1) what causes timeouts when inserting into tables that are not selected from or updated (does not having a primary key affect this - if so why?)
2) Why would other tables in the DB that are not related to this one timeout on selects / inserts and updates when this on is having problems.

When the timeouts occur, memory etc on the DB server appear normal.
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

James Luetkehoelter SSCommitted Points: 1733 More actions · Answer 1

The first thing I would do is throw a clustered index on the table, probably by a primary key if you're simply inserting. Right now the table is organized as a "heap". A clustered index will literally organize the data in the order of that index.

Richard Paterson Valued Member Points: 63 More actions · Answer 2

This was my next plan of action but I don't understand why this would help the inserts - would a clustered index not normally slow an insert down (while improving a select)?

Thanks

James Luetkehoelter SSCommitted Points: 1733 More actions · Answer 3

A clustered index would mean that SQL knows exactly what data page to write the record. It doesn't have to search for room and such. For sequential writing, it can help. Give it a shot, and see if your performance improves.

ray_higdon SSCarpal Tunnel Points: 4589 More actions · Answer 4

To add to James's post, watch where you place that clustered index, placed on the wrong type of column and you will get additional pagesplits as SQL tries to insert the data into the physical order of the table, if you have a column with the identity property, having a clustered index on this will create a hot spot on the disk which will improve inserts.

If you have deletes on the table, not having a clustered index will really hurt you as heaps reclaim empty space.

HTH

------------

Ray Higdon MCSE, MCDBA, CCNA

Richard Paterson Valued Member Points: 63 More actions · Answer 5

Hi

Thanks for that.

Do you know why the other unreleated SP's were timing out when this one started to?

jmyrna SSC Enthusiast Points: 127 More actions · Answer 6

Could it have anything to do with the database having to grow to accomodate the insert into the audit table, and all other inserts queueing up behind that. It the auto grow is set to 10% and the database is large, it can take a while to expand the database.

Richard Paterson Valued Member Points: 63 More actions · Answer 7

Ahh - hadn't thought of that. The DB is over 20G. Is this is addition to not having the primary key or as well as?

Should SQL Server not be able to cope with growing DB's better than that?

Thanks

ray_higdon SSCarpal Tunnel Points: 4589 More actions · Answer 8

Make sure you do not have auto-shrink on as it will lead to this problem. The percentage of MB growth depends on your level of need, if your db is consistently growing by 10% you may want to do some preemptive alter database commands to grow the footprint

------------

Ray Higdon MCSE, MCDBA, CCNA