Migrate a database from SQL Server 2008 to 2016 and use new features

By: Pablo Echeverria | Comments | Related: > Upgrades and Migrations

Problem

We have a database that requires trace flag 4136 due to performance issues with parameter sniffing, so it had to be put on its own instance to disable the parameter sniffing because most of the queries are created dynamically. So we decided to move the database to SQL Server 2016 where parameter sniffing is a database scoped configuration and also simplifies performance tuning using query store.

But there are some constraints with this migration:

We want to use multiple filegroups, because this database grows quickly, and anyway it is a good practice.
There are 915 tables, from which some are HEAP and others have BLOB data (more on this ahead).
The database size is 104 GB.
The target instance is Enterprise, so we can partition tables (also more on this ahead).

Solution

The first step is to take a backup from the SQL Server 2008 database, copy it to a drive in the SQL Server 2016 instance, and restore the database.

After it has been restored, we see the following output messages:

100 percent processed.
Processed 9629048 pages for database 'test', file 'test_dat' on file 1.
Processed 1094 pages for database 'test', file 'test_log' on file 1.
Converting database 'test' from version 661 to the current version 852.
Database 'test' running the upgrade step from version 661 to version 668.
…
Database 'test' running the upgrade step from version 851 to version 852.
RESTORE DATABASE successfully processed 9630142 pages in 267.635 seconds (281.112 MB/sec).

Although you can see the structure has been upgraded to the most recent version (from 661 to 852), the database compatibility is still 2008, so you can’t use the new features until you execute the following statement (130 is for SQL Server 2016):

USE [master]
ALTER DATABASE [test] SET COMPATIBILITY_LEVEL = 130

And now we can disable parameter sniffing at the database level:

USE [test]
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = Off;

And enable query store:

USE [master]
ALTER DATABASE [test] SET QUERY_STORE = ON
ALTER DATABASE [test] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, INTERVAL_LENGTH_MINUTES = 30, MAX_STORAGE_SIZE_MB = 4096, QUERY_CAPTURE_MODE = AUTO)

This database had only the PRIMARY filegroup, so we need to add a MAIN filegroup with two files and make this the default filegroup:

USE [master]
ALTER DATABASE [test] ADD FILEGROUP [MAIN]
ALTER DATABASE [test] ADD FILE (NAME = 'MAIN01', FILENAME = 'D:\DATA\MAIN01.ndf') TO FILEGROUP [MAIN]
ALTER DATABASE [test] ADD FILE (NAME = 'MAIN02', FILENAME = 'D:\DATA\MAIN02.ndf') TO FILEGROUP [MAIN]
USE [test]
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = 'MAIN') ALTER DATABASE [test] MODIFY FILEGROUP [MAIN] DEFAULT

Tables to be moved

We can query the tables that need to be moved and their type by using the script from this page:

SELECT DS.name 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
      ,SCH.name AS SchemaName
      ,OBJ.type_desc AS ObjectType      
      ,OBJ.name AS ObjectName
      ,IDX.type_desc AS IndexType
      ,IDX.name 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)
            OR
            (AU.type = 2
             AND AU.container_id = PA.partition_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
ORDER BY DS.name
        ,SCH.name
        ,OBJ.name
        ,IDX.name

There are 4264 records and are very diverse, here is a sample of them:

DataSpaceName	AllocationDesc	ObjectType	ObjectName	IndexType	IndexName
PRIMARY	IN_ROW_DATA	USER_TABLE	A_D	CLUSTERED	PK_A_D
PRIMARY	IN_ROW_DATA	USER_TABLE	Acctdfts	NONCLUSTERED	acctdflt_ndx
PRIMARY	IN_ROW_DATA	USER_TABLE	Acctdfts	CLUSTERED	acctdflt_ndx1
PRIMARY	IN_ROW_DATA	USER_TABLE	Bulletin	NONCLUSTERED	bulletin_ndx
PRIMARY	ROW_OVERFLOW_DATA	USER_TABLE	Bulletin	CLUSTERED	bulletin_ndx
PRIMARY	IN_ROW_DATA	USER_TABLE	Bulletin	CLUSTERED	bulletin_ndx
PRIMARY	IN_ROW_DATA	USER_TABLE	Bulletin	NONCLUSTERED	bulletin_ndx1
PRIMARY	IN_ROW_DATA	USER_TABLE	Comm	NONCLUSTERED	comm_ndx
PRIMARY	LOB_DATA	USER_TABLE	Comm	CLUSTERED	comm_ndx1
PRIMARY	IN_ROW_DATA	USER_TABLE	Comm	CLUSTERED	comm_ndx1
PRIMARY	IN_ROW_DATA	USER_TABLE	Comm	NONCLUSTERED	comm_ndx2
PRIMARY	IN_ROW_DATA	USER_TABLE	Comm	NONCLUSTERED	comm_ndx3
PRIMARY	IN_ROW_DATA	USER_TABLE	Asset	HEAP	NULL
PRIMARY	IN_ROW_DATA	USER_TABLE	Cond	HEAP	NULL
PRIMARY	ROW_OVERFLOW_DATA	USER_TABLE	Cond	HEAP	NULL
PRIMARY	IN_ROW_DATA	USER_TABLE	Cond	NONCLUSTERED	cond_ndx
PRIMARY	IN_ROW_DATA	USER_TABLE	Cond	NONCLUSTERED	cond_ndx3
PRIMARY	IN_ROW_DATA	USER_TABLE	Cond	NONCLUSTERED	cond_ndx4
PRIMARY	IN_ROW_DATA	USER_TABLE	Desc	HEAP	NULL
PRIMARY	LOB_DATA	USER_TABLE	Desc	HEAP	NULL

To move the user tables to the new filegroup, we’re going to perform the steps in the following order:

Generate a script of all PRIMARY KEY, CLUSTERED and NONCLUSTERED indexes, then perform a search-replace within the script, and when it is executed, it will re-create the indexes but in the new filegroup.
For the HEAP tables, create a clustered non-unique index on any column on the new filegroup and then drop it.
For the BLOB tables:
1. If these aren’t heaps, use a partition function to move the CLUSTERED or NONCLUSTERED index to the new filegroup, and then remove the partitioning leaving the index in the new filegroup.
2. If these are heaps, use a partition function to create an index in the new filegroup partitioned, then remove the partitioning, and finally drop the index.

Move tables with a PRIMARY KEY, CLUSTERED or NONCLUSTERED index

We need to generate a script of the indexes. There is an option in SSMS to script objects from a database, but you’re forced to include the table definition in it and this is hard to remove later. There is another option using dbatools.io, but it requires PowerShell V3, which is not the case for me.

So instead, we use the code from this page:

$Filepath='C:\' # local directory to save build-scripts to
$DataSource='SQL2016' # server name and instance
$Database='test'# the database to copy from
# set "Option Explicit" to catch subtle errors
set-psdebug -strict
$ErrorActionPreference = "stop" # you can opt to stagger on, bleeding, if an error occurs
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$ms='Microsoft.SqlServer'
$v = [System.Reflection.Assembly]::LoadWithPartialName( "$ms.SMO")
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
[System.Reflection.Assembly]::LoadWithPartialName("$ms.SMOExtended") | out-null
   }
$My="$ms.Management.Smo" #
$s = new-object ("$My.Server") $DataSource
if ($s.Version -eq  $null ){Throw "Can't find the instance$Datasource"}
$db= $s.Databases[$Database] 
if ($db.name -ne $Database){Throw "Can't find the database '$Database' in$Datasource"};
$transfer = new-object ("$My.Transfer") $db
$CreationScriptOptions = new-object ("$My.ScriptingOptions") 
$CreationScriptOptions.ExtendedProperties= $false
$CreationScriptOptions.DRIAll= $false
$CreationScriptOptions.Indexes= $true
$CreationScriptOptions.ClusteredIndexes= $true
$CreationScriptOptions.Triggers= $false
$CreationScriptOptions.ScriptBatchTerminator = $true # this only goes to the file
$CreationScriptOptions.IncludeHeaders = $false
$CreationScriptOptions.ToFileOnly = $true #no need of string output as well
$CreationScriptOptions.IncludeIfNotExists = $false
$CreationScriptOptions.Filename =  "$($FilePath)\$($Database)_Build.sql"; 
$CreationScriptOptions.PrimaryObject = $false
$CreationScriptOptions.Default = $false
$CreationScriptOptions.AllowSystemObjects = $false
$CreationScriptOptions.AgentJobId = $false
$CreationScriptOptions.ScriptSchema = $true
$CreationScriptOptions.LoginSid = $false
$transfer = new-object ("$My.Transfer") $s.Databases[$Database]
$transfer.options=$CreationScriptOptions # tell the transfer object of our preferences
$transfer.ScriptTransfer()

Note that you need to change the variables for your environment:

$Filepath: is the path where the script will be saved, the name of the script will be the name of the database followed by “_Build.sql”.
$DataSource: Is the name of the SQL instance where the database exists. This can be either the 2008 or the 2016 instance.
$Database: The name of the database to script. This can be either the name of the 2008 or the 2016 database, the one that matches the $DataSource.

The output is similar to the following:

CREATE USER [SOL] FOR LOGIN [SOL] WITH DEFAULT_SCHEMA=[dbo] 
CREATE SCHEMA [SOL] 
CREATE FULLTEXT CATALOG [textcatalog]WITH ACCENT_SENSITIVITY = ON 
CREATE PARTITION FUNCTION [DATETIME_PF](datetime) AS RANGE RIGHT FOR VALUES (N'2000-01-01T00:00:00.000', N'2001-01-01T00:00:00.000') 
CREATE PARTITION SCHEME [DATETIME_PS] AS PARTITION [DATETIME_PF] TO ([PRIMARY], [PRIMARY]) 
CREATE TYPE [dbo].[GMToffset] FROM [nvarchar](8) NULL 
ALTER TABLE [dbo].[A_D] ADD  CONSTRAINT [PK_A_D] PRIMARY KEY CLUSTERED 
( 
[A_D_ID] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
ALTER TABLE [dbo].[B_E_D_PERM] ADD  CONSTRAINT [UK_B_E_D_PERM] UNIQUE NONCLUSTERED 
(
   [M_SHIP_ID] ASC,
   [T_ID] ASC,
   [D_VER_NUM] ASC,
   [P_GRP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
			
CREATE UNIQUE CLUSTERED INDEX [acctdflt_ndx1] ON [dbo].[acctdfts] 
( 
[oid] ASC, 
[dfltgrp] ASC, 
[grpval] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY] 
CREATE CLUSTERED INDEX [assethist_ndx1] ON [dbo].[assethist] 
( 
[sid] ASC, 
[wno] ASC, 
[assetno] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY] 
CREATE UNIQUE NONCLUSTERED INDEX [acctdft_ndx] ON [dbo].[acctdfts] 
( 
[acctdftsid] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY] 
CREATE NONCLUSTERED INDEX [actci_ndx2] ON [dbo].[actci] 
( 
[clssstctid] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY] 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  
CREATE PROCEDURE [dbo].[Process] 
WITH EXECUTE AS 'SQL' 
AS 
BEGIN 
SET NOCOUNT ON 
DECLARE @message_body XML 
END

Note that because of the complexity of this database, the script has 251662 lines, and even when we specified to exclude most of the objects, some are still included.

Then we must perform the following changes in this script manually:

Remove statements to create users.
Remove statements to create schemas.
Remove statements to create fulltext catalogs.
Remove statements to create types.
Remove statements to create stored procedures and functions.
Create a statement to drop the partition function and scheme before they’re created, and redirect the partition scheme to the new filegroup.

And perform the following changes in the script as a whole, not line by line:

For the “PRIMARY KEY” statements, use Notepad++ to replace this regular expression: ^.*?ALTER TABLE (.*?) ADD CONSTRAINT (.*?) PRIMARY KEY CLUSTERED $\R?
With this regular expression: CREATE UNIQUE CLUSTERED INDEX \2 ON \1

So, the following statement:

ALTER TABLE [dbo].[A_D] ADD  CONSTRAINT [PK_A_D] PRIMARY KEY CLUSTERED 
( 
[A_D_ID] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Will be converted into:

CREATE UNIQUE CLUSTERED INDEX [PK_A_D] ON [dbo].[A_D](
   [A_D_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Using Notepad++ - Description: Search and replace using regular expression

For the “UNIQUE NONCLUSTERED” statements, use Notepad++ to replace this regular expression: ^.*?ALTER TABLE (.*?) ADD CONSTRAINT (.*?) UNIQUE NONCLUSTERED $\R?
With this regular expression: CREATE UNIQUE NONCLUSTERED INDEX \2 ON \1

So the following statement:

ALTER TABLE [dbo].[B_E_D_PERM] ADD  CONSTRAINT [UK_B_E_D_PERM] UNIQUE NONCLUSTERED 
(
   [M_SHIP_ID] ASC,
   [T_ID] ASC,
   [D_VER_NUM] ASC,
   [P_GRP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Will be converted into:

CREATE UNIQUE NONCLUSTERED INDEX [UK_B_E_D_PERM] ON [dbo].[B_E_D_PERM](
   [M_SHIP_ID] ASC,
   [T_ID] ASC,
   [D_VER_NUM] ASC,
   [P_GRP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

To recreate the primary key and unique nonclustered indexes, replace: PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
with: DROP_EXISTING = ON

To recreate all other clustered and nonclustered indexes, replace: DROP_EXISTING = OFF
with: DROP_EXISTING = ON

To specify the new filegroup, replace: ON [PRIMARY]
With: ON [MAIN]

After executing the script, all of the indexes (except the ones with BLOB data) will be on the new filegroup, as you can confirm by running the query in “Tables to be moved”.

Move HEAP tables

Using the output of the query in “Tables to be moved”, we just need the name of any column in each of the HEAP tables to create an index on that and drop the index, which is the way to move a HEAP table according to this link.

So, we use the following query to get any column in each of the tables excluding any BLOB type (in this case ‘image’):

SELECT o.name, MIN(c.name), t.name
  FROM syscolumns c
INNER JOIN sysobjects o ON o.id = c.id AND o.type = 'U'
INNER JOIN sys.types t ON t.system_type_id = c.xtype
 WHERE t.name <> 'image'
GROUP BY o.name, t.name

And merge both tables using this Excel function: =VLOOKUP(H2,Sheet2!$A$2:$C$3183,2,FALSE)

Then we generate the script using this Excel function: ="CREATE CLUSTERED INDEX [IX_"&H2&"] ON ["&H2&"](["&K2&"]) ON [MAIN]; DROP INDEX [IX_"&H2&"] ON ["&H2&"];"

We’re able to generate a script for all heaps that looks like the following:

CREATE CLUSTERED INDEX [IX_ACCT_P_T_05132014] ON [ACCT_P_T_05132014]([ACCT_P_ID]) ON [MAIN]; DROP INDEX [IX_ACCT_P_T_05132014] ON [ACCT_P_T_05132014];

After running the script, all of the HEAP tables (except the ones with BLOB data) will be on the new filegroup, as you can confirm by running the query in “Tables to be moved”.

Move BLOB tables

In order to move these tables, we need to partition them into the new filegroup according to this link, that’s why we need an Enterprise version of SQL Server. This will be a manual process, so hopefully there aren’t too much of these, in my case there were only a few, and those are returned in the output of the query in “Tables to be moved”. But if there are multiple, you can use Excel formulas to make it easier.

So, we start by defining the partition functions and schemes we will be using, depending on the columns to partition, here’s a sample of the possible functions we will need:

CREATE PARTITION FUNCTION pf_bigint (BIGINT) AS RANGE RIGHT FOR VALUES (9223372036854775807)
CREATE PARTITION SCHEME ps_bigint AS PARTITION pf_bigint TO ( [MAIN], [PRIMARY] )
CREATE PARTITION FUNCTION pf_int (INT) AS RANGE RIGHT FOR VALUES (2147483647)
CREATE PARTITION SCHEME ps_int AS PARTITION pf_int TO ( [MAIN], [PRIMARY] )
CREATE PARTITION FUNCTION pf_nchar16 (NCHAR(16)) AS RANGE RIGHT FOR VALUES ('ZZZZZZZZZZZZZZZZ')
CREATE PARTITION SCHEME ps_nchar16 AS PARTITION pf_nchar24 TO ( [MAIN], [PRIMARY] )
CREATE PARTITION FUNCTION pf_nchar24 (NCHAR(24)) AS RANGE RIGHT FOR VALUES ('ZZZZZZZZZZZZZZZZZZZZZZZZ')
CREATE PARTITION SCHEME ps_nchar24 AS PARTITION pf_nchar24 TO ( [MAIN], [PRIMARY] )
CREATE PARTITION FUNCTION pf_nchar30 (NCHAR(30)) AS RANGE RIGHT FOR VALUES ('ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ')
CREATE PARTITION SCHEME ps_nchar30 AS PARTITION pf_nchar24 TO ( [MAIN], [PRIMARY] )
CREATE PARTITION FUNCTION pf_datetime (datetime) AS RANGE RIGHT FOR VALUES ('9999-12-31 23:59:59')
CREATE PARTITION SCHEME ps_datetime AS PARTITION pf_datetime TO ( [MAIN], [PRIMARY] )

Note that in the partition function, we’re specifying the max value of the data type to be used (to make it simpler and to reuse the same partition function for all tables).

CLUSTERED and NONCLUSTERED indexes

We need to create manually the scripts, using this record as an example:

DataSpaceName	AllocationDesc	ObjectType	ObjectName	IndexType	IndexName
PRIMARY	LOB_DATA	USER_TABLE	Comm	CLUSTERED	comm_ndx1

Get the index script generated in the section “Move tables with a PRIMARY KEY, CLUSTERED or NONCLUSTERED index”

CREATE UNIQUE CLUSTERED INDEX [comm_ndx1] ON [dbo].[Comm]([SA_REQ_ID] ASC, [REVISION_ID] ASC)WITH (DROP_EXISTING = ON) ON [MAIN]

Get the data type of the column, in this case the column SA_REQ_ID was of type BIGINT.
Create the following script:

CREATE UNIQUE CLUSTERED INDEX [comm_ndx1] ON [dbo].[Comm]([SA_REQ_ID] ASC, [REVISION_ID] ASC)WITH (DROP_EXISTING = ON) ON ps_bigint(SA_REQ_ID)
CREATE UNIQUE CLUSTERED INDEX [comm_ndx1] ON [dbo].[Comm]([SA_REQ_ID] ASC, [REVISION_ID] ASC)WITH (DROP_EXISTING = ON) ON [MAIN]

HEAP tables

We need to create a script, using this record as an example:

DataSpaceName	AllocationDesc	ObjectType	ObjectName	IndexType	IndexName
PRIMARY	LOB_DATA	USER_TABLE	Desc	HEAP	NULL

Search for any column in the table that is not of BLOB type, and its size. In this case, the column “P_SCHD_INST_ID” and is of type BIGINT.
Create the following script:

CREATE CLUSTERED INDEX [IX_Desc] ON [Desc]([P_SCHD_INST_ID]) ON ps_bigint(P_SCHD_INST_ID)
CREATE CLUSTERED INDEX [IX_Desc] ON [Desc]([P_SCHD_INST_ID]) WITH(DROP_EXISTING = ON) ON [MAIN]
DROP INDEX [IX_Desc] ON [Desc];

Finally, we join the script of the partition functions and schemes to use, the script generated for each table, and another script to drop the partition schemes and functions.

After running the script, all of the user tables are on the new filegroup, as you can confirm by running the query in “Tables to be moved”.

Final steps

We just need to confirm that all user tables are in the new filegroup and the system tables are in the PRIMARY filegroup, by running the query in “Tables to be moved”. Then, we need to shrink the datafile used in the PRIMARY filegroup, which is big because there used to be the user tables, with the following instruction:

USE [test]
DBCC SHRINKFILE (N'test_dat' , 0)

And we’re done, the database has been migrated to SQL Server 2016 with compatibility for SQL Server 2016 and with the user tables out of the PRIMARY filegroup.

Next Steps

You can learn more about poor performing queries and the possible fixes here.
You can learn more about correcting parameter sniffing here.
You can learn more about using multiple filegroups in a database here.

About the author

Pablo Echeverria is a talented database administrator and C#.Net software developer since 2006. Pablo wrote the book "Hands-on data virtualization with Polybase". He is also talented at tuning long-running queries in Oracle and SQL Server, reducing the execution time to milliseconds and the resource usage up to 10%. He loves learning and connecting new technologies providing expert-level insight as well as being proficient with scripting languages like PowerShell and bash. You can find several Oracle-related tips in his LinkedIn profile.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips