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:
- 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.
- 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]
- 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
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips