Category Archives: Intermediate

Query Tuning – Adding Included Columns To An Existing Index

— by Lori Brown @SQLSupahStah

For the most part, I stick to my daily DBA duties of making sure that SQL Server maintenance is running and that the proverbial lights are on in all my SQL boxes. However, sometimes it is fun to do some tuning and it’s even better when you have a success.

I have a SQL Server that hosts some Microsoft ERP databases. There was a problem with some slow performance and after collecting some performance data, I narrowed the root cause down to a view (vp_03400Exception1) that was used in a stored procedure. Out of the 15 million reads performed by the proc when I captured it, 12 million were directly from the offending view. In checking the execution plan for the view I found that a large table (APTran) was scanned in one spot and there were key lookups going on in other places (APTran & APDoc).

Bad stuff in bad plan…thick arrow indicates that almost 4 million records were scanned


Key lookups are not good (**on small tables these might not matter but on large tables they do) and should be eliminated if possible. Key lookups occur when you have an index seek against a table, but your query wants extra columns that are not in that index. This causes SQL Server to have to go back and get those extra columns. Lots of trips back to get extra columns results in slow performance. Of course scans are not good either so I wanted to try to get rid of that too.

Since the key lookups have an output list in the execution plan that lists what extra columns were needed, I took a look and simply added those columns as included columns on the existing indexes that were being used in the plan. In effect, I created a covering index using included columns. The included columns are only used when specific queries need it. If other queries use the index with included columns but doesn’t actually need them, those won’t be pulled in.

Here are the modified indexes with the included columns.


With the new columns included in the indexes, index seeks with no key lookups are now being performed.  The thick arrow has disappeared because instead of needing to scan the whole APTran table, it knows to return only 4 records.  This means that the rest of the query does not have to perform anywhere near as much work as it did before.  See the new execution plan below:

Much better stuff in more efficient plan…small arrow indicates that only 4 records were needed from APTran so much less work is being done.


Below you can see that overall the new plan uses less memory, is smaller and just plain old more efficient.


Boom!! Mic drop!!


You might be thinking that I should not celebrate since my query only returned 1 row. However, what if the results were hundreds or thousands or millions of rows? The work done by the old execution plan would be significant and the users would feel it. So, what’s the bottom line here? If you have queries accessing large tables, check execution plans to see if they are being accessed efficiently. Sometimes a small change to an existing index will work wonders. Sometimes you have to create a new index to make queries efficient. The execution plan might just tell you what it needs if you know where to look.

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!


Moving Non-clustered Indexes To New Data File

-by Ginger Keys

It is not uncommon for database files to grow to the point you need more disk space. But what if it’s not possible or not in the budget to add more space? There are several creative options you have to move or shrink files, or delete unneeded items from the disk. One solution we recently implemented with a client was to move the indexes of a large database to another drive.

Create File/Filegroup

Only non-clustered indexes can be moved, and they must reside in a different filegroup from the primary filegroup. If you don’t already have a separate file and filegroup created, you must do this first. Make sure you have created a folder path on your system to the drive that will be holding your index files.


Get Space Requirement for Indexes

Determine the non-clustered indexes you have in your database and their size by running this script to ensure you have enough space allocated on the target drive to house the index files.

USE AdventureWorks2016



OBJECT_NAME(i.OBJECT_ID) AS TableName, AS IndexName,

8 * SUM(a.used_pages) AS ‘Indexsize(KB)’

FROM sys.indexes AS i

JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id

JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

JOIN sys.tables AS t ON i.object_id = t.object_id

WHERE i.type_desc = ‘NONCLUSTERED’

GROUP BY i.OBJECT_ID,i.index_id,



Create and Execute Stored Procedure

The following script will create a stored procedure called MoveIndexToFileGroup that will move an index from one file group to another, and even supports the INCLUDE columns. Run this script against the master database to create the stored procedure:

In order to get a list of tables for your database, run the following script.

Use AdventureWorks2016 –use your database name




After the above stored procedure is created and you have gotten a list of schemas/tables/indexes you want to move, execute the following

EXEC MoveIndexToFileGroup

@DBName = ‘AdventureWorks2016’, –your database name

@SchemaName = ‘HumanResources’, –schema name that defaults to dbo

@ObjectNameList = ‘Shift,  





                    JobCandidate’, –a table or list of tables

@IndexName = NULL, –an index or NULL for all of them

@FileGroupName = ‘INDEX’; –the target file group


Your indexes will appear in the target destination you created.



There are many useful reasons for moving your non-clustered indexes to a separate drive…in this case ours was simply to create more space for the drive holding the data files. Whatever your reason, the steps above should give you a simplistic way to achieve this.

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!







Find Currently Executing Jobs Running Longer Than 30 Day Average

–By Lori Brown   @SQLSupahStah

I needed to update some of our long running job monitoring code to improve it from the version that we have right now. I like this version because it uses msdb.dbo.syssessions ( to validate that a job is actually running. I also wanted to know the percent difference between the current run duration versus an average duration per job from the past 30 days. I decided to place the calculated average into a table variable and then join on it to get my results. I also used the IIF function ( to help me avoid a divide by zero error that comes up when the average duration equals 0.

I also learned about a function that I had never heard of before, msdb.dbo.agent_datetime. Interestingly, I cannot find documentation of this function from MSDN. However, this had apparently been around for a while. I don’t feel too bad about not knowing about this one since even super smart Pinal Dave had only recently found it too (

— currently executing jobs running longer than 30 day avg runtime

declare @startdt date

declare @enddt date

declare @JobsAvgDuration table (job_id uniqueidentifier, JobName varchar(250), ThirtyDayAvgDurMin int)


set @startdt = getdate()-30

set @enddt = getdate()


insert into @JobsAvgDuration

select j.job_id, as JobName,

AVG((h.run_duration/10000*3600 + (h.run_duration/100)%100*60 + h.run_duration%100 + 31 ) / 60) as ThirtyDayAvgDurMin

from msdb.dbo.sysjobs j

join msdb.dbo.sysjobhistory h on (j.job_id = h.job_id)

where j.enabled = 1

and msdb.dbo.agent_datetime(h.run_date, 0) between @startdt and @enddt

group by j.job_id,

order by


select as LongRunningJobName,

datediff(mi, a.start_execution_date, getdate()) as MinsRunning,


(datediff(mi, a.start_execution_date, getdate())-d.ThirtyDayAvgDurMin)*100/IIF(d.ThirtyDayAvgDurMin=0,1,d.ThirtyDayAvgDurMin) as PercentDiff

from msdb.dbo.sysjobs j

join msdb.dbo.sysjobactivity a on (j.job_id = a.job_id)

join @JobsAvgDuration d on (d.job_id = j.job_id)

where a.session_id in (select session_id from msdb.dbo.syssessions) — active session

and (a.start_execution_date IS NOT NULL and a.stop_execution_date IS NULL)

and datediff(mi, a.start_execution_date, getdate()) > d.ThirtyDayAvgDurMin

I had to force one of my monitoring jobs to run long so I could show you the results.


All that is left is to put the results into a table or a report and I will be done. Let me know if you have a unique way of monitoring for jobs that run extraordinarily long. We would love to see other solutions!

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!



–By Lori Brown   @SQLSupahStah

After installing Service Pack 1 for SQL 2016, I ran across USE HINT and thought I would put out what I could find to document it. Here’s where you find info from Microsoft: and here is a link for all of the bugs that are fixed in SP1:

USE HINT ( hint_name ) Provides one or more additional hints to the query processor as specified by a hint name inside single quotation marks. Hint names are case-insensitive. USE HINT can be utilized without having to be a member of the sysadmin server role.

The following hint names are supported:

  • ‘DISABLE_OPTIMIZED_NESTED_LOOP’ Instructs the query processor not to use a sort operation (batch sort) for optimized nested loop joins when generating a query plan. Equivalent to trace flag 2340.
  • ‘FORCE_LEGACY_CARDINALITY_ESTIMATION’ Forces the query optimizer to use Cardinality Estimation model of SQL Server 2012 and earlier versions. Equivalent to trace flag 9481 or Database Scoped Configuration setting LEGACY_CARDINALITY_ESTIMATION=ON.
  • ‘ENABLE_QUERY_OPTIMIZER_HOTFIXES’ Enables query optimizer hotfixes (changes released in SQL Server Cumulative Updates and Service Packs). Equivalent to trace flag 4199 or Database Scoped Configuration setting QUERY_OPTIMIZER_HOTFIXES=ON.
  • ‘DISABLE_PARAMETER_SNIFFING’ Instructs query optimizer to use average data distribution while compiling a query with one or more parameters, making the query plan independent of the parameter value which was first used when the query was compiled. Equivalent to trace flag 4136 or Database Scoped Configuration setting PARAMETER_SNIFFING=OFF.
  • ‘ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES’ Causes SQL Server to generate a plan using minimum selectivity when estimating AND predicates for filters to account for correlation. Equivalent to trace flag 4137 when used with cardinality estimation model of SQL Server 2012 and earlier versions, and has similar effect when trace flag 9471 is used with cardinality estimation model of SQL Server 2014 or higher.
  • ‘DISABLE_OPTIMIZER_ROWGOAL’ Causes SQL Server to generate a plan that does not use row goal adjustments with queries that contain TOP, OPTION (FAST N), IN, or EXISTS keywords. Equivalent to trace flag 4138.
  • ‘ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS’ Enables automatically generated quick statistics (histogram amendment) for any leading index column for which cardinality estimation is needed. The histogram used to estimate cardinality will be adjusted at query compile time to account for actual maximum or minimum value of this column. Equivalent to trace flag 4139.
  • ‘ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS’ Causes SQL Server to generate a query plan using the Simple Containment assumption instead of the default Base Containment assumption for joins, under the query optimizer Cardinality Estimation model of SQL Server 2014 or newer. Equivalent to trace flag 9476.
  • ‘FORCE_DEFAULT_CARDINALITY_ESTIMATION’ Forces the Query Optimizer to use Cardinality Estimation model that corresponds to the current database compatibility level. Use this hint to override Database Scoped Configuration setting LEGACY_CARDINALITY_ESTIMATION=ON or trace flag 9481.

The list of all supported USE HINT names can be queried using the dynamic management view sys.dm_exec_valid_use_hints. More this view can be found here:

Some USE HINT hints may conflict with trace flags enabled at the global or session level, or database scoped configuration settings. In this case, the query level hint (USE HINT) always takes precedence. If a USE HINT conflicts with another query hint or a trace flag enabled at the query level (such as by QUERYTRACEON), SQL Server will generate an error when trying to execute the query.



SET @qty = 4

SELECT h.OrderDate, h.Freight, d.OrderQty, d.UnitPrice

FROM Sales.SalesOrderDetail d

JOIN Sales.SalesOrderHeader h ON (d.SalesOrderID = h.SalesOrderID)

WHERE d.OrderQty > @qty


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!

Synchronize Logins on AlwaysOn Replicas

–By Lori Brown   @SQLSupahStah

At one of my clients who has an AlwaysOn failover cluster, I noticed as I was checking up on things on the secondary replica that there were several fewer Windows and SQL logins on the secondary than on the primary. I really did not want to tediously compare the logins on each so instead came up with a script that can be run periodically to script out any logins that need to be synched up between the replicas. I actually found most of the script that I needed on the SQLSoldier’s blog ( in a stored procedure that was intended to synch logins between mirrored partners. Since AlwaysOn is Mirroring on steroids I thought that it would work and it did. I did put my own touches on the proc by turning it into a standalone script that uses OPENROWSET to query the AG primary replica and also changed it from automatically executing the statements for missing logins to simply printing them into a script that can be applied as needed.

And now….here is what you came here for….

— Original logic from

— Sync Logins to AlwaysOn Replicas

—     Inputs: @PartnerServer – Target Instance (InstName or Machine\NamedInst or Instname,port)

—     Output: All Statements to create logins with SID and Password for both Windows and SQL logins

—             Will also add logins to server roles

—     Person executing this must be sysadmin

—     Ad Hoc Distributed Queries must be enabled for this to work without setting up a linked server


— Turn on Ad Hoc Distributed Queries so we don’t have to set up a linked server

sp_configure ‘show advanced options’, 1


reconfigure with override


sp_configure ‘Ad Hoc Distributed Queries’, 1


reconfigure with override




Use master;


Declare @MaxID int,

@CurrID int,

@PartnerServer sysname,

@SQL nvarchar(max),

@LoginName sysname,

@IsDisabled int,

@Type char(1),

@SID varbinary(85),

@SIDString nvarchar(100),

@PasswordHash varbinary(256),

@PasswordHashString nvarchar(300),

@RoleName sysname,

@Machine sysname,

@PermState nvarchar(60),

@PermName sysname,

@Class tinyint,

@MajorID int,

@ErrNumber int,

@ErrSeverity int,

@ErrState int,

@ErrProcedure sysname,

@ErrLine int,

@ErrMsg nvarchar(2048)


SET @PartnerServer = ‘InstName’


Declare @Logins Table (LoginID int identity(1, 1) not null primary key,

[Name] sysname not null,

[SID] varbinary(85) not null,

IsDisabled int not null,

[Type] char(1) not null,

PasswordHash varbinary(256) null)


Declare @Roles Table (RoleID int identity(1, 1) not null primary key,

RoleName sysname not null,

LoginName sysname not null)


Declare @Perms Table (PermID int identity(1, 1) not null primary key,

LoginName sysname not null,

PermState nvarchar(60) not null,

PermName sysname not null,

Class tinyint not null,

ClassDesc nvarchar(60) not null,

MajorID int not null,

SubLoginName sysname null,

SubEndPointName sysname null)


Set NoCount On;


If CharIndex(‘\’, @PartnerServer) > 0 — Check for Named Instance


Set @Machine = LEFT(@PartnerServer, CharIndex(‘\’, @PartnerServer) – 1);


Else If CharIndex(‘,’, @PartnerServer) > 0 — Check for Instance with port in connection string


Set @Machine = LEFT(@PartnerServer, CharIndex(‘,’, @PartnerServer) – 1);




Set @Machine = @PartnerServer;



— Get all Windows logins from principal server

— using OPENROWSET and Windows Authentication

Set @SQL = ‘Select a.* From OPENROWSET (”SQLNCLI”, ”Server=’+@PartnerServer+‘;Trusted_Connection=yes;”, ”Select, P.sid, P.is_disabled, P.type, L.password_hash

             From master.sys.server_principals P

             Left Join master.sys.sql_logins L On L.principal_id = P.principal_id

             Where P.type In (””U””, ””G””, ””S””)

             And <> ””sa””

             And Not Like ””##%””

             And CharIndex(””’ + @Machine + ‘\””, = 0”)as a;’


–print @SQL


Insert Into @Logins (Name, SID, IsDisabled, Type, PasswordHash)

Exec sp_executesql @SQL;


— Get all roles from principal server

— using OPENROWSET and Windows Authentication

Set @SQL = ‘Select a.* From OPENROWSET (”SQLNCLI”, ”Server=’+@PartnerServer+‘;Trusted_Connection=yes;”, ”Select as RoleName, as LoginName

             From master.sys.server_role_members RM

             Inner Join master.sys.server_principals RoleP

             On RoleP.principal_id = RM.role_principal_id

             Inner Join master.sys.server_principals LoginP

             On LoginP.principal_id = RM.member_principal_id

             Where LoginP.type In (””U””, ””G””, ””S””)

             And <> ””sa””

             And Not Like ””##%””

             And RoleP.type = ””R””

             And CharIndex(””’ + @Machine + ‘\””, = 0”)as a;’


–print @SQL


Insert Into @Roles (RoleName, LoginName)

Exec sp_executesql @SQL;


— Get all explicitly granted permissions

— using OPENROWSET and Windows Authentication

Set @SQL = ‘Select a.* From OPENROWSET (”SQLNCLI”, ”Server=’+@PartnerServer+‘;Trusted_Connection=yes;”, ”Select Collate database_default,

                    SP.state_desc, SP.permission_name, SP.class, SP.class_desc, SP.major_id,

           Collate database_default,

           Collate database_default

             From master.sys.server_principals P

             Inner Join master.sys.server_permissions SP

             On SP.grantee_principal_id = P.principal_id

             Left Join master.sys.server_principals SubP

             On SubP.principal_id = SP.major_id And SP.class = 101

             Left Join master.sys.endpoints SubEP

             On SubEP.endpoint_id = SP.major_id And SP.class = 105

             Where P.type In (””U””, ””G””, ””S””)

             And <> ””sa””

             And Not Like ””##%””

             And CharIndex(””’ + @Machine + ‘\””, = 0”)as a;’


–print @SQL


Insert Into @Perms (LoginName, PermState, PermName, Class, ClassDesc, MajorID, SubLoginName, SubEndPointName)

Exec sp_executesql @SQL;


Select @MaxID = Max(LoginID), @CurrID = 1

From @Logins;


While @CurrID <= @MaxID


Select @LoginName = Name,

@IsDisabled = IsDisabled,

@Type = [Type],

@SID = [SID],

@PasswordHash = PasswordHash

From @Logins

Where LoginID = @CurrID;


If Not Exists (Select 1 From sys.server_principals

Where name = @LoginName)


Set @SQL = ‘Create Login ‘ + quotename(@LoginName)

If @Type In (‘U’, ‘G’)


Set @SQL = @SQL + ‘ From Windows;’




Set @PasswordHashString = ‘0x’ +

Cast( As XML).value(‘xs:hexBinary(sql:variable(“@PasswordHash”))’, ‘nvarchar(300)’);


Set @SQL = @SQL + ‘ With Password = ‘ + @PasswordHashString + ‘ HASHED, ‘;


Set @SIDString = ‘0x’ +

Cast( As XML).value(‘xs:hexBinary(sql:variable(“@SID”))’, ‘nvarchar(100)’);


Set @SQL = @SQL + ‘SID = ‘ + @SIDString + ‘;’;



Print @SQL;


If @IsDisabled = 1


Set @SQL = ‘Alter Login ‘ + quotename(@LoginName) + ‘ Disable;’

Print @SQL;



Set @CurrID = @CurrID + 1;



Select @MaxID = Max(RoleID), @CurrID = 1

From @Roles;


While @CurrID <= @MaxID


Select @LoginName = LoginName,

@RoleName = RoleName

From @Roles

Where RoleID = @CurrID;


If Not Exists (Select 1 From sys.server_role_members RM

Inner Join sys.server_principals RoleP

On RoleP.principal_id = RM.role_principal_id

Inner Join sys.server_principals LoginP

On LoginP.principal_id = RM.member_principal_id

Where LoginP.type In (‘U’, ‘G’, ‘S’)

And RoleP.type = ‘R’

And = @RoleName

And = @LoginName)


Print ‘Exec sp_addsrvrolemember @rolename = ”’ + @RoleName + ”’, @loginame = ”’ + @LoginName + ”’;’;


Set @CurrID = @CurrID + 1;



Select @MaxID = Max(PermID), @CurrID = 1

From @Perms;


While @CurrID <= @MaxID


Select @PermState = PermState,

@PermName = PermName,

@Class = Class,

@LoginName = LoginName,

@MajorID = MajorID,

@SQL = PermState + space(1) + PermName + SPACE(1) +

Case Class When 101 Then ‘On Login::’ + QUOTENAME(SubLoginName)

When 105 Then ‘On ‘ + ClassDesc + ‘::’ + QUOTENAME(SubEndPointName)

Else End +

‘ To ‘ + QUOTENAME(LoginName) + ‘;’

From @Perms

Where PermID = @CurrID;


If Not Exists (Select 1 From sys.server_principals P

Inner Join sys.server_permissions SP On SP.grantee_principal_id = P.principal_id

Where SP.state_desc = @PermState

And SP.permission_name = @PermName

And SP.class = @Class

And = @LoginName

And SP.major_id = @MajorID)


Print @SQL;


Set @CurrID = @CurrID + 1;



Set NoCount Off;




— Turn off Ad Hoc Distributed Queries

sp_configure ‘Ad Hoc Distributed Queries’, 0


reconfigure with override



On my test AG replicas, you can see that there are two users that are not present on the secondary. I need to add them so that if a failover occurs then there will not be any login failures.


Using the script above on SQLSRVTST2, I get the following output and can use that to create my users with their correct permissions. I had to set the @PartnerServer variable to SQLSRVTST1 in the script and enable Ad Hoc Distributed Queries.





The nice thing is that the password and SID’s are also scripted for you. There is no need to install sp_hexadecimal and sp_help_revlogin in master. If you have AG replicas with a lot of logins, this can be a real timesaver for you. Hope you enjoy and if you make any improvements, please share them with us!

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!


Script to De-Orphan SQL Logins

–By Lori Brown   @SQLSupahStah

I am working with a client who is migrating from SQL 2012 to SQL 2016. It just so happens that while they have just a few databases to move, they also have a ton of SQL logins that needed to be migrated. This is not an issue since I know to use sp_help_revlogin to script logins and passwords into a script that can be applied to the new SQL 2016. However, inevitably, there are SQL logins that have been deleted from SQL but not from the database or while testing on SQL 2016 the databases are refreshed and now logins are orphaned. I needed to have a script that would look at each database and give me a script that could be used to create or de-orphan these logins.

I figured that with a little Google-fu I would find someone who had already created a way to do this. And, while I did find a solution, I decided to give it my own twist. My solution is to have a script that I can run when needed that will output the statements that I need to create or de-orphan logins from all databases in an instance. I combined things from these two blogs:

Here is my code and a quick look at the output:

USE master


DECLARE @usercount INT

DECLARE @sqlstr1 NVARCHAR(1000)

DECLARE @sqlstr2 NVARCHAR(500)

DECLARE @dbname VARCHAR(128)





SELECT name FROM sys.databases

WHERE name NOT IN (‘master’,‘tempdb’, ‘msdb’, ‘model’)

AND is_read_only = 0 AND state = 0







SET @sqlstr1 = ‘SET @orphanedusers = CURSOR


       FROM [‘+@dbname+‘].sys.database_principals a

       LEFT OUTER JOIN sys.server_principals b

             ON a.sid = b.sid

       WHERE (b.sid IS NULL)

            AND (a.principal_id > 4)

             AND (a.type_desc = ”SQL_USER”)


       OPEN @orphanedusers’


EXEC sys.sp_executesql @sqlstr1, N’@orphanedusers cursor output’, @OrphUsers OUTPUT


FETCH NEXT FROM @OrphUsers INTO @username




SELECT @usercount = COUNT(*) FROM sys.server_principals WHERE name = @username


IF @usercount = 0


PRINT ‘–User ‘+@username+‘ does not exist and must be created. Please use a strong password.’

SET @sqlstr2 = ‘USE [master] CREATE LOGIN ‘ + @username + ‘ WITH PASSWORD = ”ABC123”’




                    PRINT ‘–User ‘+@username+‘ is an orphan.’

SET @sqlstr2 = ‘USE [‘+@dbname+‘] ALTER USER ‘ + @username + ‘ WITH LOGIN = ‘ + @username



PRINT (@sqlstr2)

FETCH NEXT FROM @OrphUsers INTO @username


CLOSE @OrphUsers










The output labels what needs to be done to make sure the users have access to their databases. If the user does not exist in SQL at all then a CREATE LOGIN statement is generated. If the user is simply orphaned an ALTER LOGIN statement is generated.

The only thing I did not do yet (mostly because my needs were centered around SQL logins) is to include Windows domain accounts in my logic. As soon as I add that to my script, I will repost. Or, if someone out there adds it, please send an update.

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!


List All Objects and Indexes in a Filegroup or Partition

— by Lori Brown @SQLSupahStah

One of my clients has a rather large database with multiple filegroups and multiple database files in them. I happened to notice that a specific drive that is occupied by 2 specific files that are supposed to hold indexes is getting utilized at a much higher rate than any of the other files. The index file group has 8 files in it. I was anticipating gathering some performance data to try to figure out what indexes are located on those specific files and needed a way to figure that out. My searching for a script led me to TechNet where there are a bunch of scripts that are freely downloadable. The one I needed came Olaf Helper. I added a join on the database files so that I could see what file they are on and filter on that.

Since I need to join my query to the actual database files, I needed to figure out what the file_id was for the specific files that I am interested in.

SELECT * FROM sys.database_files

This is a very modified output so you can see the different filegroups and the files in them.


Checking the data in sys.database files will give you the answer.

Anyway, put it all together and mine looks like this:

SELECT AS DataSpaceName


,AU.type_desc AS AllocationDesc

,AU.total_pages / 128 AS TotalSizeMB

,AU.used_pages / 128 AS UsedSizeMB

,AU.data_pages / 128 AS DataSizeMB

, AS SchemaName

,OBJ.type_desc AS ObjectType

, AS ObjectName

,IDX.type_desc AS IndexType

, AS IndexName

FROM sys.data_spaces AS DS

INNER JOIN sys.allocation_units AS AU

ON DS.data_space_id = AU.data_space_id

INNER JOIN sys.partitions AS PA

ON (AU.type IN (1, 3)

AND AU.container_id = PA.hobt_id)


(AU.type = 2

AND AU.container_id = PA.partition_id)

JOIN sys.database_files f

on AU.data_space_id = f.data_space_id

INNER JOIN sys.objects AS OBJ

ON PA.object_id = OBJ.object_id

INNER JOIN sys.schemas AS SCH

ON OBJ.schema_id = SCH.schema_id

LEFT JOIN sys.indexes AS IDX

ON PA.object_id = IDX.object_id

AND PA.index_id = IDX.index_id

WHERE f.file_id IN (13,14) AND AU.total_pages > 0 — Look at specific files, could also filter on file group

ORDER BY AU.total_pages desc — Order by size





The output looks like this…I sorted on size but left the columns needed to sort by name:


Now I am ready to collect performance data to see what stressful queries are using the indexes located on these files.

I don’t have the results from the performance data just yet but will likely follow up later on putting all the pieces together to solve this very specific problem.

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!


Get SQL Start Up Parameters and Other Info From sys.dm_server_registry

–By Lori Brown @SQLSupahStah

The dynamic management view sys.dm_server_registry has been around since SQL 2008 R2 SP1 so it is not new but it’s relatively new to me. Since I have a ton of SQL Servers to monitor, it is always good to have some way of easily knowing if there are any start up parameters configured for the instance. I found that this and more can be pulled from the registry using sys.dm_server_registry. ( )

If you look at all values returned by the dmv, you will see something like this:


In my local instance there are 191 rows returned so this can get to be a little difficult to read through. I wanted to just report on certain things so I have narrowed what is returned. Most of the time I am not interested in the actual registry key, so here is what I did:

— Startup Parameters

SELECT value_name, value_data

FROM sys.dm_server_registry

WHERE registry_key LIKE N’%Parameters’

Which returns this as the results:


Notice SQLArg3 which shows that I have added the T1118 trace flag to the startup parameters. Of course I can get some of the same info by opening up the configuration manager and checking the Startup Parameters tab of the SQL Server service but I wanted my results sent to me periodically in a report so that I can know if someone had added or changed anything. I’ll show my report later.

I also have found that it is good to know what protocols are enabled and what port SQL is listening on. Since I have a lot of servers to manage, with some on the default port and others not, I thought that it would be good to report on this. My clients also have different protocols enabled for different servers. So, since nothing is standardized, it’s good that this info be put into a report so that I can easily refer to it should I ever need it.

— List protocols enabled

SELECT sr.value_data AS EnabledProtocol

FROM sys.dm_server_registry sr

WHERE sr.registry_key IN (SELECT k.registry_key

FROM sys.dm_server_registry k

WHERE k.value_name = ‘Enabled’ AND k.value_data = 1)

AND sr.value_name = ‘DisplayName’


— List TCP port

SELECT value_name AS PortName, value_data AS PortValue

FROM sys.dm_server_registry

WHERE registry_key LIKE N’%SuperSocketNetLib\Tcp\IPAll’

AND value_name NOT LIKE N’DisplayName’


And, here is what my report looks like. I like to make small HTML formatted reports that I know I can generate on pretty much any server.


With a little bit of research and creativity, you can come up with ways to get at information in SQL easily.

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!


AlwaysOn 2016 – Maintenance

— by Ginger Keys

SQL Server AlwaysOn provides a fairly robust environment for disaster recovery; however you can never be too careful when it comes to technology and protecting your data. Regular maintenance routines (i.e. backups, etc.) need to be set up and performed on an ongoing basis, even when you have your databases in an AlwaysOn Availability Group.

AlwaysOn allows some maintenance routines to be performed on the secondary replicas, but determining which maintenance to perform on what replica can be somewhat confusing. We will discuss the following basic maintenance routines and where to execute them.

  • Backups
  • Integrity Checks
  • Index Maintenance
  • Update Statistics


Even though there is a copy of your database on the secondary replica(s), you should still continue to back up your databases and transaction logs on a regular basis.   Backups can be very resource intensive by putting strain on I/O and CPU especially when using compression. Because of this, offloading your backup operations to a secondary replica can greatly improve the performance of your primary production replica.

With an AlwaysOn group, you have several options of where to back up your databases as shown below. You can choose your backup preference when you set up your AlwaysOn group, or you can go back later and set your backup preferences through the AG properties.


Prefer Secondary – backups should occur on a secondary replica except when the primary replica is the only replica online. In that case, the backup should occur on the primary replica. This is the default behavior.

Secondary Only – backups will happen only on a replica that is designated as a secondary, and will execute in the order of the backup priority you set. Backups will not occur on the primary replica.

Primary – backups will only occur on the replica currently designated as primary.

Any Replica – backups will occur on the replica with the highest backup priority setting.

If you perform backups on your primary replica, Full, Differential, and Tlog backups can be run normally. If you perform backups on a secondary replica, there are some differences to be aware of:

  • You must configure your AlwaysOn group to specify where you want your backups to occur. You can do this through the New Availability Group setup GUI (as shown in picture above), or by using TSQL script (for more info click here These backup preferences will apply to both Full and Tlog backups.
  • The automated backup preference setting has no impact on ad-hoc backups. Manual ad-hoc backups will occur on whatever replica you perform the backup.
  • If you create your backup jobs through the Maintenance Plan, create the jobs on all replicas that have any intention of potentially performing backups. The job will automatically include the scripting logic that calls and checks the sys.fn_hadr_backup_is_preferred_replica function, and will not execute if the replica is not the preferred backup location. The job will not fail, it simply won’t execute. And in the event of failover the job will already exist on the replica the AlwaysOn Group has failed over to, and will execute accordingly.
  • Full Backups run on secondary replicas are copy only backups. Regular backups are not allowed. Copy-only backups do not impact the log chain or clear the differential bitmap.
  • Differential backups are not allowed on secondary replicas.
  • Tlog Backups only support regular backups on a secondary replica (copy only is not allowed). Tlog backups can be taken on any replica regardless of synchronization mode, and the log chain will remain consistent.
  • If databases exist in your SQL instance that are not part of the AlwaysOn Group, create separate maintenance jobs for those databases apart from the AlwaysOn databases so that the sys.fn_hadr_backup_is_preferred_replica function is not invoked. This way your non-AlwaysOn databases will not get lumped in with the AlwaysOn databases. Having separate maintenance routines for separate groups of databases can get a little hairy, as well as managing the jobs that need to run on each replica…for excellent guidance on this click here
  • Since the instances on the AlwaysOn replicas do not share an msdb database backups will not show up in the msdb backup tables.  This is important to know in the event you have to perform audits, or need to research backup history.


Integrity Checks

Perform Integrity Checks on all replicas if possible.

There are many differing opinions in the IT world on where to run the DBCC CHECKDB in an AlwaysOn Group.   Some believe integrity checks should be run on the secondary only, some believe it should be run wherever your database backups are happening, and some believe it should be run on all replicas.

SQL allows integrity checks to be offloaded to any of the secondary replicas in order to prevent resource contention on the primary replica, which keeps performance optimized. This is a good idea in theory, however if there is corruption in the data files of the primary database (assuming you have offloaded your integrity checks to a secondary) it will not be detected by the integrity check.   Even though data in the databases is the same across all replicas, the actual data files reside on the separate disk subsystems of each replica. The integrity check runs against the data files on each disk subsystem, and if your hardware or a file is corrupt on one of your replicas the integrity check run on another replica will not catch it.

Corruption on a primary does not propagate to the secondary because complete database pages are not sent, only the tlog records which only describe the physical changes made to the pages…they don’t contain the actual pages. Performing an integrity check on a primary replica doesn’t detect corruption on a secondary and vise-versa.   They are separate databases with their own data files kept in sync through transaction logs that describe the physical changes.

If your organization is extremely sensitive to performance for your AlwaysOn group, we recommend you restore a copy of your database backups to a server not in your AlwaysOn group (like a test server) and run the integrity checks there. This will provide a consistency check of your data, will ensure your backups are viable, and will eliminate overhead of running CHECKDB on the primary.

Index Maintenance

Perform Index Maintenance on primary replica

Because secondary databases are read-only, index maintenance must be performed on the primary replica. Index maintenance generates large amounts of logged changes by design and consequently will result in large transaction logs being replicated to the secondaries. Also index maintenance can cause the primary log files to grow large because the log cannot be truncated until redo has completed the changes in all secondary replicas.

Some ways to reduce resource contention for index maintenance is:

  1. Schedule during off peak times if possible
  2. Break up maintenance into smaller phases, spread out over time period
  3. Use MAXDOP to limit the number of processors used for the operation
  4. Rebuild/reorganize based upon specified criteria:
    1. Ignore heaps or small tables
    2. Rebuild/reorganize based upon fragmentation levels acceptable to your environment

If you are fortunate enough to have a large maintenance window to be able to rebuild your indexes on a daily basis, there is not much need to update statistics as rebuilding indexes does this by design. The frequency of your maintenance routines will depend on each individual SQL environment. As a general rule with our clients, we rebuild indexes once per week and update statistics every day…but again, your environment may require something different.

Update Statistics

Update Statistics on the primary replica.

SQL Server creates statistics on database tables and indexes that gives the query optimizer information about the distribution of data. This allows SQL to compile the most efficient query plan in order to improve query performance and run the queries as quickly as possible.

In an AlwaysOn Group of databases, statistics that are created and updated on the primary replica are persisted to the secondary replicas as part of the data replication of the transaction logs records.

But what if you’re using your secondaries for read-only operations and the secondary databases need different statistics than the primary? Databases on the secondary replicas are restricted to read-only access so statistics cannot be created on them! If the secondary replicas cannot create statistics, then performance of read-only workloads can suffer.

Thankfully SQL will create and maintain statistics on the secondary replicas in tempdb by using the suffix _readonly_database_statistic appended to the name of the temporary statistic. This keeps it separate from the permanent statistics that have been sent over from the primary. These temporary statistics can only be created by SQL Server, and you need to allow auto update and auto creation of statistics.

For more information on SQL statistics, click here


This article covers a high-level overview for performing some basic maintenance on your AlwaysOn databases. These are suggestions based on a simple AlwaysOn environment. There are always exceptions to these rules and circumstances where a system is set up in a more complex manner.   For example, you may want Tlog backups to occur on a different replica than the Full backups. You may have your AlwaysOn group also involved in log shipping or some other functionality that requires more or different maintenance routines.   There are methods to accommodate every environment, but is beyond the scope of this article. Hopefully these topics have given you a basic foundation for maintaining your AlwaysOn environment.

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 Trace Flag 4199

— by Lori Brown @SQLSupahStah

Once again, I find things about SQL Server that I had no idea were even available when I actually have time to do some in-depth reading and research. Most of the time, my regular duties keep me so busy that I don’t have time to play with new SQL functionality much so this makes it very easy to miss things like all the interesting trace flags that are available.


I found TF 4199 while doing some research on SQL 2016. Trace flag 4199 is used to turn on all hotfix or cumulative update (CU) functionality after it is installed. So, if you had an issue that should have been resolved by installing a CU or SP and did not find it happening, you could turn on trace flag 4199 to force all of the fixes to become active. Fixes and CUs are apparently not supposed to be turned on by default since they could affect execution plans. If you installed a specific hotfix, most of the time those come with their own trace flag that was needed to enable the fix. TF 4199 is the code to turn them all on.

To turn on a TF globally use the –T4199 command in your SQL startup parameters.


To turn on a TF for your session use DBCC TRACEON (4199).

TF 4199 can be enabled in a specific query by using the OPTION clause with QUERYTRACEON.

SELECT col1, col2, f4 FROM MyTable WHERE f1 = 0 AND f2 = 1 OPTION (QUERYTRACEON 4199)

In my defense, I have rarely installed hotfixes or CUs and have tended to install service packs and so far (knock on wood) to not have to use a trace flag to get expected benefits.

In SQL 2016, TF 4199 is enabled by default for databases in 130 compatibility.


For those of you who have not upgraded to SQL 2016, please be very cautious about enabling trace flags globally. Even with TF 4199, you can cause unexpected behavior to show up. If you do enable a trace flag, please have a good reason to do so. I have seen places (ahem** Microsoft ** ahem) that recommend TF 4199 be set globally but I would be very skeptical of doing this.

More info on trace flag 4199 can be found here:

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!