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...Patrick Keisler
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...VMware vSphere Storage Performance – Thick vs Thin Provisioning
Industry experts will tell you that virtualization of your environment is not done to improve performance, it’s done to make it cheaper and easier to manage. The task of most VM administrators is to cram as many VMs into a farm as possible. One of the ways is to accomplish that is to allocate “thin provisioned” storage to each VM.For each VM that is created, the VM admin has to specify the number of virtual CPUs, the amount of virtual RAM, the number and size of each virtual disk, as well as a few other items. The virtual disks can...
Continue reading...Investigating Plan Cache Bloat
SQL Server includes a DMV, sys.dm_exec_query_stats, that returns performance statistics for each query plan cached in memory. However, it can also help give you insight into how consistent your developers are with writing code.For this topic, we’ll just concentrate on a few columns returned by the DMV: sql_handle and plan_handle. Per Books Online, sql_handle is a value that refers to the batch or stored procedure that the query, and plan_handle is a value that refers to the compiled plan of that query. For each query that is processed, SQL Server can generate one or more compiled plans for that query. This one-to-many relationship can be caused by a number...
Continue reading...
