New Article – How expensive are column-side Implicit Conversions?

Last week I blogged about Implicit Conversions that cause Index Scans, and showed two charts for where implicit conversions could result in an index scan operation instead of an index seek during query execution.  As a part of writing that blog post I thought it would be interesting to also look at the performance overhead of the column-side implicit conversion occurring, given that I’ve seen it’s effects a number of times in the last few weeks.  Today my article How expensive are column-side Implicit Conversions? went live on the SQLPerformance.com site to show using Performance Monitor information on CPU usage and batch requests per second, the effect of the performance overhead when column-side implicit conversions occur that cause index scans during query execution.

New Article on SQLPerformance.com comparing “Observer Overhead” of Trace vs Extended Events

I have been so busy this week that I didn’t get a chance to blog about this yesterday when it happened, but I had a new article published on SQLPerformance.com that compares the performance impact or "observer overhead" of using SQL Trace and Extended Events.  I had a lot of fun running different benchmarks for this article and the results are very revealing about the overhead associated with diagnostic data collection against SQL Server under load. 

http://www.sqlperformance.com/2012/10/sql-trace/observer-overhead-trace-extended-events

I’ll be writing additional performance related articles on SQLPerformance.com in the next few months along with other members of SQLskills so make sure you add the RSS Feed to your favorite feed reader.

Announcing the SQLskills Availability Group Demonstrator

This spring at SQL Connections, I presented a pre-conference seminar with Kimberly on the new features in SQL Server 2012, including Availability Groups.  While on my flight to Las Vegas, I was working on an application in C# that motivated me to write a new demonstrator application for use with Availability Groups.  I spent a little time putting together a small application that I have used for every Availability Group demonstration since, and I’ve been asked a few times if I would make the application publicly available.  Paul and I discussed this and agreed that we’d offer the application as a free download.

The application is really simple in its design and usage, so I am going to use this post to show how to configure the application as well as how to use it for demonstrations of Availability Groups, or just to play with all the features provided by Availability Groups during your own testing.

Application Configuration Options

The application uses a basic application configuration file that most .NET applications use.  The configuration file is really simple and provides the ability to customize the defaults that are available in the New Connection window to simplify demonstrations based on your own environment setup.

image

The settings in the configuration file provide the default values for the following options in the New Connection window.

image

Using the application

I usually start out with a simple Availability Group (hereafter referred to as an AG) configuration where a SQL Server login is used and the AG was created through the UI.  I use the application to connect directly to the primary server using a SQL Server login without using the Availability Group Listener to show how you can connect directly to the primary server with its name.  Then I perform a failover to the synchronous replica that is configured for high availability, and open a new connection to show that the connectivity will fail since the SQL Server login wasn’t created on the secondary.  Then I use DDL to create the login and show that even though I’ve created the login on the secondary, it will fail because the SID for the new login isn’t the same as the SID that existed on the original primary server.  At this point I explain that you could use sp_help_revlogin to transfer the login with the SID intact, but then show that Partially Contained Databases with database authentication can prevent having to manually migrate every login, so I failover to the original primary and setup partial containment on all the nodes and migrate the server level login to being a contained database login.  Then I close all of the connection windows and create a new connection that uses the Availability Group Listener for the connection so that I can then failover between the primary replica and the high availability synchronous successfully. A single New Connection in the application can support this.

Then I configure ReadOnly routing for the AG and show two connections, one configured for the Read/Write workload, and another configured as a ReadOnly connection to the listener using a ReadOnly workload.

image

After setting up ReadOnly routing, and then specifying that the connection is for ReadOnly operation in the application, it will automatically redirect the connections to the first readable secondary in the routing configuration as expected.  Performing a failover will allow the application to show the expected failover error messages and then redirect the applications connections based on the configuration of the ApplicationConnection windows to the desired hosts in the AG.

image

An example of the failover messages returned is below:

image 

Once the failover completes, the application will reconnect to the appropriate nodes based on the ReadOnly routing configuration for the AG.

Requirements

The application requires .NET 4.0 and the SQL Server 2012 Native Client be installed to make use of the new ApplicationIntent connection string option. 

UPDATE:

You also need to create the following table in whatever database you are using for the demonstration:

USE [DBNAME]
GO
CREATE TABLE InsertCount (RowID int identity primary key)
GO
INSERT INTO InsertCount DEFAULT VALUES;
GO

The application can be downloaded from:

http://www.sqlskills.com/free-tools/availability-group-demonstrator/ 

If you have any questions, feel free to email me at jonathan@sqlskills.com or through the contact form on this blog.