Bug: sys.dm_db_session_space_usage Reporting Extremely High Tempdb Allocations

A little more than a year ago while working at Idera, I was consulted on reports from a few clients using the Idera monitoring tool SQL diagnostic manager (SQLdm). SQLdm was reporting very high numbers for internal object allocations (internal_objects_alloc_page_count) and very low numbers for internal object deallocations (internal_objects_dealloc_page_count) to the point that SQLdm showed that individual sessions were reported using more space than was available in the database. I worked with Vicky Harp (blog|@vickyharp), SQLdm dev lead and an
read more

Day 14 of 31 Days of Disaster Recovery: Fixing a Corrupt Tempdb

Welcome to day 14 of my 31 Days of Disaster Recovery series. I’ve previously discussed handling corruption for nonclustered indexes and allocation pages. Today, I’m going to talk about a specific corruption that is very simple to fix; however, it does mean taking the server offline briefly. How brief depends on a few things, such as is the corruption in the log file or the data file. And if it’s in the data file, is Instant File Initialization (IFI) enabled.
read more

Tempdb Contention That Can’t be Soothed

Tempdb Contention That Can’t be Soothed By Adding Files I’ve talked a lot in the past about tempdb contention and how to monitor for and identify it in blog posts, a whitepaper, webcasts, and live presentations. Okay, so I tend to get a little preachy about certain things and configuring tempdb to prevent tempdb contention is one of my top pet projects. But there’s some common types of tempdb contention that can’t be fixed by adding data files. i got
read more

Recent Presentation Session Files

Recent Presentation Session Files I’ve been a little tardy about getting my session files posted, and I’m a little behind. So I’m going to try to catch up in one blog post. So here are my session files for SQL PASS Summit 2012, various recent SQL Saturdays, and 24 Hours of PASSe. Please let me know if I’m missing anything I promised to get posted. SQL PASS Summit 2012 Lightning Talk: Man Vs Machine: ManVMachine.zip (599 KB) Regular Session: Tempdb:
read more

SQL Rally 2012 Recap and Session Files

SQL Rally 2012 Recap and Session Files SQL Rally 2012 was hosted by the North Texas SQL Server User Group. More tha anything, it was nice to be back in the area where I grew up. I was born in Houston, but I grew up in the Dallas/Ft. Worth area. It really brought back a lot of great memories for me like canoeing on Lake Grapevine or swimming at Lake Lewisville. I remembered fondly when the NFL banned spiking the
read more

Whitepaper: Demystify Tempdb Performance & Management

Whitepaper: Demystify Tempdb Performance & Management There are many misconceptions and myths about tempdb and purported best practices are inconsistent at best. It’s hard to know which advice to follow when one resource says to always do it one way and another tells you to always do it the opposite way. Many times, both resources are correct in certain situations or to a certain degree. Part of the problem is that rarely is there a single correct solution to any
read more

Breaking Down TempDB Contention (part 2)

Breaking Down TempDB Contention (part 2) I wrote a somewhat popular script and blog post a while back called Breaking Down TempDB Contention. This post explains how to identify tempdb contention. This past Friday, Jonathan Kehayias (blog|@SQLPoolboy) contacted me about the script. Jonathan said that he thought the math was off just a little bit in the script. The original script has the PFS pages repeating every 8088 pages and the GAM and SGAM pages repeating every 511232 pages. That
read more

Secrets of SQL Server Webcast: TempDB: Performance and Manageability

Secrets of SQL Server Webcast: TempDB: Performance and Manageability Register today for my Secrets of SQL Server webcast on July 13th. TempDB: Performance and Manageability Misconceptions and inconsistent advice on tempDB abounds. Even highly skilled DBAs cannot always agree on how to best configure tempDB. Part of the problem is that there is no single solution that can be applied to every situation. How many files should tempDB have? Should it be on RAID 1? RAID 10? How about Solid
read more

Idera ACE Program Members Announced

Idera ACE Program Members Announced Idera announced the lineup for their new ACE (Advisors & Community Educators) Program today, and I’m excited to be on of the Idera ACEs. The new Idera ACEs are established members of the SQL community who are already educators and advisors through various programs. Idera is enabling the ACEs to take an even bigger role in the SQL community and fostering our participation in even more SQL events. Below is a brief excerpt of what
read more

New Whitepaper: Performance Evaluation of Hosting TempDB on FusionIO

New Whitepaper: Performance Evaluation of Hosting TempDB on FusionIO A colleague I work with on the PQO* Operations SQL V-Team, Jonathan Foster, has authored and published a whitepaper on using FusionIO SSD drives for tempDB performance in SQL Server. I served as technical reviewer and Jimmy May (blog|@aspiringgeek) from Microsoft’s SQL Cat Team (blog|@SQLCat) and Ken Hughes from FusionIO provided highly beneficial technical advice. Andre Ciabattari and John Yi from the PQO Operations Infrastructure/Lab team provided technical excellence with installing
read more