by

You are the partition that won’t ever lapse…

I recently needed to know which tables in my database were partitioned. I tried a bunch of queries and some got incredibly complex. I finally found one that I like:

select 
    object_schema_name(i.object_id) as [schema],
    t.name as [table_name],
    i.name as [index_name],
    s.name as [partition_scheme]
from sys.indexes i
    join sys.partition_schemes s on i.data_space_id = s.data_space_id
    join sys.tables t on i.object_id = t.object_id  
WHERE i.type = 1

This query makes an assumption that the partitions are on the Clustered Index. In it, I am showing the schema, the table name, the name of the clustered index, and the partition scheme.

The song for this post is Boxes by the Goo Goo Dolls.

by

I tried to Move SSISDB, It Hit Me So Hard, How Can It Be This Heavy?

Recently I needed to move a dev instance from a physical server to a virtual one and needed to do SQL upgrades. We decided it was time to spin up a new dev instance and that required moving SSISDB. I will be honest, I was not prepared for all the things. I also did not have a password for the encryption so I had to force things to work. Because this was a dev server, I wasn’t as worried about what I would lose, I just wanted it to work. Here is what I did.

I spun up my new instance and restored SSISDB, which was wrong. So I deleted it. I found that I had missed the SSIS install piece during the install process, so I ran that again and installed SSIS.

Next I configured CLR on the new server:

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
GO

Since I didn’t have the password from the old server, I ran this on the old server:

Backup master key to file = 'D:\Backup\SSISDB_key' --Replace with the location you want to save it.
Encryption by password = 'StrongPassword' --Replace with your password

Then I created a new backup of the SSISDB on the old server.

Because I had restored my SSISDB when I shouldn’t have, I ran this powershell code on the new server to adjust all the little things I was going to miss:

# Load the IntegrationServices Assembly
[Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Management.IntegrationServices”)

# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = “Microsoft.SqlServer.Management.IntegrationServices”

Write-Host “Connecting to server …”

# Create a connection to the server
$sqlConnectionString = “Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;”
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

# Create the Integration Services object
$integrationServices = New-Object $ISNamespace”.IntegrationServices” $sqlConnection

# Provision a new SSIS Catalog
$catalog = New-Object $ISNamespace”.Catalog” ($integrationServices, “SSISDB”, “StrongPassword”)  #REPLACE THE PASSWORD
$catalog.Create()

I restored the SSISDB to to the new server from the backup I took above. Then I restored the SSISDB_key that I created: 

RESTORE MASTER KEY FROM FILE = 'D:\Install\SSISDB_key' --Change to the Location of your MasterKey Backup
DECRYPTION BY PASSWORD = 'StrongPassword' --Password used to encrypt. 
ENCRYPTION BY PASSWORD = 'StrongPassword' --New Password
FORCE

Then I opened and altered the master key:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongPassword'--–‘Password used when creating SSISDB in Powershell Script’
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Next, I verified my keys were working:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongPassword';

Finally, I synchronized logins and made sure my new version was compatible by watching the compatibility status (0 means all is good)

EXEC catalog.check_schema_version 0 --0 for 64, 1 for 32

It was a lot, but I was so thrilled when it all started working.

The song for this post is Dancing with My Eyes Closed – Performed by Wind Walkers

by

Everything turns around when QueryStore comes around…

I have this problem where I want to see how a newly released query is performing, but it may not be bad enough to make any of the canned reports that SQL Server provides in QueryStore. I was able to look up the plan handle, but always struggled to get to the query id for QueryStore, until now.

Here I am using a small and unique section from the query I want to find in the WHERE clause between two percent signs to work with my LIKE:

    SELECT plan_handle, text
    FROM sys.dm_exec_cached_plans
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
    WHERE text LIKE N'%SELECT S.SuperHeroName, S.SuperHeroDescription,%'
      AND text NOT LIKE N'%DBCC%'

I use the text that was returned to verify that I have the right query that I want to find in QueryStore. Then I take that plan_handle and put it in this query:

	SELECT qsp.query_id, qsp.plan_id
	FROM sys.dm_exec_query_stats eqs INNER JOIN sys.query_store_plan qsp
	ON eqs.query_plan_hash = qsp.query_plan_hash
	WHERE eqs.plan_handle = 0x05000900EBC0B12E005DDEC92402000001000000000000000000000000000000000000000000000000000000 --plan handle here

Finally, I can take that query id and plug it into the Tracked Queries report in QueryStore:

Then I can see my query and even force the plan that I want to use moving forward (as you can see I have done above).

The song for this post is Everything Turns Around by Dogstar.

by

You’re still the oxygen I breathe, I see your face when I close my eyes…

This is not a technical post, but I wanted to share a small glimpse of what is going on with me. My dearest love, husband and best friend, Ryan Allred, passed away last month. It was something we saw coming, but even with that knowledge, it still took all the breath out of us when it hit. We had known each other for nearly 25 years and were rarely apart from the day we met (I think a 2 week business trip that he had was the longest we were ever apart, but we still talked at least twice a day that whole time). The last several weeks, my brain has tried to tell me he is on another business trip, and that he is coming home. I know he isn’t and it hurts more than anything I have ever experienced. Every text, I think it is him. His stuff is all still here, how can he be gone?

I wanted to take a moment to thank all the people that have reached out with love, kindness and support in this difficult time. Your thoughtfulness has made all the difference and I can’t thank you all enough. Thank you to the people that helped me with arrangements, contributed to the gofundme, sent gifts, sent notes and just overall sent love my direction. I am so thankful for the SQLFamily and the support I have received. Even my family was blown away by all of the love.

I had set up auto-posting to my blog so it would keep going even when I couldn’t and have now run out of posts. I will get them going again as soon as I am able. I have been trying to write this post for weeks and am hoping that this time I can finish it.

If I am not around for a little while, it is because my heart is trying to remember how to beat with what is now the saddest part of me.

Thank you again for all the love.

The song for this post is Loneliest by Maneskin.

by

My database is so tired building this index, thankfully I have Resumable…

When Pause and Resume came out for indexes, I didn’t have an instance that supported them, and then I completely forgot about this feature. Recently, I had a very large table that I needed to build an index on and I figured it was a great time to try it.

I started to build my index and had set RESUMABLE = ON. 

CREATE INDEX [IX_SuperHero_BirthDate_PowerKey] ON [dbo].[SuperHero]
([BirthDate], [PowerKey])
INCLUDE ([FirstSeenKey]) WITH (ONLINE = ON, RESUMABLE = ON)

It had been building for about 10 minutes, when I realized I had the fields in the wrong order. No big deal, I just canceled it, corrected it and tried to start it again. “Tried” was the keyword here. I got an error because I had set RESUMABLE = ON but hadn’t properly cleared it out of the system. I started to furiously dig through articles about how to clear out an index that was resumable but had been canceled.

Thankfully I found this fantastic article from Brent Ozar which saved me. It told me that I had to “Abort” my index before I could start over. 

ALTER INDEX [IX_SuperHero_BirthDate_PowerKey] ON [dbo].[SuperHero] ABORT

It ended my process and I was able to restart the create on my index. It took a long time but successfully created and took a 25 minute query down to a few seconds.

The song for this post is Unconditional by Matt Maeson.

by

When the audit table gets a little too big, delete some data or don’t, just follow your arrow where ever it points…

Last month I talked about how I started collecting data from sp_whoisactive. The long term goal was to analyze and tune those long running processes that are sometimes hard to quantify in Query Store. We had started collecting the data in a table (whoisactiveLog), but wanted to make a simple table that our Engineers could refer to and find the long running processes. We also wanted to archive off the whoisactiveLog into another table and save that for 70 days in case it was needed for further research on the tuning of processes. Each night, we have a break in our processes where we can do this maintenance so we decided we would empty the whoisactiveLog table completely.

We decided that for the Analysis table, we would only collect processes that had been running for longer than 5 minutes. How would we be able to tell that they had been running for 5 minutes or longer? We looked at the SQLText and count the number of times for that session_id and start_time that text appeared.

We started by creating the WhoIsActiveAnalysis table and the WhoIsActiveArchive Table again in my dba schema:

CREATE TABLE [dba].[whoisactiveAnalysis](
	[ProcessTime] [varchar](20) NULL,
	[session_id] [smallint] NOT NULL,
	[login_name] [nvarchar](128) NOT NULL,
	[SQLText] [nvarchar](max) NULL,
	[SQLCommand] [nvarchar](max) NULL,
	[program_name] [nvarchar](128) NULL,
	[start_time] [datetime] NOT NULL,
	[collection_time] [datetime] NOT NULL,
	[EstimatedMinutes] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dba].[whoisactiveArchive](
	[dd hh:mm:ss.mss] [varchar](20) NULL,
	[percent_complete] [varchar](30) NULL,
	[host_name] [nvarchar](128) NULL,
	[session_id] [smallint] NOT NULL,
	[login_name] [nvarchar](128) NOT NULL,
	[status] [varchar](30) NOT NULL,
	[sql_text] [xml] NULL,
	[wait_info] [nvarchar](4000) NULL,
	[blocking_session_id] [smallint] NULL,
	[blocked_session_count] [varchar](30) NULL,
	[reads] [varchar](30) NULL,
	[physical_reads] [varchar](30) NULL,
	[writes] [varchar](30) NULL,
	[tempdb_allocations] [varchar](30) NULL,
	[tempdb_current] [varchar](30) NULL,
	[CPU] [varchar](30) NULL,
	[used_memory] [varchar](30) NULL,
	[open_tran_count] [varchar](30) NULL,
	[sql_command] [xml] NULL,
	[database_name] [nvarchar](128) NULL,
	[program_name] [nvarchar](128) NULL,
	[start_time] [datetime] NOT NULL,
	[login_time] [datetime] NULL,
	[request_id] [int] NULL,
	[collection_time] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Next I created my stored procedure to load the Analysis table, load the Archive table, remove anything older than 70 days from the Archive table (we don’t want to keep too much) and empty the Log table so it can start fresh in the morning. 



/*Get only what has been running longer than 5 minutes 
and only bring in one record for what has been running longer than 5 minutes.
There may be multiple records for the same process if there are multiple steps taking longer
than 5 minutes.*/

CREATE PROC [dba].[LoadWhoIsActiveAnalysis]

AS

INSERT INTO [dba].[whoisactiveAnalysis]
           ([ProcessTime]
           ,[session_id]
           ,[login_name]
           ,[SQLText]
           ,[SQLCommand]
           ,[program_name]
           ,[start_time]
           ,[collection_time]
           ,[EstimatedMinutes])
 SELECT AL.[dd hh:mm:ss.mss] AS ProcessTime  
	  ,AL.[session_id]
      ,AL.[login_name]     
	  ,CAST(AL.[sql_text] AS nvarchar(MAX)) AS SQLText    
	  ,CAST(AL.[sql_command] AS nvarchar(MAX)) AS SQLCommand 
	  ,AL.[program_name]
      ,AL.[start_time]    
	  ,AL.[collection_time]
	  ,AG.StepCount AS EstimatedMinutes
 FROM [dba].[whoisactiveLog] AL
 INNER JOIN (SELECT MAX([dd hh:mm:ss.mss]) AS ProcessTime
        , COUNT([dd hh:mm:ss.mss]) AS StepCount
        , CAST(sql_text AS nvarchar(MAX)) AS SQLText
        , session_id
        , start_time
  FROM [dba].[whoisactiveLog] 
  GROUP BY CAST(sql_text AS nvarchar(MAX)) 
		, start_time
        , session_id
 HAVING COUNT([dd hh:mm:ss.mss]) > 5) AG ON AL.session_id = AG.session_id AND AL.[dd hh:mm:ss.mss] = AG.ProcessTime


/*Insert everything from the Log into the Archive*/
INSERT INTO [dba].[whoisactiveArchive]
           ([dd hh:mm:ss.mss]
           ,[percent_complete]
           ,[host_name]
           ,[session_id]
           ,[login_name]
           ,[status]
           ,[sql_text]
           ,[wait_info]
           ,[blocking_session_id]
           ,[blocked_session_count]
           ,[reads]
           ,[physical_reads]
           ,[writes]
           ,[tempdb_allocations]
           ,[tempdb_current]
           ,[CPU]
           ,[used_memory]
           ,[open_tran_count]
           ,[sql_command]
           ,[database_name]
           ,[program_name]
           ,[start_time]
           ,[login_time]
           ,[request_id]
           ,[collection_time])

SELECT [dd hh:mm:ss.mss]
      ,[percent_complete]
      ,[host_name]
      ,[session_id]
      ,[login_name]
      ,[status]
      ,[sql_text]
      ,[wait_info]
      ,[blocking_session_id]
      ,[blocked_session_count]
      ,[reads]
      ,[physical_reads]
      ,[writes]
      ,[tempdb_allocations]
      ,[tempdb_current]
      ,[CPU]
      ,[used_memory]
      ,[open_tran_count]
      ,[sql_command]
      ,[database_name]
      ,[program_name]
      ,[start_time]
      ,[login_time]
      ,[request_id]
      ,[collection_time]
  FROM [dba].[whoisactiveLog]





/*Rolling Delete to remove anything older than 70 days from the Archive*/
DELETE 
FROM  [dba].[whoisactiveArchive]
WHERE collection_time < GETDATE()-70

/*Turncate after the archive has been completed to prepare for tomorrow with empty table*/
TRUNCATE TABLE [dba].[whoisactiveLog]

After creating this stored procedure I was ready to create a pipeline that executes once a day to do my Analysis and clean my tables. So far it has been great.

The song for this post is Follow Your Arrow by Kacey Musgraves.

by

You can call it magic when I log WhoIsActive to a table

I needed to collect sp_WhoIsActive into a table, but the twist was that it is on my Azure Managed Database, so I had to get creative with how I did it. We needed an Azure Pipeline to run it, but we wanted to record it every minute and firing a pipeline every minute adds up fast. So we decided that we would kick it off once an hour and have the process wait for a minute and then fire until the hour ended. Then it fire again at the top of the next hour and the same process would happen.

First, I created my table in my special dba schema because this is for me:

CREATE TABLE [dba].[whoisactiveLog](
	[dd hh:mm:ss.mss] [varchar](20) NULL,
	[percent_complete] [varchar](30) NULL,
	[host_name] [nvarchar](128) NULL,
	[session_id] [smallint] NOT NULL,
	[login_name] [nvarchar](128) NOT NULL,
	[status] [varchar](30) NOT NULL,
	[sql_text] [xml] NULL,
	[wait_info] [nvarchar](4000) NULL,
	[blocking_session_id] [smallint] NULL,
	[blocked_session_count] [varchar](30) NULL,
	[reads] [varchar](30) NULL,
	[physical_reads] [varchar](30) NULL,
	[writes] [varchar](30) NULL,
	[tempdb_allocations] [varchar](30) NULL,
	[tempdb_current] [varchar](30) NULL,
	[CPU] [varchar](30) NULL,
	[used_memory] [varchar](30) NULL,
	[open_tran_count] [varchar](30) NULL,
	[sql_command] [xml] NULL,
	[database_name] [nvarchar](128) NULL,
	[program_name] [nvarchar](128) NULL,
	[start_time] [datetime] NOT NULL,
	[login_time] [datetime] NULL,
	[request_id] [int] NULL,
	[collection_time] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Then I built my procedure to collect the value of the hour and set up a while loop for as long as the hour matches this collected value, I want it to load my results from sp_whoisactive into my table. Then I have it wait for one minute and do it again. 

CREATE PROCEDURE [dba].[LoadWhoIsActiveLog]

AS

DECLARE @Hour INT 
SET @Hour= (SELECT DATEPART(HOUR,GETDATE()))
WHILE ( @Hour = (SELECT DATEPART(HOUR,GETDATE())))
BEGIN
    
EXEC sp_whoisactive 
	 @get_outer_command = 1
	, @find_block_leaders = 1
	, @output_column_list = '[dd hh:mm:ss.mss][percent_complete][host_name][session_id][login_name][status][sql_text][wait_info][blocking_session_id][blocked_session_count][%]'
	, @sort_order = '[blocked_session_count] DESC, [start_time] ASC'
	, @destination_table = 'dba.whoisactiveLog' 

	BEGIN
	WAITFOR DELAY '0:01'
	END
END;

It runs exactly as I wanted. I put it in an Azure Pipeline and have it fire at the top of the hour during our busy hours so we can collect usage.

The song for this post is Call the Moondust by Glen Phillips.

by

I got lucky with an Azure database restore…

Recently, the wrong table got dropped and we needed to bring it back. I had never done a restore in an Azure Managed Database before so I learned something really fast.

First, I log into the Azure Portal (portal.azure.com)

I select “SQL servers”:

I pick the right server that my managed database is on (it has the type of “SQL server”) and I click on the name of the server (it is a link to where I need to go)

In the next screen under Settings, I select SQL databases:

I click on the name of the database that I need to restore. It will take me to another screen.

It gives me this list of options at the top (this is also where I would go to create a copy of a database for another environment like dev).

I select “Restore” and it takes me to another screen where it has most of the restore ready to go. Here is where I can pick a point in time recovery or the most recent possible. Since I knew about the time the delete had happened, I am going to restore to just a few minutes before the delete. (It will be in UTC time) Most recent would have not have what I need because it took the user a little bit of time to let me know what had happened.

I can name what the database is going to be, I can add a date or time to this name so I know when it was created.

I set my storage for the cheapest options because this is just a restore that I am going to pull data from back into my current database. I won’t be keeping it long.

I select review + create at the bottom and it will start restoring my database. Depending on the size, it might take time, but it was pretty quick for me.

I pull the data back into the table that I need and then go back to the SQL Databases screen and find my newly created database name. I click on the newly created database name and this time, instead of “Restore”, I select “Delete” and follow the prompts to remove it.

This was the easiest point in time restore I have EVER done. It was fast and I didn’t have to keep adding each log to roll forward.

The song for this post is Lucky by Elle King.

by

The query was perfectly broken…

I have been teaching a T-SQL 101 class and for the homework, we asked the students to get all the records where our heroes had a birthdate between 1995 through 1999. I expected something like this:

SELECT FirstName, LastName, Birthdate
FROM Heroes
WHERE Birthdate BETWEEN '1/1/1995' AND '12/31/1999'

OR

SELECT FirstName, LastName, Birthdate
FROM Heroes
WHERE Birthdate >= '1/1/1995' AND Birthdate <= '12/31/1999'

Imagine my surprise when one of the students turned in this:

SELECT FirstName, LastName, Birthdate
FROM Heroes
WHERE Birthdate BETWEEN '1995' AND '1999'

When I first saw the query I thought, “There is no way they ran that and it worked.” So I wrote it up and ran it on my data. Guess what? IT RUNS AND RETURNS DATA! I was shocked.

I started looking at the plan and what it did to the data and found that it had done an implicit conversion on the dates and assumed 1/1/1995 to 1/1/1999 based on the year. So we were missing data from the results, but I was still in shock that it had run in the first place and shared this information with my co-worker who reminded me that dates get crazy and if I only put in ’12/31/1999′ and there is a time in the field, it will cut off most of the times within that day because it will assume I want ’12/31/1999 00:00:00′. If I want the full day, I need to get in the habit of specifying ’12/31/1999 23:59:59′ or ‘1/1/2000 00:00:00’ and then test my results to make sure I am getting what I truly want back from the database.

The song for this post is BANNERS – Perfectly Broken.

by

We are never coming undone with NOT IN…

Last summer I wrote about NOT EXISTS and how I could speed up the query by adding a left join and adding an IS NULL to the WHERE clause. Last week, I had a similar situation, but with a NOT IN. It was killing my performance. I wanted to remove all my test emails as I prepared my production email list and was calling them out specifically.

SELECT *
FROM dbo.SuperHeroes s 
INNER JOIN dbo.Contact c ON s.ContactId = c.ContactId
  AND c.EmailAddress NOT IN ('Batman@TestEmail.com', 'Superman@TestEmail.com', 'WonderWoman@TestEmail.com', 'Aquaman@TestEmail.com')

So I tried my super cool trick:

CREATE TABLE #SuperHeroTestEmail (EmailAddress varchar(50));
  INSERT INTO #SuperHeroTestEmail  (EmailAddress) VALUES ('Batman@TestEmail.com');
  INSERT INTO #SuperHeroTestEmail  (EmailAddress) VALUES ('Superman@TestEmail.com');
  INSERT INTO #SuperHeroTestEmail  (EmailAddress) VALUES ('WonderWoman@TestEmail.com');
  INSERT INTO #SuperHeroTestEmail  (EmailAddress) VALUES ('Aquaman@TestEmail.com');

SELECT *
FROM dbo.SuperHeroes s 
INNER JOIN dbo.Contact c ON s.ContactId = c.ContactId
LEFT OUTER JOIN #SuperHeroTestEmail em ON c.EmailAddress = em.EmailAddress
WHERE em.EmailAddress IS NULL;

Here I create my temp table so I can load in the email addresses that I want to remove. Then I do a LEFT JOIN on those email addresses and in my where clause I force that join to only bring me back the records that show NULL for the temp email table. This way, I am telling SQL exactly what I want it to bring back instead of telling it what I don’t want. This makes my query run faster.

I was so excited, but I started to test and noticed my counts were way off. I couldn’t figure out why until I realized my NOT IN was removing all my NULL email records. I don’t have email addresses for all of my super heroes! As soon as I figured out that, I knew what I had to do. 

CREATE TABLE #SuperHeroTestEmail (EmailAddress varchar(50));
  INSERT INTO #SuperHeroTestEmail  (EmailAddress) VALUES ('Batman@TestEmail.com');
  INSERT INTO #SuperHeroTestEmail  (EmailAddress) VALUES ('Superman@TestEmail.com');
  INSERT INTO #SuperHeroTestEmail  (EmailAddress) VALUES ('WonderWoman@TestEmail.com');
  INSERT INTO #SuperHeroTestEmail  (EmailAddress) VALUES ('Aquaman@TestEmail.com');

SELECT *
FROM dbo.SuperHeroes s 
INNER JOIN dbo.Contact c ON s.ContactId = c.ContactId
LEFT OUTER JOIN #SuperHeroTestEmail em ON c.EmailAddress = em.EmailAddress
LEFT OUTER JOIN dbo.Contact c2 ON c2.ContactId = c.ContactID AND c2.EmailAddress IS NULL
WHERE em.EmailAddress IS NULL
AND c2.ContactId IS NULL;

Here I am using the primary key in contact to join back to it but also am telling it in the join to only bring me records where the email is NULL. Then, I tell it in my where clause to only look at the records that where C2.ContactID IS NULL, this gets me around using an “IS NOT NULL” which is much slower in this case.

At the end, it cut my query run time in half, which was a win for me. The main idea is that you want to tell SQL Server what you want, not what you don’t want. It helps the engine be more precise and know exactly what you bring you. I compare this to going to a restaurant and being able to tell the waiter what dish I want instead of telling the waiter that I, “Don’t want anything with fish”. Then the waiter has to check every dish and ask me if that is what I want. It takes longer. Instead, I just tell the waiter and SQL Server what I want to consume.

The song today is a mashup of Taylor Swift (We are Never Getting Back Together) and Korn (Coming Undone) and has been living rent free in my head for weeks. Huge thanks to my awesome co-worker, Bree, that showed it to me.