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;

Rokusaburo Michiba (Iron Chef Japanese)

When the show was on the Food Network, I always enjoyed the Japanese battles the best. I was a fan of Rokusaburo Michiba because of the skills that he demonstrated. Not just his cooking proficiency, but the very way he approached every battle. I like to imagine myself approaching battle in the same way. The difference is that my opponent isn’t another chef or DBA. My opponent is a production outage.

Eye of the Storm

Michiba Prepares the Menu

On Michiba’s side of the kitchen, something different was occurring. He would get his assistants started on different preparations while he took a piece of paper and a pen and wrote out the menu in beautiful Japanese characters. At first, I thought this was crazy. He’s wasting precious time. That’s 4 or 5 minutes he can’t get back at the end of the battle when he’ll need it. Yet somehow, he never needed it. In fact, his dishes always seemed to finish with perfect timing right at the very end. At the same time, I saw his opponents many times talking about dishes they had planned to make but ran out of time. How could this time waster end up so perfectly so often?

The answer of course is planning. When others were panicking and rushing around, he was writing out his menu. After that, he and all of his assistants knew exactly what needed to be done and when. They had a plan, and they knew how to follow a plan. The trouble with this is that nobody wants to plan when there is a production outage. Business wants action. They want you to cowboy up and get it back online by any means. Nobody wants to look like they’re not giving it their all, so planning hardly ever comes into play.

Real-life Example

Here’s a real-life example where failure to plan was costly. Someone called me at home one morning because their replication publisher (mirrored asynchronously) was down and they needed to failover to the mirror. I was in the shower when they called. Because I didn’t answer, they asked a “friend” who is a “DBA” what to do. He gave his suggestions and they followed through with it, but what he had advised wasn’t working. I called them when I got out of the shower and had listened to their message.

I asked them what was going on, and their reply was, “We need to failover the publisher to the mirror. I’ve dropped mirroring, now what do I do?” I listed what they would need to do and then walked them through each step. The publisher wasn’t online, so there was no option to re-establih mirroring. Their friend had told them that the only way to bring the mirror online if mirroring asynchronously was to drop mirroring.

Here’s what they had to do to rectify the problem (actual time: 5 hours):

1. Recover the mirror database
2. Change the application connection strings to point to the original mirror as the primary (for write operations)
3. Change the application connection strings to point to the original mirror for read operations
4. Drop replication
5. Recreate replication
6. Reinitialize and reconfigure the 5 subscribers to the publisher
7. Change the application connection strings to repoint to the subscribers for read operations

Here’s what they should have done (estimated time: 5 seconds):

1. Force service on the mirror with allow data loss

Summary

To be a good DBA, you don’t need to write out a full menu every time a problem occurs, but you should be doing at least some minimal planning. Make sure everyone knows what needs to be done and what their part of the plan is. Be like Rokusaburo Michiba. Let others run around wildly while you take a couple of minutes to understand the situation and devise the best plan. Then take action. You’ll be surprised how planning can help you reach your end goal right on time instead of struggling to get there.

Including Missing Data

The question was about how to include missing sets of data from a range of results (i.e., include missing data with a value of null). The stock answer is to create a lookup table and do a LEFT JOIN to the data so that that values not represented in the data set gets returned with null values. In this particular scenario, the possible data values would not all be known ahead of time and could be different values each time you check it. That makes it difficult to pre-populate a lookup table. Fortunately, the increment of the values in the range is known (5 seconds), so it turns out that it is very simple to generate the lookup values dynamically using a recursive CTE.

The Email Exchange

Initial Email

Subject: Adding extra rows of data to a query result

---

Hello all. First I want to send a huge thank you to all on this alias. I have asked many questions recently while trying to get a reporting project completed for Visual Studio Load Testing. The feedback has been invaluable and I have made great strides on this effort.

So here’s my current predicament. I am querying a table that contains perfmon counters, collected throughout the run. However, for various different reasons it is not uncommon for there to be short intervals throughout the run where values are not collected for various counters. The DB does not add an entry for these, so when I get the results, I will have an incomplete result set (based on graphing the results). Example below.

Is there a way to add something to the query (or a view or whatever) that will add extra rows of data with the missing timestamps and null data for the counter values?

I get this from the DB:

2:13:08 AM	0.150998
2:13:13 AM	1.476516
2:13:18 AM	0.094423
2:13:28 AM	0.724203
2:13:33 AM	0.175526

I want this in the final set:

2:13:08 AM	0.150998
2:13:13 AM	1.476516
2:13:18 AM	0.094423
2:13:23 AM
2:13:28 AM	0.724203
2:13:33 AM	0.175526

My Final Solution

I played with the recursive CTE idea for a bit and quickly had a solution. I start by grabbing the MIN and MAX values from the data table into variables to define the overall range. Then I simply recurse the range in 5 second intervals until I reach the end of the range. Creating the range of values turned out to be simpler than I expected.

-- Create dummy table for testing and populate with smaple data
If OBJECT_ID('dbo.PerfMonData') Is Not Null
  Begin
    Drop Table dbo.PerfMonData;
  End
Go

Create Table dbo.PerfMonData (
    DataID int identity(1, 1) not null primary key,
    TrackTime time(0) not null,
    TrackValue decimal(9, 6))
Go

Insert Into dbo.PerfMonData (TrackTime, TrackValue)
Values ('2:13:08 AM', 0.150998),
    ('2:13:13 AM', 1.476516),
    ('2:13:18 AM', 0.094423),
    ('2:13:28 AM', 0.724203),
    ('2:13:33 AM', 0.175526)
Go

-- Query the data
Declare @MaxTT time(0),
    @MinTT time(0);

-- Get the MIN and MAX values for track time
Select @MaxTT = MAX(TrackTime),
    @MinTT = MIN(TrackTime)
From dbo.PerfMonData;

-- Build list of all times between MIN and MAX
-- values in 5 second intervals
With TrackTime (TrackTime)
As (Select @MinTT As TrackTime
    Union All
    Select DateAdd(ss, 5, TrackTime) As TrackTime
    From TrackTime
    Where TrackTime < @MaxTT
    )
Select TT.TrackTime, PMD.TrackValue
From TrackTime TT
Left Join dbo.PerfMonData PMD On TT.TrackTime = PMD.TrackTime;

Final Results

Production Line

Problems come up everyday that could have been avoided by simply configuring SQL Server appropriately before using it. The tricky part is knowing what to change and how to determine the correct setting. Some settings require knowledge of how SQL Server works internally and how the setting affects performance. We’ll take a look at the default settings in SQL Server that should be changed before a SQL Server is put into production and provide guidelines on how to determine the best settings with which to go into production.

If your process for putting a new SQL server into production looks like the below, then this webcast is for you.

1. Install SQL Server
2. Deploy

XMLA

The Question

The following request was sent in a series of tweets:

Is there a way to pass variables between tsql and mdx queries within a sql job to automate #SSAS db backups. #sqlhelp

Correction: Is there a way to pass variables between tsql and XMLA queries within a sql job to automate #SSAS db backups. #sqlhelp

I have the tsql part working which creates the backup syntax, now I need it to execute the XMLA to perform the backups. #sqlhelp

My Proposed Solution

As I stated above, the popular solution was to use dynamic SQL to have it create a job that runs the backup and then delete the job. In my session A DBAs Guide to Administering BI Systems at the PASS Summit 2011, I talked about executing MDX queries via T-SQL across a linked server. This approach works with XMLA as well. I simply need to use dynamic SQL to build the XMLA command and then execute it across the linked server. I set up a test to demonstrate how to do it before recommending it.

I started by using the SSMS GUI to script out the backup command for the Adventure Works DW 2008R2 database in my local SSAS instance. I suspect that adding a date serial to the backup name is likely to be the reason for using this, that is the scenario I used. I then use the EXEC() AT command to execute the string across a linked server (named SSAS in my sample code).

Declare @XMLA nvarchar(1000),
    @DateSerial nvarchar(35);

-- Change date to format YYYYMMDDHHMMSS
Set @DateSerial = Convert(nvarchar, getdate(), 112) +
        Replace(Convert(nvarchar, getdate(), 108), ':', '');

-- Create the XMLA string
Set @XMLA = N'<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>Adventure Works DW 2008R2</DatabaseID>
  </Object>
  <File>c:\bak\Adventure Works DW 2008R2_' + @DateSerial + '.abf</File>
</Backup>';

-- Execute the string across the linked server (SSAS)
Exec (@XMLA) At SSAS;

Gotcha - Norelco commercial (1977)

This discussion started on Twitter via the #sqlhelp hash tag. The question that was asked is, “What are the undocumented gotchas of Transactional Replication? #SQLHelp”. I responded with several things that came to mind. I thought these would be good to capture in a blog post and hopefully others would contribute some more gotchas to the list.

If you can think of any other gotchas about any type of replication, not just transactional, please add it to a comment. Also, if you have a blog post on it, please include the link to the post. I’m getting the ball rolling with the ones I mentioned on Twitter.

Gotchas of Replication

Transactional replication
• If you run the agent continuously, and it stops (fails), it won’t restart or raise an alert until the subscription expires.
• If the agent is failing, it will retry forever and you may never notice it is failing unless you are monitoring closely.
• If you add/remove an article using the GUI, it will regenerate the entire snapshot. Use TSQL to avoid this.
• Where you place the distribution db is very important for performance and manageability. Plan this point very well.
• Check the VLFs in your db log before configuring repl. as it can have a very high impact on performance of the log reader
• Be wary of non-replicated trans that create lots of log records (eg, index maint.) as it affects log reader performance
• DML ops. to LOB columns in repl. tables will fail if the length of value exceeds the “max text repl size (B)” setting.
• Make sure you are testing deployments in repl. preprod. environ. so you aren’t surprised by related failures in Prod.
• When the publication is mirrored. Transactions will not replicate until hardened on mirror, unless a trace flags used
  • Reported by John Sansom (blog|@JohnSansom)
  • John and Martin Cairns (blog|@MartinCairnsSQL) report that the whitepaper can be downloaded here.
• Log reader fails if it encounters blank (empty) XML in an XML column.
  • Reported and blogged by Kendall Van Dyke (blog|@SQLDBA) here.
• Including an article in multiple publications can lead to performance problems.
  • Reported and blogged by Kendall Van Dyke (blog|@SQLDBA) here.
• Altering articles to not replicate deletes may not be set on all articles as expected.
  • Reported and blogged by Kendall Van Dyke (blog|@SQLDBA) here.

Merge replication
• Changing conflict tracking level from row to column generates conflicts when different columns are updated
  • Reported and blogged by David Williams (blog|@is_brandon) here.

This blog entry is participating in T-SQL Tuesday #024, hosted this month by Brad Schulz (Blog). You are invited to visit his blog to join the blog party. You are welcome to write your own participating blog post for the party or just to read more blogs participating in this month’s theme: Prox ‘n’ Funx.

For my part in this month’s blog party, I’m going to write about the performance counters DMV in SSAS. Yes, that’s right. I said SSAS. There are DMVs in Analysis Services too!

$system.discover_performance_counters

DMVs in Analysis Services can be queried directly using MDX or XMLA. it can also be queried via T-SQL by using a linked server to the SSAS engine. Personally, I prefer to use MDX. One of the most useful DMVs for a DBA is $system.discover_performance_counters. This DMV is the is not documented in Books Online like the other discover DMVs. This DMV cannot be queried directly like most DMVs. You must use the SYSTEMRESTRICTSCHEMA function.

Attempting to query this DMV directly will give teh following error:

-- Will not work
Select *
From $system.discover_performance_counters
Where PERF_COUNTER_NAME = '\MSAS 2008:Connection\Current connections';

Executing the query ...
Obtained object of type: Microsoft.AnalysisServices.AdomdClient.AdomdDataReader
Errors from the SQL query module: The 'PERF_COUNTER_NAME' restriction is required but is missing from the request.  Consider using SYSTEMRISTRICTSCHEMA to provide restrictions.

Execution complete

As the error states, you have to use the SYSTEMRISTRICTSCHEMA function. The following query works:

-- Query cCurrent connections
Select *
From SYSTEMRESTRICTSCHEMA(
    $SYSTEM.DISCOVER_PERFORMANCE_COUNTERS,
    PERF_COUNTER_NAME = '\MSAS 2008:Connection\Current connections'
);

Unfortunately, there is no way to have the DMV return all performance counters. you have to tell it explicitly whihc counters to return. You can request multiple performance counters in a single query by supplying multiple counter names one at a time.

-- Query multiple perf counters
Select *
From SYSTEMRESTRICTSCHEMA(
    $SYSTEM.DISCOVER_PERFORMANCE_COUNTERS,
    PERF_COUNTER_NAME = '\MSAS 2008:Threads\Processing Pool Idle Threads',
    PERF_COUNTER_NAME = '\MSAS 2008:Threads\Processing Pool Busy Threads',
    PERF_COUNTER_NAME = '\MSAS 2008:Cache\Current KB',
    PERF_COUNTER_NAME = '\MSAS 2008:Connection\Current connections'
);

SSAS Perf Counters