July 28, 2003 at 11:32 am
We have a OLTP DB which has over to 400k transactions divided over several users. Some of the users have been deactivated. Would it improve performance if the transactions for the inactive users would be deleted? Would it in any way affect existing indexes or keys? Or would it be better to leave the users data in the DB. The inactive users would have approx 10-15% of the transactions.
July 28, 2003 at 11:45 am
Take the old stuff out and into an OLAP or mixed OLTP/OLAP database. It should speed things up a little, but only if the active user transactions are mixed in with the inatcive user transactions.
If the table is keyed on an IDENTITY or DATETIME field, however, with constantly increasing values where the inatcive transactions are all in the beginning data pages, you won't really notice any difference since these pages haven't needed to be spooled to the cache anyway and SQL Server has essentially been ignoring them.
Just remember to reindex or DBCC INDEXDEFRAG <table> after you've deleted 10-15% of the table's data.
July 28, 2003 at 12:04 pm
The active users are mixed in with the inactive. Also the table is keyed on the Identity field.
Are there any articles that provide information on the working of SQL Server.
Thank-you.
July 28, 2003 at 12:07 pm
Specifically, what do you mean by "working of SQL Server". If you are talking about the internals of SQL's storage and retrieval engine, or the query optimizer internals, I would highly suggest Kalen Delaney's Inside SQL Server (MS Press).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply