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. 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...
Continue reading...Patrick Keisler
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...
Continue reading...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,...
Continue reading...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...
Continue reading...Setup an Availability Group with Multiple Subnets in VMware Workstation
Before we get started, I want to make it clear this is NOT how you would normally configure all these items in a production environment. This is meant for a lab or demo area to play with Availability Groups over multiple subnets. I use VMware a lot for demos at work as well as tooling around with various Windows and SQL Server related stuff. In working with Availability Groups, one of the things I would like to do for my demos is have multiple subnets in VMware Workstation, so I can simulate a site failover. Just to test Availability Groups...
Continue reading...
