Category Archives: Database

Inconsistent result sets and another case against pessimistic isolation

I’m a big fan of demonstrating when transaction processing goes bad in Database Management Systems because it is a reminder to us all to ensure that we know exactly what we are doing when we write our code. Not only should you test and execute your code multiple times in serial (for instance running in sequence in isolation), but you should also execute in parallel with itself or other potentially dependent transactions.

Elsewhere in this blog you will find many other concurrency and consistency examples which will make your hairs stand on end with fear and dread of the dangers that might lurk in your system, and whilst I would love to claim full credit for this latest toe-curling installment, I feel it is only fair to blow smoke towards the mighty Erland Sommarskog (b) for first raising a similar observation within a private forum many months ago. The comments and code are mine.

As we all know, Database Management Systems are designed with consistency in mind. That is, the level of consistency we observe should adhere to the level of isolation we chose. You will all no doubt be aware by now that the use of NOLOCK locking hint (or more specifically the READ UNCOMMITTED ISOLATION) is our way of telling the DBMS to favor concurrency over consistency and lead to reading result sets that are either incomplete or have not yet been committed. In other words, we are able to read in-flight transactional changes.

Both (on-disk) pessimistic and optimistic isolation in SQL Server implement pre-defined behaviors with the use of row, page, or object-level locks for the non-in-memory (or traditional/ on-disk) levels of isolation. Even on-disk “optimistic” isolation takes out write-level locking (but uses row versioning for reads). Locking (and latching) is often the root cause of many transaction processing bad behaviors that we find in SQL Server.


Scenario

We have a table called Cars which is pre-loaded with 10 records.

We will have two user sessions, both running under (the default) READ COMMITTED isolation level. The first user session is continuously looping whilst each time executing a single transaction that deletes all records in the Cars table before inserting 10 records and finally committing. The second user session is continuously looping over a select statement to query our table.
Therefore, given that the delete and insert during session 1 are isolated under a transaction, we would expect that the query from session 2 would either be temporarily blocked (by session 1) OR return 10 records.

Implementation

Setup

My examples utilize the Cars table. You can set this up as follows:

CREATE TABLE Cars (
   id uniqueidentifier DEFAULT NEWID(),
   carname VARCHAR(20),
   lastservice datetime DEFAULT getdate(),
   SpeedMPH INT, Details CHAR (7000) CONSTRAINT [PK__Cars] PRIMARY KEY CLUSTERED ([id])
)

Now we need to configure two sessions that will run continuously until we hit a failure.

Session 1

In our first session, we start with 10 records in the Cars table and all subsequent changes are made within a transaction scope deleting and inserting 10 records at a given time. We understand there should be an atomic guarantee that success or failure of the transaction will ensure that 10 records should only ever be visible outside of Session 1 regardless of that outcome.

There is a tiny delay implemented within this first transaction, and whilst it is not the root cause of this problem, it helps to exacerbate the issue in testing. Feel free to do testing with our without.

Our Session 1 code is as follows:

WHILE 1=1
BEGIN 
   BEGIN TRAN
      DELETE FROM Cars

      INSERT INTO Cars(Carname, SpeedMPH, Details) VALUES('Ferrari', 170, '')
      INSERT INTO Cars(Carname, SpeedMPH, Details) VALUES('Porsche', 150, '')
      INSERT INTO Cars(Carname, SpeedMPH, Details) VALUES('Lamborghini', 175, '')
      INSERT INTO Cars(Carname, SpeedMPH, Details) VALUES('Mini', 110, '')  
      WAITFOR DELAY '00:00:00.02'
      INSERT INTO Cars(Carname, SpeedMPH, Details) VALUES('Datsun', 90, '')
      INSERT INTO Cars(Carname, SpeedMPH, Details) VALUES('Ford', 125, '')
      INSERT INTO Cars(Carname, SpeedMPH, Details) VALUES('Audi', 138, '')
      INSERT INTO Cars(Carname, SpeedMPH, Details) VALUES('BMW', 120, '')
      INSERT INTO Cars(Carname, SpeedMPH, Details) VALUES('Honda', 87, '')
      INSERT INTO Cars(Carname, SpeedMPH, Details) VALUES('Mercedes', 155, '')   
   COMMIT TRAN
END

Remember that out of the box (under READ COMMITTED isolation level), SQL Server uses exclusive locks to serialize data access to the table data and block access by other sessions to it until they are released. The exclusive locks are of course held until the end of the transaction.

Session 2

In our second session, the code will break if we manage to query less than 10 records from Cars table. If we successfully read 10 records we increment a @ConsistentResults counter and attempt another read. We expect session 2 to continue running, but if it breaks we will see how many times we managed to read 10 records.

It is worth pointing out that when we query session 2, the results are inserted into a table variable. The only reason we do this is so that we can count how many records are queried and return those rows back later.

Our Session 2 code is as follows:

DECLARE @Cars TABLE (id uniqueidentifier DEFAULT NEWID(), carname VARCHAR(20), 
    lastservice datetime DEFAULT getdate(), SpeedMPH INT, Details CHAR (7000));
DECLARE @ConsistentResults INT = 0
WHILE 1=1
BEGIN
    DELETE FROM @Cars
    INSERT INTO @Cars SELECT * FROM Cars
    IF @@ROWCOUNT <> 10
        BREAK

    SET @ConsistentResults = @ConsistentResults + 1
    WAITFOR DELAY '00:00:00.013'
END
SELECT @ConsistentResults AS SuccessfulPriorRuns
SELECT * FROM @Cars

Test results

Our first run of Session 2 code gives us:

SuccessfulPriorRuns
-------------------
4

id                                   carname              lastservice             SpeedMPH    Details
------------------------------------ -------------------- ----------------------- ----------- -------
4587384F-E79A-4D4C-9A69-1D969F31E431 Porsche              2020-04-19 18:58:25.627 150                                                                                                                                                                                                                                                                         
F9AC6FFF-51B7-44E6-B773-311B17F0C5D7 Lamborghini          2020-04-19 18:58:25.627 175                                                                                                                                                                                                                                                                         
3C0C196A-95DE-40F5-B6A3-432574CECBA5 Ford                 2020-04-19 18:58:25.640 125                                                                                                                                                                                                                                                                         
5F844E89-13A4-4D46-874A-55513F389A3C Audi                 2020-04-19 18:58:25.640 138                                                                                                                                                                                                                                                                         
BADB1001-C4F2-4C7E-A65C-5A4099C43244 BMW                  2020-04-19 18:58:25.640 120                                                                                                                                                                                                                                                                         
E0E4EA82-DF24-44A9-93BB-89BB43B8FEB6 Datsun               2020-04-19 18:58:25.640 90                                                                                                                                                                                                                                                                          
DD29F38F-01E2-459C-A14E-DE8D456B21FE Mercedes             2020-04-19 18:58:25.640 155                                                                                                                                                                                                                                                                         
32BF996D-6077-4AAE-B23F-E5BB3A99365F Ferrari              2020-04-19 18:58:25.627 170                                                                                                                                                                                                                                                                         
B5E854C9-382B-4EB8-8E63-FB76F45B6EB5 Mini                 2020-04-19 18:58:25.627 110                                                                                                                                                                                                                                                                         

Completion time: 2020-04-19T18:58:25.6962254+01:00

As you can see, we managed to successfully read 10 records in the Cars table four times before reading only 9 records.


Our second run of Session 2 code gives us:

SuccessfulPriorRuns
-------------------
35

id                                   carname              lastservice             SpeedMPH    Details
------------------------------------ -------------------- ----------------------- ----------- -------
0B537CEF-6C8A-49A4-9295-054D29FEADC2 Porsche              2020-04-19 19:01:01.947 150                                                                                                                                                                                                                                                                         
DFC2990C-E410-45AF-A225-34405AF83E17 Ford                 2020-04-19 19:01:01.993 125                                                                                                                                                                                                                                                                         
9AC2AF9B-25C1-44BD-B503-38BE8D1C656A Audi                 2020-04-19 19:01:01.993 138                                                                                                                                                                                                                                                                         
65D57774-BDE8-4072-BA3D-59A093AC4103 Honda                2020-04-19 19:01:01.993 87                                                                                                                                                                                                                                                                          
3BF7C449-7FCA-4A93-B2CD-6688141410D2 Lamborghini          2020-04-19 19:01:01.947 175                                                                                                                                                                                                                                                                         
1D5D62B3-11C7-4EC5-984F-67FB87296021 Mini                 2020-04-19 19:01:01.947 110                                                                                                                                                                                                                                                                         
68A32AE3-F2C7-41DA-BFAD-8FA0276F543A Datsun               2020-04-19 19:01:01.993 90                                                                                                                                                                                                                                                                

Completion time: 2020-04-19T19:01:02.1127856+01:00

This time we managed to successfully read 10 records in the Cars table thirty-five times before reading only 7 records.


Our final run of Session 2 code gives us:

SuccessfulPriorRuns
-------------------
0

id                                   carname              lastservice             SpeedMPH    Details
------------------------------------ -------------------- ----------------------- ----------- -------
85227E5F-4C0F-4863-9895-21BA0232CFF5 Porsche              2020-04-19 19:05:43.380 150                                                                                                                                                                                                                                                                         
F9AE7CE7-F092-4F09-9A69-25423CD0D9F4 Mercedes             2020-04-19 19:05:43.410 155                                                                                                                                                                                                                                                                         
8E0F5C2D-3744-422A-A2B0-27D50BC89A96 Datsun               2020-04-19 19:05:43.410 90                                                                                                                                                                                                                                                                          
D48E0EBD-9171-4278-A76B-32E2BCD64260 BMW                  2020-04-19 19:05:43.410 120                                                                                                                                                                                                                                                                      

Completion time: 2020-04-19T19:05:43.5190517+01:00

The results this time were even worse. Our code failed on the first read of the Cars table reading only 4 records

Isolation Levels and In-Memory OLTP

If we run session 2 under all Pessimistic Isolation Levels (using on-disk), we get the following results:

Isolation Level Outcome Description
READ UNCOMMITTED FAIL result set size varies widely
READ COMMITTED FAIL this was the default used in the examples
REPEATABLE READ PARTIAL FAIL result set size varies and the occasional deadlock
SERIALIZABLE SUCCESS either a successful run OR deadlock

If we now run session 2 under the Optimistic Isolation Levels (using on-disk), we get the following results:

Isolation Level Outcome Description
SNAPSHOT SUCCESS all runs successful
READ COMMITTED SNAPSHOT SUCCESS all runs successful

If we implement the Cars table as an In-Memory OLTP table and run session 2 we get the following results:

Isolation Level Outcome Description
* SUCCESS all runs successful

Summary

As with nearly all cases of inconsistencies and strange transactional behaviors, they are usually displayed in the lower transactional isolation levels and this situation is no different. As is also demonstrated in our testing, we can usually fix these bad behaviors by using more restrictive levels. As you can see in our results, the problem still exists in REPEATABLE READ -although it appears to partially reduce the problem through the occasional deadlock (deadlocks exist for consistency reasons – so you must obviously handle them). SERIALIZABLE, which is the most restrictive isolation level of all, fully resolves the issue by deadlocking every single time that a successful run isn’t made. This clearly will be terminal for performance if your application handles and replays transactions on deadlock.

SNAPSHOT isolation comes (as expected) to the rescue and we do not see any consistency bad behaviors for any read, but quite surprisingly so does the optimistic implementation of READ COMMITTED (known to you and me as READ COMMITTED SNAPSHOT). I wasn’t really expecting that to work.

Finally, when we look towards In-Memory OLTP (IMOLTP), given that under the covers IMOLTP uses an entirely different (and optimistic) concurrency model and is completely lock and latch free, it is perhaps no surprise that no issues were experienced there. These tests add yet more weight (if you even needed it) to a move towards (on-disk) optimistic or In-Memory OLTP.

When this issue was first reported by Erland, Paul Randal (b|t) of SQLSkills confirmed that was a known behavior and offered up his post titled Read committed doesn’t guarantee much…. While a quick read of Paul’s post will on the face of it look like the same issue as the one I describe, the one thing that does not align is the use of a HEAP table -since we have added a clustered index our example.

That aside, I believe the biggest trigger for this specific problem is very similar and that the following are pivotal to things going wrong:
– The use of a table Cluster GUID
– The use of mixed extents – and how SQL Server accesses them

Final Thought

I have been saying for a very long time now that no-one should be using pessimistic isolation on their SQL Servers (despite it still being the default) with the only caveat being a 3rd party support contract requirement (and if the 3rd party doesn’t support it, you should look to move to other software). Furthermore, in SQL Server 2016 and onwards, mixed extents are disabled by default so I would expect this issue not to raise its ugly head – however, that is the only scenario I haven’t tested so I cannot confirm this.

This issue WILL NOT occur with optimistic isolation nor with In-Memory OLTP -so perhaps it is time for you to change!

Azure subscription is not registered to use Cosmos DB namespace

It is usually the simplest things that often leave me feeling like I am a complete dummy. One such issue I ran into fairly recently when trying to deploy Cosmos DB into an Azure subscription.

From Azure DevOps I received the following error:

2019-05-31T16:28:55.4288261Z ##[error]MissingSubscriptionRegistration : The subscription is not registered to use namespace ‘Microsoft.DocumentDB’. See https://aka.ms/rps-not-found for how to register subscriptions.
2019-05-31T16:28:55.5005526Z ##[section]Finishing: Deploy Cosmos Account and Database with Shared Capacity

I initially assumed that the error was Azure DevOps related so I attempted to deploy using PowerShell and ran into an almost identical error.

I had deployed this Cosmos DB template successfully many times in our other subscriptions and could not understand why a simple deployment to an alternative subscription would fail. Looking back at the error message I followed the link provided which took me to a Microsoft doc titled Troubleshoot common Azure deployment errors with Azure Resource Manager and linked within I ended up on Resolve errors for resource provider registration.

It turns out that the Azure resource providers, which you can almost think of as Class libraries, can (like their programmatic counterparts) be in either Registered or NotRegistered state. When they are in a NotRegistered state, this means that we are unable to call that provider to create a specific resource (such as Cosmos DB in my case).

We can use PowerShell Az or the Azure CLI (both talked about elsewhere in this blog) to report what resources are available. In this specific example, I am going to return all providers that match the wildcard pattern of Microsoft.D*. The code searches for and sets the relevant subscription using Azure Cloud Shell (for simplicities sake), but you can do this through a remote Azure CLI or PowerShell Az session if you would prefer (and connectivity allows).

$subscription = Get-AzSubscription | Where-Object Name -Match "MySubscription1*"
Select-AzSubscription $subscription 
$providers = Get-AzResourceProvider -listavailable |Select-Object ProviderNamespace, RegistrationState
$providers  | Where-Object ProviderNamespace -Match "Microsoft.D*"

We get the following results:

ProviderNamespace               RegistrationState
-----------------               -----------------
Microsoft.DBforPostgreSQL       Registered
Microsoft.DevTestLab            Registered
Microsoft.Databricks            Registered
Microsoft.DataLakeStore         Registered
Microsoft.DataLakeAnalytics     Registered
Microsoft.DBforMySQL            Registered
Microsoft.DevSpaces             Registered
Microsoft.Devices               Registered
Microsoft.DataFactory           Registered
Microsoft.DataBox               NotRegistered
Microsoft.DataBoxEdge           NotRegistered
Microsoft.DataCatalog           NotRegistered
Microsoft.DataMigration         NotRegistered
Microsoft.DataShare             NotRegistered
Microsoft.DBforMariaDB          NotRegistered
Microsoft.DeploymentManager     NotRegistered
Microsoft.DesktopVirtualization NotRegistered
Microsoft.DevOps                NotRegistered
Microsoft.DigitalTwins          NotRegistered
Microsoft.DocumentDB            NotRegistered
Microsoft.DomainRegistration    NotRegistered

Notice that the Microsoft.DocumentDB namespace is disabled. If you are wondering, DocumentDB was the precursor name of the Cosmos DB SQL API (before Cosmos DB supported multiple APIs). Like many other Microsoft products, early names tend to stick with the products :).

To register this namespace we can simply run the following line of code against the subscription using the Register-AzResourceProvider cmdlet.

Register-AzResourceProvider -ProviderNamespace Microsoft.DocumentDB

The following output is returned:

ProviderNamespace : Microsoft.DocumentDB
RegistrationState : Registering
ResourceTypes     : {databaseAccounts, databaseAccountNames, operations, operationResults…}
Locations         : {Australia Central, Australia East, Australia Southeast, Canada Central…}

If it is not obvious you would unregister a provider namespace (if you wanted to make it unavailable) using the Unregister-AzResourceProvider cmdlet as follows:

UnRegister-AzResourceProvider -ProviderNamespace Microsoft.DocumentDB

Once I had registered the Microsoft.DocumentDB namespace, I was able to deploy my Cosmos DB template into my subscription without error!

Summary

Depending upon your subscription and region, your enabled provider namespaces may vary, however in my case someone had explicitly un-registered Microsoft.DocumentDB from it. You might ask why someone might do that? Well, it is a good way to prevent deployments of certain resource types if they go against your company policy.

As you can see, if you run into a similar problem or want to start using resource types that are by default NotRegistered you can register and start using them incredibly easily.

Custom Job scheduling, remote queries, and avoiding false negatives using PowerShell and SQL Agent

Say what you want about the SQL Server Agent, it is the lifeblood of SQL Server for scheduling maintenance routines, data loading, and other such ongoing operations. It has been around since the dawn of time and has grown into a rich (but simple) job execution scheduler, yet at the same time, still lacks certain capabilities that mildly annoy me.

Scheduling exclusions

One such annoyance is the inability to create intelligent exclusions for job schedules. In other words the “Don’t run this job if” scenarios that you might commonly run into at retailers, education or health sectors, financial institutions and other environments that may have time-sensitive or complex execution logic.

Now I know what you are thinking. You are thinking that you have created plenty of job schedules in the past and managed to add exceptions to the schedule, and with this, you are partially correct.

In the SQL Agent Job Schedule dialog above, we have a weekly schedule that is set to run every Monday to Friday, running only twice a day, starting at 00:00:00. By very definition, this schedule will not run on Saturday or Sunday (and therefore we have an exception), but we are constrained within the bounds of the dialog interface and available fields and logic.

Complex scheduling logic requirements

Consider an environment that consumes lots of ETL feeds from third parties on a (working) daily basis. The feeds, however, are not created during holidays, meaning that the dependent job/s would fail if they ran on those days. There are three obvious ways to work around this situation:

  1. Allow failures to occur (rolling back changes on failure)
  2. Embed feed existence logic *1 (and/or exception logic) within the code/ package (to prevent the package execution from failing)
  3. Prevent job execution on-condition within a job step

Option 1 (quite clearly) is not a great solution and would involve false negative reporting (in systems such as SCOM since the error would be just that) and require human interaction to review. Jobs would fail, but the failure would only be a result of missing feeds and nothing more. Recovery in this instance would (hopefully!) only require allowing the job to run on schedule once the feeds are delivered the following day.

Option 2 would address the problem of failure in this instance but does not implement schedule exception logic. In other words, the job (and its package) would execute successfully, but this in itself creates false success reporting (the package did not perform its usual workload). Implementing schedule exception logic inside the code/package is also far from ideal since its function is easily lost in the logic of the actual job step and maintaining and building upon this logic could become unwieldy over time.

Option 3 is perhaps the best choice and provides isolation between the job schedule logic and the job function itself (because requirements can change) and improved visibility (feedback) of why the job did not run – but we still need to understand how to implement this for situations that are outside the capability of the Job Schedule dialog.

*1 Whilst it is fair to argue that existence logic is not the solution to our problem, it should be obvious to the reader that creating packages that gracefully handle failure is a given. In reality, all package dependencies should be tested for, and reported on error -and I tend to favor the “bubbling up approach” to failure.

Remote queries for exclusion lookups

In this specific scenario, we need to implement our custom logic to perform our holiday test and raise an error if the current day is one (we will address the handling of this error shortly). In our environment, we maintain a centralized calendar (in a table of course!) of all important dates and public holidays up to five years in advance, and this makes for an excellent reference point for our test query. We want to store our table on a different (but highly available) SQL instance so it can be used by all SQL instance Agent jobs, but its location introduces yet another challenge to our problem -how do we query this remote data from our Agent job?

If you automatically jumped to the conclusion that we should use a linked server, then I can tell you are a bit of a masochist. One of the (several) problems with this approach is that we would have to create a linked server to our calendar data instance within every instance that needs to reference it. In an enterprise environment, this could be a problem to deploy and maintain.

SQL Server 2008 R2 introduced PowerShell Agent job steps, and this gives us a very easy mechanism to programmatically run our remote query without a linked server.

In the following piece of code, we are simply querying our remote server hosting the calendar table ([DBAAdmin].[dbo].[FullCalendar]) and searching for today’s date.

$serverinstance = "server1"
$sqlCommandText = @"
DECLARE @testdate DATE = GETDATE()
SELECT IsHoliday
FROM [DBAAdmin].[dbo].[FullCalendar]
WHERE DateKey=@testdate
"@

if ((Invoke-Sqlcmd $sqlCommandText -ServerInstance $serverinstance).IsHoliday) {
throw "Current date is a Holiday"
}

If today’s date is classed in the table as a holiday (see the evaluation against the returned dataset’s IsHoliday property), then an exception will be thrown and the step will be failed.

On step failure success

The only problem with failing the job when today *is* a holiday, is that we are essentially raising a false-negative which is not much better than just allowing a job just to fail because a feed wasn’t delivered for that day. In the ideal world, we need the job to quit but terminate in such a way that is uniquely different to a “success” (i.e. that today is not a holiday) or “failure” (i.e. that the job has failed for some other reason that needs investigation).

The trick here is to change the job step On failure action to “Quit the job reporting success” via the Job Step/ Advanced Properties as follows:

So your job steps are like this:

By doing so, your skip logic will result in the job step failure showing the job execution as an alert (rather than failure). Only the step itself will show as a failure -which, I think you will agree, is pretty cool! This means that not only do you have a visual indicator that job was canceled by design, but you should be able to programmatically exclude these alerts in SCOM.

Summary

In this post, we have done several things in order to deliver a richer scheduling solution for our agent jobs. These are as follows:

  1. We have prevented our scheduling custom logic job step from causing false negatives through setting its “On Failure: Quit the job reporting success” to allow the job to report only an alert but and only fail the job step.
  2. We have created a centralized table to store custom metadata to allow all SQL instances to query it for scheduling logic scale-out (where you might need to!).
  3. We have utilized PowerShell to query our remote metadata in order to avoid the need to create (and maintain) instance-specific linked servers. This is clearly a good thing for the deployment of standardized scripts.

I hope you can see from this very simple example that not only can we now implement very rich custom scheduling solutions for our jobs, but we can also use some of these techniques to avoid the unnecessary use of linked servers and other things. Enjoy!

In-Memory logging and log “compression”

Introduced with SQL Server 2014, In-Memory OLTP (IMOLTP) provided a new optimistic concurrency model which implemented a lockless and latchless mechanism resulting in far better throughput for short concurrent OLTP workloads. Unlike the traditional on-disk concurrency model, IMOLTP stores table data as data rows in-memory (rather than in-buffer page structures) and this data (or changes to it) will eventually be asynchronously written into checkpoint file pair structures on-disk. Given that the speed of physical disk is no longer a major consideration for the speed of data changes when compared against the traditional on-disk concurrency model, you would presume that we no longer need to worry too much about their performance. Unfortunately, we have something else to consider – the transaction log.

The transaction log is almost certainly going to become your biggest concern for potential bottlenecks with databases enabled for IMOLTP since (for SCHEMA_AND_DATA In-Memory tables at least) the transaction log is still required to persist transaction log records for durability purposes. In other words, under default behavior, even In-Memory transaction log records must be flushed and written to the transaction log when those transactions commit.

Luckily IMOLTP provides several logging benefits including the following:

  • No index logging overhead. Only index definitions are persisted meaning that they are rebuilt upon database recovery (should that happen!)
  •  No undo logging overhead. Undo is unnecessary because In-Memory data structures are not overwritten and instead, new structures are created (on commit, the old structures are marked for garbage collection). Rollback, therefore, only requires the old structures to remain.
  • Log record ordering by Transaction End Timestamp. This removes the requirement for a single log stream (implemented in SQL 2016).
  • Log record “compression”. We will examine this in more detail below.

Log record “compression”

It is perhaps important to highlight the fact that the term Log record “compression” is something that I have coined myself, and may or may not be the correct or official term for this feature. However, I have not managed to find any official terminology for it to date, but after reading further I think it will become clear why I am using it. Before we do that, first let’s examine logging with on-disk tables.

Logging with on-disk tables

I will first create a simple table called Assimilations:

CREATE TABLE Assimilations
	(id INT IDENTITY,
	assimilation_date datetime DEFAULT getdate(),
	NewBorg INT,
	Details CHAR (50));
GO

Next we will execute a multi-statement transaction:

BEGIN TRAN
		INSERT INTO Assimilations (assimilation_date, NewBorg)
			VALUES (GETDATE(), 10);
		INSERT INTO Assimilations (assimilation_date, NewBorg)
			VALUES (GETDATE(), 15);
		INSERT INTO Assimilations (assimilation_date, NewBorg)
			VALUES (GETDATE(), 5);
		INSERT INTO Assimilations (assimilation_date, NewBorg)
			VALUES (GETDATE(), 7);
COMMIT

This committed transaction’s log records should now be available to query using fn.dblog. We first find the most recent transaction id in the log for our operation, and then use that to pull back all log records for it:

DECLARE @TransactionID NVARCHAR(14)
DECLARE @CurrentLSN NVARCHAR(23)
SELECT TOP 1 @TransactionID =
        [Transaction ID], @CurrentLSN = [Current LSN]
	FROM    sys.fn_dblog(NULL, NULL)
	WHERE   Operation = 'LOP_INSERT_ROWS'
	ORDER BY [Current LSN] DESC;

SELECT  [Current LSN], [Operation], [Transaction ID]
	FROM    sys.fn_dblog(NULL, NULL)
	WHERE   [Transaction ID] = @TransactionID
	ORDER BY [Current LSN] ASC;
GO

This returns the following results:

Current LSN             Operation         Transaction ID
----------------------- ----------------- --------------
00000024:00000378:0001  LOP_BEGIN_XACT    0000:00000384
00000024:00000378:0002  LOP_INSERT_ROWS   000:00000384
00000024:00000378:0003  LOP_INSERT_ROWS   0000:00000384
00000024:00000378:0004  LOP_INSERT_ROWS   000:00000384
00000024:00000378:0005  LOP_INSERT_ROWS   0000:00000384
00000024:00000378:0006  LOP_COMMIT_XACT   0000:00000384

(6 rows affected)

As you can see, we have a total of six log records returned. The first being the transaction BEGIN log record followed by four INSERT log records and terminating with one COMMIT log record. You will see that these quite obviously align with our transactional statements.

Logging with In-Memory tables

For this example I will create a similar but basic IMOLTP table called AssimilationsIM:

CREATE TABLE AssimilationsIM
	(id INT IDENTITY PRIMARY KEY NONCLUSTERED HASH
		WITH (BUCKET_COUNT=32) NOT NULL,
	Assimilation_Date datetime DEFAULT getdate(),
	NewBorg INT,
	Details CHAR (50))
	WITH (MEMORY_OPTIMIZED=ON,
	DURABILITY = SCHEMA_AND_DATA)
GO

And of course we still need to execute a multi-statement transaction (as before) but this time inserting values into our new in-memory table:

BEGIN TRAN
		INSERT INTO AssimilationsIM (assimilation_date, NewBorg)
			VALUES (GETDATE(), 10);
		INSERT INTO AssimilationsIM (assimilation_date, NewBorg)
			VALUES (GETDATE(), 15);
		INSERT INTO AssimilationsIM (assimilation_date, NewBorg)
			VALUES (GETDATE(), 5);
		INSERT INTO AssimilationsIM (assimilation_date, NewBorg)
			VALUES (GETDATE(), 7);
COMMIT

Again our committed transaction’s log records should now be available to query using fn.dblog. As before we first find the most recent transaction id in the log for our operation (this time we are looking for the LOP_HK operation rather than the LOP_INSERT_ROWS operation), and then use that to pull back all log records for it:

DECLARE @TransactionID NVARCHAR(14)
DECLARE @CurrentLSN NVARCHAR(23)
SELECT TOP 1 @TransactionID =
        [Transaction ID], @CurrentLSN = [Current LSN]
	FROM    sys.fn_dblog(NULL, NULL)
	WHERE   Operation = 'LOP_HK'
	ORDER BY [Current LSN] DESC;

SELECT
	@TransactionID AS '[Transaction ID]',
	@CurrentLSN AS '[Current LSN]'

SELECT  *
	FROM    sys.fn_dblog(NULL, NULL)
	WHERE   [Transaction ID] = @TransactionID
	ORDER BY [Current LSN] ASC;

Interestingly this now returns the following results:

Current LSN             Operation         Transaction ID
----------------------- ----------------- --------------
00000024:00000478:0002  LOP_BEGIN_XACT    0000:00000396
00000024:00000478:0003  LOP_HK            0000:00000396
00000024:00000478:0004  LOP_COMMIT_XACT   0000:00000396

(3 rows affected)

This time, we have a total of three log records returned. The first and last being the transaction BEGIN and COMMIT log records as before, but now we have a single log record sandwiched in-between. We can use an undocumented in-memory function (fn_dblog_xtp) to break this record open based up our transaction id:

SELECT
	[Current LSN],
	operation_desc,
	Operation,
	[Transaction ID]
	FROM    sys.fn_dblog_xtp(NULL, NULL)
	WHERE   [Current LSN] = @CurrentLSN
	ORDER BY [Current LSN] ASC
GO

This now yields the following:

Current LSN             operation_desc      Operation    Transaction ID
----------------------- ------------------- ------------ --------------
00000024:00000478:0003  HK_LOP_BEGIN_TX     LOP_HK       0000:00000396
00000024:00000478:0003  HK_LOP_UPDATE_ROW   LOP_HK       0000:00000396
00000024:00000478:0003  HK_LOP_INSERT_ROW   LOP_HK       0000:00000396
00000024:00000478:0003  HK_LOP_INSERT_ROW   LOP_HK       0000:00000396
00000024:00000478:0003  HK_LOP_INSERT_ROW   LOP_HK       0000:00000396
00000024:00000478:0003  HK_LOP_INSERT_ROW   LOP_HK       0000:00000396
00000024:00000478:0003  HK_LOP_COMMIT_TX    LOP_HK       0000:00000396

(7 rows affected)

We can now see the same number of BEGIN, INSERT, and COMMIT log records as with the on-disk transaction. This means that IMOLTP has somehow “compressed” our multiple log records into one single log record. This is made possible by another optimization that we haven’t yet mentioned, although it could have perhaps been inferred by the fact that UNDO is not logged. Basically, IMOLTP transactions do not cause any physical log IO until they have been committed and allows these multi-statement log records to be rolled into one.

On a final note, I have yet to figure out what exactly the HK_LOP_UPDATE_ROW record is recording, but it is my suspicion that this is related to an internal meta operation or something to do with the hash index on the table. I will update this post when this mystery is solved.

In summary, IMOLTP has many logging efficiencies baked into it out of the box. If the log file of your database is one of your biggest bottlenecks, then using this technology in the right situation could be a very good idea!

Manipulating SQL Agent Jobs through PowerShell

In our last post titled Loading and using SQL Server Management Objects in PowerShell, we demonstrated how to load and consume SQL Server Management Objects in PowerShell, which then allows us to access your SQL instance through an SMO instance object. Please make sure you read that post before continuing.

In this specific post, we will demonstrate and walk through how to use PSH and SMO to view and change SQL Agent jobs.

If you recall we had previously instantiated an object to our SQL Server instance and assigned it to our $sqlinstance variable as follows:

$sqlinstance = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server("server2\sql2016")

We can access the SQL Server Agent through its Jobserver property (returning an object of type Microsoft.SqlServer.Management.Smo.Agent.JobServer). This object has lots of available properties and methods available, but the one we are interested in is clearly the Jobs property (providing a collection of type Microsoft.SqlServer.Management.Smo.Agent.JobCollection). Obviously, this means we have to iterate over each one or access a specific element through the collections indexer.

For now, let’s take a look at all the jobs in the Jobs collection:

$sqlinstance.jobserver.jobs|Select-Object -Property Name, Description

This results in the following…

Name                    Description
----                    -----------
My Maintenance Job      No description available.
syspolicy_purge_history No description available.

Through the indexer we can access our job in question using its collection numerator:

$sqljob=$sqlinstance.jobserver.jobs[0]

Or via its named property:

$sqljob=$sqlinstance.jobserver.jobs['My Maintenance Job']

Either way, we now have an object which we can retrieve or change properties on.

A word of caution, it is possible for the underlying SQL instance settings to change between the time that your SMO instance object was created and now, so if you intend on changing something it is advisable to make sure you are looking at the most recent change. I fully expected that calling the Refresh method on the Jobs collection would be enough to rebuild the collection with updated properties, but this does not appear to be the case. In fact, doing this on the parent JobServer object or even the sqlinstance object does not do that either.

The only guaranteed way to ensure your SQL job information is accurate appears to be calling the Refresh method on a specific job itself as follows:

$sqljob.Refresh()

Querying our specific job now shows that we now have a description value (albeit misspelled!).

$sqljob|Select-Object -Property Name, Description

Gives us…

Name               Description
----               -----------
My Maintenance Job My descriptionz2

Let’s fix that problem and change the description. Obviously, this should be a simple operation by setting the SQL job Description property:

$sqljob.Description = 'My description'

Unfortunately, if we take a look at the SQL job through SSMS (feel free to right-click and refresh the Agent node first), we see that our change has not taken effect.

In order to write back your change/s to the SQL Server instance you must call the objects Alter method as follows:

$sqljob.Alter()

And this is enough to update the SQL Job on the SQL instance!

This concludes what I hope was a fairly easy two-post demonstration of how to use SQL Server Management Objects and PowerShell and use them to programmatically manipulate your SQL Server instances. In this specific case we ultimately used SMO through PSH to query and manipulate a simple property on a SQL Job (the job description), however I hope you can visualize the potential for doing much more advanced operations using this method to the SQL job server and the entire SQL instance!

Using conditional COUNT(*)s

Whilst recently working with historic financial data, I ran across a situation that needed an aggregate view of transactional data grouped by a certain set of attributes in order to backfill some missing aggregate data sets. In front of me, I had transactional data from time immemorial along with a series of pre-built (validated) historic aggregates which had been created from a different (but now unknown) process.

My mission then, was to understand how this source transactional data must be aggregated so that it could be compared against the sets of pre-built aggregate data. Once I validated the process, I could (re)populate the missing aggregate tables in confidence.

The source transactional table (simplified for brevity) consisted of:
Transaction_Date, Credit_Card_Type, Transaction_Type, and Sales_Value.

The aggregated destination table consisted of:
Transaction_YYMM, Credit_Card, Number_Of_Sales, and Sales_Value

The task in itself appeared relatively trivial and looked to require a grouping on Transaction_YYMM (a calculated field from Transaction_Date) and Credit_Card_Type, with Number_Of_Sales being a simple COUNT(*) of each transaction. Therefore Sales_Value appeared to be a simple SUM of the transaction Sales_Value -easy right!

If we run the following query:

SELECT
	CAST(FORMAT(Transaction_Date, 'yyyy-MM') AS VARCHAR(max)) AS 'Transaction_YYMM',
	Credit_Card_Type,
	COUNT(*) AS 'Number_Of_Sales',
	SUM(Transaction_Amount) AS 'Sales_Value'
FROM #Transactions
GROUP BY
	CAST(FORMAT(Transaction_Date, 'yyyy-MM') AS VARCHAR(max)),
	Credit_Card_Type
GO

We get the following results:

results1

When I came to compare the results against aggregated data that I had, I noticed that the values were off and it became fairly obvious that the transactional data also contained refunds and rebates (positive values but logically reflected as negative by the Transaction_Type status) and these were not just causing inaccuracies for the SUM on Sales_Value, but were also causing the COUNT for Number_Of_Sales to be wrong. In other words, refunds and rebates must be removed from the SUM total and not aggregated in the Number_Of_Sales columns. Now at this stage, you might be thinking that we can do this by a simple WHERE clause to filter them from the aggregates, but not only is it wrong to “throw away” data, I realised that my target tables also contained aggregate columns for refunds and rebates.

Therefore our target table now consists of the following columns:
Transaction_Date, Credit_Card_Type, Transaction_Type, and Sales_Value, Number_Of_Refunds, Refund_Value

Conditional COUNT

In order to achieve this goal from our source data, it is obvious that we need a conditional statement to aggregate the payment data only within the payment columns and aggregate the refund data only in the refund columns. Implementing this logic within the SUM statement was easy and was as follows:

SELECT ...
SUM(CASE WHEN Transaction_Type IN ('PAYM') THEN Transaction_Amount
ELSE 0 END) AS 'Sales_Value'
FROM ...

In the function above, SUM will simply aggregate all Transaction_Amount(s) if they are of Transaction_Type PAYM, otherwise it will consider that specific value as zero (thereby excluding it from the aggregation). When it came to the COUNT statement, things were not quite so obvious. I confess to mostly using COUNT in its COUNT(*) guise in the past, but imagine my surprise after browsing the online pages for the COUNT statement, I notice the expression keyword (see below).

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

If expressions are also permitted inside the COUNT function (like SUM) then perhaps I can do something similar to the way I calculated the conditional SUM?

I tried the following function call for that aggregation:

SELECT ...
COUNT(CASE WHEN Transaction_Type IN ('PAYM') THEN 1
ELSE 0 END) AS 'Number_Of_Sales'
FROM ...

And while this code code ran, it did not make any difference to the counts returned (let’s demonstrate this using the full code for payments and refunds):

SELECT
    CAST(FORMAT(Transaction_Date, 'yyyy-MM') AS VARCHAR(7)) AS 'Transaction_YYMM',
    Credit_Card_Type,
    COUNT(CASE WHEN Transaction_Type IN ('PAYM') THEN 1
	ELSE 0 END) AS 'Number_Of_Sales',
    SUM(CASE WHEN Transaction_Type IN ('PAYM') THEN Transaction_Amount
	ELSE 0 END) AS 'Sales_Value',
    COUNT(CASE WHEN Transaction_Type IN ('REVR') THEN 1
	ELSE 0 END) AS 'Number_Of_Refunds',
    SUM(CASE WHEN Transaction_Type IN ('REVR') THEN Transaction_Amount
	ELSE 0 END) AS 'Refund_Value'
FROM #Transactions
GROUP BY
    CAST(FORMAT(Transaction_Date, 'yyyy-MM') AS VARCHAR(7)),
    Credit_Card_Type
GO

We get the following results:

results2

We can quite clearly see that this time the Sales_Value and Refund_Value aggregates (using SUM) are correct (see the difference for BARCLAYSCARD data), but the counts are obviously wrong -in fact the “conditional COUNT” appears to just be ignoring the expression.

However I realised that I could use SUM instead of COUNT as a workaround to this problem as follows:

SELECT
    CAST(FORMAT(Transaction_Date, 'yyyy-MM') AS VARCHAR(7)) AS 'Transaction_YYMM',
    Credit_Card_Type,
    SUM(CASE WHEN Transaction_Type IN ('PAYM') THEN 1
	ELSE 0 END) AS 'Number_Of_Sales',
    SUM(CASE WHEN Transaction_Type IN ('PAYM') THEN Transaction_Amount
	ELSE 0 END) AS 'Sales_Value',
    SUM(CASE WHEN Transaction_Type IN ('REVR') THEN 1
	ELSE 0 END) AS 'Number_Of_Refunds',
    SUM(CASE WHEN Transaction_Type IN ('REVR') THEN Transaction_Amount
	ELSE 0 END) AS 'Refund_Value'
FROM #Transactions
GROUP BY
    CAST(FORMAT(Transaction_Date, 'yyyy-MM') AS VARCHAR(7)),
    Credit_Card_Type
GO

And this time we get the results we were looking for:
results3

It was only after getting a working solution that I revisited using a conditional count to perform the aggregation. After thinking a little more about what COUNT is actually doing (increments a “count for every item in a group”), my expression logic didn’t quite make sense. In that case COUNT was incrementing regardless of whether a 1 or 0 was returned, so instead of returning a 0, this time I decided to return a NULL. The code fragment is as follows:

SELECT ...
COUNT(CASE WHEN Transaction_Type IN ('PAYM') THEN 1
ELSE NULL END) AS 'Number_Of_Sales'
FROM ...

The results were a success! However we are not entirely done since this code could be refactored further to remove the ELSE clause since a NULL would be implicitly returned if the CASE statement did not find a match.
The final solution is as follows:

SELECT
    CAST(FORMAT(Transaction_Date, 'yyyy-MM') AS VARCHAR(7)) AS 'Transaction_YYMM',
    Credit_Card_Type,
    COUNT(CASE WHEN Transaction_Type IN ('PAYM') THEN 1 END) AS 'Number_Of_Sales',
    SUM(CASE WHEN Transaction_Type IN ('PAYM') THEN Transaction_Amount
	ELSE 0 END) AS 'Sales_Value',
    COUNT(CASE WHEN Transaction_Type IN ('REVR') THEN 1 END) AS 'Number_Of_Refunds',
    SUM(CASE WHEN Transaction_Type IN ('REVR') THEN Transaction_Amount
	ELSE 0 END) AS 'Refund_Value'
FROM #Transactions
GROUP BY
    CAST(FORMAT(Transaction_Date, 'yyyy-MM') AS VARCHAR(7)),
    Credit_Card_Type
GO

And the results are identical as before.

Should we use SUM or COUNT?

While my head tells me that using the COUNT operator to perform conditional counts is the purest (and most logical) solution,  I personally think it is less readable (despite being less code) for the layman and instead quite like the SUM approach since it is self-explanatory.

Furthermore, while the query plans of both approaches appear to be identical upon first glance, I noticed that the COUNT approach had (very) slightly higher query costs. I haven’t compared execution times or other performance metrics, but I suspect there will be little difference between them. In other words, use whatever works for you!

Summary

After using the COUNT operator for well over 20 years, this exercise has proved to me that there are still functional nuances lurking that will bite me from time to time. This is not a SQL Server problem, it’s just a ME problem (in not always understanding operations correctly)!

If you would like to try this out yourself I have provided the setup code below:


CREATE TABLE #Transactions
	(Transaction_Date DATE,
	 Credit_Card_Type VARCHAR(16),
	 Transaction_Type CHAR(4),
	 Transaction_Amount INT)
GO

INSERT INTO #Transactions VALUES
	('2010-11-14',
	 'BARCLAYSCARD',
	 'PAYM',
	 12000
	),
	('2010-11-14',
	 'GOLDENFISH',
	 'PAYM',
	 10000
	),
	('2010-11-14',
	 'BARCLAYSCARD',
	 'REVR',
	 12000
	),
	('2010-11-15',
	 'BARCLAYSCARD',
	 'PAYM',
	 9000
	),
	('2010-11-18',
	 'AMERICANEXPRESSO',
	 'PAYM',
	 9000
	),
	('2010-11-18',
	 'AMERICANEXPRESSO',
	 'PAYM',
	 5610
	)
GO

 

Configuring Red Hat Enterprise Linux 7.2 for YUM without a Redhat Subscription

It has been a very long time since I have installed a Redhat Enterprise Linux distribution having tended to prefer Ubuntu based distributions (such as Linux Mint) or CentOS if I really wanted a Redhat derivative (although for Oracle Database installations on Linux, I would always tend to use Oracle Enterprise Linux for simplicity). With the development and impending arrival of SQL Server on Linux, I thought it was about time that I returned back to the playground with a vanilla copy of Redhat EL so that I could test it with SQL Server, Docker Linux Containers, Spark and lots of other sexy things that have been avoiding my immediate attention for a little too long.

After a basic installation, I decided that it was time to start using YUM to add some of my favorite packages to this build when I hit across this quite annoying error:

[retracement@localhost]$ sudo yum install nano
Loaded plugins: product-id, search-disabled-repos, 
subscription-manager
This system is not registered to Red Hat Subscription Management.
You can use subscription-manager to register.
There are no enabled repos.
Run "yum repolist all" to see the repos you have.
You can enable repos with yum-config-manager --enable 

Ok so this is obviously not going to fly and I am certainly not going to pay for a Redhat Subscription so I decided to break out Bingoogle and came across this rather useful post from Aziz Saiful called HowTo Install redhat package with YUM command without RHN -and I recommend you also give it a read (although some of its details are ever-so-slightly out of date with this release of RHEL 7.2.). The post discusses how to set up an alternative source to the installation DVD, and for Windows people, this is an equivalent of the -source parameter that we would use in PowerShell with the Add-WindowsFeature cmdlet to add new features from local media.
To cut a long story short, I decided to work my way through this article and provide an updated post (and if nothing else, I will not need to Bingoogle this again!).

Our first step is to ensure that we have a mounted Redhat Enterprise Linux 7.2 DVD (i.e. the one we installed Linux from).
The next step is to mount the DVD to a mount point. For simplicities sake, I chose cdrom off the root.

[retracement@localhost]$ sudo mkdir /cdrom
[retracement@localhost]$ sudo mount /dev/cdrom /cdrom

Ok so now we have a mounted cdrom, we can create the YUM repo configuration file within the path /etc/yum.repos.d to point to this location. Unfortunately, you will need to use vi to do this (I hate vi!), but if you need any tips on vi, please use this Vi Cheat Sheet. Once in vi, create the file dvd.repo (or called anything else you want – but ensure you keep the .repo extension otherwise the file will not be recognized by YUM).

[dvd-source]
name=RHEL 7.2 dvd repo
baseurl=file:/cdrom/
enabled=1
gpgcheck=0

Once you have created this file, if you have performed every step correctly, you can take a look at YUM’s repolists.

[retracement@localhost]$ sudo yum repolist

And while you still receive the same error regarding the System not being registered to Red Hat Subscription Management, you should also see your new repo listed underneath.
To check it all works, let’s install nano!

[retracement@localhost]$ sudo yum install nano

yum

Perfect! Like everything in Linux, it is easy when you know how. On a closing note, it is unclear to me at this moment in time, whether this will entirely resolve my installation problems since I will still obviously need access to an online repo or sources in order to install third-party packages not included with the installation media,  but once I have figured that out, I will update this post.