Category: DBA Utilities

DBA Utilities

How to change mdf and ldf files path to instance default path in SQL Server

Here is a code snippet to change the mdf and ldf file path of an existing database to instance default path in SQL Server.
If you want to move to fixed path, then you can change the script as required.

Assumptions:
1. The script is mainly written for SQL Server 2016, for other version, please change as required.
2. The script will not execute or change the path automatically. It just prints the statements to execute. The user can validate the scripts and do the action as required. This is to prevent any unexpected events.


--Print 'Collect Default Data & Log information'
declare @DefLOG nvarchar(512)
declare @DefDATA nvarchar(512)

--Prepare the SQL statements for remapping
if (Cast(SERVERPROPERTY('Productmajorversion') as varchar(2))='13')
Begin

Set @DefLog = Cast( Serverproperty('InstanceDefaultLogPath') as varchar(512))
Set @DefDATA = Cast( Serverproperty('InstanceDefaultDataPath') as varchar(512))

--Print 'Move all datafiles'
declare cur_move cursor for
select --DB_NAME(dbid),name,filename, reverse(SUBSTRING(reverse(filename),1,CHARINDEX('\',reverse(filename))-1)),
'ALTER DATABASE ['+DB_NAME(dbid)+'] MODIFY FILE ( NAME = '''+name+''' , FILENAME = '''+@DefDATA+reverse(SUBSTRING(reverse(filename),1,CHARINDEX('\',reverse(filename))-1))+''' )'
from sys.sysaltfiles where dbid <32000
and groupid=1 and dbid>5 and charindex(@DefDATA,filename)=0
union all
select --DB_NAME(dbid),name,filename, reverse(SUBSTRING(reverse(filename),1,CHARINDEX('\',reverse(filename))-1)),
'ALTER DATABASE ['+DB_NAME(dbid)+'] MODIFY FILE ( NAME = '''+name+''' , FILENAME = '''+@DefLOG+reverse(SUBSTRING(reverse(filename),1,CHARINDEX('\',reverse(filename))-1))+''' )'
from sys.sysaltfiles where dbid <32000 and charindex(@DefDATA,filename)=0
and groupid=0 and dbid>5

declare @move varchar(max)
open cur_move
fetch next from cur_move into @move

while @@fetch_status=0
begin

print @move
--Exec (@move)

fetch next from cur_move into @move
end
close cur_move
deallocate cur_move

End
Else
Begin
Select 'This script supports SQL Server 2016 or later! For older version, you need to below to identify the default path, otherwise all are same.'
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @DefLOG OUTPUT
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DefDATA OUTPUT
return;
End
DBA Utilities, SCRIPT GALLERY

Cleanup of Load Runner Test data from SQL Server

Here is a script to purge the Load test data from your load runner database. This would be handy at times like running of disk space or performance issues related to load runner etc.


--Provide your Load runner database name
USE <<Loadrunner DBName>>
Create proc SQLZealot_CleanupTestData  (@DeleteDateUpto Datetime)
with encryption
as
Begin

	If not exists(Select 1 From sys.tables where name = 'TempTobeDeletedLoadRecords')
		Select LoadtestrunID into TempTobeDeletedLoadRecords from Loadtestrun (nolock) where StartTime < @DeleteDateUpto

	Declare @LoadtestrunID bigint
	While exists(Select 1 From TempTobeDeletedLoadRecords )
	Begin
		Set @LoadtestrunID = (Select Top 1 Loadtestrunid from TempTobeDeletedLoadRecords ORder by 1 asc)
		Exec Prc_DeleteLoadTestRun @Loadtestrunid
		Delete TempTobeDeletedLoadRecords Where Loadtestrunid = @LoadtestrunID
	End

	Drop Table TempTobeDeletedLoadRecords

End
DBA Utilities, Errors and Exceptions, SCRIPT GALLERY

Decoding hexa error code to windows error message in SQL Server

Problem Statement:
Sometimes, the error messages generated are not friendly to understand the underlined issues. This is going to be an extra effort to google out for most of us with the error code (mostly a hexa value) and identify the corresponding windows error message.

Solution:
A simple solution would be to use NET HELPMSG with the errocode. But if the error code is a hexa value, then we need to identify the corresponding Decimal value to use as a parameter to NET HELPMSG.

Here is a script to identify actual error message from hex error code. This is important to be aware that this approach will only work for Win32 error codes from Microsoft Windows. If the net helpmsg command does not return a result, the cause is likely the error has not originated from Windows, or no longer a valid Win32 process.

DECLARE @hex VARCHAR(64) = '0x80070643'
Set @hex = Right(@hex,4)

DECLARE @rez BIGINT;
SELECT @rez = ISNULL(@rez,0) * 16 + 
CHARINDEX(substring(@hex,n.number+1,1),'0123456789ABCDEF') - 1
FROM MASTER..spt_values n WHERE n.TYPE='P' AND n.number<len(@hex)

/*
	--To use NET HELPMSG, need to enable xp_cmdshell as below:

	EXEC sp_configure 'show advanced options',1
	RECONFIGURE WITH OVERRIDE
	EXEC sp_configure 'xp_cmdshell',1
	RECONFIGURE WITH OVERRIDE
	EXEC sp_configure 'show advanced options',0
	RECONFIGURE WITH OVERRIDE
*/

Declare @Table Table(WindowsErrorMessage nVarchar(MAX))
Declare @s nvarchar(500) = 'NET HELPMSG ' + Cast(@rez as varchar(MAX))
Insert into @Table 
exec xp_cmdshell @s

Select @hex HexaVal,@rez DecimalVal,* From @Table 
where WindowsErrorMessage is not null
DBA Utilities

How to Replay a SQL Server Profiler trace in SQL Server

It is one of the most interesting topic in SQL Server “How to Replay profiler”. But, I have not seen this feature is used efficiently by many. Today, we are going to see how can we use Replay profiler in SQL Server.

Usages:

1. Can be used to reproduce issues in one environment to another environment(production environment to lower environments)
2. Can be used to run the trace multiple times as part of investigation

Now, Let us quickly see how can we use the Replay option in SQL Server.

1. Collect the Profiler trace with Replay option.

This is a very important step to collect what needs to replay. SQL Profiler provides a default template to choose the Replay option. We need to select the template and run the profiler on the source server/database while we execute the functionality that needs to be applied to target database/server.

2. Save the collected trace to a Table

3. Once saved the Replay trace, you can connect to target server and open the trace collected

File -> Open -> Trace Table -> Connect the server that trace saved

4. Once the trace is opened in the Profiler window, we can run the Replay Trace

Replay -> Start -> Select “Target Server”
You can select Replay Configuration in the window as below:(Not covering all the option here, please explore and configure as required)

I (my personal choice) usually include “Display Execution Time”, but its optional.

Once the configurations are set, we can “Start” the Replay.

If you see the above sample snapshot, we can see that the trace has been run successfully. However, we see errors in the screenshot as when I tried to run the trace in the same server and database.

How do we run this trace in different server and different database?

Its always a case that we cannot expect the database name will be same in all environment. For an example, developer would have kept multiple version of same database like database_dev, database_unittest etc to test various purposes. If we try to run the trace as it is, we will end up with errors. To run on different environment, you need to make few changes as below:

1. Go to Management Studio (SSMS) and select the database where the trace table collected
2. Update the database Name and database id columns of the table to your target database name and database id


That means, the trace data needs to be changed for its database id and database name as per the target environment.Once the trace table data has been modified, we can follow the same procedure mentioned above(3 and 4).

DBA Utilities, SQL Server 2016, Trace Flags

SQL Server 2016 Database Scoped Configuration to force The Legacy CE

Problem Statement:

Recently, we identified a performance issue with a query once we upgraded the database server to SQL Server 2016. If you look at the query, it uses system catalogs to fetch some data. This query can be optimized or rewritten in better way, however, we are going to discuss about how the same query behaves with different scenario/settings.


SELECT UPPER(ccu.table_name) AS SourceTable
    ,UPPER(ccu.constraint_name) AS SourceConstraint
    ,UPPER(ccu.column_name) AS SourceColumn
    ,LOWER(ccu.TABLE_SCHEMA) AS SourceSchema
    ,UPPER(kcu.table_name) AS TargetTable
    ,UPPER(kcu.column_name) AS TargetColumn
    ,LOWER(kcu.TABLE_SCHEMA) as TargetSchema
    ,LOWER(kcu.constraint_name) as TargetConstraint
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
    INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
    ON ccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON kcu.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
WHERE ccu.TABLE_NAME not like 'tablename%' and ccu.TABLE_SCHEMA = 'dbo'
ORDER BY ccu.TABLE_SCHEMA, ccu.table_name, ccu.COLUMN_NAME

The above query runs for 3 minutes in SQL Server 2016 with compatibility 130, but quite faster in SQL Server 2008 R2 that returns in 3 seconds. It is also observed that, when we change the compatibility 100 in SQL Server 2016, the query runs faster as expected.

Analysis:

 
--SQL 2016 (with compatibility 130) Took 3 min 16 sec
(424 row(s) affected)
Table 'sysidxstats'. Scan count 7971, logical reads 38154, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 13063, logical reads 104556, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 3265, logical reads 8681635, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysiscols'. Scan count 2696, logical reads 6120, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


--SQL 2008 R2 (with compatibility 100) Took 0 min 3 sec
(424 row(s) affected)
Table 'sysidxstats'. Scan count 235, logical reads 1280, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 11, logical reads 1314787, physical reads 0, read-ahead reads 6, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 2659, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysiscols'. Scan count 2, logical reads 410, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

If we see the IO statistics, we can clearly see that the number of IO is more with compatibility 130 compared to 100. So it is evident that there are some fundamental changes at optimizer level that causes this difference.

Solutions:

We have multiple ways to solve this difference:

1. Change the compatibility to 100


Alter database DBNAME SET COMPATIBILITY_LEVEL = 100

Considerations:

Once we set lower compatibility, we will not be able to use new features introduced in SQL 2016 like RLS/In Memory OLTP and many others.

2. Use Trace Flag 9481

Trace Flag 9481 forces Legacy CE model used by SQL Server 7.0 – 2012.
There are three ways to use this option for different scopes.

a. Query Scope: Use QUERYTRACEON query hint specified in the OPTION clause for a query. 


Select * From Table OPTION ( QUERYTRACEON 9481 )

Considerations:

1. This option is to use trace flags at the query level. The change is ONLY applicable for the query, It does not have any impact on other queries.
2. This option requires sysadmin fixed server role. In production, it is unlikely to have this privillage in most cases for application logins.
3. This requires changes in the procedures/application code.

b. Session Scope: If you dont want to make any modification in query or procedures or there are many places we need to make the changes, then you can use this option at session level to use the legacy cardinality estimator.


DBCC TRACEON (9481);
GO
Select * From Table
GO
DBCC TRACEOFF (9481);

Considerations:

1. It may appear to be a quick fix in first place. But, this will not use the latest CE abilities.
2. If the procedure plan is already cached, then it will still be continuing to use the old one.

c. Global Scope: Trace flags can be set at server level and the changes will impact on every connection to the SQL Server instance.

Global trace flags can be set in two different ways as below:


	1. Using Query
		DBCC TRACEON (9481, -1);
	2. Using Startup parameters
                Go To "SQL Server 2016 Configuration Manager" -> "SQL Server services" -> Right Click on the Service -> Properties -> Startup Parameters - > Add "-T9481".

Considerations:

1. When you want to move your databases to use compatibility of 130 (to use new feature), but, you still need some time to test the existing code performance with new CE.
2. You may be restricting the new CE cost based benefits while creating the plans.

3. Use LEGACY_CARDINALITY_ESTIMATION Database Scoped Configuration

This is a new feature added to SQL Server 2016 – Database Scoped Configuration. To stick with the subject, lets cover LEGACY_CARDINALITY_ESTIMATION option in this feature. We have seen QUERYTRACEON option at different scopes like Query/session or Server level. SQL Server 2016 provides another option at DATABASE level using LEGACY_CARDINALITY_ESTIMATION. There are two ways, we can use this option.

a. Using GUI:


OR


ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON;

b. Using Query HINT: SQL Server 2016 SP1 introduced a new query hint which will influence the query optimizer. This does not require the sysadmin fixed server role unlike QUERYTRACEON, however, it is logically equivalent of the same.


SELECT * FROM Tablename
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

Additional information: Using DMV sys.database_scoped_configurations, we would be able to see the settings with Database scoped configuration.


SELECT configuration_id, name, value, value_for_secondary
FROM sys.database_scoped_configurations OPTION (RECOMPILE);

Hope, this helps you to understand various options to use Legacy CE in SQL Server 2016.
Happy reading and learning!!!

DBA Utilities, Encryption

WITH Encryption clause in SQL Server

This is quite an old feature, however, a very useful one in SQL Server.

Applies to: SQL Server 2005 and above.

Why do we need this or its Usages?

“WITH ENCRYPTION” clause can be used for procedures and function in SQL Server.
I personally used this clause in many places, where I wanted to hide my procedure definition. When we need to manage multiple environments and different people have access to environments, its possible, the procedure definition gets changed by others. So, if we need to have to keep the same definition across all environments, we need to get the definition encrypted which would prevent the users to change the definition.

Usually, DBAs will use this option to prevent any changes or hide the logic implemented for certain purposes.Sometimes business critical logic are also encrypted using this option to hide from others.

How to Implement?

The implementation is very simple to provide the clause at the time of creation or alter.


CREATE OR ALTER procedure Test_Encryption
WITH ENCRYPTION
AS
Begin
	
	Select 'This is an encrypted procedure.'
		
End

Let us try to get the definition using sp_helptext, we will be getting a message “The text for object ‘Test_Encryption’ is encrypted.”

If we try to get the definition through SSMS, we will get the below error message.


And you can see a lock symbol to identify the encrypted procedures.

Gotchas:
1. Once the procedure is encrypted, you will not be ale to see the execution plan for the procedure.
2. It is always important to save the definition safe, you will never be able to retrieve the procedure definition once its encrypted.
3. CLR procedures and CLR User Defined Functions cannot be encrypted.
4. Encrypted procedures or functions cannot be replicated.