What’s all this new TempDB I/O? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

What’s all this new TempDB I/O?

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)
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?
Welcome to the forums.
Did you check Paul’s blog here – http://sqlkpi.com/BLOGS/PAUL/category/tempdb.aspx

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |