Question about a large table

[brownie124]

Hi,

Would this be a bad idea:

A table that could have in upwards of 25,000,000 records. There are 4 fields numeric fields in the table and the record length is 14 bytes. The primary key is 12 bytes. This table would not be modified during the day. There would be a process at night that would modify it. The process could delete some stale records, as many as 2000 a night. It could also add that many back in. Initially the table would be hit a lot during the day, but eventually, the data that would be retrieved would be stored in a local cache on each users system. The cache would only need to get updated if the table data had changed for that particular user, requiring a trip to the server.

I guess I am wondering if the performance would be bad, would the nightly deletion of this many records cause too much disk fragmenation, etc.

Any insight here would be much appreciated.

Thanks,
- Michael

 

[TheBugSlayer]

Your table is relatively big but SQL Server still can handles that pretty comfortably. 2000 records deleted/added is no big deal either. Your table is approximately 350Mb.

If the table is one which receives a lot of updates you might consider creating it in a separate file with a file group that resides on a disk of its own. Carefully consider creating another index on the table also, given that the primary key is already one. Whenever possible use stored procedures to wrap statements that interact with the tables, among the advantages it reduces network traffic as all the processing is done in one batch at the server and procedures are optimized and exec plans kept in memory for reuse.

As part of the nightly procedures you could rebuild indexes (DBCC DBREINDEX) and fix fragmentation. You may want to assess fragmentatio before you consider including the procedure (DBCC SHOWCONTIG).