Archive

Archive for the ‘T-SQL’ Category

Return SQL Server Context Settings from sys.query_context_settings

October 30, 2017 1 comment

Today, I attended Erin Stellato’s (b|t) precon session Solving Common Performance Problems Using Query Store. In her presentation, Erin showed that the same query text can return multiple results in Query Store if the context settings for the queries are different. The context settings can be found in the sys.query_context_settings DMV in the set_options column. set_options is stored as a hex value. The following query will return the hex and integer value for each row in the table (NOTE: Query store must be enabled for the database to return values):

USE YourQueryStoreDatabase;

SELECT set_options, CONVERT(INT, set_options) AS IntSetOptions
FROM sys.query_context_settings;

The set_options value represents a bit mask, with each binary digit representing a specific set option. The full list of values can be found here. I created stored procedure ReturnSetOptions to take the IntSetOptions from the query above and return the set options represented. The code for the procedure is listed below.


/* Change database context if you want to create the procedure in
a database other than master */
USE master;

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DROP PROCEDURE IF EXISTS ReturnSetOptions;
GO
/* =============================================
-- Author:		Frank Gill
-- Create date: 2017-10-30
-- Description:	Stored Procedure to return SET options for a INT context_settings value
-- Sample Execution: EXEC ReturnSetOptions @ContextSettings = 251;
-- =============================================*/
CREATE PROCEDURE ReturnSetOptions
@ContextSettings INT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DROP TABLE IF EXISTS #binaryvalues;

/* Create temp table to hold each binary position and its associated
context setting */
CREATE TABLE #binaryvalues
(RowId INT IDENTITY(1,1)
,BinaryValue INT
,BinaryFlag BIT
,SetOption SYSNAME);

/* Insert context setting information into temp table
Values found here
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-plan-attributes-transact-sql*/
INSERT INTO #binaryvalues
(BinaryValue
,BinaryFlag
,SetOption)
VALUES
(1,0,N'ANSI_PADDING'),
(2,0,N'Parallel Plan'),
(4,0,N'FORCEPLAN'),
(8,0,N'CONCAT_NULL_YIELDS_NULL'),
(16,0,N'ANSI_WARNINGS'),
(32,0,N'ANSI_NULLS'),
(64,0,N'QUOTED_IDENTIFIER'),
(128,0,N'ANSI_NULL_DFLT_ON'),
(256,0,N'ANSI_NULL_DFLT_OFF'),
(512,0,N'NoBrowseTable'),
(1024,0,N'TriggerOneRow'),
(2048,0,N'ResyncQuery'),
(4096,0,N'ARITH_ABORT'),
(8192,0,N'NUMERIC_ROUNDABORT'),
(16384,0,N'DATEFIRST'),
(32768,0,N'DATEFORMAT'),
(65536,0,N'LanguageID'),
(131072,0,N'UPON'),
(262144,0,N'ROWCOUNT');
--SELECT * FROM #binaryvalues;

/* Declare local variables */

DECLARE @RowId INT,
@BinaryValue INT,
@ModValue INT,
@DivValue INT,
@OddValue INT,
@MaxRowId INT,
@BinaryChar CHAR(1),
@BinaryString VARCHAR(MAX) = '';

/* Determine if input value is odd
Used for the one position of the binary value */
SELECT @OddValue = @ContextSettings % 2;

/* Return the largest value and row id from the temp table
less than the input value */
SELECT @RowId = RowId, @BinaryValue = BinaryValue
FROM #binaryvalues
WHERE BinaryValue <= @ContextSettings;

/* Loop backwards through the rows in the temp table
chacking to see if the binary position should be set */
WHILE @RowId > 1
BEGIN

/* Calculate the modulus and dividend for the largest binary value */
SELECT @ModValue = @ContextSettings % @BinaryValue;
SELECT @DivValue = @ContextSettings / @BinaryValue;

/* If the dividend is 1, the current binary position is set to 1*/
IF @DivValue = 1
BEGIN

UPDATE #binaryvalues
SET BinaryFlag = 1
WHERE RowId = @RowId;

END

/* Update the input value to the modulus for use in the next calculation */
SELECT @ContextSettings = @ModValue;

/* If @ContextSettings is greater than 1 get the previous values from the
temp table
Otherwise, short circuit the loop */
IF @ContextSettings > 1
BEGIN

SELECT @RowId = RowId, @BinaryValue = BinaryValue
FROM #binaryvalues
WHERE BinaryValue <= @ContextSettings;

END
ELSE
BEGIN

SELECT @RowId = 1;

END

END

/* If the input value is odd, set the 1 position to 1 */
IF @OddValue = 1
BEGIN

UPDATE #binaryvalues
SET BinaryFlag = 1
WHERE BinaryValue = 1;

END

/* Get the max row with a binary position set */
SELECT @MaxRowId = MAX(RowId)
FROM #binaryvalues
WHERE BinaryFlag = 1;

/* Loop through and concatenate the binary flag values
to generate the binary number equivalent to the input value */
WHILE @MaxRowId >= 1
BEGIN

SELECT @BinaryChar = CAST(BinaryFlag AS CHAR(1))
FROM #binaryvalues
WHERE RowId = @MaxRowId

SELECT @BinaryString = CONCAT(@BinaryString, @BinaryChar)

SELECT @MaxRowId -= 1;

END

/* Select the binary number */
SELECT @BinaryString AS BinaryValue;

/* Select the set options indicated by the binary number */
SELECT SetOption
FROM #binaryvalues
WHERE BinaryFlag = 1;

END
GO

Thanks to Erin for an excellent and informative training session.

Joining sys.dm_exec_requests to sp_whoisactive results

October 28, 2015 1 comment

Previously, I’ve blogged about returning formatted estimated_completion_time values from sys.dm_exec_requests and getting sortable output from sp_whoisactive. In this post I combine the two, joining the two result sets together. In my environment, we use Ola Hallengren’s Maintenance Solution to run our backups. Because the backup job defaults to run backups for all user databases, it can be difficult to tell which database is currently being backed up.

USE [master]
GO
 
--Drop procedure if it exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_join_whoisactive_execrequests]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].sp_join_whoisactive_execrequests
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* =============================================
-- Author:      Frank Gill
-- Create date: 2015-10-27
-- Description: This procedure executes Adam Machanic's sp_whoisactive, dumps the results to a temp table, and then joins those results to 
-- sys.dm_exec_requests.  This will return estimated completion time for the following commands along with the statement being executed
-- in a single pass:
-- ALTER INDEX REORGANIZE
-- AUTO_SHRINK option with ALTER DATABASE
-- BACKUP DATABASE
-- DBCC CHECKDB
-- DBCC CHECKFILEGROUP
-- DBCC CHECKTABLE
-- DBCC INDEXDEFRAG
-- DBCC SHRINKDATABASE
-- DBCC SHRINKFILE
-- RECOVERY
-- RESTORE DATABASE,
-- ROLLBACK
-- TDE ENCRYPTION
-- ============================================= */
CREATE PROCEDURE sp_join_whoisactive_execrequests
AS
BEGIN
 
	IF OBJECT_ID('tempdb..#WhoIsActive') IS NOT NULL 
	BEGIN
		SELECT 'Dropping'
		DROP TABLE #WhoIsActive
	END
 
	CREATE TABLE #WhoIsActive
	([dd hh:mm:ss.mss] VARCHAR(20)
	,[dd hh:mm:ss.mss (avg)] VARCHAR(20)
	,[session_id] SMALLINT
	,[sql_text] XML
	,[sql_command] XML
	,[login_name] SYSNAME
	,[wait_info] NVARCHAR(4000)
	,[tran_log_writes] NVARCHAR(4000)
	,[CPU] VARCHAR(30)
	,[tempdb_allocations] VARCHAR(30)
	,[tempdb_current] VARCHAR(30)
	,[blocking_session_id] SMALLINT
	,[blocked_session_count] VARCHAR(30)
	,[reads] VARCHAR(30)
	,[writes] VARCHAR(30)
	,[physical_reads] VARCHAR(30)
	,[query_plan] XML
	,[used_memory] VARCHAR(30)
	,[status] VARCHAR(30)
	,[tran_start_time] DATETIME
	,[open_tran_count] VARCHAR(30)
	,[percent_complete] VARCHAR(30)
	,[host_name] SYSNAME
	,[database_name] SYSNAME
	,[program_name] SYSNAME
	,[start_time] DATETIME
	,[login_time] DATETIME
	,[request_id] INT
	,[collection_time] DATETIME)

	/* Execute sp_whoisactive and write the result set to the temp table created above */
	EXEC master.dbo.sp_WhoIsActive
	@get_plans = 2, 
	@get_outer_command = 1, 
	@get_transaction_info = 1, 
	@get_avg_time = 1, 
	@find_block_leaders = 1,
	@destination_table = #WhoIsActive

	/* Join the #whoisactive temp table to sys.dm_exec_requests to get estimated completion time and query information in one pass */ 
	SELECT CASE WHEN ((r.estimated_completion_time/1000)%3600/60) < 10 THEN '0' +
	CONVERT(VARCHAR(10),(r.estimated_completion_time/1000)%3600/60) 
	ELSE CONVERT(VARCHAR(10),(r.estimated_completion_time/1000)%3600/60)
	END  + ':' + 
	CASE WHEN ((r.estimated_completion_time/1000)%60) < 10 THEN '0' +
	CONVERT(VARCHAR(10),(r.estimated_completion_time/1000)%60)
	ELSE CONVERT(VARCHAR(10),(r.estimated_completion_time/1000)%60)
	END
	AS [Time Remaining],
	r.percent_complete,
	r.session_id,
	w.login_name,
	w.[host_name],
	w.sql_command,
	w.sql_text 
	FROM #WhoIsActive w
	RIGHT OUTER JOIN sys.dm_exec_requests r
	ON r.session_id = w.session_id
	WHERE r.percent_complete > 0;
 
END

This procedure takes the output from sp_whoisactive and joins it to sys.dm_exec_requests on session_id, returning the estimated completion time and current backup statement in a single pass. Here is a sample result set from the procedure (Click to Enlarge):

SpWhoIsActiveExecRequestsResults

The sql_command column contains the full command being run, in this example Ola Hallengren’s DatabaseBackup procedure. The sql_text column contains the actual SQL statement being run, BACKUP DATABASE [AdventureWorks2012] TO DISK…. The DatabaseBackup procedure runs database backups in alphabetical order based on the database name, so this allows tracking a job progress.

Many thanks to Adam Machanic (b|t) for sp_whoisactive, Ola Hallengren (b|t) for his Maintenance Solution, and Aaron Bertrand (b|t) for his advice on formatting the estimated completion time more elegantly. I finally had an opportunity to use it.

I hope you can put this procedure to good use. Let me know if you have any questions.

Generate ALTER Statements for Database Moves

October 13, 2014 1 comment

Last week, I posted Identifying Corrupt Rows USING DBCC CHECKDB and DBCC PAGE. I wrote the scripts in that post to identify corrupt rows in a client’s database. As we continued to investigate, things got strange. Repeated runs of DBCC CHECKDB were generating different results. Each one showed serious corruption, but on different pages with each run. In an effort to remove the storage system from the list of potential causes, I needed to move to data and log files for the database to a a different storage array. This post from TechNet gave me the steps I needed to run.

In this case, only one database had to be moved. To move more than one database, I wrote the following script to generate the statements specified in the TechNet article. In addition to the ALTER DATABASE statements, the script checks to see if the database is part of an availability group. If it is, a statement is generated to remove it. As adding a database to an availability group is far more complicated than removing it, I will leave that to the user.

As usual, I hope you find this useful. And remember, executing code you find on the internet against your production instance always has the potential to become a resume updating event. Use caution and test.

/*---------------------------------------------------------------
Created By - Frank Gill
Created On - 2014-10-13
 
Script to generate ALTER DATABASE statement to move a database to
a different storage location
---------------------------------------------------------------*/

USE master;

SET NOCOUNT ON;

/* Drop temp table if it exists */

IF OBJECT_ID('tempdb..#dbfiles') IS NOT NULL
BEGIN

	DROP TABLE #dbfiles;

END

/* Declare local variables and create temp table */
DECLARE @dbname SYSNAME;
DECLARE @agname SYSNAME;
DECLARE @sqlstr NVARCHAR(4000);
DECLARE @physicalname NVARCHAR(260);
DECLARE @logicalname SYSNAME;
DECLARE @filetype TINYINT;
DECLARE @datapath NVARCHAR(1000);
DECLARE @logpath NVARCHAR(1000);
DECLARE @agdb BIT = 0;
DECLARE @loopcount INT = 1;
DECLARE @looplimit INT;

CREATE TABLE #dbfiles
(logicalname SYSNAME
,physicalname NVARCHAR(260)
,filetype TINYINT)

/* Initialize variables */
SET @dbname = 'Your_Database';
SET @datapath = 'Your_Data_Path';
SET @logpath = 'Your_Log_Path';

/* If the database is part of an availability group, generate a statement to remove it prior to taking the database offline */
IF(SELECT replica_ID FROM sys.databases WHERE name = @dbname) IS NOT NULL
BEGIN

	PRINT '/* This database is part of an availability group and must be removed prior to taking the database offline
   You will need to add the database back to the availability group after the database files are moved */'

	SELECT @agname = g.name FROM sys.databases d
	INNER JOIN sys.availability_replicas r
	ON d.replica_id = r.replica_id
	INNER JOIN sys.availability_groups g
	ON g.group_id = r.group_id
	WHERE d.name = @dbname;

	SET @sqlstr = 'ALTER AVAILABILITY GROUP [' + @agname + ']
REMOVE DATABASE [' + @dbname + '];';

	PRINT @sqlstr;
	
	SET @agdb = 1;

END

/* Generate a statement to set the database offline */
SET @sqlstr = 'ALTER DATABASE ' + @dbname + '
SET OFFLINE WITH ROLLBACK IMMEDIATE';

PRINT @sqlstr;

/* Get a list of database files for your database and insert them into the temp table for use in the loop */
SET @sqlstr = 'INSERT INTO #dbfiles
SELECT name, physical_name, type FROM ' + @dbname + '.sys.database_files;'

EXEC sp_executesql @sqlstr;

/* Set the loop limit to the number of files returned from the insert statement */
SET @looplimit = @@ROWCOUNT;

WHILE @loopcount <= @looplimit
BEGIN

	/* Get the first set of physical and logical file name and generate an ALTER DATABASE statement using the file type to determine the new physical location */

	SELECT TOP 1 @logicalname = logicalname, @physicalname =  REVERSE(SUBSTRING(REVERSE(physicalname),1,(CHARINDEX('\',REVERSE(physicalname))-1))), @filetype = filetype FROM #dbfiles;

	IF @filetype = 0
	BEGIN

 SET @sqlstr = 'ALTER DATABASE ' + @dbname + '
MODIFY FILE 
(NAME = ' + @logicalname + ' 
,FILENAME = ''' + @datapath + @physicalname + '''' + ');';
 
 END
 ELSE IF @filetype = 1
 BEGIN

 SET @sqlstr = 'ALTER DATABASE ' + @dbname + '
MODIFY FILE 
(NAME = ' + @logicalname + ' 
,FILENAME = ''' + @logpath + @physicalname + '''' + ');';
 END

	PRINT @sqlstr;

	SET @loopcount += 1;

	DELETE TOP (1) FROM #dbfiles 

END

/* Generate a statement to bring the database back online */
SET @sqlstr = 'ALTER DATABASE ' + @dbname + '
SET ONLINE';

PRINT @sqlstr;


IF @agdb = 1
BEGIN

	PRINT '/* Reminder!  ' + @dbname + 'is part of an availability group 
You will need to add the database back to the availability group if necessary */'

END