7

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:

  1. Working database with only the most recent 24 hours worth of records
  2. 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

migrated from stackoverflow.com Dec 21 '15 at 6:05

This question came from our site for professional and enthusiast programmers.

  • 3
    What indices (INDEXes) exist on tb_applicationusage? – TT. Dec 19 '15 at 10:41
  • 1
    Can you give us a sample query plan and the table definition? Also the sample is a select statement not an update. It is generally better to give us the exact query that is causing the problem instead of a similar one. Sometimes the devil is in the details and your sample doesn't exhibit the same slow performance. – Erik Jan 5 '16 at 0:21
5

You can do better than two databases. There are two things you should look at in your existing database before sharding off part of the old data:

  1. Choose a good clustered index. There are three rules you should follow for the clustered index to work well with this data:

    1. It should use an increasing value, so that new records will always belong at the end of the table in cluster order, or at least in the last page. This is especially important when you have lots of inserts, as in this case. Something like an identity/autoincrement field, but you'll see in a moment why we can do better.
    2. It should uniquely or nearly-uniquely identify the record, so the updates for the application close records will be fast.
    3. You should be able to know the clustered index based on the application close records coming into the DB (this rules out the identity column from earlier).
    4. You don't want anything changed by the application close records to be part of the index, as that could force the database to need to move the record to a new location on the disk when you have updates.

    If there is an increasing timestamp (ie: f_starttime), that may good for the first field in the index, as long as it's also part of the close record as indicated in requirement #3. Add any other fields that you'll need to uniquely or nearly-uniquely identify a record. Note that you can still use an identity column for the table. Just don't use it as the first column in the clustered index. Based the sql code in the question, I might go with f_starttime, f_computername, f_application, f_ID.

    Even if you go with the staging table suggested in the other answer, these index changes may still be a good idea.

  2. Table partitioning. Table partitioning helps the db server keep only the recent records in memory, so that older data from the same table can remain on disk. Sql Server 2016 will even let you push the historical data to cloud storage on Azure via Stretch Database.

The other suggestion to keep completed records separate from open records is good, too. Even with that suggest, though, indexing and table partitioning can help as the size of the table for completed records becomes large. You can start looking at sharding old data to a separate (linked) db only after all of these options fail.

Really, though, Sql Server is easily able to handle six million-ish records without resorting to these kinds of tricks (changing the index may still be worth doing, though). Are you sure the server is correctly provisioned for this? You might do just as well simply adding RAM to the server.

Finally, separating a reporting database from the live processing database is also common, and not at all a bad thing to do. We sometimes call this a "Data Warehouse", though that also often involves schema changes and an SSIS process to move the data. This is a good feature to have, because it prevents an accidental mistake in a data analysis query from causing performance problems in production. You can best accomplish this via database mirroring/log shipping to a read-only slave, or more recently via an AlwaysOn Availability Group.

  • Thank you for the very detailed reply. @Hogan below also mentioned indexing, but this is not a concept I'm familiar with. "Indexable" is set to "Yes" for each field in the database, but I don't know if that is the same thing you are talking about. I do have an ID column that is otherwise unused. Current Table Layout: i.imgur.com/c4sNaUy.jpg. What is the optimal method (do you have a link) to enable this clustered index? – Beems Dec 19 '15 at 15:48
  • Indexable to yes is definitely not the same thing. I'd make sure the clustered index (primary key) on the table uses the f_starttime, f_computername, f_application, f_endtime, f_ID fields, similar to what I suggested in the answer, and make sure to put the columns in the index in that order. – Joel Coehoorn Dec 19 '15 at 17:24
  • Thank you for the help thus far. I've been reading as much as I can regarding your suggestions. I have the columns in the index as you stated, so my last questions are: Should the "Is Unique" option be set as "Yes" and should the "Create as Clustered" option be set to "Yes" as well? Screenshot of settings: imgur.com/uQ3fF31.jpg – Beems Jan 4 '16 at 22:11
  • You do want "Is Clustered" to be "yes" here. "Is Unique" should be yes only if you can guarantee those columns will always refer to a specific record. If it's even remotely possible for two instances of the same app on the same computer to report the same start time, you'll either need a way to tell the records apart or you'll need to set "Is Unique" to "No". Typically, you do want your clustered indexes to be unique; you want to set this to "Yes" if you can. However, it's not required, and collisions in this case should be rare enough to avoid problems if you are unable to guarantee it – Joel Coehoorn Jan 4 '16 at 22:16
  • 2
    If this helped, I'd really like to hear some before and after numbers. – Joel Coehoorn Jan 5 '16 at 19:06
6

Two things

  1. 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.

  2. 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

  • Thank you for taking the time to respond. Forgive me, as before when I said "I'm no SQL guru", I should have clarified and said "I use SQL on occasion and frankly I'm not sure how I've gotten as far as I have". I'm not familiar with the concept of an index. In searching the Internet before posting, the information returned is quite voluminous. Do you have an article that you can point me to that would describe indexing and how to add it to an existing table/database? EDIT: I went through each field in the table and noted that "Indexable" was set to "Yes". Is that what you were looking for? – Beems Dec 19 '15 at 15:37
2

I think a filtered index would fit the bill quite nicely. Based on your comments to the other answers it looks like a sample create index statement would be useful to you. The create index statement would look something like:

CREATE NONCLUSTERED INDEX <index name> ON 
   <schema>.tb_applicationusage(f_application, f_computername, f_starttime) INCLUDE (f_ID)
   WHERE f_endtime IS NULL;

It would be nice if you could put a UNIQUE constraint on that index but I doubt you'll be able to get away with that because invariably something will happen that will prevent a normal log-out/shutdown of the application. Of course you should replace <index name> with an appropriate name and <schema> with the table's schema (probably dbo). I only added the INCLUDE statement because it was in your sample query. If you find that you don't need that then feel free to drop it.

Your update statement as it is written isn't an update but a select. Here is a sample of the full query written as an update:

WITH LastLogin_CTE AS 
(
  SELECT TOP 1
    * -- Generally avoid * notation but I used it here because we're just driving an update
  FROM tb_applicationusage 
  WHERE 
    f_application = @ApplicationName -- Parameterize the name
    AND 
    f_computername = @ComputerName -- Parameterize the name 
    AND 
    f_endtime IS NULL 
  ORDER BY
    f_starttime DESC
)
UPDATE LastLogin_CTE
SET
  f_endtime = SYSUTCDATETIME();

This update will of course leave orphaned rows that were never logged out. My guess is that is what the monthly report is designed to detect.


Parting thoughts:

  • You should probably avoid the tb_, f_, and other prefixes if possible. They just add noise to your object names that make it harder to read and doesn't seem to be industry best practice.
  • On performance issues you should really include a query plan in your question. That will help us spot missing indexes and provide more useful information.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.