June 26, 2012 at 1:13 pm
I have SQL Server 2008 R2 setup as an OLAP system. I run the database engine along with SSIS, SSIS, and SSRS. I have a basic ETL process that runs at night which writes approximately 200,000 records. After rebooting the server in the afternoon and then running a SELECT @@TOTAL_WRITE the next morning, I have over 1.5 million writes. I have tried running SQL traces to determine what the culprit is with no luck. What exactly causes the increment to @@TOTAL_WRITE? Does this include multiple writes to individual disks in the Raid 5 config? Just trying to determine why the system is doing 7x the writes than I would expect. Any light shed on this would be greatly appreciated.
Thanks
June 26, 2012 at 2:15 pm
This is the number of write operations that SQL Server has sent to the Windows API for writing to disk. As such, it is unaware of your RAID level and any write cache in Windows, storage controller or elsewhere.
When you estimate the number of writes expected, remember to include the transaction log, indexes, full text indexing, triggers inserting into tables and anything causing writes in tempdb (cursors, temp tables, temp variables, large objects, sorts, hash tables).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply