SQL Server – Monitoring a Rollback

Standard

Recently bumped into an issue where we needed to kill a large insert, obviously causing SQL Server to perform a rollback. I needed to find out what was going on, so looked to Pinal Dave’s blog post about finding out which queries are running, which you can find here. Which uses the sys.dm_exec_requests and sys.dm_exec_sql_text DMVs.

The below script adds a few little things, to tell you for a given SPID, what the SQL text is that’s being executed and most importantly the expected finish time.

--Set how long you want the script to run for in HH:MM:SS format.
--The longer you leave it running the more accurate it will be
DECLARE @Delay VARCHAR(20)	= '00:00:10';

--Add your SPID, which you can get from sp_who2
DECLARE @SPID INT			= 206;


DECLARE @StartPercent DECIMAL(18,9), @Statement VARCHAR(MAX), @EndPercent DECIMAL(18,9), @DelaySeconds INT, @PercentToGo DECIMAL(18,9), @Increase DECIMAL(18,9);

--Set the starting percentage complete
SELECT @StartPercent = req.percent_complete, @Statement = sqltext.TEXT
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE session_id = @SPID

--Wait for the specified delay time
WAITFOR DELAY @Delay

--Get the end percentage complete
SELECT @EndPercent = req.percent_complete
FROM sys.dm_exec_requests req
WHERE session_id = 206

SET @PercentToGo = 100 - @EndPercent;
SET @Increase = @EndPercent - @StartPercent
SET @DelaySeconds = DATEDIFF(SECOND, 0, @Delay)

--Return the output including the expected finish time
SELECT @SPID [SPID], @Statement [SQLText], @DelaySeconds [DelaySeconds], @StartPercent [StartPercent], @EndPercent [EndPercent], @Increase [Increase], DATEADD(SECOND,(@PercentToGo / @Increase) * @DelaySeconds, GETDATE()) [ExpectedFinishTime]

 

You can use the same query to monitor the below events (source):

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

SSAS – Negative total value

Standard

We encountered a strange issue when connecting to analysis services through Excel where all of the row level sums were correct, however the total value was a negative, as per below:

IntBarrier3.PNG

As you can see the row level sums are all positives, but the grand total is a negative, why?

The underlying data is all positive, however the underlying datatype in the database is a small int, however there are 6 billion rows in the table.

The issue is that when SSAS sums those 6 billion rows of small int values the total ends up breaking the INT barrier (2,147,483,647) which Excel displays as a negative.

The measure group will inherit its data type from the underlying view and attempt to use that:

IntBarrier1.PNG

To fix this issue you need to alter the data type in the Source and then either leave the DataType in Advanced as Inherit, or change it to BigInt:

IntBarrier.PNG

Now when you deploy and process your cube, the issue should be resolved!

SSMS – Auto Save is finally here!!

Standard

If you haven.t already upgraded to SSMS 2016 you should do immediately. The guys at Microsoft have decoupled SSMS from the SQL Server release cycle and one of the best features by far is the auto-save\recover functionality.

Remember all those times you were putting the finishing touches on the best code you’ve ever written and SSMS crashed? Well worry no more. I did post previously about how SSMSBoost can help you to recover recent sessions, but now SSMS does it out of the box.

When SSMS crashed you’ll be asked to recover recent work and will see something like the below:

AutoSave.PNG

All you need to do is connect them back up and viola you’re away!

Of course you could always (and you should always) save your work as soon as you create a new window, but who am I to tell you how to live your life….

SSIS – Unicode data is odd byte size for column 4. Should be even byte size.

Standard

This one was interesting and if it wasn’t for a quite obscure article on page 3 of a Google search (who even goes to page 3 anymore) it would have taken me a lot longer to figure out the problem.

Basically we’re hitting a Kafka queue in SSIS to get and process messages as they appear. There’s some info here on how to connect SSIS to Kafka.

The messages that are coming down from the queue are in UTF8 format and I wanted to store them in an NVARCHAR(MAX) column, in case we ever need to process multilingual messages.

As we get the messages down I’m storing them as a string in UTF8, which as per the below isn’t unicode:

string text = Encoding.UTF8.GetString(msg.Payload, 0, msg.Payload.Length);

And then when we push them to the output buffer we’re using UTF8, still not unicode:

JSonOutputBuffer.AddRow();

JSonOutputBuffer.JSonRaw.AddBlobData(System.Text.Encoding.UTF8.GetBytes(text));

The problem came when I tried to push the field into an NVARCHAR(MAX) column in the database, now this IS unicode So we end up getting an error when attempting to push the value from the buffer into the table:

“Unicode data is odd byte size for column 4. Should be even byte size.”

It was an easy fix, just use Unicode as the encoding when you’re pushing the value to the otuput buffer.

JSonOutputBuffer.AddRow();

JSonOutputBuffer.JSonRaw.AddBlobData(System.Text.Encoding.Unicode.GetBytes(text));

This is caused by SQL Server expecting an even number of bytes in an NVARCHAR column as per the below:

“Notice that compressed Unicode strings are always an odd number of bytes. This is how SQL Server determines that the string has actually been compressed, because an uncompressed Unicode string—which needs 2 bytes for each character—will always be an even number of bytes” Source (You’ll need to search for ‘odd’ to find this section..

If nothing else this was an interesting error that I couldn’t find any more info on! So hopefully this helps someone..

 

SQL Server – Join to list of Strings

Standard

This one can also be used when passing a multi-value parameter from SSRS to the database.

Firstly you need to use the delimited8k split function that was created and refined by many people such as Itzek Ben Gan, Lynn Pettis and Jeff Modern. If you don’t know who these people are, go and find out as there’s some excellent reading to be had. Anyway, you can download the function here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Here’s some code you can use to see it in action: 

DECLARE @IntsAsStrings VARCHAR(100) = '1,2,3,4,5,6,7,8,9';

DECLARE @TestTable TABLE 
(
	Id INTEGER IDENTITY(1,1),
	ValueString VARCHAR(50)
);

INSERT INTO @TestTable VALUES ('We'),('Are'),('Leeds'),('MOT');

SELECT *
FROM @TestTable t
RIGHT JOIN [dbo].[DelimitedSplit8K](@IntsAsStrings, ',') ss ON ss.ItemNumber = t.Id

You can then pass a string into your proc and at the top split that out into a table or join directly onto the split function table results (as it’s a Table Valued Function (TVF)).

SSIS – RecordSet Object set to NULL after ForLoop

Standard

This was a weird and frustrating one. I’ve got a package that has a ForLoop in it and inside the ForLoop I’m loading records that need to use a Lookup. I thought what better time to use a CascheTransform than now! The only issue was that I needed to use the Lookup from inside a Script Component in my Data Flow (not a Script Task).

Now, knowing little about c# I figured I could simply load a new ADO RecordSet into a package variable and use that in my Script Component. so I created a OleDb source and a RecordSet destination:

lookup

Then populated a variable (User::Result) of type Object with my RecordSet for use later in my Script Component

lookup1

Now later in my Script Component I passed the variable in as a ReadOnly variable and populated a new DataTable using the source variable. And it worked for the first run of the ForLoop and then the underlying variable (User::Result) got set to Null. Turns out the issue was caused by using an OleDb adapter to fill a data table:

DataTable dt = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.Fill(dt, Variables.Result);
Credit: Brad2575

The above section of code causes the object to be emptied.

The Answer

The solution to this is to populate the DataTable first, not a RecordSet and pass the DataTable into the object. This avoids having to create the DataTable from the RecordSet each time.

lookup2

You can use code similar to the below to populate the DataTable: 

public class ScriptMain : UserComponent
{
    //Create a new DataTable. This will end up getting passed around your package
    DataTable tbl = new DataTable();


public override void PreExecute()
    {
        base.PreExecute();

        //Set up the columns and PrimaryKey
        tbl.Columns.Add("Column1", typeof(int));
        tbl.Columns.Add("Column2", typeof(int));

        tbl.PrimaryKey = new DataColumn[] { tbl.Columns["Column1"] };

    }
        public override void PostExecute()
    {
        base.PostExecute();

        //Populate the PackageVariable with the newly create DataTable object.
        Variables.LookupTable = tbl;
    }

    public override void LookupInput_ProcessInputRow(LookupInputBuffer Row)
    {
        //Now populate the table with rows from your query
        tbl.Rows.Add(Row.Column1, Row.Column1);
    }

}

 

There are a couple of blog posts here and here that helped me to work out the solution for this.

I hope this can help you too 🙂

Merge – Incremental Soft Delete

Standard

Sometimes we may have situations where we only load a day’s worth of data into our data warehouse. We may also need to keep the destination data in line with the incoming data.

For instance, imagine we had a shopping basket filled with items one day, which we load into our data warehouse, but the next day (for whatever reason) two of the products are no longer valid. How can we remove these records without deleting all of the other values in our data warehouse for our other trolleys that aren’t in the incoming data set?

The answer is with an incremental MERGE: 

--Declare temp tables to store data
DECLARE @Target TABLE
(
PackageId INTEGER,
JournalId INTEGER,
Amount MONEY,
IsDeleted BIT DEFAULT 0
)


DECLARE @Source TABLE
(
PackageId INTEGER,
JournalId INTEGER,
Amount MONEY
)

--Insert some dummy data
INSERT INTO @Source VALUES (1,1,10),(1,2,20),(1,3,30),(2,1,40),(2,2,50)

--Now merge it into the destination table.
MERGE INTO @Target AS TARGET
	USING
	@Source AS SOURCE
	ON TARGET.PackageId = SOURCE.PackageId
	AND TARGET.JournalId = SOURCE.JournalId

--When we match on the merge key, then update the destination table with the new value
WHEN MATCHED THEN
	UPDATE SET
		TARGET.Amount = Source.Amount

--Whne we don't match, then we're a new record, so insert the new record into the destination
WHEN NOT MATCHED BY TARGET THEN
	INSERT
	(
	PackageId,
	JournalId,
	Amount
	)
	VALUES
	(
	SOURCE.PackageId,
	SOURCE.JournalId,
	SOURCE.Amount
	);

--Now show our results.
SELECT * FROM @Source;
SELECT * FROM @Target;


--Now let's get rid of some data and carry out an update to show how we would handle a merge for
--an incremental load
DELETE FROM @Source WHERE PackageId = 1 OR (PackageId = 2 AND JournalId = 1);
UPDATE @Source SET Amount = Amount + 20

--Use a CTE to limit the destination rows we want to match against, becuase if we've only loaded
--1 day worth of values in from our source system, we could end up deleting lots of data by accident
;WITH tgtCTE AS
(
SELECT T.*
FROM @Source S
INNER JOIN @Target T ON T.PackageId = S.PackageId
)


MERGE INTO tgtCTE AS TARGET
	USING @Source AS SOURCE
	ON TARGET.PackageId = SOURCE.PackageId
	AND TARGET.JournalId = SOURCE.JournalId

--Again, update any existing records
WHEN MATCHED THEN
	UPDATE SET
		TARGET.Amount = Source.Amount

--Again, insert any new records
WHEN NOT MATCHED BY TARGET THEN
	INSERT
	(
	PackageId,
	JournalId,
	Amount
	)
	VALUES
	(
	SOURCE.PackageId,
	SOURCE.JournalId,
	SOURCE.Amount
	)

--Here is the intersting part; we're going to soft delete any records from the 
--destination table that do not exist in the incoming data set.
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TARGET.IsDeleted = 1;


--Show our final results. As you can see in the second merge we only had 1 record for PackageId = 2
--the other JournalId didn't exist in the source table, so it was soft deleted from the destination.
SELECT * FROM @Source;
SELECT * FROM @Target;

Go ahead and run the above code and see how it all works. Hopefully this will help you on your way.

Paul White gives a great example here at DBA.StackExchange