SQL Server: Using Dual Databases for Performance?

We have a SQL database that stores application usage logs for about 3000 PCs. These PCs send their usage data to the SQL server around 10-20 times per day. We used to store only the most recent 60 days of application usage, but the customer asked us to no longer purge data. Now that we have about a year's worth of data (about 6,000,000 rows), the SQL database is suffering from some performance issues. Not significant, mind you, but far more than any other database we have. There are a significant number of records added each hour (application open records), and within a few hours at most that record will be updated just once with the associated application close. It is these updates that you can see via SQL Activity Monitor that are taking considerable time to complete.

That UPDATE query is simple:

SELECT TOP 1 f_ID 
from tb_applicationusage 
WHERE f_application = 'xxxxxxx' AND 
      f_computername = 'xxxxxxxxx' AND 
      f_endtime IS NULL 
ORDER BY f_starttime DESC

Effectively, it finds the most recent matching application start for a specific machine that doesn't yet have an associated application close. I can't think of a more efficient way to run the query, so I'm considering the following alternative:

Move to two databases:

• Working database with only the most recent 24 hours worth of records

• Final database with all other records

I'm no SQL guru, so I'm probably missing some drawbacks of this method. The goal would be to just have a SQL Agent job move the completed records over to the final database every night. Then, when the customer wants to run their monthly reports, I can just have that report query only the final database and not the working database. With only maybe 10,000 records to query in the working database instead of 6,000,000 it would seem logical that it would work faster. But again, it seems so simple I'm probably missing something obvious.

Version: Microsoft SQL Server 2008 R2

Two things

-
You don't actually say you have an index on the table -- I expect this would just solve your problem. An index on f_application, f_computername, f_endtime, f_starttime should make your update time tiny with only 6mill records.

-
If you want to split it up, don't do it the way you describe, create a table for open but not closed records that you use before your current table. Then when something "updates" delete it from the that staging table and insert it in your big table. Using a staging table in this way is considered a leading/best practice - having a table arbitrarily split in two is always a nightmare to work with

StackExchange Database Administrators Q#124204, answer score: 6