Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2003-06-15 : 15:39:33
|
| Looking at profiler and perfmon, I see that a good deal of the work the SQL server is doing is related to very frequent, fairly smallish writes. The bulk of them are logging for a web app's pageviews.The log table is very small, has only one nonclustered index, and the SP and app for logging are very optimized. But it just gets banged on so often that SQL server is spending a fair amount of time on it.So I'm considering some way to do the log inserts in batches rather than one by one. This being the first time I've looked at doing something like this, I'd love any advice people have. The way I see it, I've got a couple of choices- Use a global temp table or a staging table for the inserts, and then do a insert/select to copy them into the real log table periodically. The advtage here would be that there wouldn't be an index on the temp/staging table; it would just be a heap, and then all of the index work would be done in batches during the copy. I'm not sure if that's an improvement of not.- Do the batchification application-side; populate an array stored as an application variable (it's an ASP app), and then have some periodic process to submit the batch. The issue I see here is the serialization of access to the application variable when the insert is going on.- Write some kind of component that takes the logging data, stores it in memory, then periodically writes it in batches to the DB asynchronously. Of course, this is the most difficult and scary one, since it could destabilize the application (as if a huge ASP app needs any help with that!).I'd love any thoughts on those approaches, or if there's some other approach that makes sense (MMQ? something else?)The data does *not* have to get into the DB in a timely manner, it just has to get there eventually. It doesn't even have to be 100% reliable, since it's used for statistical analysis. Thanks in advance-b |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-06-15 : 15:55:45
|
| I don't think having a staging area is a good approach, because all you're really doing is adding MORE writes to the process. Getting rid of the index might be a good idea, you can always create one if needed during a query and drop it afterwards.Or, if you don't want to drop the index, but you have multiple disks available, add a filegroup on a drive separate from the data, and put the index(es) on that filegroup. That way the write operations can happen in parallel.Ultimately though, regular log files are the best way, especially if it's web page info, the web server keeps those logs automatically anyway. You're probably better off importing those log files periodically and running queries when needed. |
 |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2003-06-15 : 16:11:01
|
| Problem is, the log data includes a number of things that are relational to the database (user ID, etc). And the stats need to be current to within the hour so that editorial decisions can be made based on traffic. I suppose I could get into making IIS do a custom log format, and then do periodic imports, but it's ugly.I'm still on a quest to optimize the DB solution rather than move to files/imports, but I do appreciate your advice.Thanks-b |
|
|
|
|
|
|
|
|
Strategies for caching OLTP writes