One Year Later

Wow!  It’s been one year since I launched my blog, and my how things have changed.

Accomplishments Over the Past Year
I’ve had a chance to interact with a lot of people relating to many of the posts on my blog, and even run into a few people that said “Hey I know you through your blog”. I’ve gotten much more involved in the #sqlfamily through Twitter, Stackexchange, as well as through my local SQL Server user group. Although I’ve attended meetings at my local group off and on over the past several years, I am now making a specific point to attend every meeting for both the Charlotte SQL Server User Group and the Charlotte BI Group. I’ve attended SQL Saturday’s. I’ve moved into a new job at my company, where I am now responsible for platform engineering of SQL Server for Wells Fargo Securities. I’ve gone from outdated MCP certifications to the more current MCITP: Database Administrator 2008. And most importantly, the Atlanta Braves won their first division title since 2005.

The Roadmap for the Upcoming Year
I plan to keep writing about SQL Server through my blog, as well as continue learning about SQL Server through reading other blogs. That’s one thing I learned quickly about blogging. The more I wrote about SQL Server, the more I have read. My wife keeps telling me “For someone who hates to read, you sure do read a lot."

I had hoped to eventually get to an MCM certification, but Microsoft derailed that recently by terminating the program. So for now, I’ll continue on with the development exams for SQL Server 2008 and then move to upgrade them to the SQL Server 2012 MCSE: Data Platform. For my new job, I’m not required to have certifications, but I do need to have a more holistic view of SQL Server, rather than have a more narrow view on just the database engine. Studying for the certifications has helped in those areas that I’m less familiar with, such as Analysis Services.

In just a few more weeks I’ll be attending my first SQL PASS Summit. I have been so excited about this ever since I found out it will be hosted in my town, Charlotte, NC. The Charlotte Convention Center is right next door to where I work, and I’m obviously familiar with the surrounding area. I’ve been to the DEV Connections conference in Las Vegas before, but this will be my first PASS Summit.

I also hope to start speaking at local events. I already do this within my company, so now I want to venture out and do it in a more public arena. I might start with my local user group and move up to SQL Saturdays and beyond.

I also want to make sure I set aside plenty of time for my own family. My wife has been incredibly supportive in my blogging, attending user group meetings, and studying for certifications. I want her to know how much I’m indebted to her.

Thanks to all who have read my blog, and I hope I can continue to provide quality information.


Go Braves!
</ <| <\ <| </ <| <\ <| </ <| <\ <|
(That’s the tomahawk chop)

The Case of the NULL Query_Plan

As a DBA, we're often asked to troubleshoot performance issues for stored procedures.  One of the most common tools at our disposal is the query execution plan cached in memory by SQL Server. Once we have the query plan, we can dissect what SQL Server is doing and hopefully find some places for improvement.

Grabbing the actual XML query plan for a stored procedure from the cache is fairly easy using the following query.

USE AdventureWorks2012;
GO
SELECT qp.query_plan FROM sys.dm_exec_procedure_stats ps
    JOIN sys.objects o ON ps.object_id = o.object_id
    JOIN sys.schemas s ON o.schema_id = s.schema_id
    CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) qp
WHERE ps.database_id = DB_ID()
    AND s.name = 'dbo'
    AND o.name = 'usp_MonsterStoredProcedure';
GO

From this point, we can open the XML query plan in Management Studio or Plan Explorer to start our investigation. But what happens if SQL Server returns NULL for the query plan?

Let's back up a little bit.  We were pretty sure the query plan is still in cache, right?  Let's verify it.

USE AdventureWorks2012;
GO
SELECT * FROM sys.dm_exec_procedure_stats ps
    JOIN sys.objects o on ps.object_id = o.object_id
WHERE o.name = 'usp_MonsterStoredProcedure';
GO

Sure enough.  The query plan is still cached in memory, and we even can even see the plan_handle.

So why did our first query not return the XML plan?  Let's copy the plan_handle and manually run it through the sys.dm_exec_query_plan function.

SELECT * FROM sys.dm_exec_query_plan(0x05000500DD93100430BFF0750100000001000000000000000000000000000000000000000000000000000000);
GO

Why are we getting NULL returned for the XML query plan when we know is in the cache?  In this case, because the query plan is so large and complex, we're hitting an XML limitation within SQL Server.  "XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels".  

Let's try to pull the text version of query plan.

SELECT * FROM sys.dm_exec_text_query_plan(0x05000500DD93100430BFF0750100000001000000000000000000000000000000000000000000000000000000,DEFAULT,DEFAULT);
GO

It looks as though we have solved the issue; however, we didn't.  Management Studio has a 65535 character limit in grid results and 8192 character limit in text results.  Our query plan has been truncated far from the end.  Now it seems we are back to square one.  

We still know the query plan is in cache, but we just need a tool other than Management Studio to retrieve it.  This is where Powershell enters the picture.

With Powershell, we can create a simple script to execute the sys.dm_exec_text_query_plan function and then output the data to a file.  All we need is to pass two variables.  The first is the SQL Server name where the plan is cached, and the second is the plan_handle. 

param (    
    [Parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()]
    [string]
    $SqlInstance

   ,[Parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()]
    [string]
    $PlanHandle
)

The script will simply execute a TSQL script and capture the output into a string variable.

$SqlCommand = "SELECT query_plan FROM sys.dm_exec_text_query_plan(" 
    + $PlanHandle + ",DEFAULT,DEFAULT);"
$QueryPlanText = $cmd.ExecuteScalar()

The final step will use System.IO.StreamWriter() to output the data to a file.

$stream = New-Object System.IO.StreamWriter($FileName)
$stream.WriteLine($QueryPlanText)

The Powershell script will save the entire XML query plan to a file named output.sqlplan.  As you can see below, the actual plan was over 5MB.

Finally we're able to view the entire query plan in our favorite tool and see the complexity of the stored procedure.

This is just another example of why  DBAs need to set aside some time to learn Powershell.  The entire script is posted below.  Feel free to modify it as needed to fit your environment.

######################################################################################
#
#   File Name:    Get-QueryPlan.ps1
#
#   Applies to:   SQL Server 2008
#                 SQL Server 2008 R2
#                 SQL Server 2012
#
#   Purpose:      Used to retrieve an XML query plan from cache.
#
#   Prerequisite: Powershell must be installed.
#                 SQL Server components must be installed.
#
#   Parameters:   [string]$SqlInstance - SQL Server name (Ex: SERVER\INSTANCE)
#                 [string]$PlanHandle - Binary query handle
#
#   Author:       Patrick Keisler
#
#   Version:      1.0.0
#
#   Date:         08/30/2013
#
#   Help:         http://www.patrickkeisler.com/
#
######################################################################################

#Define input parameters
param ( 
  [Parameter(Mandatory=$true)]
  [ValidateNotNullOrEmpty()]
  [string]
  $SqlInstance
  
  ,[Parameter(Mandatory=$true)]
  [ValidateNotNullOrEmpty()]
  [string]
  $PlanHandle
  )

Write-Host "Script starting."

#Grab the path where the Powershell script was executed from.
$path = Split-Path $MyInvocation.MyCommand.Path

#Build the SQL Server connection objects
$conn = New-Object System.Data.SqlClient.SqlConnection
$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$cmd = New-Object System.Data.SqlClient.SqlCommand

#Build the TSQL statement & connection string
$SqlCommand = "SELECT query_plan FROM sys.dm_exec_text_query_plan(" + $PlanHandle + ",DEFAULT,DEFAULT);"
$builder.psBase.DataSource = $SqlInstance
$builder.psBase.InitialCatalog = "master"
$builder.psBase.IntegratedSecurity = $true
$builder.psBase.ApplicationName = "Get-QueryPlan"
$builder.psBase.Pooling = $true
$builder.psBase.ConnectTimeout = 15
$conn.ConnectionString = $builder.ConnectionString
$cmd.Connection = $conn
$cmd.CommandText = $SqlCommand

try
{
 if ($conn.State -eq "Closed")
 {
  #Open a connection to SQL Server
  $conn.Open()
 }
 
 #Execute the TSQL statement
 [string]$QueryPlanText = $cmd.ExecuteScalar()

 #Write the output to a file
 $FileName = $path + "\output.sqlplan"
 $stream = New-Object System.IO.StreamWriter($FileName)
 $stream.WriteLine($QueryPlanText)

 if ($stream.BaseStream -ne $null)
 {
  #Close the stream object
  $stream.close()
 }

 if ($conn.State -eq "Open")
 {
  #Close the SQL Server connection
  $conn.Close()
 }
 
 Write-Host "Script completed successfully."
}
catch
{
 #Capture errors if needed
 if ($_.Exception.InnerException)
 {
  $Host.UI.WriteErrorLine("ERROR: " + $_.Exception.InnerException.Message)
  if ($_.Exception.InnerException.InnerException)
  {
   $Host.UI.WriteErrorLine("ERROR: " + $_.Exception.InnerException.InnerException.Message)
  }
 }
 else
 {
  $Host.UI.WriteErrorLine("ERROR: " + $_.Exception.Message)
 }
 
 Write-Host .
 Write-Host "ERROR: Script failed."
}

How to Tell If Your Users are Connecting to the Availability Group Listener

You've spent a lot of time planning and building out a new SQL Server 2012 environment complete with Availability Group Listeners, but how can you be sure the end users are connecting to the listener and not directly to the SQL Server instance?

So why would we care about this?  To begin with, if the users are not connecting to the listener, then upon a failover to another replica, those users would have to connect to a different SQL Server instance name.  Having a single point of connection is crucial for the high availability process to work correctly.

In a previous blog post, we setup an Availability Group Listener, AdventureWorks.mcp.com, with two IP addresses:   192.168.1.55 & 192.168.2.55.  We'll use this one for our example.

The DMV, sys.dm_exec_connections, contains information about each connection to a SQL Server instance, and can be used to answer our question.

Open a TSQL connection to either the Availability Group listener, and execute the following command.

SELECT
     session_id
    ,local_net_address
    ,local_tcp_port
FROM sys.dm_exec_connections;
GO

The local_net_address and local_tcp_port columns will display the IP address and port number of the client's connection target.  This will be the connection string the users entered to connect to the SQL Server instance.

If the IP address and port number match the Availability Group IP, then you're in good shape.  If they do not match, then some users are likely connecting directly to the SQL Server instance, and that will need to be changed.

By joining the sys.dm_exec_sessions DMV, you'll also be able to get the hostname and program name of each connection.

SELECT
     ec.session_id
    ,es.host_name
    ,es.program_name
    ,local_net_address
    ,local_tcp_port
FROM sys.dm_exec_connections ec
    JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id;
GO

As you can see in this picture, we have one connection on session_id 62 that is connecting directly to the SQL Server instance and not the to the Availability Group Listener.  At this point, I would track down that user, and have them use the correct connection string.

Using this DMV will allow you to verify the users are connecting to SQL Server using the correct connection strings, and help prevent unneeded outages during a failover between replicas.

PASS Summit 2013 - You Ain't From Around Here Are Ya?

I know what y'all are thinkin', what's Charlotte got to do with SQL Server?  Just hear me out.  There's a lot more to Charlotte than NASCAR, fried chicken, and rednecks. I assume most of the 5000 attendees have never been to Charlotte, and probably don't know much about the area.  To help everyone out, I have made a list of useful tips.

My history and why you should listen to me. I begged my management for nearly a decade to send me to the PASS Summit, and this year they finally granted my request.  And to top it off even more, I just happen to live in Charlotte and work in the building right across the street from the Charlotte Convention Center.  I'm native to North Carolina and I have lived in Charlotte for about 17 years.  I even graduated from The University of NorthCarolina at Charlotte.

Queen City History. Charlotte is named in honor of Charlotte of Mecklenburg-Strelitz who was married to King George III of Great Britain.  This is why the city is nicknamed the "Queen City".  It is currently the 17th largest city in the US and it's the 2nd largest financial city, trailing only New York City. The city center is called uptown instead of downtown. The term downtown gives off a negative vibe; hence the term Uptown Charlotte.

Hotels. Just pick one, they're all about the same. However, if you are staying in a hotel on the south side of town near Pineville or Ballentyne, be prepared for I-77 and I-485 to be a parking lot during rush hour. Trust me on this one.

Transportation. The good news for anyone staying on the south side of town is the Lynx light rail. There is only one rail line but it runs from the center of town all the way south to Pineville. My suggestion is to take the light rail if it's near your hotel. Just get off at the 3rd St/Convention Center station, and the convention center is right across the street.

The CATS bus sytem is also not a bad option. The main transit center in uptown is only 3 blocks from the convention center. Any of the bus lines that end in an X are express routes (i.e. 54X) that pick you up from the commuter lots and head directly uptown. In uptown, there is a free bus line called the Goldrush. It different buses and only runs east/west along Trade Street.  It's helpful if you are staying in one of the hotels along that street.  And the best part is it's free.  Check out RideTransit.org for a complete system map.

If you like riding bicycles, the you'll want to check out CharlotteBcycle. There are about a dozen bicycle rentals around uptown. You just pay a small fee at the automated kiosk to share a bike, even if it's for a one way trip.

For those of you driving uptown, you'll need a place to park. There are over 40,000 parking spaces uptown, but you will have to compete with the daily workforce, like me. Most parking decks will run you about $15-20 per day. Once you get uptown, look for the giant "P" signs outside each of the parking decks. The signs will tell you the number of spaces available.

The parking lots are usually cheaper than the decks, $3-10 per day, and most of those you can pay by credit card at the kiosk. Some lots even allow you to pay using the Park Mobile app (Apple | Android | Windows). Just look for the Park Mobile sign near the kiosk for the lot number.

 

You might wonder what these over-street walkways are used for.  This is part of the Overstreet Mall.  It's a maze of walkways that interconnect some of the buildings and it's full of restaurants and shops.  Even if you're not interested in the shops, it's a nice way to get from building to building when it's raining.

While walking around uptown, you'll see these "You Are Here" street signs. The maps divide uptown four color-coded regions, North, South, East, and West. Each map provide you with information about attractions, hotels, and parking. 

Dining. You shouldn't have any issue finding a place to eat uptown; however, there are a few places of interest you should try out.  

For breakfast:

• Amelie's French Bakery
• Owen's Bagle and Deli (just south of uptown)
• Just Fresh
• Hazelnuts Creperie

For lunch:

• Price's Chicken Coop (just south of uptown, but THE BEST fried chicken in town!) CASH ONLY and TAKE-OUT ONLY!!
• French Quarter Restaurant
• Just Fresh
• Mert's Heart and Soul
• Johnny Burritos
• Fuel Pizza
• American Roadside Burgers
• Pike's Old Fashioned Soda Shop (just south of uptown)

For dinner:

• E2 Emeril's Eatery
• Sullivan's Steakhouse (just south of uptown)
• Dandelion Market
• Caffe Siena
• Bentley's on 27 (great view of Charlotte's skyline)
• Blue Restaurant and Bar
• Chima Brazilian Steakhouse

Also, if you're thinking of going to Ruth's Chris Steakhouse, then chose Sullivan's Steakhouse or Morton's Steakhouse instead.  I've never had a good experience at the uptown location, but that's just my opinion.

On a side note, when eating out, just keep in mind that you're in the south.  If you order iced tea, it WILL be sweet tea.  If you want unsweet tea, then ask for it.

Entertainment. There's plenty to do uptown as well as around town after the conference is over.  Next door to the convention center is the NASCAR Hall of Fame.  There are several other museums: Mint Museum, Bechtler Museum of Modern Art, etc.  The EpiCentre is a multi-use entertainment complex only 2 blocks from the convention center. There are restaurants, bars, and other entertainment there.  For beer lovers, there are plenty of bars uptown.  There are way too many to list, but a few are:

• The Carolina Ale House
• Prohibition Bar
• Connolly's on Fifth
• Ri Ra Irish Pub
• Picasso's Sports Bar
• Fitzgerald's Irish Pub
• Alexander Michael's Tavern

For wine lovers, check out Threes and The Wooden Vine.  Both have a wide range of selections.

The NC Music Factory is about 2 mile walk north from the convention center or only a 4 or 5 minute drive, but they do have free parking.  It's an entertain complex with live music, restaurants, bars, and even stand up comedy at The ComedyZone.  If you head over that way, be sure to visit the VBGB Beer Hall and Garden; definitely the best bar at the music factory.

Don't forget about the Carolina Panthers.  They'll have a home game on Sunday, October 20th at 1PM.  It might be your only chance to see the future superbowl champions in action!  

I know some of you might health nuts and would like find a place to workout besides your hotel gym.  The YMCA has a location uptown in my building.  $10 will get you a day pass, and $20 will get a 7-day pass.

If you prefer jogging outdoors, any of the streets uptown will work nicely.  However, if you like a little more scenery for your job, then head over to the Little Sugar Creek greenway.  The Charlotte Parks and Recreation built 35 miles of greenways around town.

This one is a beautiful, winding route nearly 6 miles long, and located just outside the south side of the I-277 belt loop uptown.

Finally, for the super adventurous attendees, the US National Whitewater Center is about 15 miles west of uptown, or head north to take a ride at 150mph at the Richard Petty Driving Experience.  It's only about 20 miles north of uptown at the Charlotte Motor Speedway.

As a bonus item, the very popular Showtime original Homeland is filmed right here in Charlotte.  If you have the time, why not try out as an extra for the show.

Other links with information about Charlotte:

• Charlotte Center City Partners
• Charlotte Visitor's Guide
• Charlotte Observer
• Charlotte Magazine
• Creative Loafing

I think I covered a lot, but if anyone has questions about Charlotte, please don't hesitate to contact me.

Are You the Primary Replica?

UPDATED -- Jul 3, 2015 -- To verify database exists, per comments by Konstantinos Katsoridis. Thanks for finding the bug!

In my recent adventures with AlwaysOn Availability Groups, I noticed a gap in identifying whether or not a database on the current server is the primary or secondary replica.  The gap being Microsoft did not provide a DMO to return this information.  The good news is the documentation for the upcoming release of SQL Server 2014 looks to include a DMO, but that doesn't help those of us who are running SQL Server 2012.

I've developed a function, dbo.fn_hadr_is_primary_replica, to provide you with this functionality.  This is a simple scalar function that takes a database name as the input parameter and outputs one of the following values.

 0 = Resolving
 1 = Primary Replica
 2 = Secondary Replica
-1 = Database Does Not Exist

The return values correspond to the role status listed in sys.dm_hadr_availability_replica_states.

In this example, I have setup 2 SQL Servers (SQLCLU1\SPIRIT1 and SQLCLU2\SPIRIT2) to participate in some Availability Groups.  I have setup 2 Availability Groups; one for AdventureWorks2012 and a second for the Northwind database.  SQLCLU1\SPIRIT1 is the primary for AdventureWorks2012 and secondary for Northwind.  SQLCLU2\SPIRIT2 is the primary for Northwind and secondary for AdventureWorks2012.

First let's run the function for both databases on SQLCLU1\SPIRIT1.

On this server, the function returns 1 because it's the primary for AdventureWorks2012, and returns 2 because it's the secondary for Northwind.

Now let's run it again on SQLCLU2\SPIRIT2.

As expected we get the opposite result.

This function does not take into account the preferred backup replica; it only returns information based on whether it is the primary or secondary replica.  It was created to use within other scripts to help determine a database's role if it's part of an Availability Group.  I hope this script can help you as well.

USE master;
GO

IF OBJECT_ID(N'dbo.fn_hadr_is_primary_replica', N'FN') IS NOT NULL
    DROP FUNCTION dbo.fn_hadr_is_primary_replica;
GO

CREATE FUNCTION dbo.fn_hadr_is_primary_replica (@DatabaseName SYSNAME)
RETURNS TINYINT
WITH EXECUTE AS CALLER
AS
/********************************************************************

  File Name:    fn_hadr_is_primary_replica.sql

  Applies to:   SQL Server 2012

  Purpose:      To return either 0, 1, 2, or -1 based on whether this 
                @DatabaseName is a primary or secondary replica.

  Parameters:   @DatabaseName - The name of the database to check.

  Returns:      0 = Resolving
                1 = Primary
                2 = Secondary
               -1 = Database does not exist

  Author:       Patrick Keisler

  Version:      1.0.1 - 07/03/2015

  Help:         http://www.patrickkeisler.com/

  License:      Freeware

********************************************************************/

BEGIN
    DECLARE @HadrRole TINYINT;

    IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @DatabaseName)
    BEGIN
        -- Return role status from sys.dm_hadr_availability_replica_states
        SELECT @HadrRole = ars.role
        FROM sys.dm_hadr_availability_replica_states ars
        INNER JOIN sys.databases dbs 
            ON ars.replica_id = dbs.replica_id
        WHERE dbs.name = @DatabaseName;
    
        -- @DatabaseName exists but does not belong to an AG so return 1
        IF @HadrRole IS NULL RETURN 1;

        RETURN @HadrRole;
    END
    ELSE
    BEGIN
        -- @DatabaseName does not exist so return -1
        RETURN -1;
    END    
END;
GO