big table, 20 million rows, used as a usage log, to partition or not?

Last Post 15 Oct 2010 01:23 PM by gunneyk. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

15 Oct 2010 08:34 AM
Hi. We have a table used for storing live usage stats called tblUsageLog. So, inserts are occuring quite often.
It now has 20 million records for about 5 years of activity.
It is used for some reports.
It has 3 columns indexed besides the PK in order to aid the speed of the reports. I'm that isn't helping with the inserts.
The reports are not hit often. They are rarely used but need to be available.
Several app pages contain code to do an insert into tblUsageLog.

We've been getting deadlocks lately and I'd like to figure out how to reduce them.
There isn't any obvious code to revise.
I'm thinking that if I were to purge the table, archive a couple years that would do it.
Could I partition this table on year basically and have that table behave like it was one fifth it's size in generalized terms?
New Member
New Member

15 Oct 2010 09:30 AM
I think there are a few things you could do. You could add history tables where you run jobs periodically to move older data so that your active tables are more manageable. In your report just change it to pull from both tables and do a union. You could also do something similar with table partitioning I believe.
New Member
New Member

15 Oct 2010 01:20 PM
Thanks yeah that's what I ended up doing. Looking at the error logs if it's not too early to tell, no more deadlocks. I turned on a trace to tell prior and the trace is still running.

I moved all records with   year(createdatetime) < 2010    to the archive table.
That still leaves 9 million in the current table but I can move more if necessary.
Breaking on year seems tidy.
Created a view which unions the twoand call that in appropriate sps.
Eventually I'll bypass the view and use a nested select just to avoid the dependancy on the view object.

Thanks for your input, it makes me feel better about having chosen this path.
I avoided a true partitioned table because it looks overly complex.
I should say, the current table remains indexed on several columns and the archive table is also indexed on those columns.
New Member
New Member

15 Oct 2010 01:23 PM
Partitioning can certianly help if you want to archive or remove older data. As for the deadlocks you can turn on trace flag 1222 to capture info on the deadlocks to see where to start. BooksOnLine has a lot of good info on troubleshooting and detecting deadlocks that you should have a look at. Most deadlocks are a result of two users (apps etc.) trying to update the same set of tables in opposite orders.

Acceptable Use Policy