SQL Server Log Page Views

I currently have a table setup to record every page my users see along with some session information, used for later analysis.

The problem is that the Logging table is getting huge and causing maintenance issues and deadlocks. What is the best alternative to this approach?

Here are a couple of ideas, but I am totally open to any advice. I will need the end result in SQL Server, with many session variables saved.

1. Customize IIS Logs to save the logs with session variables, then import the log files into SQL Server nightly. Using Response.AppendToLog in ASP. I think the downside here is that I am limited in how much I can append.

2. Create a holding table for daily logs, then push them to the main Logging table nightly, and wipe the holding table. I am not sure if this will use as much resources as what I am doing currently.

3. Use a third party like Google Analytics. The problem here is that user session information is sensitive and I am not sure how much if google allows me to pass variables to them. And I may need to encrypt the data before I send, then decrypt it to get it back to SQL Server.

4. Write straight to text files, then import nightly. I guess DISK I/O would be just as high with this approach.

The websites are primarily written in classic ASP and ASP.net. What's the optimal approach?

开发者_如何转开发Thanks for the help.

---

5 - Normalize your SQL logging tables. I'm certain there is a lot of information you are duplicating (i.e. "session information"). For each new session, create a new record in your "Sessions" table, and your "SessionDetail" table records specific information about pages viewed, etc.

You really shouldn't be getting deadlocks on this.

Your option #2 should work OK as well if you design it right, but it's going to depend on how you are using the data. If you NEVER update your historical (i.e. before today) logs, I would make a separate DB for those and have your write-intensive active logging tables in a separate DB on a separate drive. Another option related to this is to create several logging DBs to distribute disk I/O. If you have several platforms/servers you are monitoring, you can have them all in different DBs and use a view to see them all at once.