Dallas Convention Center

The Highlights

Other than being back in my old home town, the highlights for me at SQLRally were the engagmeent of the attendees and spending time with SQL friends that I don’t normally get to see in person.

I gave my first pre-con session at SQL Rally along with my good friend and fellow Certified Master Argenis Fernandez (blog|@DBArgenis). The session, Demystifying Database Administration Best Practices, was Argenis’ concept and I was honored to be included as co-presenter. We had a full room of very engaged participants. There was a lot of discussion and back-ad-forth during the pre-con session and during my regular session.

I also got to see a lot of my SQL friends at SQLRally. The evening events were lots of fun as well. The entire place was singing along with the SQL Karaoke performances. I had a great time talking to David Stein (blog|@Made2Mentor) between sessions. Met some new people when I was hanging out at the Idera booth and talked about the Certified Master program, data compression, etc.

Room for Improvement

As with any event, there is room for improvement. There are two things in particular I would like to touch on: signage and accessibility.

The exact location of the event was not publicized very well which was made a bigger issue due to the enormity of the Dallas Convention Center. When the hotel shuttle dropped me off on the day of my pre-con, I did not know where they should drop me. The driver let me off at what she said was the main entrance. this turned out to be the entrance for lobby C. When I entered, there were no signs for the event. I eventually made my way up to the 2nd level and still did not see any signs though I saw some fellow attendees outside the Starbucks (which was not yet open). It turns out that SQLRally was at the far end of the center in lobby A. Lobby C to lobby A doesn’t sound very far, but when you have a torn tendon in one knee and must use a cane to walk, it becomes very far, very quickly. I did not see any signs for SQL Rally until I made it far enough into lobby A to see the actual registration table.

The initial long walk aside, the convention center was not very accessible. They had an elevator and some escalators, but th elevator on the lower 2 levels was inside of a remodeling zone and many of the escalators were turned off. In order to get from the street drop-off point, I had to go up a flight of stairs and across a courtyard (and back when leaving). The escalator to the 2nd floor was from the parking lot that had no in/out privileges for dropping someone off. Likewise, the elevator was located next to the parking area and was blocked off on the firt two floors. In order to use the elevator, i had to make my way through hoses and debris.

I realize that much of that is tha fault of the facility and not the organizer’s. One thing I found odd was that during the pre-con on Wednesday, the coffee was on the 3rd floor by the session rooms. During the event days, the coffee was down on the frst floor across the lobby by registration. This meant that someone like me who is using a cane to walk could not conceivably make the walk to the coffee area and back during a break time. It wimply would take too much time to make the round trip. Basically, this meant that coffee breaks were a no-go for anyone disabled.

My Tempdb Session Files

Tempdb: Performance And Manageability: TempdbPerformanceAndManageability.zip (3.92 MB)

Houston Skyline

My session Files

Strategies for Working with VLDBs: StrategiesforWorkingwithVLDBs.zip (5.3 MB)
Replication Magic: Initializing from Backup: ReplicationMagic.zip (5.04 MB)

Houston Skyline

Schedule for SQL Saturday #107

Key:

Reflections of Microsoft By Jeff Sandquist (http://www.flickr.com/photos/jeffsand/)

Another friend, Dev Nambi (blog|@DevNambi), had his last day at Microsoft just a few days before mine. He wrote a great blog post on it: Retrospective: Leaving Microsoft. This blog post has inspired me to write my own retrospective blog post on leaving Microsoft (hint: you’re reading it right now).

Reflections of Microsoft

I spent a little less than 5 years of my career at Microsoft. In DBA years^*, that’s at least 8 ½ years. Here are some stats for that time:

• I worked on 4 different groups
  • TS&E Operations (Technical Services & Engineering)
  • CAP Architecture & Performance Engineering (APE for short)
  • PQO Operations (Product Quality & Online)
  • Microsoft Learning Advanced Certifications team
• Worked as a DBA, a SQL developer, an Operations Engineer, and lastly as a Program Manager
• Spent 2 years a v- contractor and 3 years as an FTE
• Worked with some truly talented and dedicated people
• Became a SQL Server Certified Master
• Presented at my first SQL Saturday in October 2009 at the Microsoft Commons (pictured above)

^* DBA years = the time it would take you to put in the number of actual hours worked if you did it without going over 40 hours per week. For the record, I just made this term up, and I claim copyright on it henceforth.

Reflections on Developers

We database administrators really like to poke fun at developers, and I’m just as guilty of that as anyone. But the truth is that I worked with some really great developers at Microsoft. The best engineering team I have worked with to date in or outside of Microsoft was the CAP engineering team. I worked with the CAP team as both a SQL developer and as an operations engineer. They are a highly skilled and dedicated team who work really hard to make sure they are producing efficent code in the client application, middle-tier, and in the database. Yes, you heard me right. They write efficient, performant SQL code. They have to. CAP is a highly critical application for Microsoft and performance is very important. CAP also interfaces with a lot of other internal critical applications (too many to remember them all), and if CAP is down or slow, they are all down or slow to some degree.

Some of the things that I think the CAP team does right (other than right good code):

• They have 3 development teams: New features, Sustaining (bug fixes), and Architecture & Performance
• They have several dedicated QA/Test environments including one that mirrors the production environment for performance testing
• They listen to others. I never felt like I had to fight to get things done that were right for the operations team. They truly partnered with operations and listened to our needs (like automating tasks that other dev teams would have just expected Ops to do manually)
• They were a shining example of how Agile Engineering is supposed to work. I’ve heard Agile often referred to as Fragile, but not when talking about CAP. They were fluid, consistent, and delivered with great results.
• 

  Party Monkey

  
  They really embodied the Microsoft philosophy of “Word hard, play hard”. It was often referred to as the “Dave and Melissa^* Show” (Melissa DeLong and Dave Benton). The head program managers were equally skilled at party planning. Their events were always wacky, a lot of fun, made the team’s bonds stronger, and somehow always seemed to involve large quantities of inflatable monkeys.

^* I would be remiss if I didn’t also point out that Melissa is also a very gifted chocolate maker. Her chili pepper chocolates are to die for!

Reflections on Relationships

More than anything, what I will take with me when I leave are some friendships that I am fortunate to have. There are several people that I want to call out. this is by no means the complete list, but these people have been really key for me.

• Bob Elder: I actually worked with Bob before Microsoft. I’ve worked off and on with Bob since I got my first real IT job in February 2000. Bob was the reason I went to work at Microsoft, and he really helped me figure out how to make my way through the Microsoft landscapes. Bob was sometimes my coworker, sometimes my manager, and always my friend.
• Jimmy May: You may know him as that guy that taught us all about disk partition alignment or you may know him as @AspiringGeek on Twitter. However you know Jimmy, you respect him. Jimmy is the closest thing I had to a mentor in my career. I’ve talked to Jimmy a lot about career paths, and the best thing I learned from Jimmy is to follow my passion. I’m a happier person for it, and I don’t think that I would be making the move that I am now if I hadn’t learned this valuable lesson from him.
• Meher Malakapalli: I met Meher (@MeherSQL) shortly after I joined Microsoft. I had done a big database mirroring project at my previous employer and one of my first projects at Microsoft was to consult on a mirroring project for an application there. Meher was one of the IT engineers for the application. I had many conversations with Meher after that about database mirroring. It was really Meher’s idea that I write a book on database mirroring. It probably wouldn’t have happened if he had not planted that idea in my head.
• Jonathan Foster: I remember shortly after I joined PQO Operations, Jonathan told me that he’s always wanted them to hire a DBA from whom he could really learn how to be a good DBA. I didn’t know it then, but I soon figured out that Jonathan is the one who should be teaching other people. I convinced Jonathan to co-present one of my sessions at the SQL PASS Summit last year. Jonathan talked about the soft skills of a good DBA like working with your engineering team, engineering for operations, and understanding the needs of your stakeholders. Jonathan is a great DBA, but he truly excels at the soft skills more than any other DBA I have ever worked with. I think he is under-appreciated and under-utilized at Microsoft, and I constantly urge him to look elsewhere.
• Katherine Myers: I worked with Katherine in TS&E Operations and again later in PQO Operations. There isn’t a Certified Master program for SCOM, but if there was, she would be the first person I’d think of. She did really really unique stuff with SCOM when most people were still using MOM. She built in a lot of automated processes for the application test teams to perform common tasks that they simply did not have permissions to do like getting the event logs from the machines and resetting test data. It may not sound like much from my description, but her work in this area drastically reduced the demands on the time of the entire team. Katherine left TS&E before I did when she accepted a position in XBox Live Operations. When Katherine told me later that she was leaving XBox Live operations and going to return to being a contractor, I immediately lobbied my managers to hire her. She is now revolutionizing monitoring in PQO Operations as part of their infrastructure team. I keep telling Katherine that one day I’m going to start my own company so she can come work for me and spread her SCOM expertise far and wide.

Demystify Tempdb Performance & Management

The purpose of this whitepaper is to guide you on how to make the right decisions for managing tempdb; to help you determine the proper solution for any given scenario. In order to be able to make these decisions, there are several concepts that must be understood. We will take a look at what tempdb is and how it is used and some common major problems and how to prevent them. We will also provide some best practices on configuring and monitoring tempdb.

Title: Demystify Tempdb Performance & Management
Writer: Robert L Davis
Published: March 20, 2012
Publisher: Idera
Applies to: SQL Server 2008, SQL Server 2008 R2
Summary: This whitepaper provides clear guidance on best practices for managing tempdb to
provide the optimum balance between performance, reliability, and manageability.

Goodbye Microsoft, Hello World

Goodbye Microsoft. Helloe new adventure.

Goodbye Microsoft

I joined Microsoft as a contractor in 2007. I worked as an operations engineer on the TS&E Operations Team (I don’t recall what TS&E stood for). Then our team was “re-org’d” and split into three parts in three different groups. The part I was in was then “phased out” and all of the applications we managed went into sustained engineering mode which is a nice way of saying our jobs were outsourced.

One of the other sections of our group had gone on to become a very strong team doing development for a critical application known as CAP (Customer Assistance Portal). As the old team was dying, I joined the development team of the other group. I was brought in to work on their architecture and performance engineering (APE) team. The CAP application had been brought back into Microsoft (it had been in “sustained engineering” for a few years. Our first charter was to improve performance of the application so that it would make it through another holiday season that year. Our second charge would be to rearchitect it for sustained growth. In short, the application was originally written to be used by 20 people and it now needed to handle a load about 10,000 times it’s original load.

Our holiday performance project for CAP was a huge success. The CAP development team fell under the PQO (Product quality & Online) group. They decided to bring operations for CAP under PQO as well, and I was offered a full time employee position with PQO Operations to take over as Sr. Operations Engineer for the CAP application. We had an operations team of 8 to 9 managing this application and its associated BI application. The CAP operations transition was another huge success.

As a stipulation of accepting the CAP operations position, Microsoft agreed to send me through the Certified Master program whcih I did in March of 2010. After the success of CAP, I was made the technical lead of the SQL Virtual Team for PQO Operations. Our purpose was to educate our fellow operations engineers, most of whom were not DBAs, and to provide DBA services to the various applications managed by PQO Operations. Again, this was a huge success.

When i heard that Joseph Sack was moving on, I jumped at the chance to lead the SQL MCM program in his place. I have been in the role of Project Manager for the SQL MCM program for less than a year now, but an offer came along that was too perfect for me. I simply could not turn it down. When a position that is this ideal for you, you have to realize that this is something rare. I had no plans to leave my role as PM of teh MCM program in the foreseeable future, but this offer was just too good.

So with this, I say goodbye to Microsoft. Time for my next adventure. I will officially ride off into the sunset on Friday, March 30th.

Hello world.

Best Practices

This of course led to me adopting the best ractice of not following the advice of someone simply because they claimed to know more than others. I learned that you needed to evaluate the source that it was coming from. I don’t even take best practices from Microsoft itself as gospel. Having worked at Microsoft for many years, I can assure you that internally, we don’t all agree on certain practices. I have heard from people who set all of their OLTP SQL Servers to MAXDOP = 1 because an engineer in PSS (Microsoft’s Premier Support Services) told them this was the best practice for fixing CXPacket waits and they should always use this on an OLTP server. I’m not going to go into detail in this post on why this is a bad recommendation (read my post The Barking Dog Analogy if you want to know why I say this). My point is that it is not always easy to know who you can trust for best practices.

I’m not by any means trying to disparage PSS and its engineers. They have some truly brilliant guys working there, and I advise people to open a case with them quite frequently. I was just demonstrating that even noramlly reliable sources can distribute bad practices as good. At some point in my career, I came to the realization that for most reported best practices I could find posted on the internet, with just a little more reasearch, I could find the exact opposite advice posted as a best practice on another website or blog. In addition to figuring out who I could trust for best practices, I started developing the skill of evaluating the best practice myself to determine if what they were saying agreed with what I could prove. In short, I learned the value of testing before implementing.

Dilbert by Scott Adams (www.dilbert.com)

The skill of being able to verify a best practice is critical for DBAs. At some point, we all are faced with others recommending a bad best practice to management and management buying in to it. You either need to have already proven that the practice would be bad or be able to prove it now. A number of times I have won out over the bad practice by saying, “Let’s try it in test and see what happens.” This only works if you make sure that the proper scenarios are run in test so that it is accurately evaluated. Other times, I already had empirical data to back me up. If management doesn’t understand the technology, it’s very easy for them to buy in to a bad practice because they are relying on us to advise them of these things. I have found that it is very easy to get management on your side when it’s a case of the other side “read it on a blog somewhere” versus you “have real data showing that it would be bad.”

At some point, I started cultivating my own set of best practices. Ultimately, it’s a culmination of what I have learned from others, my own experiences, my own tests, and my understanding of how SQL operates internally. I have also learned to tailor my best practices to the audience. I was the SQL technical lead on an operations team at Microsoft and most of the operations engineers on the team were not DBAs. I spent a fair amount of time trying to tech them best practices. When talking to non-DBAs, I will give them a different set of best practices than i would to experienced DBAs. For example, I would tell a non-DBA to create one data file per CPU for tempdb so they can avoid the possibility of tempdb contention altogether. Conversely, I would tell an experienced DBA that they should start with 1 data file for every 2 or 4 CPUs for tempdb, monitor for tempdb contention, and adjust if needed. I also held monthly training sessions for non-DBAs where I tried to educate them on things like tempdb contention to try to elevate their SQL Server knowledge level.

Demystifying Database Administration Best Practices

Being able to develop and evaluate best practices is a critical skill for DBAs. That’s one reason that fellow Certified Master (MCM) Argenis Fernandez (blog|@DBArgenis) and I are doing a pre-con at SQLRally on May 9th called Demystifying Database Administration Best Practices. This training will cover more than simply enumerating best practices. We will show you how why they are best practices and show you how to evaluate best practices themselves.

More info:

Our pre-con: Demystifying Database Administration Best Practices
Pre-con schedule: PASS SQLRally Dallas 2012: Pre-Conference Sessions
SQLRally homepage: Join Us in Dallas for PASS SQLRally 2012

SQL Rally 2012, May 10 = 11, Dallas, TX

SQL Saturday 108 Replication Magic

Session files are now available for download. The zip file contains the PowerPoint slide deck and demo code.

Zip file: ReplicationMagic.zip (5.2 KB)

Included files:
• Demo: Initializing replication from a log backup
  • InitializeFromLogPub.sql
  • InitializeFromLogSub.sql
• Demo: Initializing replication from a filegroup backup using named filegroups
  • FGByNameBackup.sql
  • FGByNameRestore.sql
• Demo: Initializing replication from a filegroup backup using read-write and read-only filegroups (not demoed during session)
  • ReadWriteFGBackup.sql
  • ReadWriteFGRestore.sql
• Slidedecks
  • PowerPoint 2010: ReplicationMagic.pptx
  • PowerPoint 2003-2007: ReadWriteFGRestore.sql

SQL PASS SQLRally

Argenis Fernandez (|@DBArgenis) and yours truly have submitted a Pre-Conference for SQL Rally 2012 in Dallas, TX called “Demystifying Database Administration Best Practices”. We think it’s going to be an epic session with just loads and loads of tips and best practices on multiple aspects of Database Administration, from Windows/AD setup to Daily Operations to Code Reviews.

While our session wasn’t chosen by the committee in the first round, it made it to the community voting round – so this post is to kindly ask for your vote. We promise we’ll deliver great content! Argenis and I have combined over 25+ years of experience. I am an MCM, and Argenis is halfway there (he actually will take a shot at the MCM lab Thursday, February 9, 2012).

Please vote here:

http://www.sqlpass.org/sqlrally/2012/dallas/UserLogin.aspx?returnurl=/sqlrally/2012/dallas/CommunityChoice.aspx

If you haven’t become a member of PASS yet (You haven’t? What are you waiting for?) It’s free, go sign up now here:

http://www.sqlpass.org/RegisterforSQLPASS.aspx

And even if you do not feel that our session would be the best choice, please vote and make your voice heard. Do your part in keeping our community strong.

Tempdb Contention via Idera Diagnostic Manager

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 is almost right.

The first PFS page is page 1 (which is actually the second page in the file as the header page is page 0). The 2nd PFS page is 8088 exactly, not 8088 pages later. After the first page, it does repeat every 8088 pages.

Likewise, the first GAM page is page 2, the second GAM page is page 511232 and then repeats every 511232 pages. The first SGAM is page 3, the second SGAM page is 511233 and then repeats every 511232 pages.

The old incorrect formula for determing which type of page on which the contention is occurring was:

GAM: (Page ID – 2) % 511232
SGAM: (Page ID – 3) % 511232
PFS: (Page ID – 1) % 8088

The new and improved forumala is:

GAM: Page ID = 2 or Page ID % 511232
SGAM: Page ID = 3 or (Page ID – 1) % 511232
PFS: Page ID = 1 or Page ID % 8088

Proving the New Formula

I wanted you to be able to prove for yourself that the new forula was correct. I threw together a script to check the page types of the suspected allocation pages. The first thing i want to do is grow the tempdb data file to a size large enough that I can check multiple instances of the allocation files. The GAM and SGAM pages reoccur every 4 GB, so this script will work just fine with a smaller amount than I’ve chosen. I grow the tempdb data file to 20 GB.

-- Resize tempdb main data file to 20 GB
Declare @FileSize int;

Select @FileSize = size * 8 / 1024
From tempdb.sys.database_files
Where name = N'tempdev';

If @FileSize < 2048
  Begin
    Alter Database tempdb
        Modify File (
            Name = N'tempdev',
            Size = 20480MB)
  End
Go

Now that the tempdb is large enough, I can select some choice pages. We know for a fact that page 1 is PFS , page 2 is GAM, and page 3 is SGAM. If we look at the page with DBCC PAGE, we will see a page type of 11, 8, and 9 respectively. We also check several other pages to see if they return the page types we expect.

-- Check page type of Page type of
-- page IDs of 1, 8088, 16176
Declare @DBCCPage Table (
    ParentObject nvarchar(255),
    Object nvarchar(255),
    Field nvarchar(255),
    Value nvarchar(255))

-- Read pages 1, 8088, 16176
Insert Into @DBCCPage
Exec sp_executesql N'DBCC traceon (3604);
        DBCC Page(tempdb, 1, 1, 1) With TableResults;
        DBCC Page(tempdb, 1, 2, 1) With TableResults;
        DBCC Page(tempdb, 1, 3, 1) With TableResults;
        DBCC Page(tempdb, 1, 8088, 1) With TableResults;
        DBCC Page(tempdb, 1, 16176, 1) With TableResults;
        DBCC Page(tempdb, 1, 511232, 1) With TableResults;
        DBCC Page(tempdb, 1, 511233, 1) With TableResults;
        DBCC Page(tempdb, 1, 1022464, 1) With TableResults;
        DBCC Page(tempdb, 1, 1022465, 1) With TableResults;';

-- Parse page numbers and types (11 = PFS page
Select m_pageId, m_type
From (Select Object, Field, Value
    From @DBCCPage
    Where Field In ('m_type', 'm_pageId')) As Pvt
Pivot (Min(Value)
    For Field In ([m_type], [m_pageId])) As Pvt2;

the Results

New Script

Jonathan also sent a version of the previous script that he thought would fit the bill. I changed it up a little bit to use a CTE simply because I think it is easier to read and understand with a CTE than with a derived table.

With Tasks
As (Select session_id,
        wait_type,
        wait_duration_ms,
        blocking_session_id,
        resource_description,
        PageID = Cast(Right(resource_description, Len(resource_description)
                - Charindex(':', resource_description, 3)) As Int)
    From sys.dm_os_waiting_tasks
    Where wait_type Like 'PAGE%LATCH_%'
    And resource_description Like '2:%')
Select session_id,
        wait_type,
        wait_duration_ms,
        blocking_session_id,
        resource_description,
    ResourceType = Case
        When PageID = 1 Or PageID % 8088 = 0 Then 'Is PFS Page'
        When PageID = 2 Or PageID % 511232 = 0 Then 'Is GAM Page'
        When PageID = 3 Or (PageID - 1) % 511232 = 0 Then 'Is SGAM Page'
        Else 'Is Not PFS, GAM, or SGAM page'
    End
From Tasks;