TSQL Tuesday #96: Folks Who Have Made a Difference

November 14, 2017 1 comment

T-SQL Tuesday
I started working as a SQL Server DBA in November of 2007. In November of 2010, I attended my second PASS Summit in Seattle. At the time I was working for a large insurance company, and our Microsoft sales rep had scheduled a dinner for my team at 6pm. The afternoon of the dinner, I went to see Paul Randal (b|t) do a 90-minute Spotlight Session called DBA Mythbusters scheduled until 6:15. About 15 minutes into Paul’s session, I texted my boss to let him know I would be late to dinner. The information Paul presented and the way in which he presented made me not want to miss a minute of the session. Walking out of the Convention Center that night, I remember thinking, “I want to do that! I want to teach people about SQL Server!”

The following March, I attended my first SQL Saturday in Chicago. For the session after lunch, I wanted to see Brent Ozar (b|t) present about SQL Server storage. By the time I got to the room for Brent’s presentation, it was SRO. Two doors down, Jes Borland (b|t) was giving a presentation called Make Your Voice Heard! In it, Jes provided advice on blogging, presenting, and leveraging social media to increase visibility within the community.

Attending Jes’s presentation introduced me to the force of nature known as Jes Borland. Her unbridled enthusiasm for the SQL community gave me the motivation to start teaching people about SQL Server. Within a year I started this blog and started presenting at PASS Local Chapters. I am blessed to have become friends with Jes. She continues to inspire me both professionally and personally.

In April of 2011, I attended SQL Skills Immersion Event on Performance Tuning and Optimization – Part 1, taught by Paul Randal and Kimberly Tripp. Since then I have been fortunate to become friends with the two of them. Their involvement in the community never ceases to amaze me. To close I will share my favorite story about the strength of the SQL community.

I attended PASS Summit 2015 with a co-worker who was a first timer. At the Tuesday Welcome Reception, I saw Paul and Kimberly across the room. My colleague and walked over, and as we were waiting to say hello, Paul turned, looked at my colleagues badge and said, “Michael, first timer, how are you! I’m Paul Randal!” Paul welcoming nature and openness to new members of the community is something that I try and emulate on a daily basis.

I volunteer with PASS because I will never be able to give back as much as I’ve gotten. Paul and Jes are two individuals that have given me an enormous amount through the years and I thank them for it.

Categories: SQL Server, PASS Tags: , ,

Validate SQL Server Database Mail Settings

November 13, 2017 1 comment

In my last post, I shared a script to automate the migration of SQL Server Database Mail settings. In this post, I show how to send test e-mails from all Database Mail profiles on an instance. The migration I was working on contained 21 Database Mail profiles. The following script will send a test e-mail from each profile to confirm successful configuration. I hope you can put this code to use in your migrations.

USE msdb;

SET NOCOUNT ON;

/* Drop and create temp table
   and declare local variables */
DROP TABLE IF EXISTS #profiles;

CREATE TABLE #profiles
(RowId INT IDENTITY(1,1)
,ProfileName SYSNAME);

/* Update @recipient 
   Set @noexec = 1 to print the EXEC sp_send_dbmail statements
   Set @noexec = 0 to execute the statements immediately */
DECLARE @profilename SYSNAME,
@sqlstr NVARCHAR(2000),
@loopcount INT = 1,
@looplimit INT,
@noexec INT = 1,
@recipient NVARCHAR(255) = N'fgill@concurrency.com';

/* Insert Database Mail profile names into temp table
   and initialize the loop limit variable */
INSERT INTO #profiles
(ProfileName)
SELECT name
FROM sysmail_profile;

SELECT @looplimit = MAX(RowId) FROM #profiles;

/* Loop through the list of Database Mail profiles and 
   execute sp_send_dbmail for each one */
WHILE @loopcount <= @looplimit
BEGIN

	SELECT @profilename = ProfileName FROM #profiles WHERE RowId = @loopcount;


	SELECT @sqlstr = CONCAT('EXEC msdb.dbo.sp_send_dbmail  
		@profile_name = ''',
	@profilename,''',  
		@recipients = ''', @recipient, ''',  
		@body = ''Database mail succeeded for ', @profilename, ' on SQLWEBDB-01.'',  
		@subject = ''Database mail test for ', @profilename, ';''');

	SELECT @loopcount += 1;

	IF @noexec = 1
	BEGIN

		PRINT @sqlstr;

	END
	ELSE
	BEGIN

		EXEC sp_executesql @sqlstr;

	END
		
END

Migrating SQL Server Database Mail Settings

November 6, 2017 3 comments

This week, I was working on a migration for a client.  The migration was moving databases from a stand-alone instance to a two-node Availability Group.  When it came to moving the Database Mail settings, I discovered they had 21 sets of profiles and accounts.  Not wanting to manually create 42 Database Mail profiles, I set out to automate the process.  A web search yielded this blog post by Iain Elder. This script does what I was looking for, but would only generate settings for a single Database Mail profile.  Using Iain’s code as a starting point, I modified it to create Database Mail settings for all profiles on an instance.  The script is listed below. I hope this simplifies your SQL Server migrations.

USE msdb;
GO

/*
	Author -		Frank Gill, Concurrency
	Date -			2017-08-18
	Description -	Script queries sys tables to generate dynamic SQL statements
					to recreate database mail accounts and profiles
					Code modified to generate multiple mail profiles from the script found here:
					https://basetable.wordpress.com/2012/04/03/script-out-database-mail-settings/
	Note -			Execute query in SQL Server Management Studio and output Results to Text (Ctrl + T)
					Copy the results to a separate query window 
					For mail acounts with passwords, the password will appear as 'NotTheRealPassword'
					You will need to change this to the real password
					Each result set will contain a header row of Text
					Do a group replace (Ctrl + H) of Text to --Text to comment the headers out
					
*/

SET NOCOUNT ON;

/* Drop and create temp table 
   Declare local variables */
IF OBJECT_ID('tempdb.dbo.#sysmail_info') IS NOT NULL
BEGIN

	DROP TABLE #sysmail_info;

END

CREATE TABLE #sysmail_info
(RowId INT IDENTITY(1,1)
,account_name SYSNAME
,email_address NVARCHAR(120)
,display_name NVARCHAR(128)
,replyto_address NVARCHAR(128)
,[description] NVARCHAR(256)
,servername SYSNAME
,servertype SYSNAME
,[port] INT
,credential_identity NVARCHAR(4000)
,use_default_credentials BIT
,enable_ssl BIT
,profile_name SYSNAME
,sequence_number INT
,database_principal_name SYSNAME NULL
,is_default BIT);

DECLARE @loopcount INT = 1,
@looplimit INT,
@SQLText VARCHAR(MAX),
@ProfileText VARCHAR(MAX),
@CrLf CHAR(2) = CHAR(13) + CHAR(10);

/* Insert the required database mail info into the temp table */
INSERT INTO #sysmail_info
(account_name
,email_address
,display_name
,replyto_address
,[description]
,servername
,servertype
,[port]
,credential_identity
,use_default_credentials
,enable_ssl
,profile_name
,sequence_number
,database_principal_name
,is_default)
SELECT a.name AS account_name,
a.email_address,
a.display_name,
a.replyto_address,
a.[description],
servername,
servertype,
[port],
c.credential_identity,
s.use_default_credentials,
s.enable_ssl,
p.name AS profile_name,
pa.sequence_number,
dp.name AS database_principal_name,
pp.is_default
FROM msdb.dbo.sysmail_profile AS p
INNER JOIN msdb.dbo.sysmail_profileaccount AS pa ON
  p.profile_id = pa.profile_id
INNER JOIN msdb.dbo.sysmail_account AS a ON
  pa.account_id = a.account_id
LEFT OUTER JOIN msdb.dbo.sysmail_principalprofile AS pp ON
  p.profile_id = pp.profile_id
LEFT OUTER JOIN msdb.sys.database_principals AS dp ON
  pp.principal_sid = dp.sid
LEFT OUTER JOIN msdb.dbo.sysmail_server AS s ON
  a.account_id = s.account_id
LEFT OUTER JOIN sys.credentials AS c ON
  s.credential_id = c.credential_id;

/* Set loop limit to max RowId value */
SELECT @looplimit = MAX(RowId) FROM #sysmail_info;

/* Generate commands to enable database mail */
SELECT @SQLText = '
EXEC msdb.dbo.sp_configure
	@configname = ''show advanced options'',
	@configvalue = 1;
RECONFIGURE;

EXEC msdb.dbo.sp_configure
	@configname = ''Database Mail XPs'',
	@configvalue = 1;
RECONFIGURE;';

SELECT @SQLText AS [Text];

/* Loop through each row in the temp table 
   build commands to recreate database mail accounts and profiles */
WHILE @loopcount <= @looplimit
BEGIN

	SELECT @SQLText = '

	EXECUTE msdb.dbo.sysmail_add_profile_sp
	  @profile_name = ''' + profile_name + ''',
	  @description  = ''' + ISNULL([description],'') + ''';

	EXEC msdb.dbo.sysmail_add_account_sp
	  @account_name = ' + CASE WHEN account_name IS NULL THEN 'NULL' ELSE + '''' + account_name + '''' END + ',
	  @email_address = ' + CASE WHEN email_address IS NULL THEN 'NULL' ELSE + '''' + email_address + '''' END + ',
	  @display_name = ' + CASE WHEN display_name IS NULL THEN 'NULL' ELSE + '''' + display_name + '''' END + ',
	  @replyto_address = ' + CASE WHEN replyto_address IS NULL THEN 'NULL' ELSE + '''' + replyto_address + '''' END + ',
	  @description = ' + CASE WHEN [description] IS NULL THEN 'NULL' ELSE + '''' + [description] + '''' END + ',
	  @mailserver_name = ' + CASE WHEN servername IS NULL THEN 'NULL' ELSE + '''' + servername + '''' END + ',
	  @mailserver_type = ' + CASE WHEN servertype IS NULL THEN 'NULL' ELSE + '''' + servertype + '''' END + ',
	  @port = ' + CASE WHEN [port] IS NULL THEN 'NULL' ELSE + '''' + CONVERT(VARCHAR,[port]) + '''' END + ',
	  @username = ' + CASE WHEN credential_identity IS NULL THEN 'NULL' ELSE + '''' + credential_identity   + '''' END + ',
	  @password = ' + CASE WHEN credential_identity IS NULL THEN 'NULL' ELSE + '''NotTheRealPassword''' END + ',
	  @use_default_credentials = ' + CASE WHEN use_default_credentials = 1 THEN '1' ELSE '0' END + ',
	  @enable_ssl = ' + CASE WHEN enable_ssl = 1 THEN '1' ELSE '0' END + ';

	EXEC msdb.dbo.sysmail_add_profileaccount_sp
	  @profile_name = ''' + profile_name + ''',
	  @account_name = ''' + account_name + ''',
	  @sequence_number = ' + CAST(sequence_number AS NVARCHAR(3)) + ';
	' +
	  COALESCE('
	EXEC msdb.dbo.sysmail_add_principalprofile_sp
	  @profile_name = ''' + profile_name + ''',
	  @principal_name = ''' + database_principal_name + ''',
	  @is_default = ' + CAST(is_default AS NVARCHAR(1)) + ';
	', '')
	FROM #sysmail_info
	WHERE RowId = @loopcount;

	WITH R2(N) AS (SELECT 1 UNION ALL SELECT 1),
	R4(N) AS (SELECT 1 FROM R2 AS a CROSS JOIN R2 AS b),
	R8(N) AS (SELECT 1 FROM R4 AS a CROSS JOIN R4 AS b),
	R16(N) AS (SELECT 1 FROM R8 AS a CROSS JOIN R8 AS b),
	R32(N) AS (SELECT 1 FROM R16 AS a CROSS JOIN R16 AS b),
	R64(N) AS (SELECT 1 FROM R32 AS a CROSS JOIN R32 AS b),
	R128(N) AS (SELECT 1 FROM R64 AS a CROSS JOIN R64 AS b),
	Tally(N) AS (
	  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
	  FROM R128
	),
	SplitText(SplitIndex, [Text]) AS (
	  SELECT
		N,
		SUBSTRING(
		  @CrLf + @SQLText + @CrLf,
		  N + DATALENGTH(@CrLf),
		  CHARINDEX(
			@CrLf,
			@CrLf + @SQLText + @CrLf,
			N + DATALENGTH(@CrLf)
		  ) - N - DATALENGTH(@CrLf)
		)
	  FROM Tally
	  WHERE
		N < DATALENGTH(@CrLf + @SQLText) AND
		SUBSTRING(@CrLf + @SQLText + @CrLf, N, DATALENGTH(@CrLf)) = @CrLf
	)
	SELECT [Text]
	FROM SplitText
	ORDER BY SplitIndex;

	SELECT @loopcount += 1;

END

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.

DBCC SHRINKDATABASE Log Space Usage

August 21, 2017 1 comment

Yesterday, I was running a health assessment for a client. They are running a weekly maintenance plan that is shrinking all of their data files. After I picked myself up off the floor, I searched the web for “Paul Randal shrink” and hit on Paul’s excellent post Why you should not shrink your data files. In the post, Paul (b|t) demonstrates the effect of DBCC SHRINKDATABASE on index fragmentation. After the demo script, Paul writes, “As well as introducing index fragmentation, data file shrink also generates a lot of I/O, uses a lot of CPU and generates *loads* (emphasis Paul’s) of transaction log.”

This led me to ask the question, “How much is *loads*?”. To find an answer, I made the following modification to Paul’s script:

DECLARE @maxlsn NVARCHAR(46);
SELECT @maxlsn = CONCAT(N'0x',MAX([Current LSN])) FROM fn_dblog(NULL,NULL);

-- Shrink the database
DBCC SHRINKDATABASE ([DBMaint2008]);

SELECT SUM([Log Record Length]) AS ShrinkLogSpace
FROM fn_dblog(@maxlsn,NULL);

My additions use the table-valued function fn_dblog to return information from the transaction log.

DECLARE @maxlsn NVARCHAR(46);
SELECT @maxlsn = CONCAT(N'0x',MAX([Current LSN])) FROM fn_dblog(NULL,NULL);

The code above returns the log sequence number (LSN) of the last record in the transaction log and stores it in variable @maxlsn.

SELECT SUM([Log Record Length]) AS ShrinkLogSpace
FROM fn_dblog(@maxlsn,NULL);

This code uses @maxlsn from the first query to return all log records resulting from the DBCC SHRINKDATABASE command, and return the total log space used in bytes.  In Paul’s demo script, there is 10MB of space available to shrink when DBCC SHRINKDATABASE runs.  The total log space used by the shrink is 16MB.

So, in this example, *loads* is 60% of the space you are reclaiming with the shrink operation.  Yet another reason to not shrink your data files.

Many thanks to Paul for his post.

Categories: Uncategorized

My SQL Saturday Season Begins

January 26, 2017 Leave a comment

We are nearly a month into the new year and my SQL Saturday season is about to start. I will be presenting Creating an Azure SQL Database Using PowerShell.  This will be my third year in Cleveland and I am looking forward to another great time. You can find more information and register here.

SQL Saturday Chicago is taking place Saturday, March 11. I will be helping organize and run the event, so I will not be presenting. I did that a few years ago, and it is just too many balls in the air. The event is already at capacity, but you can add yourself to the waitlist here.  If you sign up for either pre-conference session, you can send your registration receipt to sqlsaturday600@sqlpass.org and you will be registered for SQL Saturday. The pre-conference sessions are Tuning Your Biggest Queries with Adam Machanic and Complete Primer for SQL Server Infrastructure and the Cloud.

Finally, SQL Saturday Madison takes place Saturday, April 8. This will be my fifth straight time in Madison and they put on an amazing event. The venue is in the heart of the University of Wisconsin campus and is one of the more beautiful SQL Saturdays I’ve been to. You can find more information and register here.

If these events aren’t geographically desirable, you can find other SQL Saturday events worldwide here. If you do make it to any of the three, be sure to say hello. Meeting new people is one of the best parts of SQL Saturdays.

Categories: Uncategorized

Monitoring and Alerting for Availability Groups and Why I Love PASS Summit

October 31, 2016 Leave a comment

I have returned from my eighth PASS Summit and, as in years past, they just keep getting better.  I am amazed and humbled that I am a part of the PASS community.  PASS has given me the opportunity to meet and learn from many of the best and brightest in the SQL world.  When I meet first-timers, I tell them to talk to people, no matter who they are.  If you share an interest, they will share with you.  If you have never been to PASS Summit, I recommend it.  If you cannot make Summit, get involved with your local PASS Chapter or SQL Saturday.  Virtual Chapters are another great way to find out what PASS has to offer.

One of the highlights of this year’s Summit was Shawn Meyers’ (t) presentation on Monitoring and Alerting of Availability Groups. (You will need to be logged in to the PASS website to access the link.)  I have worked with Availability Group since its release in SQL Server 2012 and Shawn provided the best solution for monitoring and alerting.

Shawn suggested implementing three alerts:

  • 1480 – AG Role Change
  • 35264 – AG Data Movement Suspended
  • 35265 – AG Data Movement Resumed

Additionally, Shawn provided an MSDN link with recommendations for using Policy-Based Management (PBM) to monitor AGs.  The custom policies monitor the recovery time objective (RTO) and recovery point objective (RPO) for your availability groups.  Microsoft recommends 600 seconds, or 10 minutes, for RTO, and 3600 seconds, or 60 minutes, for RPO. You can set your own values when defining the policy conditions.

I have scripted out the PBM conditions, policies, and SQL Server Agent alerts.  The conditions and policies can be used as-is, because they use system schedules.  The alerts need to updated to replace the TestOperator with an SQL Agent operator of your own.

I hope you can make use of these scripts.  And I encourage you to get involved with PASS. You will be amazed at what it can do for you.

agmonitoringandalertingscripts