Good developers typically have a good handle on what Donald Knuth’s famous quote about “premature optimization [being] the root of all evil” is all about – it’s a constant reminder that developers shouldn’t needlessly agonize over every line of code and worry about its potential performance cost. Instead, they should code as necessary (using as many best practices as possible), and then come BACK and manage any performance tuning needed – once their application is put under load.

Still, one area where seasoned developers typically tend to be a bit worried or leery about potential performance problems is when it comes to explicit transactions – because any seasoned developer knows that transactions come with a certain amount of processing overhead and an increase in resource utilization.

Explicit Transactions Can Actually Make SOME Operations Faster

As such, it can come as a big surprise to many developers that there are actually SOME kinds of operations that you can actually SPEED up in SQL Server when you wrap them within explicit transactions. And the key to gaining a sense for how all of this works is to remember that the SQL Server Transaction Log has to keep writing out changes, serially, as each and every data modification comes through. Consequently, if a large number of UPDATE, for example, or INSERTs, is fired off – one after another, that means that the Transaction Log is going to be hit by a bunch of back-to-back, individual and distinct, operations that can each be rolled back, independently, if needed.

Of late, I’ve spent a decent amount of time working with a couple of different clients to help diagnose potential problems with multi-tenant servers – where large numbers of transactions can have an ugly ‘aggregate’ effect when there are lots of writes being sent to multiple databases at the same time. Accordingly, one of the things I’ve been paying attention to a lot of late is the SQL Server Log Manager and how it works – including how it buffers data and so on. And, along those lines, I was reminded of the fact that in SOME cases, explicitly blocking lots of (typically repeated) operations into a single, explicit, transaction can actually do a lot of good in terms of reducing disk-activity generated by the transaction log.

Proof is in the Pudding - Or: Only Attempt this with Appropriate Testing

Granted, this isn’t something you’d necessarily believe without seeing some hard numbers to back up such a claim NOR is it something you’d want to blindly consider for all operations without checking the actual metrics as well. So, with that in mind, let’s look at some examples.

To do that, you’ll want to carve out a little sandbox for yourself – somewhere in a NON-production database. For me, that’s my ‘Meddling’ database, which, as you can see, I start of with by creating a new table, and then running some backups to make sure I can clear out any transactions in the transaction log:

USE Meddling

GO

IF OBJECT_ID('dbo.TransactionTest','U') IS NOT NULL

DROP TABLE dbo.TransactionTest

GO

CREATE TABLE dbo.TransactionTest (

RowId int IDENTITY(1,1) NOT NULL,

OptionName nvarchar(40) NOT NULL,

OptionValue int NOT NULL

)

GO

BACKUP DATABASE Meddling TO DISK = N'D:\SQLBackups\Meddling_Full.bak'

WITH COMPRESSION

GO

BACKUP LOG Meddling TO DISK = N'D:\SQLBackups\Meddling_FULL.trn'

WITH COMPRESSION

GO

Then, once I’ve created a table and cleared everything out, I can check on the amount of space being used in my transaction log. The documented, or supported, way to do this is to use DBCC SQLPERF() command with the LOGSPACE argument, but I personally hate how that shows me log-space info for all tables. (Er, well, I hate that aspect when working on a single database – especially when I have a lot.) So, I prefer to use an admittedly ugly way of getting the same info – by using performance counters:

As the benefit of such an approach is that it’s pretty easy to restrict results down to a single database.

And, in my case, after a t-log backup, I’m using roughly 4MB of my 100MB log. And, with such a base-line set, I can then kick off a pretty simple operation where I’ll log 20,000 back to back inserts by creating some bogus data from the master’s spt_value table (something that’s really only good for demos):

SET NOCOUNT ON

GO

DECLARE cursorsAreUgly CURSOR FAST_FORWARD

FOR

SELECT TOP 20000 t1.name, t1.number

FROM master.dbo.spt_values t1

LEFT JOIN master.dbo.spt_values t2 ON t1.number = t2.number

LEFT JOIN master.dbo.spt_values t3 ON t2.name = t3.name

LEFT JOIN master.dbo.spt_values t4 ON t3.name = t1.name

ORDER BY NEWID()

DECLARE @name nvarchar(40), @number int

OPEN cursorsAreUgly

FETCH NEXT FROM cursorsAreUgly INTO @name, @number

WHILE @@FETCH_STATUS = 0 BEGIN

INSERT INTO TransactionTest

VALUES (ISNULL(@name,''), @number)

FETCH NEXT FROM cursorsAreUgly INTO @name, @number

END

CLOSE cursorsAreUgly

DEALLOCATE cursorsAreUgly

GO

And, while 20,000 INSERTs may seem like a lot (and is), these are exceptionally narrow rows – so bear that in mind when comparing this technique against your own data.

Likewise, in my example, I’m using a cursor – but your code might be processing either INSERTs or UPDATEs much differently.

The key thing, though, is that after running that statement above (which takes 13 seconds on my dev/testing server that runs older/cruddy SATA drives), I end up with 13MB of my log file used.

If, however, I re-backup the transaction log (to clear it out – and get back down to around 4MB used), and then run the following operation – which wraps everything in an EXPLICIT transaction (something that might make you think this operation would take LONGER than the implicit version listed above) :

SET NOCOUNT ON

GO

BEGIN TRAN

DECLARE cursorsAreUgly CURSOR FAST_FORWARD

FOR

SELECT TOP 20000 t1.name, t1.number

FROM master.dbo.spt_values t1

LEFT JOIN master.dbo.spt_values t2 ON t1.number = t2.number

LEFT JOIN master.dbo.spt_values t3 ON t2.name = t3.name

LEFT JOIN master.dbo.spt_values t4 ON t3.name = t1.name

ORDER BY NEWID()

DECLARE @name nvarchar(40), @number int

OPEN cursorsAreUgly

FETCH NEXT FROM cursorsAreUgly INTO @name, @number

WHILE @@FETCH_STATUS = 0 BEGIN

INSERT INTO TransactionTest

VALUES (ISNULL(@name,''), @number)

FETCH NEXT FROM cursorsAreUgly INTO @name, @number

END

CLOSE cursorsAreUgly

DEALLOCATE cursorsAreUgly

COMMIT

GO

I end up with an operation that only took 8 seconds, and which only managed to generate an additional 3MB of transaction log usage (taking me up to 7MB used total instead of 13MB – quite a nice savings). And, of course, if I watch disk performance counters, I can see that they’re the primary source of contention during the previous operation – or they end up being my bottleneck because I’m thumping them over and over and over again.

In fact, if you’d like to get a better insight into what’s going on, run the tests above in your own environment and pay attention to your disk metrics AND take a peek at what’s being logged to the T-LOG itself by means of the most-excellent, and undocumented, T-SQL functionality made available by fn_dblog() as follows:

SELECT * FROM fn_dblog(NULL, NULL)

And, just make sure that you backup your t-log between each of your tests – to clear out used data and to make it easier to see the differences in what’s going on by looking at fn_dblog() output.

Conclusion

Obviously, this blog post isn’t an open invitation to run out and wrap every single operation in an explicit transaction. Instead, it’s a reminder that when you know what’s going on under the covers, performance benefits and boosts can come in what might, otherwise, seem to be surprising ways. That, and this post is a reminder that testing and hard-numbers are a great way to ensure performance optimizations when they’re actually needed.

Skriv en kommentar

Ditt namn:
*
E-post:
*
Ämne:
Kommentar:
Due to problems with SPAM we have closed the possibility to comment. The function
of commenting will be soon back!

SWCYeiXFlkdgXX

you uncheck the menegamant tools-basic but check the other options (Database Engine being the critical one), then you will have SQL Server installed and running. You just won't have the SQL Server Management Studio GUI tool for managing the database. So you'll have to create and access the databases in other ways (for example, programmatically with C# or some other language or another database tool).