Category Archives: Installation

Rx for Demystifying Index Tuning Decisions – Part 3

— by Jeffry Schwartz


In Parts 1 and 2 of this blog series the author discussed various aspects of SQL Server indices such as:

  • Types of indices and their variations, e.g., clustered, nonclustered, filtered, and covering
  • Physical versus logical index access methods, e.g., seeks, scans, key/RID lookups, singleton lookups, and range scans
  • Why index tuning should begin with queries
  • Determining an Appropriate Strategy for Index Tuning
  • Determining Queries that Need Indices the Most
  • Capturing query performance information using Extended Events

Part 3 covers the following topic:

  • Using query plans to determine relationships between queries and indices

Query or Execution Plans

Query plans have already been mentioned as being useful for query tuning. They can be invaluable for determining which part of a query causes performance issues, which indices are used, and HOW the indices are accessed, e.g., randomly or sequentially. A full discussion of query plans is beyond the scope of this presentation, but graphical plans are used most often. Graphical plans come in two forms: Estimated (predicted behavior) and Actual (actual behavior). XML plans contain additional information, but they are difficult to read even with an XML editor. Querying the XML data can provide invaluable information, but the queries to extract this information can consume a great deal of CPU time, especially when many complex plans must be examined, so it is best to peruse this kind of data on a server that is not used for production work whenever possible.

Interpreting Graphical Query Plans

It is important for any database analyst to have a basic understanding of how to read query plans. These plans are graphical flow diagrams of how the query will be executed and what information will flow from one operator to another. The diagrams flow from bottom right to top left, and quite often the most crucial operators are near the right and sometimes near the bottom as well. Each node in the tree structure is represented by an icon and specifies the logical operator that will be used to execute that portion of the query. A popup appears displaying details about the operator when the mouse pointer is placed over an operator. This information is also available, and often easier to read, via the properties window (F4 in SSMS or right-click the node and click Properties). Each node is related to a parent node by arrows whose width is proportional to the number of rows returned by the operator. Note: The actual number of rows is used when available. Otherwise, the estimated number of rows is used. Sometimes, these are vastly different and these differences often indicate that statistics are not up-to-date or sufficiently comprehensive.

Large operators may run on parallel threads because the single-threaded version would run too long. For parallel queries that involve multiple threads/CPUs the node properties will display information about the operating system threads used. Parallelized nodes have graphical indicators, two arrows on a beige circle, to inform the viewer that the operator will be divided into multiple pieces for execution as shown in Figure 1 and Figure 2.


Figure 1: Query Plan Parallelism Operator

It is important to know what the most common data access operators are, as well as how to interpret them. An index seek operator indicates the retrieval of one or more rows using keys. Clustered or NonClustered indicate the type of index that is accessed, and a NonClustered Index Seek could possibly indicate the use of a covering index. Key and RID Lookups indicate a lookup of a row using the row ID or clustering key (heap or clustered index) to obtain information from the record that is not contained in the nonclustered index that was used for primary access. Key and RID Lookup operators are most often associated with a nested loop operator and a nonclustered seek operator. The Key and RID Lookup operators usually produce output, but not always. When they are present, the output column list indicates a possible opportunity to add those columns to the nonclustered index to create a covering index for this query. When the output column is missing, these operators are used to effect a join. A simple way to determine whether output columns exist is to view the Output List that is displayed for the operator popup like the list shown in Figure 3. Index Scan (Clustered or NonClustered) operators scan ALL rows of the index even if a where clause is used. Scan operators read ALL rows from a table or clustered index regardless of the where clause. All scans should be avoided a much as possible unless the tables are small. Examples of a clustered index scan operator and its properties are shown in Figure 2 and Figure 3.



Figure 2: Clustered Index Scan Operator Graphic



Figure 3: Clustered Index Scan Operator Properties


Other commonly used operators include nested loop, bitmap, and hash match. Nested Loops perform inner join, left outer join, left semi join, and left anti semi join logical operations. These operators are potentially very deceptive because although each iteration of a loop might be very efficient, there may be millions of iterations. Bitmap operators may speed up query execution by eliminating rows with key values that cannot produce any join records before passing rows through another operator such as a Parallelism operator. Hash Match operators build hash tables by computing a hash value for each row from its input. These are usually very bad and frequently indicate that the query and its joins should be rewritten.

The plan for the query below (originally covered in Part 2)….


XEvents Statement Completed Sample Output


…is shown in Figure 4 and the properties for the nonclustered index scan are shown in Figure 5. The highlighted portions illuminate some important aspects of the query plan. The first one highlights the missing index recommendation and illustrates the key and included columns for the suggested index that would make this query run faster. The second highlight emphasizes the hash match, and the last one illustrates why the nonclustered index scan occurred. Specifically, the EventClass column was used by the index scan operator to look up records in the table with values of 166 because no appropriate index existed on the CompletedEventText table. The output columns, RowNumber and QueryChecksum, were returned by the index scan operator. It is important to note that the missing index recommendation specifies the same columns that are shown in the Output List in Figure 5. Generally speaking, index and table scan operators do not produce missing index recommendations, which will be discussed in more detail later in this series. Note: Prior to SQL Server 2014, the author had never observed an index scan operator that produced a missing index recommendation. This appears to be a function of the new Cardinality Estimator that was implemented in SQL Server 2014 and requires that the database compatibility level be set to SQL Server 2014 or later to use it. Otherwise, the old estimator that goes back to SQL Server 7.0 is used. Further information regarding the cardinality estimator can be found at


Figure 4: Query Plan with Hash Match and Index Scan Operators




Figure 5: Index Scan Operator Properties from Query Plan with Hash Match and Index Scan


The next article in this blog series will cover the query optimizer.   Until then….happy tuning and please contact us with any questions.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at We will be happy to help! Leave a comment and feel free to track back to us. Visit us at!

Installing and Connecting to SQL 2017 on Ubuntu (Linux)

–By Lori Brown

SQL Server 2017 is available as a CTP for testing ( and is able to be installed on a system running Linux. Because I have done all of my professional work on Windows systems, the leap to a Linux system was a bit of a stretch. I learned that while Linux is the OS kernel, Red Hat, SUSE, Ubuntu and Docker Engine are all flavors of Linux. A rough analogy might be that something like Ubuntu is sort of like a version of Windows. Regardless of how you think of it, it is a truly interesting experience to get SQL 2017 installed.

The first thing to do is get your friendly System Admin to stand up a Linux server. We used Ubuntu 16.04 but there are other flavors of Linux that can also be installed as listed below. Your Admin should provide you with a login and password that is an administrator so that you can get SQL running.


Since everything I have ever done on a computer has had a GUI, I decided to install Gnome to try to shorten my learning curve on Ubuntu. ( ) Truthfully, navigating in Gnome was not super intuitive to me but I was able to bumble around and get things done. When following the install instructions from Microsoft, there are places where commands are used to get things done that are labeled “bash”. It took me a bit to figure out that bash is a Unix shell or command prompt that is used to execute commands. I found a bash guide if anyone is interested. ( Apparently you can use bash on Windows….I learn something new every day. (

In Gnome, I had to search for terminal to get the command prompt (bash) window. To get SQL installed, I followed the instructions from Microsoft and have taken screenshots of what it looks like after running commands. (

curl | sudo apt-key add –


curl | sudo tee /etc/apt/sources.list.d/mssql-server.list


sudo apt-get update


sudo apt-get install -y mssql-server


During the setup completion step, you should be ready to provide the SA password when prompted for the SQL Server system administrator password.

sudo /opt/mssql/bin/mssql-conf setup


systemctl status mssql-server


Now install SQLCMD and BCP tools. (  I only installed SQLCMD but the BCP commands are similar.

curl | sudo apt-key add –


sudo apt-get update


sudo apt-get install mssql-tools unixodbc-dev

You have to answer licensing questions here so be ready.



I also installed the SQL Server Agent. The commands are very similar to previous commands so I did not take any extra screen shots of those.

Once the tools are installed, you must use SQLCMD to query. You cannot use SSMS on Ubuntu.


Since I am a creature of the Microsoft world and really want to use SSMS to do things, I need to be able to connect to SQL on Ubuntu using SSMS. So that you can connect remotely, I made sure to open SQL ports in Ubuntu. First you have to enable the firewall:


Then open ports 1433 & 1434:


To connect and query from a Windows machine….install SQL 2017 SSMS. (

Connect to SQL as normal. I tried many times to get connected using my Ubuntu hostname but did not have luck. I was able to connect using the IP address however.


The Ubuntu hostname appears in the terminal connection. Use ifconfig to get the IP address. (


Once connected you can do most things as usual in SSMS.





As of this writing, I found several places where Microsoft employees warned that some things will likely break and that they are working on bugs. So, if you run into anything that you can’t find a way around, let them know. So far Microsoft seems to be very open to information and suggestions which makes me very hopeful that SQL 2017 will be released soon and will be much faster than previous versions.

For a taste of some of the new things in SQL 2017 see:

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at We will be happy to help! Leave a comment and feel free to track back to us. Visit us at!

My First SQL 2016 Management Studio Update

I opened my local SSMS today and found my first notification of an update to SQL 2016 Management Studio today. Here is what it looks like:


When you click on update it takes you to MSDN to download the update.


Apparently this one is a hotfix. Note that you do not have to download the update and if you have SSMS set to automatically check for updates you will get this notification every time you open SSMS.

I closed SSMS and installed….



No reboot was required and I could use SSMS immediately after the install was complete. The install took only a few minutes. The download took several minutes but was not an issue.

Hope your SSMS update goes well!! Let us know if you encounter issues and any solutions you may have!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at We will be happy to help! Leave a comment and feel free to track back to us. Visit us at!


SQL 2016 SSMS Download Location

Now that SQL Server 2016 has been released ( ), DBA’s are going to notice that the Management Studio tools are not in the list of options to install. Starting in SQL 2016, SSMS is a separate download and install. You can find the download at:

The install notes say that it can be installed side-by-side with other versions of SSMS. The look is a little bit different but all is essentially the same.


There are new and improved features such as Live Query Statistics and the ability to edit top 200 rows in a table without SSMS crashing like it used to.


For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at We will be happy to help! Leave a comment and feel free to track back to us. Visit us at!

SQL Server 2016 RTM Available!!

In case you live under a rock or were at the dentist, Microsoft announced that SQL Server 2016 has been made generally available today June 1, 2016. See:

Pricing is as follows:


A handy feature comparison of previous versions of SQL can be located here:

SQL 2016 feature comparison between editions can be found here:

Let the upgrades begin!! Everything upgrade related can be found here:

We are excited to be working with this new version! Give us a shout with questions and stories of your SQL 2016 experiences!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at We will be happy to help! Leave a comment and feel free to track back to us. Visit us at!

Steps for Installing SQL Server AlwaysOn Availability Groups

–by Ginger Keys

After you make sure you have everything in your environment ready to go and all the prerequisites that need to be done before deployment have been done, we are ready to install the AlwaysOn Availability Group!

The following steps need to be performed in this order:

  1.  Add Windows Failover Clustering (WSFC) to each replica (server)
  2. Configure WSFC on your primary replica
  3. Configure SQL Server on each replica
  4. Configure Logins & Replicas
  5. Install AlwaysOn

1. Add Windows Failover Clustering:

On each replica, open Server Manager > click Add Roles & Features > select Add Failover Clustering > click Install

Proceed through the wizard, and when you get to the Select Features page, select the Failover Clustering checkbox.

And if you don’t already have .NET Framework 3.5.1 or greater installed on your server, select that checkbox as well to install. (If you do need to install the .NET Framework, you will need to reboot the machine after installing).

Proceed next through the wizard and click Install to finish the wizard. You will need to do this on every replica in your AlwaysOn group. Blog_20150430_12. Configure Windows Failover Cluster (WSFC)

On your primary replica, open the Server Manager console > open Failover Cluster Manager

Click on Validate Configuration.


Add the names of all the SQL Servers you want to configure as replicas in your AlwaysOn group. Blog_20150430_3

On the Testing Options page, click Run all tests (recommended). It is normal to see some warning messages, especially if you decide to use shared storage.



After the validation and summary is complete, the Create Cluster Wizard will open.

In the Access Point for Administering the Cluster dialog box, enter the virtual cluster name (not the server or instance name), and the virtual IP address of your cluster.


Proceed next through the wizard, and your cluster will be created. The secondary nodes will be added to the cluster, and your cluster should now show up on all replicas (through Failover Cluster Manager). You don’t have to go through these steps on the other replicas…you’re all done with setting up the cluster.

3. Configure SQL Server

Assuming you have already installed SQL Server 2012 or 2014 Enterprise edition on all of your replicas, and have installed it as stand-alone instances, we are ready to configure SQL Server. On each of your replicas, open SQL Server Configuration Manager.

Expand the SQL Server Network Configuration node, and click on Protocols for MSSQLSERVER. You will see in the right panel of the dialog box the TCP/IP entry. Right click on the TCP/IP entry and select EnableBlog_20150430_7

While you are still in SQL Server Configuration Manager, right click on SQL Server Services to open the Properties dialog box.   Navigate to the AlwaysOn High Availability tab, and select Enable AlwaysOn Availability Groups checkbox. Blog_20150430_8

Restart the SQL Server Service after making these changes.

Do these steps on all of your replicas.

4. Configure Logins and Replicas

If it isn’t there already, add your SQL Service account (which should be a domain account – not the local machine service account) as a SQL login through SQL Management Studio (SSMS).

Add your SQL Service account to the Administrators group on each replica (via Computer Management)

Give connect permissions to the SQL Service account through SSMS: Right click on the SQL Service login to open the Properties dialog box. Navigate to the Securables page, and make sure the Connect SQL Grant box is checked. You will do this on every replica. Blog_20150430_9

Make sure all your replicas Allow Remote Connections. You can do this through SSMS in the instance Properties, or by using sp_configure.

EXEC sp_configure ‘remote access’, 1;




 Make any necessary adjustments to your Windows Firewall, to allow the replicas to communicate with each other.

Create a File Share (through Server Manager) that your SQL Service account and all your replicas can access with read/write permissions. This file share will be used for the initial backup/restore process that happens when your databases are joined to the AlwaysOn group during setup.   There are other options to join your databases to the AlwaysOn group, if you prefer not to create this temporary File Share. In fact, if your databases are large I would recommend using one of the other options, in which you would restore the databases yourself on all of the secondary replicas, instead of having the wizard do this step. We’ll look at that step in a minute…

5.  Install the AlwaysOn Availability Group

Make sure full backups have been run on each database.

Make sure all databases are in Full Recovery mode.

Remove these databases from any tlog backup maintenance during the installation of AlwaysOn (you can add them back later). You don’t want tlog backups happening on these databases while the AlwaysOn group is being created.

On your primary replica, open SQL Management Studio (SSMS) and expand the AlwaysOn High Availability folder. Right click on Availability Groups and select New Availability Group Wizard… to open the wizard: Blog_20150430_10

First, you will specify your AlwaysOn group name. Name it something descriptive and unambiguous: Blog_20150430_11

Next, you will select the databases you want to include in your AlwaysOn group. All of the databases in your instance will show up in this list…you don’t have to include all of them in your group… select only the ones to be included in the AlwaysOn group. Blog_20150430_12

Also, next to each database is a blue link that signifies whether your database is ready to be included into your group or not. If the link does not say ‘Meets prerequisites’, then you can click on the link to get a more in-depth explanation of what you need to do. Blog_20150430_13

Correct any discrepancies, and then select the databases to include in the AlwaysOn group: Blog_20150430_14

Next, is the Specify Replicas page where you will add the replicas to be included in your AlwaysOn group. Add and connect the replicas by clicking the Add Replica… button.

For each replica, you will need to specify whether you want Automatic or Manual Failover, Synchronous or Asynchronous Data Replication, and what type of Connections you will allow your end users to connect with. Blog_20150430_15

On this Specify Replicas page, there are several tabs at the top. The second tab is the Endpoints tab. On this tab verify that the port number is 5022. If you have more than one instance on your server, you might need to create another endpoint. Click here for further explanation:  Blog_20150430_16

Next tab is the Backup Preferences tab.   This is where you will choose where you want your backups to occur, and how you prioritize which replica will run your backups. Blog_20150430_17

The last tab in the page is the Listener tab. Here you will select the Create an availability group listener button.

Enter the DNS name, which is the name that will be used in your application connection string.

Enter port number 1433.

And enter the IP address for your listener. This should be an unused IP address on your network. Blog_20150430_18

Next page in the wizard is the Select Initial Data Synchronization page. Here is where you will join your databases to the AlwaysOn group. The Full option is the Microsoft default option, and is the one that uses the File Share.   The other two options (Join and Skip) are fine too, especially if you have large databases. With these other two options, you will restore the databases yourself, to each secondary replica.   But this example uses the Full option, so you will browse to and select the File Share created earlier. And remember the SQL Service account and all replicas must have read/write permission to the File Share. Blog_20150430_19

Next, ensure that your Validation checks return successful results. If you get any errors, you need to stop and correct these before proceeding. Blog_20150430_20

In the Summary page, verify that all your configuration settings are correct, and click Finish.


The Results page will show the progress of the installation. Verify that all tasks have completed successfully.   Because there was no quorum set up while creating the WSFC earlier, we are seeing a warning message here….this will not cause the installation to fail. Blog_20150430_22

After the results are complete, and everything has finished successfully, you can now see the AlwaysOn Availability Group created in SSMS:

The Availability Group Name: AGroup_Dev

All the Replicas, and whether they are primary or secondary

All the Databases included in the AlwaysOn group

And the Listener created for the group.

This AlwaysOn Group will also be visible on all of the replicas as well.


For more information about AlwaysOn Availability Groups, concepts, definitions, further explanations, or questions you may have…please contact us at We will be happy to help!

Prerequisites for Installing SQL Server AlwaysOn

-by Ginger Keys

Before implementing your AlwaysOn Availability Group, make sure you have everything in your environment ready to go. There are several prerequisites that need to be done to ensure a successful deployment. These prerequisites focus on your Windows environment, the SQL instances, and the databases to be included in your AlwaysOn group.


Windows Server 2008 R2. Windows Server 2008R2 is the minimum Windows Server edition allowed for deploying AlwaysOn. However, it is strongly recommended that you use Windows Server 2012 instead, as there are many issues and headaches associated with 2008R2. Click here for a more in-depth explanation:

Windows Server Failover Cluster. WSFC must be installed on every replica (primary and secondaries) in the AlwaysOn group. This is done through Server Manager > Add Roles & Features > Add Failover Clustering.

And each SQL Server that hosts an availability group replica must reside on separate nodes of a single WSFC cluster.

.Net 3.5.1 or greater. If .net 3.5.1 or greater is not already installed on your primary and secondary replicas, you will need to install this. This can be installed at the same time you install your Windows Failover Cluster (via Server Manager > Add Roles & Features > Add Failover Clustering):


Windows hotfixes. You need to install all available windows hotfixes on every replica prior to deploying AlwaysOn. This is particularly important if you are using Windows Server 2008R2, as most of the hotfixes are aimed toward that version.   Again, it is strongly recommended that you use Windows Server 2012 on all of your replicas instead. Here is a list of the available hotfixes at this time:

Windows Domain.   All replicas in your AlwaysOn group must be in the same windows domain. They must be able to communicate with each other.

No domain controller. None of your replicas can be a domain controller. AlwaysOn groups are not supported on a domain controller.

Windows Firewall. Most likely you will need to adjust your Windows Firewall setting to allow the replicas to communicate with each other.

Sufficient Disk Space. You don’t have to have identical hardware on each replica, but you do need enough disk space to hold all of your databases, and to account for the growth of those databases.   As your databases on the primary replica grow, the replicated databases on your secondaries will also grow the same amount. And if you have other databases on your secondary replicas that are not part of your AlwaysOn group, your disk space must account for the size and growth of those databases as well.

Sufficient Resources. Again, you don’t have to have identical hardware on each of your replicas, but you need to have sufficient resources on your secondaries to handle the same workload as your primary. If you’re thinking that “we’ll use our good, powerful server as the primary replica, and use the slower, weaker server as a secondary” with the thought that if we do have to fail over, we will just know up front that the performance will be a little slower while we get the primary server back up and running… well guess what. You’re right. It will be slower performance if you have to fail over. But it will also be slower if you don’t fail over! That’s because the primary sends transactions to the secondary, and with synchronous data replication, the primary has to wait for the secondary to harden the log before it can move on to the next task. So your primary will only operate as fast as your slowest secondary.   Your AlwaysOn group is only as fast and strong as your weakest link.   So be very familiar with your workload, and try and make sure your replicas are equally yoked.


SQL Server 2012 or 2014 Enterprise Edition. For testing or development purposes, you can install Developer or Evaluation edition, but for a production environment, you must install Enterprise edition. Standard edition will not work.

Database Collation. Databases in your AlwaysOn group must use the same collation…they cannot differ across replicas.

SQL Server Collation. All replicas in your AlwaysOn group must use the same SQL Server collation.

Active Directory Services. No replicas can run Active Directory services. This is not supported with AlwaysOn.

Database Mirroring Endpoint. Each instance needs a database mirroring endpoint. If you have more than one instance on your server, you will need to create an additional endpoint so the instances can communicate with each other. Here is instructions on how to do that:

SQL Service Account. Your account that runs SQL Services must be a domain account. Do not use the local machine service account. The SQL service account must be able to access every replica, and therefore must be a domain account.

Also, this account must belong to the Administrators Group on each of the replicas.

And this SQL service account needs connect permissions (given through SSMS). Right click on the SQL Service login to open the properties dialog box, go to the Securables page, and make sure the ‘Connect SQL’ Grant box is checked. Do this on all your instances:



Full Recovery Model. Make sure your databases are in Full Recovery Mode, not Simple or Bulk Logged. Also, these databases must be taken out of any tlog backup maintenance process while the AlwaysOn group is being created.

User database. Databases included in your AlwaysOn group must be user databases. System databases cannot participate in AlwaysOn Availability Groups.

Read/Write database. Read-only databases cannot belong to an AlwaysOn group.

Multi-user database. Databases must be in multi-user mode, they can’t be in single user mode.

Don’t use AUTO_CLOSE. Check the properties of your databases, and make sure this option is set to ‘False’.

DB in only one Availability Group. Databases may only belong to one availability group at a time. You can have more than one AlwaysOn Availability Group on your instance, but databases cannot belong to more than one group.

Not configured for database mirroring. Your databases cannot be enabled for database mirroring. Make sure this feature is not enabled.

Full Backups. Make sure full backups of each of your databases are made prior to installing AlwaysOn.

Allow Remote Connections. This can be done in SSMS either through the Instance properties, or by using sp_configure, which ever you prefer.

EXEC sp_configure ‘remote access’, 1;




More content here…

Steps for Installing AlwaysOn:


March 2010 Tip of the Month

SQL Server 2008 Installation:  Sometimes installing a clustered instance of SQL Server 2008 on a Windows Server 2008 Cluster can be difficult to accomplish.  Establishing proper permissions for the service accounts and knowing how to perform a slipstream installation can make your installation a success.  Make sure that the Windows OS has installed properly (meaning that there were no errors during install) and that the cluster has access to all necessary resources and can fail over quickly.  After verifying the Cluster, make sure to disable the User Account Control (UAC) and grant the following permissions to the SQL Server service accounts:

  • Act as part of the operating system
  • Adjust memory quotas for a process
  • Allow log on locally
  • Allow log on through Terminal Services
  • Bypass traverse checking
  • Impersonate a client after authentication
  • Lock pages in memory
  • Log on as a batch job
  • Log on as a service
  • Perform Volume Maintenance Tasks
  • Manage auditing and security log
  • Replace a process level token


Perform a slipstream installation by following the steps in this KB article:

Take note of this KB article to further resolve installation issues with SQL Server 2008 SP1 CU6: