Category Archives: Helpful Scripts

Log Connections to SQL Instance

— by Ginger Keys

If you ever have a need to monitor connections to your SQL server, and any related information about the connections such as database, logins, etc., there are some DMVs that can give you tons of information. Previously you might have used the sys.sysprocesses table to derive much of this information, but this is being deprecated in the most recent versions of SQL server.

Instead, you can collect valuable information from these DMVs:

sys.dm_exec_sessions   https://msdn.microsoft.com/en-us/library/ms176013.aspx

sys.dm_exec_connections   https://msdn.microsoft.com/en-us/library/ms181509.aspx

sys.dm_exec_requests   https://msdn.microsoft.com/en-us/library/ms177648.aspx

In order to capture and retain connection information for my SQL server, I will create a small database and a table to hold some basic information. Of course you can alter the script to include more, less, or different data than what I am demonstrating below, to better fit your specific information needs.

I will create a database and a table, then insert data from two of the DMVs listed above.

Step 1 – Create a table to hold login activity

— Create a database

USE master

GO

CREATE DATABASE [Connections]

ON PRIMARY

( NAME = N’Connections’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Connections.mdf’ ,

SIZE = 1024MB , FILEGROWTH = 512MB )

LOG ON

( NAME = N’Connections_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Connections_log.ldf’ ,

SIZE = 1024MB , FILEGROWTH = 512MB)

GO

 

— Create table to hold Login info

USE [Connections]

GO

CREATE TABLE [dbo].[LoginActivity]

(

host_name [nvarchar](128) NULL,

program_name [nvarchar](128) NULL,

login_name [nvarchar](128) NOT NULL,

client_net_address [nvarchar](48) NULL,

DatabaseName [nvarchar](128) NOT NULL,

login_time [datetime] NOT NULL,

status [nvarchar](30) NOT NULL,

date_time[datetime] NOT NULL,

) ON [PRIMARY]

GO

 

Step 2 – Insert Data into Table

If you need to retain or archive this connection information, you can create a database which will hold the information, or export the results to a spreadsheet or other file. Otherwise you can simply select the information from the DMV below if you only need to see current data.

 

USE Connections

GO

INSERT INTO LoginActivity

(host_name,

program_name,

login_name,

client_net_address,

DatabaseName,

login_time,

status,

date_time)

— run the following select statement by itself to see connection info if you don’t want to save the output

SELECT

s.host_name,

s.program_name,

s.login_name,

c.client_net_address,

d.name AS DatabaseName,

s.login_time,

s.status,

GETDATE() AS date_time

FROM sys.dm_exec_sessions s

JOIN sys.dm_exec_connections c ON s.session_id = c.session_id

JOIN sys.databases d ON d.database_id = s.database_id

–where d.name = ‘ABCompany’ –can specify databases if needed

WHERE GETDATE() >= DATEADD(hh,-10, GETDATE()) –date range can be adjusted

 

Step 3 – View/Save Output Results

After inserting the data into my table, I can see the current connections from the last 10 hours (as per my insert statement). On a production server, this list would be far greater.

SELECT * FROM LoginActivity

Blog_20171005_1

From the columns I have included in my table:

Host_name – will give you the name of the workstation connecting – shows NULL for internal sessions.

Program_name – tells you the name of the client program or application connecting.

Client_net_address – provides the host address of each client connecting

Login_name, DatabaseName, and login_time – self-explanatory.

date_time – is the current day and time the query is run

Status – gives the status of the session, which will be running, sleeping, dormant, or preconnect.

This information can also be output to a text or excel file if preferred.

Blog_20171005_2

Conclusion

Being able to see users or applications making connections to your SQL Server can be useful or necessary for many reasons. The steps outlined above provide a general guideline for deriving connection information that can be altered to fit your organizational needs.

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

Get Index Column Info with Includes for One or Many Tables

— by Lori Brown @SQLSupahStah

I was recently working with one of my clients on some low hanging fruit type of query tuning. We had checked the cache for plans with missing index warnings in them and were trying to see if we could tweak and existing index or add a new index to speed things up. If you ever work with missing indexes, you surely have seen it recommend crazy things, duplicates or existing indexes or it wants something that can be added to an existing index.

The bottom line for missing index recommendations is that you should NEVER, EVER create a missing index unless you know for absolutely sure that the index does not exist and that it will really help a known performance issue. For instance, you can have missing index warnings on small tables that only return a couple of rows to the query. Those are usually not worth working on since scanning a small table can usually be done very quickly by SQL. To this day, I still find many databases that are way over indexed with indexes that were implemented simply because someone found a missing index warning and did not do their homework or they ran the dreaded Database Tuning Advisor which shoved a bunch of duplicate indexes into tables.

If you are wondering how to get a list of missing indexes, please check out Jeff Schwartz’s blog post on how to do this. (https://blog.sqlrx.com/2017/06/02/query-tuning-and-missing-index-recommendations/ ) This will give you a place to start. It is better if you know what query is throwing the missing index warning so it is a good idea to collect those either in a trace or extended events. Jeff builds on his first post and in his second post on the subject (https://blog.sqlrx.com/2017/07/20/handling-multiple-missing-index-recommendations-for-the-same-table/ ) also goes over the fun of having multiple missing index recommendations for a single table and how to deal with them.

Here’s a handy set of links for some of Jeff’s great index tuning work that you should really check out:

https://blog.sqlrx.com/2016/01/28/sql-server-20122014-extended-events-for-developers-part-1/

https://blog.sqlrx.com/2017/06/02/query-tuning-and-missing-index-recommendations/

https://blog.sqlrx.com/2017/07/20/handling-multiple-missing-index-recommendations-for-the-same-table/

https://blog.sqlrx.com/2017/08/10/how-indexing-affects-deletion-queries/

One of the things that I usually need when performance tuning is to know information about the existing indexes on specific tables. I always want to know what columns are in the indexes along with the included columns so that I can compare the existing indexes to the missing recommendations. This way I can better figure out if a recommendation is something that can be added to an existing index (like an included column) or if I really need to create a brand new index if it does not exist at all.

Like most DBA’s, I keep a toolkit with all kinds of handy scripts. However, I did not have one that would give me index included columns. I also wanted the query to be able to return info from one or many tables at the same time. This would be useful when dealing with things with lots of joins. I know that there are a few bloggers who have posted something similar but I wanted to have the ability to filter on one or multiple tables. So, here is what I came up with:

/********************************

Returns index columns with included columns

plus other needed index info for tables

in @tablelist variable

*********************************/

 

DECLARE @tablelist VARCHAR(1000)

DECLARE @sqlstr NVARCHAR(MAX)

 

SET @tablelist = ‘InvoiceLines,OrderLines,StockItemHoldings’ — comma delimited list of tables, can be one or multiples EX: ‘mytable’ or ‘mytable,nothertable,thirdtable’

 

— Query the tables

IF @tablelist <> OR @tablelist <> ‘?,?,?’

BEGIN

SET @tablelist = REPLACE(QUOTENAME(@tablelist,””), ‘,’, ”’,”’) — Add quotes so the IN clause will work

 

SET @sqlstr = ‘SELECT SCHEMA_NAME(o.schema_id) AS SchemaName

       ,o.name AS TableName

       ,i.name AS IndexName

       ,i.type_desc AS IndexType

       ,c.name AS ColumnName

       ,ic.is_included_column

       ,ic.index_column_id

       ,t.name

       ,c.max_length

       ,c.precision

       ,c.scale

FROM sys.indexes i

JOIN sys.index_columns ic ON i.index_id = ic.index_id

JOIN sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id

JOIN sys.objects o ON o.object_id = i.object_id AND c.object_id = i.object_id

JOIN sys.types t on t.user_type_id = c.user_type_id

WHERE o.name IN (‘+@tablelist+‘)

ORDER BY SchemaName, TableName, IndexName, index_column_id’

 

EXEC sp_executesql @sqlstr

–PRINT @sqlstr

END

All you have to provide is a comma separated list of the table(s) you are interested in for the @tablelist variable and it will do the rest. The output looks like this:

Blog_20170928_1

I found several bloggers who had made queries that would concatenate the columns together but truthfully I found those hard to read so I settled for a list with some extra info on the data and index types. Hope this is useful to someone out there.

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

 

Find Tables That Have Special Features Enabled

Find out if any of tables in your database have special features enabled using the queries below.  These features need to be understood and carefully managed.

— CDC Enabled Tables

select distinct t.name AS CDCTables

from sys.tables t

where t.is_tracked_by_cdc = 1

 

— File Tables — SQL 2012 +

select distinct t.name AS FileTables

from sys.tables t

where t.is_filetable = 1

 

— Temporal Tables — SQL 2016 +

select distinct t.name AS TemporalTables

from sys.tables t

where t.temporal_type > 0

 

— Stretch Enabled Tables — SQL 2016 +

select distinct t.name AS StretchTables

from sys.tables t

where t.is_remote_data_archive_enabled > 0

 

— External Tables — SQL 2016 +

select distinct t.name AS ExternalTables

from sys.tables t

where t.is_external > 0

https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server

https://docs.microsoft.com/en-us/sql/relational-databases/blob/filetables-sql-server

https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables

https://docs.microsoft.com/en-us/sql/sql-server/stretch-database/enable-stretch-database-for-a-table

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql

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

Find Out Who Changed the Database Recovery Model

— By Lori Brown @SQLSupahStah

I ran into a situation where we were working on a migration and had been directed to put all databases into FULL recovery model in anticipation of using log shipping to push databases to the new server. Once we are ready to go live on the new server the plan was to ship the last transaction logs and then restore them WITH RECOVERY in an effort to make the final cutover as quick as possible. Of course this means that we had to make sure that all databases were having regular log backups, which we did. Things were going along nicely until we started receiving log backup failure notifications.

Upon checking things, we found that one of the databases had been changed to SIMPLE recovery model. You can find this type of information in the default trace or you can simply scroll through the SQL error logs until you find the entry that you are looking for. If you have a busy instance that has a lot of entries in the error log, this can be a bit time consuming so I came up with a set of queries that will grab the error log entry and attempt to tie it to the info in the default trace so that it was easier to identify WHO was the culprit who made an unauthorized change to the database properties.

 

DECLARE @tracefile VARCHAR(500)

DECLARE @ProcessInfoSPID VARCHAR(20)

 

CREATE TABLE [dbo].[#SQLerrorlog](

[LogDate] DATETIME NULL,

[ProcessInfo] VARCHAR(10) NULL,

[Text] VARCHAR(MAX) NULL

)

 

/*

Valid parameters for sp_readerrorlog

1 – Error log: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…

2 – Log file type: 1 or NULL = error log, 2 = SQL Agent log

3 – Search string 1

4 – Search string 2

 

Change parameters to meet your needs

*/

— Read error log looking for the words RECOVERY

–and either FULL, SIMPLE or BULK_LOGGED indicating a change from prior state

INSERT INTO #SQLerrorlog

EXEC sp_readerrorlog 0, 1, ‘RECOVERY’, ‘FULL’

 

INSERT INTO #SQLerrorlog

EXEC sp_readerrorlog 0, 1, ‘RECOVERY’, ‘SIMPLE’

 

INSERT INTO #SQLerrorlog

EXEC sp_readerrorlog 0, 1, ‘RECOVERY’, ‘BULK_LOGGED’

 

UPDATE #SQLerrorlog

SET ProcessInfo = SUBSTRING(ProcessInfo,5,20)

FROM #SQLerrorlog

WHERE ProcessInfo LIKE ‘spid%’

 

— Get path of default trace file

SELECT @tracefile = CAST(value AS VARCHAR(500))

FROM sys.fn_trace_getinfo(DEFAULT)

WHERE traceid = 1

AND property = 2

 

— Get objects altered from the default trace

SELECT IDENTITY(int, 1, 1) AS RowNumber, *

INTO #temp_trc

FROM sys.fn_trace_gettable(@tracefile, default) g — default = read all trace files

WHERE g.EventClass = 164

 

SELECT t.DatabaseID, t.DatabaseName, t.NTUserName, t.NTDomainName,

t.HostName, t.ApplicationName, t.LoginName, t.SPID, t.StartTime, l.Text

FROM #temp_trc t

JOIN #SQLerrorlog l ON t.SPID = l.ProcessInfo

WHERE t.StartTime > GETDATE()-1 — filter by time within the last 24 hours

ORDER BY t.StartTime DESC

 

DROP TABLE #temp_trc

DROP TABLE #SQLerrorlog

GO

 

You can find more on the following:

sp_readerrorlog is an undocumented procedure that actually uses xp_readerrorlog – https://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/

sys.fn_trace_getinfo – https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-trace-getinfo-transact-sql

sys.fn_trace_gettable – https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-trace-gettable-transact-sql

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRxSupport@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!

Use MSDB to Get Database Backup Size and Total Time For Each

— by Lori Brown

We recently started using a third party software to do our in-house SQL backups so that the backup files are stored in a redundant and safe place. To confirm that the software was indeed compressing backups as it stated it would, we wanted to see what each backup size actually was in SQL so that we could compare that to what the software was telling us.

SQL stores lots of handy backup information in msdb in the backupset and backupmediafamily tables.

https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupset-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupmediafamily-transact-sql

Here is my query. I am only wanting the information from the last 24 hours so have filtered the start date by subtracting 1 day from today. I have also provided some commented out options in case someone needs them.

— database backup size and how long it took to do backup

SELECT bs.database_name AS DatabaseName

, CAST(bs.backup_size/1024.0/1024/1024 AS DECIMAL(10, 2)) AS BackupSizeGB

, CAST(bs.backup_size/1024.0/1024 AS DECIMAL(10, 2)) AS BackupSizeMB

–, CAST(bs.compressed_backup_size/1024.0/1024/1024 AS DECIMAL(10, 2)) AS CompressedSizeGB   

       –, CAST(bs.compressed_backup_size/1024.0/1024 AS DECIMAL(10, 2)) AS CompressedSizeMB

, bs.backup_start_date AS BackupStartDate

, bs.backup_finish_date AS BackupEndDate

, CAST(bs.backup_finish_date – bs.backup_start_date AS TIME) AS AmtTimeToBkup

, bmf.physical_device_name AS BackupDeviceName

FROM msdb.dbo.backupset bs JOIN msdb.dbo.backupmediafamily bmf

ON bs.media_set_id = bmf.media_set_id

WHERE

–bs.database_name = ‘MyDatabase’ and   — uncomment to filter by database name

bs.backup_start_date > DATEADD(dd, -1, GETDATE()) and

bs.type = ‘D’ — change to L for transaction logs

ORDER BY bs.database_name, bs.backup_start_date

And, here is the output.

Blog_20170726_1

It turned out that the software was indeed compressing all backups so that was a good thing.

There is a lot more info that can be pulled from msdb regarding backups. Have some fun and experiment with getting information that you need from there. Here are some links to some other backup related topics that we have blogged about already.

https://blog.sqlrx.com/2017/03/02/sql-server-backup-and-restore-primer/

https://blog.sqlrx.com/2013/04/23/backup-database-commands/

https://blog.sqlrx.com/2013/05/28/be-prepared-for-a-crisis/

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

 

Get a List of Tables That Are Compressed

Find out if any of your tables are compressed in your database using the query below. Compressed tables can save space and make queries run faster.

— Compressed Tables
select distinct t.name AS CompressedTables
from sys.partitions p
inner join sys.tables t
on p.object_id = t.object_id
where p.data_compression > 0

If you don’t have any tables compressed but think you might want to compress some, you can check your data compression savings by running the stored procedure sp_estimate_data_compression_savings for your targeted table.

USE WideWorldImporters;
GO
EXEC sp_estimate_data_compression_savings ‘Sales’, ‘Invoices’, NULL, NULL, ‘ROW’ ;
GO

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-estimate-data-compression-savings-transact-sql

List Partitioned Tables And Other Info About Them

— By Lori Brown @SQLSupahStah

Here is a good way to find out if any of your tables are partitioned in your database using the query below. It is important to know so that you can find out how the tables are partitioned.

— Partitioned Tables

SELECT DISTINCT t.name AS ParitionedTables

FROM sys.partitions p

INNER JOIN sys.tables t

ON p.object_id = t.object_id

WHERE p.partition_number <> 1

If you have partitioned tables here is a good way to find out how it is partitioned up.

— Get partition info

SELECT SCHEMA_NAME(o.schema_id) + ‘.’ + OBJECT_NAME(i.object_id) AS [object]

, p.partition_number AS [p#]

, fg.name AS [filegroup]

, p.rows

, au.total_pages AS pages

, CASE boundary_value_on_right

WHEN 1 THEN ‘less than’

ELSE ‘less than or equal to’ END AS comparison

, rv.value

, CONVERT (VARCHAR(6), CONVERT (INT, SUBSTRING (au.first_page, 6, 1) +

SUBSTRING (au.first_page, 5, 1))) + ‘:’ + CONVERT (VARCHAR(20),

CONVERT (INT, SUBSTRING (au.first_page, 4, 1) +

SUBSTRING (au.first_page, 3, 1) + SUBSTRING (au.first_page, 2, 1) +

SUBSTRING (au.first_page, 1, 1))) AS first_page

FROM sys.partitions p

INNER JOIN sys.indexes i

ON p.object_id = i.object_id

AND p.index_id = i.index_id

INNER JOIN sys.objects o

ON p.object_id = o.object_id

INNER JOIN sys.system_internals_allocation_units au

ON p.partition_id = au.container_id

INNER JOIN sys.partition_schemes ps

ON ps.data_space_id = i.data_space_id

INNER JOIN sys.partition_functions f

ON f.function_id = ps.function_id

INNER JOIN sys.destination_data_spaces dds

ON dds.partition_scheme_id = ps.data_space_id

AND dds.destination_id = p.partition_number

INNER JOIN sys.filegroups fg

ON dds.data_space_id = fg.data_space_id

LEFT OUTER JOIN sys.partition_range_values rv

ON f.function_id = rv.function_id

AND p.partition_number = rv.boundary_id

WHERE i.index_id < 2

AND o.object_id = OBJECT_ID(‘dbo.SomeTableName’);

List Tables That May Be Over Indexed

— By Lori Brown

While not having enough indexes can be bad for query performance, having too many indexes can also be just as bad. Use the query below to get a list of tables in your database that has more than 10 indexes.

— Tables with large number of indexes

select t.name as TablesWithLargeNumInx, count(i.name) as CountIndexes

from sys.indexes i

inner join sys.tables t

on i.object_id = t.object_id

where i.index_id > 0

group by t.name

having count(i.name) > 10

If you suspect that you have too many indexes on your tables, you can also check the sys.dm_db_index_usage_stats dynamic management view to know if indexes on your heavily indexed tables are being used well. (Hint: seeks are good and scans are not so much)

select u.user_seeks, u.user_lookups, u.user_scans

from sys.dm_db_index_usage_stats u

inner join sys.indexes i

on u.object_id = i.object_id and u.index_id = i.index_id

WHERE u.object_id=object_id(‘dbo.SomeTableName’)

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql

SQL Toolkit – Get List of Database Options and Features

–By Lori Brown

I am going to start up a thread with some queries that I consider to be important to DBA’s. As a consultant, many times we have to start managing SQL instances without any knowledge of why it has been configured in various ways. We usually try to work with our clients to figure things out but many times even they don’t know what has been done to the SQL configuration and database options or features. This can happen when a software vendor is allowed to set up the SQL instance or when a developer or DBA enables certain things so that work can be done. I have some scripts that I keep in my Toolkit and can use when I need to better understand the environment. At least armed with a little knowledge, I can ask questions and help my clients better understand things.

Here is a query that I use to find out what database options have been set as well as what advanced features maybe enabled. I tried to make the output display in plain English so that if I have to pass the info on to a client, they can understand it as well. Please note that at the end of each column, I have put a comment indicating if the feature is version specific. If you don’t have that version of SQL, simply comment out the lines that don’t apply to you. As new versions of SQL come out, I’ll try to test and keep things in the Toolkit up to date.

— get a list of interesting database settings

SELECT d.name AS DBName

,(CASE d.compatibility_level WHEN 80 THEN ‘SQL 2000’

WHEN 90 THEN ‘SQL 2005’

WHEN 100 THEN ‘SQL 2008’

WHEN 110 THEN ‘SQL 2012’

WHEN 120 THEN ‘SQL 2014’

WHEN 130 THEN ‘SQL 2016’ END) AS Compatibility

,d.user_access_desc AS UserAccessSetting

,(CASE d.is_auto_close_on WHEN 0 THEN ‘OFF’ ELSE ‘ON’ END) AS AutoCloseSetting

,(CASE d.is_auto_shrink_on WHEN 0 THEN ‘OFF’ ELSE ‘ON’ END) AS AutoShrinkSetting

,d.recovery_model_desc AS RecoveryModel

,d.page_verify_option_desc AS PageVerifySetting

,(CASE d.is_auto_create_stats_on WHEN 0 THEN ‘OFF’ ELSE ‘ON’ END) AS AutoCreateStats

,(CASE d.is_auto_update_stats_on WHEN 0 THEN ‘OFF’ ELSE ‘ON’ END) AS AutoUpdateStats

,(CASE d.is_cdc_enabled WHEN 0 THEN ‘OFF’ ELSE ‘ON’ END) AS ChangeDataCaptureSetting

,(CASE d.is_encrypted WHEN 0 THEN ‘OFF’ ELSE ‘ON’ END) AS TDESetting

— Always On info

,ag.name AS AGName

,rs.role_desc AS AGRole

,UPPER(ag.automated_backup_preference_desc) AS AGBkupPref

— Other interesting stuff

,d.containment_desc AS ContainmentSetting — SQL 2012 +

,d.delayed_durability_desc AS DelDurabilitySetting — SQL 2014 +

,(CASE d.is_remote_data_archive_enabled WHEN 0 THEN ‘Stretch-disabled’ ELSE ‘Stretch-disabled’ END) AS StretchDBSetting — SQL 2016 +

,(CASE d.is_query_store_on WHEN 0 THEN ‘Disabled’ ELSE ‘Enabled’ END) AS QueryStoreSetting — SQL 2016 +

FROM sys.databases d

LEFT JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id

LEFT JOIN sys.availability_groups ag ON adc.group_id = ag.group_id

LEFT JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id

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

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.

blog_20170209_1

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

GO

SELECT

OBJECT_NAME(i.OBJECT_ID) AS TableName,

i.name 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,i.name

ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id

blog_20170209_2

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: https://gist.github.com/dalenewman/6377911

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

Use AdventureWorks2016 –use your database name

GO

SELECT * FROM INFORMATION_SCHEMA.TABLES

blog_20170209_3

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,  

Department,

                    EmployeeDepartmentHistory,

                    EmployeePayHistory,

                    Employee,

                    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.

blog_20170209_4

Conclusion

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 SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!