Senaste inläggen 

Taggar 

BOL     Reports     concatenation     platsannons SQL utvecklare     profile     HADR     login error     virtuell     0xC0202009     bugs     SSAS     sql 2008     2005     parameters     CU3     Microsoft     Cluster     SQL Denali     sp1     undocumented procedures     transactions     connect     #am_get_querystats     SQL2008     CMS     page splits     Techdays     error     sql 2005     improve     XP_cmdshell     2011     SQL Server 2012     2000     parallelism     connection     DECIMAL     constraint     dbmail     function     access denied     SQL Server     SSRS 2008     feedback     DTA     central management server     clean up     filter     CU1     sql browser     2008     HEAP     temp table     gratis verktyg     history     CTE     data warehouse     Page life expectancy     CTP1     SQL server codename Denali     features     AcquireConnection     Säkerhet     security     T-SQL     Extended Event     Activity Monitor     resource governor     Logins     package load     Business Intelligence     Datawarehouse     Trace Flag     sp_MSForEachDB     SSIS     performance     create index     rebuild     0xC0010014     reorganize index     SSRS

Guest Blog: Performance Tip - Transactions Can Actually Boost Throughput

Skrivet den 14 september 2012 i Michael K Campbell, T-SQL, Level 400, SQL Server performance, transactions, T-SQL, performance, sv, en

SQL Service are happy to welcome Michael K Campbell as a guest blogger! Michael K Campbell (mike@sqlserveraudits.com and www.sqlserveraudits.com) is a contributing editor for SQL Server Magazine and a consultant with years of SQL Server DBA and developer experience. He enjoys consulting, development, and creating free videos for www.sqlservervideos.com

Over to you Michael!

************************************

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:

 

SELECT 
        dbs.name,
        ls.cntr_value / 1024 AS [Log Size (MB)],
        lu.cntr_value / 1024 AS [Log Used (MB)]
FROM 
        sys.databases dbs
        INNER JOIN sys.dm_os_performance_counters lu ON dbs.name = lu.instance_name
        INNER JOIN sys.dm_os_performance_counters ls ON dbs.name = ls.instance_name
WHERE
        lu.counter_name LIKE N'Log File(s) Used Size (KB)%' 
        AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
        AND dbs.database_id = DB_ID()
 

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

  • 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).

    By Mildred