Fastest inserts for audit log table
September 15, 2008 12:35 PM
How should I optimize a SQL Server 2000 table which will only ever be used for INSERTs?
The table is an audit log, so only INSERT operations. No UPDATE, DELETE, or SELECT. I expect the table will grow by ~50,000 rows per day, where each row is 4 integer columns.
The main issue I can't resolve (though if there are others I'm missing, please tell me) is whether to use a clustered index, or a non-indexed heap. And Google's just serving up conflicting advice.
This page (though it's for SQL 2005) concludes the benefits of a clustered index outweigh the costs. BUT in the INSERT test, they used a non-clustered index rather than just a non-indexed heap. The index must add a cost, yes?
I don't have access to a test server to try for myself, so I'm hoping a MeFite has been through this before.
The table is an audit log, so only INSERT operations. No UPDATE, DELETE, or SELECT. I expect the table will grow by ~50,000 rows per day, where each row is 4 integer columns.
The main issue I can't resolve (though if there are others I'm missing, please tell me) is whether to use a clustered index, or a non-indexed heap. And Google's just serving up conflicting advice.
This page (though it's for SQL 2005) concludes the benefits of a clustered index outweigh the costs. BUT in the INSERT test, they used a non-clustered index rather than just a non-indexed heap. The index must add a cost, yes?
I don't have access to a test server to try for myself, so I'm hoping a MeFite has been through this before.
A clustered index will definitely adversely affect performance on inserts, and that table's gonna get big fast, which will make it worse. Best to leave it out.
posted by farmdoggie at 12:57 PM on September 15, 2008
posted by farmdoggie at 12:57 PM on September 15, 2008
Nthing no indexes...but at some point there MUST be some form of a SELECT, right? If not...if you truly have data that no one will EVER look at or query...why store it?
posted by arniec at 12:59 PM on September 15, 2008
posted by arniec at 12:59 PM on September 15, 2008
Ya, "no indexes" is my reasoning too. But some of what I'm reading suggests that using a clustered index eliminates page splits, where inserting into a heap might result in fragmentation, and INSERTs will end up taking longer because SQL Server has to search for an empty page each time.
I can't imagine how a table with no DELETEs could ever become fragmented, but then SQL Server has proven itself "smarter" than me on several occasions.
> why store it?
Heh, ever worked for a bank? I think the table is just CYA ... "We'll never need to look at it, but save it just in case."
posted by ParsonWreck at 1:09 PM on September 15, 2008
I can't imagine how a table with no DELETEs could ever become fragmented, but then SQL Server has proven itself "smarter" than me on several occasions.
> why store it?
Heh, ever worked for a bank? I think the table is just CYA ... "We'll never need to look at it, but save it just in case."
posted by ParsonWreck at 1:09 PM on September 15, 2008
re: why store it?
Who questions the managerial gods? One of my old contracts was managing a system with log tables like these which were truncated every two weeks - just in order to have those at the ready and indexable in case of rapid troubleshooty demand.
posted by grippycat at 1:11 PM on September 15, 2008
Who questions the managerial gods? One of my old contracts was managing a system with log tables like these which were truncated every two weeks - just in order to have those at the ready and indexable in case of rapid troubleshooty demand.
posted by grippycat at 1:11 PM on September 15, 2008
On preview: you might be thinking of index page splits (and yup, if you're doing massive inserts into an indexed table and a new index row fills the page, the page will split) - that's another vote for me in favour of no indexes.
posted by grippycat at 1:21 PM on September 15, 2008
posted by grippycat at 1:21 PM on September 15, 2008
Don't put any indexes. In the case that you do have to perform a CYA and reproduce the log, you can add an index later just for that query if absolutely necessary.
Also remember that if you put any kind of primary key on the table it will create an index by default, so you might want to forego any keys on the table as well.
posted by sherlockt at 2:04 PM on September 15, 2008
Also remember that if you put any kind of primary key on the table it will create an index by default, so you might want to forego any keys on the table as well.
posted by sherlockt at 2:04 PM on September 15, 2008
No indexes, and pre-define the table size large enough to accomodate all the data you plan to put in the table (this will save SQL Server the overhead of "growing" the data file, and the consequent fragmentation).
However, you might want to consider just leaving a text log file. They save the DB engine the task of storing the data in highly-structured storage. Plus, with text logs, they are easy to trim, easy to back up, can be easily imported into a database if you need to do sophisticated analysis, and can be configured to use an independent drive, so they impact your database only minimally.
You can feed the data to the text file with an (extended) stored procedure -- if you use buffered writes, the impact on the database will be well-neigh negligible.
posted by curtm at 7:49 PM on September 15, 2008
However, you might want to consider just leaving a text log file. They save the DB engine the task of storing the data in highly-structured storage. Plus, with text logs, they are easy to trim, easy to back up, can be easily imported into a database if you need to do sophisticated analysis, and can be configured to use an independent drive, so they impact your database only minimally.
You can feed the data to the text file with an (extended) stored procedure -- if you use buffered writes, the impact on the database will be well-neigh negligible.
posted by curtm at 7:49 PM on September 15, 2008
Agreeing with curtm, that it's overkill to put this data into a table if it's just going to be stored — just write it to a text file. (Except, of course, that would look funny to management, and it could adversely affect your next bonus. But then, so would a table without a primary key or indexes.)
posted by exphysicist345 at 11:10 PM on September 15, 2008
posted by exphysicist345 at 11:10 PM on September 15, 2008
« Older Help me find information that Diane Sawyer... | Why would users who have appropriate permissions... Newer »
This thread is closed to new comments.
posted by grippycat at 12:46 PM on September 15, 2008