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...An Alternative to SELECT COUNT(*) for Better Performance
Sometimes rapid code development doesn’t always produce the most efficient code. Take the age old line of code SELECT COUNT(*) FROM MyTable. Obviously this will give you the row count for a table, but at what cost? Doing any SELECT * from a table will ultimately result in a table or clustered index scan. Turning on STATISTICS IO on reveals 5753 logical reads just to return the row count of 776286.Table ‘FactProductInventory’. Scan count 1, logical reads 5753, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Starting with SQL Server 2005, Microsoft introduced a...
Continue reading...
