SQL Server Performance

What's all this new TempDB I/O?

Discussion in 'SQL Server 2008 Performance Tuning for DBAs' started by looselyhuman, Jul 27, 2011.

  1. looselyhuman New Member

    Hello,

    On 7/18 we enabled "pull" Transactional Replication for a large production database, in part because we had a reporting database on the same server - using cross-db queries and huge temp tables to pull data from large production tables - and just killing us on TempDB I/O. It's a cluster environment with a single SAN device with seperate LUNs for data and logs. TempDB is not isolated.

    On 7/23 we moved the reporting database off of the cluster, on to the replication subscriber hardware, so it can do all of its insane queries against the production DB replica on that hardware.

    I've been monitoring IO stall on TempDB for a while. I have a process that collects and stores statistics every ten minutes and aggregates them each day. I use an Excel Pivot Chart to analyze this info. On 7/18, after replication was enabled and the snapshots processed, I expected to see a big hit, but it wasn't so bad. But then, after 7/23 (Monday morning 7/25 to be exact) everything hit the fan. I've attached a graph that explains it better than I can.

    [IMG]

    I need to figure out what's causing all this TempDB thrashing. I would assume replication but it's been running unchanged since 7/18. Nothing else has changed that I know of, including the users/business operations which are normal compared to the last several weeks.

    Any insight/advice appreciated. Please just let me know what other information would be useful

    --ian

    EDIT: Environment = Windows 2k8 R2 x64, SQL 2008 SP1 (subscriber is R2, publisher is not)
  2. looselyhuman New Member

    Nothing? Oh well.

    Maybe if I ask a simpler question?

    There's lots of documentation on getting IO stats (i.e. stall) for TempDB, but none that I can find for getting the queries, connections or processes that account for that IO. Any ideas on that?
  3. satya Moderator

Share This Page