SQL SERVER – Script: Change Service Account Using WMI / SMO

Last week I wrote a post where my friend faced problem because he changed the service account of SQL Server from services. mass rather than SQL Server Configuration Manager.

SQL SERVER – Performance counter missing! DMV sys.dm_os_performance_counters is Empty

I am a strong advocate of using the tools which are designed for that purpose. When I told that same thing to my friend, he said “how can I manage 1000 SQL Servers like this? I need to automate things via script.” Here is script which I use to change the service account for ALL SQL instances on my machine. There would be other possible ways to do what I am trying to do, but I am trying to build a foundation of standard scripts related to SQL using SMO and these examples are using just WMI.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
$Server = "SQL16NodeB"
$UserName="SQLAuthority\SQLSvc"
$Password="Sysadmin@1234"

$SMO = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $Server
$Service = $SMO.Services | where {$_.type -like "SQLServer"}
Write-Host 'Properties before Change'
$Service | select name, ServiceAccount, DisplayName, StartMode  | Format-Table
$Service.SetServiceAccount($UserName, $Password)
Write-Host 'Properties after Change'
$Service | select name, ServiceAccount, DisplayName, StartMode | Format-Table

The variable are declare on the top of script. Here is the output on my machine.

wmi 01 SQL SERVER   Script: Change Service Account Using WMI / SMO

You would notice that script has picked only SQL Server account. My machine has two instances (default and named) and it has been changed for both. Notice that I have added below filter

where {$_.type -like "SQLServer"}

due to that I am getting all SQL Services not SQL Agent Services.

How do you change service account or password in your environment?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – 2016 – Creating Additional Indexes with Clustered ColumnStore Indexes

Every version of SQL Server brings new capabilities that enhance the features that were introduced in the previous versions. I am sure the team is working based on the feedbacks given by customers as they starting using the new capability. As I was scanning through some of the enhancements that reached this release, I thought some good work has gone behind the usage of ColumnStore Indexes.

So to dissect this enhancement, one of the core limitation before SQL Server 2016 for columnStore Indexes was we couldn’t set any additional indexes once a Clustered ColumnStore Index was present. This meant, we needed to drop any existing indexes before we created Clustered CloumnStore Index. With SQL Server 2016, I saw that we can create additional B-Tree indexes on top of Clustered ColumnStore Indexes.

As soon I saw this, I thought of creating a script to test the statement on my SQL Server 2016 instance. Let us see how the script flows below:

USE AdventureWorks2016
GO
-- Create New Table
CREATE TABLE [dbo].[MySalesOrderDetail](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL,
[LineTotal] [numeric](38, 6) NOT NULL,
[rowguid] [uniqueidentifier] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
)
ON [PRIMARY]
GO

Now that we have our base table, let us create the Clustered ColumnStore Index next and add some rows into the table.

-- Create clustered index
CREATE CLUSTERED COLUMNSTORE INDEX [CL_MySalesOrderDetail] ON [dbo].[MySalesOrderDetail]
GO
-- Create Sample Data Table
-- WARNING: This Query may run upto 2 minutes based on your systems resources
INSERT INTO [dbo].[MySalesOrderDetail]
SELECT S1.*
FROM Sales.SalesOrderDetail S1
GO

Now the moment of truth. Let us create an additional non-clustered Index on top of this table.

CREATE NONCLUSTERED INDEX [IX_MySalesOrderDetail_NormalBTree]
ON [MySalesOrderDetail]
(UnitPrice, OrderQty, ProductID)
GO

As you can see, this creates the index without any error and it is interesting to see this enhancement come through. I got curious and wanted to create an additional Non-Clustered ColumnStore Index as a test.

-- Error
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX2_MySalesOrderDetail]
ON [MySalesOrderDetail]
(CarrierTrackingNumber, UnitPriceDiscount)
GO

As expected, this resulted in an error and it was pretty clear too. The error said:

Msg 35339, Level 16, State 1, Line 3
Multiple columnstore indexes are not supported.

With SQL Server 2016, we have got an interesting enhancement to mix and match Clustered ColumnStore Index with B-Tree indexes based on the workload application throws. As I wrap up, let us try to clean up the table structure we just created.
-- Clean up
DROP TABLE IF EXISTS [dbo].[MySalesOrderDetail]
GO

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – FIX: Msg 4335, Level 16, State 1 – The specified STOPAT time is too early

While trying to prepare for a demo, I ran into some interesting error. When I searched for error on the internet, there were not many clear answers so I thought of sharing on my blog. Actually, my demo was all about point in time restore feature of SQL Server. I was trying something similar given in my earlier blog.

SQL SERVER – A Quick Script for Point in Time Recovery – Back Up and Restore

Here is the error which I received

Msg 4335, Level 16, State 1, Line 3
The specified STOPAT time is too early. All or part of the database is already rolled forward beyond that point.
Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally.
The error was not very clear so I checked my script and found the mistake. I was giving incorrect time in STOPAT. To understand it better, I have written a small script.

/*Clean up*/
USE [master]
GO
ALTER DATABASE [SQLAuthority] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [SQLAuthority]
GO

/*create database*/
CREATE DATABASE [SQLAuthority]
GO
USE [SQLAuthority]
GO
CREATE TABLE FIRST(I INT)
GO
BACKUP DATABASE [SQLAuthority] TO  DISK = N'E:\Backup\SQLAuth_1.bak' WITH FORMAT, INIT,  NAME = N'SQLAuthority-Full Database Backup 1', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
SELECT GETDATE() 'Approx Time of first full backup'
GO
CREATE TABLE SECOND(I INT)
GO
WAITFOR delay '00:00:10'
GO
BACKUP DATABASE [SQLAuthority] TO  DISK = N'E:\Backup\SQLAuth_2.bak' WITH FORMAT, INIT,  NAME = N'SQLAuthority-Full Database Backup 2', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
SELECT GETDATE() 'Approx Time of second full backup'
GO
CREATE TABLE THIRD(I INT)
GO
WAITFOR delay '00:00:10'
GO
BACKUP LOG [SQLAuthority] TO  DISK = N'E:\Backup\SQLAuth_3.bak' WITH FORMAT, INIT,  NAME = N'SQLAuthority-Log Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
SELECT GETDATE() 'Approx Time of log backup'
GO

Here are backups taken via about the script.

  1. Full back up – 1: SQLAuth_1.bak
  2. Full back up – 2: SQLAuth_2.bak
  3. Transaction Log back up: SQLAuth_3.bak

The error would come if I restore SQLAuth_3.bak on top of SQLAuth_2.bak BUT specify time before the SQLAuth_2.bak. I have modified the script to make sure you can understand the cause.

Here is the output of my SQL instance.

restore err 01 SQL SERVER   FIX: Msg 4335, Level 16, State 1   The specified STOPAT time is too early

If I run below command, I would get the same error which I explained earlier.

USE [master]
GO
IF (DB_ID('SQLAuthority') IS NOT NULL)
BEGIN
ALTER DATABASE
[SQLAuthority] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE
[SQLAuthority]
END
GO
USE [master]
RESTORE DATABASE [SQLAuthority] FROM  DISK = N'E:\Backup\SQLAuth_2.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [SQLAuthority] FROM  DISK = N'E:\Backup\SQLAuth_3.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5, STOPAT = '2016-01-17 22:05:50.593' --This time has to be modified
GO

In the above example, STOPAT is done at the time when the first full backup was taken. You have to replace the time as per your script execution and give any time before second output.

I am sure such small scripts make concepts very clear. Have you ever encountered any interesting restore error? Please share via comments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Hyper V: Error and Fix – Fix – Boot failure. Reboot and Select proper Boot device

While trying to deploy new machines on my Hyper-V, I faced an error.

Boot failure. Reboot and Select proper Boot device
or Insert Boot Media in selected Boot device

hyperv 01 Hyper V: Error and Fix   Fix – Boot failure. Reboot and Select proper Boot device

It was a VHD file which I downloaded from Microsoft site. I searched for the solution on many sites and they asked to repair OS etc. In my case, it’s a VHD and I can’t do anything.

I deleted my VM many times but finally I tried something different.

hyperv 02 Hyper V: Error and Fix   Fix – Boot failure. Reboot and Select proper Boot device

In “Specify Generation” screen, by default it selects “Generation 1”. I changed it to “Generation 2” and it worked for me.

It was a simple solution and I wasted my whole day in this. Hope this blog would save someone’s day.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER 2016 – InMemory OLTP support for Foreign Key

When InMemory OLTP was introduced in SQL Server 2014, there were a number of limitations that restricted customers to effectively use the capability. One of the most commonly asked capability was around the ability to create Foreign Keys. On first look, I thought this was always available. But while experimenting and reading the documentation figured out this was the most asked and missing capability.

With SQL Server 2016, I found this feature was added subtly and the first thing I wanted to do was to check if it works on my local CTP3.2 versions. Yes, this would also work once the RTM happens but I was excited because a lot of times, people want to use Foreign Keys because date integrity is of prime importance when working with databases.

Here, I am going to start creating our InMemory OLTP database and create two tables with constraints and add some data to it.

USE MASTER
GO
DROP DATABASE IF EXISTS [InMem_OLTP]
GO
-- Create the database
CREATE DATABASE [InMem_OLTP]
ON  PRIMARY
( NAME = N'InMem_OLTP_data', FILENAME = N'C:\DATA\InMem_OLTP_data.mdf',size = 30MB)
LOG ON
( NAME = N'InMem_OLTP_log', FILENAME = N'C:\DATA\InMem_OLTP_log.ldf',size = 10MB)
GO
-- Add Inmemory Filegroup
USE InMem_OLTP
GO
ALTER DATABASE InMem_OLTP
ADD Filegroup [InMem_OLTP_InMemory] CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE InMem_OLTP
ADD FILE (NAME = InMem_OLTP_InMemory, FILENAME = N'C:\Data\InMem_OLTP_mopt')
TO FILEGROUP [InMem_OLTP_InMemory]

We will create two tables as Products and Sales and create respective constraints to them.

-- Let us create the table srtuctures
USE InMem_OLTP
GO
CREATE TABLE Products
(
ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY NONCLUSTERED HASH (ProductID) WITH (BUCKET_COUNT = 10000),
ProductName VARCHAR(25)
)
WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO
CREATE TABLE ProductSales
(
SalesID INT CONSTRAINT pk_productSales_sid PRIMARY KEY NONCLUSTERED HASH (SalesID) WITH (BUCKET_COUNT = 10000),
ProductID INT CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES Products(ProductID),
SalesPerson VARCHAR(25)
)
WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

Next is to test if the Foreign Key has taken effect. Let us insert some values into our database tables for the same.

-- Insert Values to see
INSERT INTO Products VALUES (1, 'SQLAuthority');
-- Success
INSERT INTO ProductSales VALUES (1, 1, 'Pinal');
-- Error
INSERT INTO ProductSales VALUES (2, 2, 'Dave');

As guessed right, we get the foreign key violation error as:

Msg 547, Level 16, State 0, Line 4
The INSERT statement conflicted with the FOREIGN KEY constraint “fk_productSales_pid”. The conflict occurred in database “InMem_OLTP”, table “dbo.Products”, column ‘ProductID’.
The statement has been terminated.

As you can see, SQL Server 2016 has introduced capabilities that remove a lot of limitations that was available in prior editions. We will explore each of these in future too.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Dedicated Database Development with SQL Source Control

andrey SQL SERVER   Dedicated Database Development with SQL Source Control

We all make mistake and we all wish that we have not made those mistakes. In the field of the development, there are proper solutions, but in the world of SQL, there are not many solutions. I recently asked the same question to my friend Andrey from Devart and he has provided me a wonderful blog post about how one can do dedicated database development with SQL Source Control.

Andrey Langovoy is a team leader at Devart. He takes part in development and testing database management tools for SQL Server, writes articles about SQL Server, speaks at SQL Saturdays, contributes to open source projects and writes his own blog: codingsight.com


It is a good practice for database developers to have their own “sandbox” environment, rather than having everyone work in a shared environment. Thus, developers can work separately, unaffected by the changes made by other team members. Once a developer completes and tests a change, they commit the change to VCS and it becomes available to other developers.

In this article, we will discuss how to build such workflow with help of dbForge Source Control for SQL Server.

Database development models

There are two common approaches for teams to develop databases. However, some teams may use combinations of the two.

Dedicated

Developers work with their own copy of the database. The copy might be located on a local PC or on a central server. All developers make changes independently, then test those changes and commit to a source control. As soon as changes are committed, other team members can apply them to their working copies.

Each developer works in their “sandbox”, thus there are no risks to overwrite someone else’s changes.

Shared

Developers share a single copy of the database. All changes are made directly to the database. That is why there is no need to care about the state of local working copy. This approach is not too safe, because one developer may overwrite others’ changes.

Setting up SQL Source Control for dedicated development

  1. Install dbForge Source Control for SQL Server

Download and install the tool. Once it is installed, you will see the Source Control Tasks shortcut menu inside SSMS Object Explorer.

  1. Create version control repository

The tool supports the most popular source control systems: SVN, GIT, Mercurial, TFS and many others. For the demonstration purposes, Visual SVN will be used. You can use one of your favorite source control systems. At this step, you need to set up your source control system and the repository. You can place a database script folder inside the repository or just leave the repository blank. In this particular demo, the repository contains the database script folder. The folder contains DDL scripts for the sales_demo1 database objects.

SVN repo SQL SERVER   Dedicated Database Development with SQL Source Control

  1. Link a database to the source control

To link a database to the source control, you need to create a new database in SSMS. Right-click the database and navigate to the Source Control Tasks shortcut menu. Then, click Link Database to Source Control.

link db to sc SQL SERVER   Dedicated Database Development with SQL Source Control

The next step is to set up the connection to the repository. Click the Source control repository text box and then click Manage.

manage sc repo SQL SERVER   Dedicated Database Development with SQL Source Control

Select a required source control system. It is SVN in our case. Provide the URL to the repository. Click OK.

sc repo prop SQL SERVER   Dedicated Database Development with SQL Source Control

There it is! Note the database icon has changed in the Object Explorer. Also, it contains all required objects.

  1. Get latest

The tool automatically detects that there are some changes in the remote repository. To get the remote changes, select all required checkboxes and click Get Latest.

 get latest SQL SERVER   Dedicated Database Development with SQL Source Control

The process will take a while. Once it is completed, you get the following report:

progress window SQL SERVER   Dedicated Database Development with SQL Source Control

Now, just refresh the sales_demo1 database in SSMS object explorer. You will see that the database contains all required objects.

after get latest SQL SERVER   Dedicated Database Development with SQL Source Control

  1. Working with changes

Assume someone from the team has changed an object in their local sandbox and committed the change into the repository. You need to get the update from the repository. To get remote changes, just refresh the source control tool. The tool automatically detects the remote modifications and provides you a list of all changes. You can see which lines of code have changed. If you agree to the changes, select them and click Get Latest!

remote changes SQL SERVER   Dedicated Database Development with SQL Source Control

All changes will be applied to your local copy of the database.

As you can see, it is very simple to manage database changes with dbForge Source Control for SQL Server. Most database developers, however, relying on a shared database development model. Nevertheless, changes made by other developers can break the code and affect the entire project, causing delays and frustration. Even so, most developers would likely choose the dedicated model.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #054 – Retrieve User Defined Object Details from sys.objects

Sometimes questions in the interview are extremely open ended and such questions can be very useful for candidate to play around with. For example, in one of the recent interview one of the interviewer asked the candidate that what is his favorite T-SQL script as a database administrator. The answer of the candidate was very simple, however, he was able to earn some brownie points with the help of his script.

Question: How do you retrieve user defined object details?

Answer: Here is a simple script which can help retrieve user defined object’s details with the help of sys.objects.

The sys.objects object catalog view contains a row for each user-defined, schema-scoped object that is created within a database. You can retrieve any user defined object details by querying sys.objects database.

Let us see one example of sys.objects database usage. You can run following query to retrieve all the information regarding name of the foreign key, name of the table it FK belongs and the schema owner name of the table.

USE AdventureWorks;
GO
SELECT name AS ObjectName,
OBJECT_NAME(schema_id) SchemaName,
OBJECT_NAME(parent_object_id) ParentObjectName, name, *
FROM sys.objects
WHERE TYPE = 'F'
GO

You can use any of the following in your WHERE clause and retrieve necessary information.

Object type:

AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
P = SQL stored procedure
PC = Assembly (CLR) stored procedure
FN = SQL scalar function
FS = Assembly (CLR) scalar function
FT = Assembly (CLR) table-valued function
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TR = SQL DML trigger
IF = SQL inline table-valued function
TF = SQL table-valued-function
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure
IT = Internal table

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Performance counter missing! DMV sys.dm_os_performance_counters is Empty

The topic around performance tuning is the most sought out search on this blog. Almost every single day the top search that lands here are around performance or around some random errors one might receive from the application. If you are a DBA, the first high level tool that you might be using to watch perf data is around using Perfmon data. Interestingly, these PerfMon data is actually exposed via the DMV’s too.

One of uncommon issue, which I found on one of my client SQL Server box, was to get zero rows in sys.dm_os_performance_counters dynamic management views.

My first question was – “did that ever show some values?” and the answer was “yes. It was working and it broke when we changed service account”.

My next question was – how did you change the service account? And as expected, the answer was – we have used the services. ms. And once again, I wanted to pull my hairs because of seeing same mistake again. There has been enough documentation from Microsoft, which suggests to use the SQL Server Configuration Manager. I asked them to show ERRORLOG.

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

Here is what pointed to the issue.

Error: 8319, Severity: 16, State: 1.
Windows kernel object ‘Global\SQL_90_MEMOBJ_MSSQLSERVER_0’ already exists. It’s not owned by the SQL Server service account. SQL Server performance counters are disabled.
Error: 3409, Severity: 16, State: 1.

Performance counter shared memory setup failed with error -1. Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions.

Below are the things which were done to fix the problem.

  • Change the account that start SQL Server Service and SQL Agent Service to the Local System Account.
  • Restart SQL Server Service and SQL Agent Service in order to apply the last change.
  • The Follow permissions must be granted to the SQL Server account in the Local Security Policy> * User Rights Assignment.
    • Adjust Memory for A Process
    • Log on as Service
    • Log on as a batch job
    • Locks pages in memory
    • Act as part of the operating system
    • Bypass traverse checking
    • Replace a process level token
  • Add the SQL Server Domain Account to the SQL Server Groups on the server.
  • Change the account that start SQL Server Service and SQL Agent Service to the SQL Server Domain Account.
  • Restart SQL Server service and SQL Server Agent Service.
  • Check your ERRORLOG and performance counters.

This is one of the reasons, I strongly recommend changing the service account from the SQL Server Configuration Manager. Above worked for my friend. Have you faced some similar issue?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Starting / Stopping SQL Server Agent Services using PowerShell

Writing utility scripts are one way to become smarter when working with computers. From time to time I get asked about some tasks folks use repetitively and are being counterproductive. On this note, I wanted to paint a scenario which I saw one of the DBA’s doing on their environment of multiple SQL Server – restarting or stopping the SQL Server Agent Services. He was painstakingly expanding each of the notes using SSMS and stopping the services.

The reason for that is beyond the scope here. But I do remember it was a highly available AlwaysOn Cluster of SQL Server running. When I saw that, I immediately asked him if this was something he wanted to automate? The immediate reaction was – “Definitely Yes. If you can.”

So gave a simple powershell script to stop the Agent Services on his patch of multiple servers. The script was as shown below:

foreach($replica in "localhost", "Server2", "Server3")
{
"StartAgent instance " + $replica.tostring() + " "
(Get-wmiobject -ComputerName  $replica Win32_Service -Filter "Name='SQLSERVERAGENT'" ).InvokeMethod("StopService",$null)
}

This quick script allowed for stopping and obviously with a simple change, I was also able to start the 3 SQL Server instances which were stopped. You might want to change this in your environments accordingly.

foreach($replica in "localhost", "Server2", "Server3")
{
"StartAgent instance " + $replica.tostring() + " "
(Get-wmiobject -ComputerName  $replica Win32_Service -Filter "Name='SQLSERVERAGENT'" ).InvokeMethod("StartService",$null)
}

But interestingly, when I did it the first time on the server – I was getting an error.

PS C:\WINDOWS\system32> E:\StartSQLAgent.PS1
File E:\StartSQLAgent.PS1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.
+ CategoryInfo          : SecurityError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : UnauthorizedAccess

If you go through the error and do it as per the documentation, we need to make sure the PowerShell window has been started in “Administrator mode” and post this, I need to run the below scrip

t:

[c
ode language="powershell" gutter="false"]
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
[/code]

This will showup and big dialog box as executed from my “Windows PowerShell ISE” window:

agent start powershell 01 SQL SERVER   Starting / Stopping SQL Server Agent Services using PowerShell

Accept the same with “Yes to All” or “Yes” and we are ready to run the above script.

The more I explore and look at Windows PowerShell, more automated I seem to make processes that I start to love this technology. Do let me know if you every did something like this in your environments?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Live Query Statistics in 2016 … and More! – Notes from the Field #111

mikel SQL SERVER   Live Query Statistics in 2016 … and More!   Notes from the Field #111[Note from Pinal]: In this episode of the Notes from the Field series database expert Mike Lawell explains about Live Query Statistics in SQL Server 2016.  Statistics are heart of SQL Server. They play a very powerful role when SQL Server Engine needs help to build a new execution plan for a query. In earlier version of SQL Server we had the only privilege to see statistics after the query is executed. However, with the advancement of SQL Server 2016, the scenario has been changed. Here is a very interesting new feature of SQL Server 2016 which can help every performance tuning expert. Read the experience of  Mike in his own words.


Your boss has asked you to implement SQL Server AlwaysOn Availability Groups in your environment. Exciting as it may, you’re an accidental DBA with little to no experience implementing any high availability solution, let alone, a fairly new availability group.

The first thing you say is, fantastic! Then, hmmm, fantastic. You realize this is above your head, but it is a great opportunity to learn.

This happened to one of my clients, where the DBA barely had SQL Server Database Mirroring experience. The product had a required up time of 99.999% (yeah, right), that they were having problems maintaining. They wanted to improve their availability and business continuity.

The DBA had a lot of anxiety with implementing AGs in this environment without experiencing considerable downtime.

Fortunately they were able to engage our servic

Welcome to 2016! This is going to be an exciting year for SQL Server professionals with the upcoming release of SQL Server 2016. With this in mind I’m going to talk a little bit about one of the cool new features in SQL Server 2016.

Many of you have had the opportunity to see me talk about one of my favorite topics, Execution Plans. I had the privilege to talk about Execution Plans at PASS Summit 2015, and many SQL Saturdays across the US. You can find my presentation here on my blog site at SQL Server Associates.

Keeping in step with my love of Execution Plans, Live Query Statistics is one of the new features in SQL Server 2016 that gives me a smile.

Live Query Statistics? What’s the benefit in that? I’ve already heard it from some senior DBAs I know. Have you ever had someone ask you to performance tune a query that never completes? It just takes forever and you know you’re not about to wait for it to complete before you can see the actual execution plan? Well, here’s your chance to look at the operators in progress and as they complete.

Live Query Stats even gives you the ability to see a percentage of completion as the operator is in progress. More about that later.

Here is how you enable Live Query Statistics from Management Studio. Select Query > Include Live Query Statistics.

111 1 SQL SERVER   Live Query Statistics in 2016 … and More!   Notes from the Field #111

Voila, there you have it. Now it is as simple as running your query.

This screenshot shows the query in process. Notice the first operator “Clustered Index Scan” has completed (100%). The other operators are at 8% or 0%.

111 2 SQL SERVER   Live Query Statistics in 2016 … and More!   Notes from the Field #111

Now here is the best part, hover over the operator that is currently in process, the one you think is the issue. Even though it is “in process” you can see the details about the operator. Notice the Actual Number of Rows information? There is a lot of information here to give you a start on identifying where and what the issue might be with the query.

111 3 SQL SERVER   Live Query Statistics in 2016 … and More!   Notes from the Field #111

Once the query has completed you get an execution plan that looks like the screenshot below.

111 4 SQL SERVER   Live Query Statistics in 2016 … and More!   Notes from the Field #111

And there you go. So simple my mom could take advantage of it. Of course mom would be saying, “Michael Mark, you had better not be lying to me. It can’t be that easy!”

Really mom, it’s that simple and it is going to help you performance tune like a pro…oh, wait, sorry mom, performance tuning is a little more complicated.

Wait, but there’s more…. In what versions does this work? Well, of course SQL Server 2016…duh… but wait there’s more!

111 5 SQL SERVER   Live Query Statistics in 2016 … and More!   Notes from the Field #111

Yep, that’s right! I just ran Live Query Statistics against a SQL Server 2014 server. Isn’t that awesome?

I’ve been able to use this new feature with several of the Linchpin People clients (even though SQL Server 2016 isn’t available yet) because it works on SQL Server 2014 installations. It just doesn’t get any better than that.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)