Happened to catch a hotfix - 2489376 Cumulative Update package 6 for SQL Server 2008 R2: FIX: Slow performance when you recover a database if there are many VLFs inside the transaction log in SQL Server 2005, in SQL Server 2008 or in SQL Server 2008 R2 If you get message such as the following, you may have a problem: Analysis of database 'mydatabase' (7) is 0% complete (approximately 1234 seconds remain). This is an informational message only. No user action is required. The fix is intended for those who haven’t managed their transaction logs well. Of course, the best way to handle is to keep the VLF count at 1000 or below; grow your tlog out from the beginning and you won’t have a problem. Anyway, if you see this message in your logs, go get more info here and get the workaround. Lee   -----------------   http://support.microsoft.com/kb/2455009

I got an email the other day from Kevin Kline about a new project that he and Quest Software are involved in – a new idea named “Project Lucy”. Here’s the skinny: “…we (Quest) have developed a really cool cloud-based performance analysis tool currently called Project Lucy (PL). Check it out at http://www.projectlucy.com.  In a nutshell, you simply upload a good ol’ trace file and PL will analyse the file and tell you if there are any major problems, such as can be found in the plan cache, recompiles, excessive CPU consumption, sort warnings, etc.  On top of that, PL leverages crowdsourcing by telling you how your trace compares to similar workloads.  Your data is anonymous and your contact info is not used for any sort of marketing or outreach” I haven’t tried it out yet, so I’m not going to comment on its worth. Check it out and let me know, and maybe I’ll find a trace file today to upload. Thanks for visiting, Lee   ---------------------- analyse?     https://www.projectlucy.com/ https://www.projectlucy.com/50

Posted in: SQL Server Performance  Tags:
I’ve been tasked at work to create a large number of text files from SQL Server.  Get this bro, 100,000+ text files, and as fast as possible!  These files are for a program that consumes them for some sort of actuarial study…actually I haven’t gotten into the details but should understand the process better.  Why?  Well, maybe some of the files could be reused and not generated each run?  It’s much faster to create 50K files as opposed to 100K I’m thinking. Maybe there’s a way to import data directly rather than a physical file out on the NT file system?    But again, I’ll dig into the process and learn it later, and for now create the files for the application to use.  We’re attempting to rewrite a DTS package that has been in use for a long time, and because most of the DTS servers will go bye-bye soon at the company, we have to go a different route to update our process. It’s a very big challenge, and gives me the opportunity to see what tool that we have at our disposal to crank out, not a large file that many of us have done in the past, but many small files and as fast as possible.  These files, by the way, have about 45 records for each file, and one numeric “column” of data. I have several options here, and I’ve tried a few of them up to this point.  Here’s what I have brainstormed: bcp bcp from TSQL selecting “pre-calculated” rows from a table SSIS bcp from powershell .bat file with individual bcp commands on “pre-calculated” table sqlcmd c# custom app Combination of the above/others Over the next week or two I will add to this series of blogs so that I might share what I learned given my hardware, network, and software setup.  Hopefully you can learn something from my notes as well.  If you have done an exercise like this, sharing your experience would be great, so pm me on what you found. Thanks for reading, Lee Everest   ------------------------ Looks like I picked the wrong week to quit sniffing glue

Bob Dorr - Principal SQL Server Escalation Engineer – submitted a blog about the new Advanced Format Technology for disk drives a few days ago that’s worth reading.  I anticipate that there will be a lot of information in the coming weeks on how SQL Server works with the new standard for hard drive sectors.  Check out his blog, and do some searches on 512e and Advanced Format Sector sizes as he suggests…I found pretty interesting reading. Kinda makes me want to go out and buy one of the new drives and do some testing on this. Lee Everest   ------------------         http://blogs.msdn.com/b/psssql/archive/2011/01/13/sql-server-new-drives-use-4k-sector-size.aspx http://www.wdc.com/wdproducts/library/whitepapers/eng/2579-771430.pdf

Posted in: SQL Server Performance  Tags:
Lee posted on January 4, 2011 22:19
OK so last time my blog on tempdb shared some info that you may have never known, and that was the fact that if you create a permanent table in tempdb as well as one in a user database of the same DDL, and insert a bunch of rows in both, all things equal the tempdb script will run faster, much faster, than in a user database.   As mentioned, I remember Ken Henderson telling me that tempdb did some ‘shortcuts’  that other databases did not do…the only problem was, I he never told me what the shortcuts were, and I never asked. Before I begin though, let me pause for a moment and offer you an important message – I am experimenting here (as I do in many of my blog postings).  I could give a rats ass whether 1) this material is widely known, 2) whether or not there is a “de facto” expert out there lurking among us, and/or 3) whether I am off base, incorrect, or otherwise redundant in some form or fashion.  So, don’t write me if have a comment or question that falls into any of these three categories – consider your email unread and deleted.  You didn’t write the SQL Engine - neither did I, so your expertise is probably not all that.  If you consider this material “my own views” or “user generated nonsense”, then so be it. Your opinion and you are entitled to it.  No one is making you read the material, right?  I’m not being nasty – just letting you know how this particular blog operates.  You see, I get these a-holes and lackeys sending ignorant messages and hate mail and as of late I have actually spent time responding to the garbage…an enormous waste of time. These nimrods either don’t read the blog, don’t understand my point, or take my conclusions out of context.  Again, take the information that you get from my blog for what it is and you’ll be better off I guarantee. I”m not regurgitating Books Online here at this blog, and never will.  Are we good?  Good deal. Back to the blog.  We did an insert from before, so let’s do an update so we’re reasonably sure that we’re getting faster processing in tempdb for at least two types of DML.  I create a table with 10 million rows in AdventureWorks and then one in tempdb. Then I go update each one. Here’s the code: drop table AdventureWorks.dbo.Data go select cast('' as char (4)) as col into AdventureWorks.dbo.Data from (select top 10000000 '' as y from sys.syscolumns a, sys.syscolumns b, sys.syscolumns c ) x update AdventureWorks.dbo.Data SET col='AAAA' The update (not including the SELECT INTO), above, ran on my laptop in 2 minutes 55 seconds.  Then I ran the same script in a user database (AdventureWorks, but run it wherever you like) to compare the two.  Interestingly, in AdventureWorks, it took 7 minutes and 44 seconds, almost three times slower than tempdb.  Nice huh?  So what’s happening here?  Well, not exactly sure. I have some ideas though, so here’s one. There’s a tidbit about tempdb that I did not know about until someone from Microsoft mentioned it to me: The tempdb transaction log does not need to be flushed on commit I always thought that really no database log needed to be flushed “on commit”;  it was always my understanding that when a transaction completed, the write-ahead log contained ‘dirty’ pages that were eventually written to the disk, but SQL Server didn’t necessarily have to write them at that particular moment (the time that they were committed).  If SQL Server was busy, I thought that a thread (essentially the lazy writer) would wait until resources were available to flush.  This may be incorrect now as I am finding out. I know that the recovery interval has a lot to do with the “interval” that SQL Server actually flushes for recovery purposes. Something to consider, though, given that tempdb really doesn’t recover nor does it roll forward or roll back transactions after a restart – it simply starts up a new one and then clears it (see the SQL Server logs) each time SQL Server comes back.  I need to research this further as I piece together how this relates to what we are seeing in tempdb. If I crack open Perfmon and add database log flush counters for AdventureWorks and tempdb, run the next script in each database, I get the following: IF OBJECT_ID ('TestTranLogFlushes') IS NOT NULL DROP TABLE TestTranLogFlushes GO CREATE TABLE TestTranLogFlushes (id int identity, col char (1000)) GO WHILE ((SELECT IDENT_CURRENT('TestTranLogFlushes')) < 200000) BEGIN BEGIN TRY BEGIN TRAN INSERT INTO TestTranLogFlushes (col) VALUES ('') IF @@ERROR=0 COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN END CATCH END Tempdb – not much happening here   User db (AdventureWorks) - one helluva lot of flushes This one, by the way, ran in 12 seconds in tempdb, and 1:30 in AdventureWorks.  That’s over 10x faster in tempdb.  Crazy!  But, now your asking, who cares?  Who cares if tempdb can do something faster than a user database?  What could it possibly do to help?  Good question.  So, is it possible that we could actually use tempdb for some of our processing, maybe batch processing or even some online stuff, to actually leverage this fast processing?  What I mean – could we change the context of where we are performing some task over to tempdb to leverage what we are seeing?  I don’t know to be honest with you.  Stick around and let’s explore the possibilities. Thanks for reading, Lee Everest   -------------------- Lighten-up, Francis.

Posted in: SQL Server Performance  Tags:
Lee posted on December 22, 2010 17:07
Got yelled at today by Jonathan Kehayias, a slammin’ SQL dude and Microsoft MVP, about my blog on transaction logs and multiple files that I did back a few weeks ago. He brings up a good point in that my conclusion has something that I need to clarify and change in my posting: If you have more than one transaction log file it will be written to during the course of a transaction, and my verbiage said that they would not. Wrongo! Thanks for pointing this out to me…that one slipped by me. Go no further than any of the references I had linked in the blog, or better yet just go to MSDN, for great reading on the transaction log file. I quote Microsoft directly from Books Online… If the log contains multiple physical log files, the logical log will move through all the physical log files before it wraps back to the start of the first physical log file. This is easy to see in a simple example.  Create a database and add two log files.  Uncheck the Enable AutoGrowth first two files and set their maximum file size and restricted file growth to 1 or 2MB, and for the third leave this set to unlimited growth with Maximum File Size of unrestricted. Start a transaction and watch all of them fill – the third will keep growing as long as your script keeps doing DML while the others max out to the file size that you set, naturally.   We should be clear on this now I think. Boooyaaaaaaaaaaash! I would like to also reiterate and emphasize one thing while I’m on the line if I may (and I will take liberty since I am the sole proprietor of this blog), and that is, if you have two, five, or a hundred log files within a database they will not be written to simultaneously, the point of my blog post in the first place. Log files in SQL Server are written to, but not in parallel. So my point about no increased throughput gained by multiple log files is a valid one. The client where I was stationed added multiple data files in their user and tempdb databases, their DBAs doing so because of best practice. I assumed that since what they read for data files must hold true for log files, they’d go ahead and think that the more log files the merrier, possibly to obtain some sort of speed on log writes or whatever I have no idea.  Additional log files do nothing other than let the DBA on-call sleep a little better at night since he or she now has a failsafe should the log file become full, which I also mentioned in the original post. I think we’re gold now. I hope. Thanks much, Lee Everest ----------------------------- I actually checked three times that I spelled his name correctly.

Posted in: SQL Server Performance  Tags:
Lee posted on December 18, 2010 10:26
About 5 years ago or so, I was chatting with the late great Ken Henderson and we were talking about tempdb processing.  This was about the time when SQL Server 2005 had just come out and I was working on a highly-visible, large transaction processing system at a local retailer in Dallas.  This project by the way was super cutting edge implementation and used BizTalk to write thousands of transactions per second across our SQL Server.  I had noticed in testing that tempdb tended to be able to handle substantial throughput over user databases, so I pinged him Ken about this.  One reason that I also brought it up was because I noticed in all of this previous books (including his classic The Guru's Guide to Transact-SQL) his examples were mostly done in tempdb, and I was curious why he had used this space for his work.  This database does in fact process transactions at a significant rate compared to other databases;  however, geeking with it I’m seeing a few gotchas for moving some SQL Server processing there.  I’m going to jot down some notes on this subject in a little series of blogs. Let’s check it out.  Run the following: SET NOCOUNT ON DROP TABLE tempdb.dbo.test GO DROP TABLE AdventureWorks.dbo.Test CREATE TABLE tempdb.dbo.Test (id int identity(1,1) not null PRIMARY KEY, col char (1020) ) GO CREATE TABLE Adventureworks..Test (id int identity(1,1) not null PRIMARY KEY, col char (1020)) GO I’ve created two “permanent” SQL Server tables, one in tempdb and one in a user database.  If you don’t have AdventureWorks then simply put it somewhere else.  Create the data: DECLARE @ID int=10000 BEGIN TopLoop: INSERT AdventureWorks..Test (col) SELECT '' SET @ID-=1 IF @ID > 0 GOTO TopLoop END GO Now run the same script, but replace AdventureWorks with tempdb.  Here is the trace from profiler: The script which inserted slightly less than 10MB into AdventureWorks ran in 3.376 seconds, while the same script that was run in tempdb ran in 471 milliseconds, roughly 2.9 seconds faster which is equivalent to about 86% faster percentage-wise.  Smokin’!  This test was also not done on any special box, and no mods were made to tempdb or the tempdb files – I’m using a single file on a dual-core laptop. (I don’t have to worry about throughput or bottlenecks here since I’m not allocating many IAM pages in this simple example).  Compare different sizes – you’ll find that tempdb times will beat the user database times in all cases, small amounts of data or large. Cool eh?  There are some gotchas to doing this, however.  I’ll show you some of the things that I found…next time. Thanks for reading, Lee Everest   ------------------ ‘Tis the season!

Posted in: SQL Server Performance  Tags:
Lee posted on December 12, 2010 11:16
I recently left a gig where I had to review and make recommendations for two poorly running SQL Servers.  I haven't done a server assessment in a couple of years so had to refresh myself and get up to speed with all of the hardware, SAN, and SQL Server setup stuff again... it's certainly easy to forget everything unless you work on these every day, and since I've been doing a lot of SSIS, I haven't dorked much with performance as of late.  One thing that I found was that their OLTP database server - which contained a database about 3/4 of a terabyte in size - had a large number of virtual log files (VLFs), like 14,000.  If you are not up on these, simply go to MSDN and check out virtual log file architecture for background reading, and then continue on here. So this inordinate number of VLFs had me thinking; I perused the internet and found a few great things on Paul Randal's site, but wanted to do some more investigation myself, and actually put pencil-to-paper on this subject to witness what the effect of a large number of VLFs vs. a smaller number can have on performance, if any. (Actually after I did my testing, I searched some more on Google and found a few articles and blogs, so what I have here is not really new.  But, my tests were worthwhile I believe).  After running and re-running my code various servers over the past month, I have a reasonable certainty of the results below. For the sake of brevity, I am attaching the TSQL for you to run if you like.  The methodology behind the test is outlined as follows: Shrink log file Fully size a transaction log to maturity, 20 VLFs.  The log cannot grow during test Set up DML Clear buffers Run workload Record results For many VLF comparison, then: Shrink log file Create a large number of VLFs artificially Set up DML Clear buffers Run workload Record results Compare results Here's what I got from perfmon, put into Excel format. Virtual Log Files Test                       VLF count 20   773   % (as % of few VLFs) % Disk Write Time 141.282   158.675   -12.31% % Processor Time 42.29   23.504   44.42% Avg. Disk Bytes/Write 61026.94   77220.585   -26.54% Avg Disk sec/Write 0.002   0.003   -50.00% Avg Disk Write Queue Length 1.413   1.587   -12.31% Checkpoint pages/sec 0.00   991.151   - Current Disk Queue Length 1.478   1.537   -3.99% Log Bytes Flushed/sec 47824847   26314148   44.98% Log Flush Wait Time 1.391   13.707   -885.41% Log Flush Waits/sec 0.818   1.874   -129.10% Elapsed Time/sec 29   39   -34.48% Because everyone has their favorite perfmon counters, I won't get into a debate about which to add and which ones not to here, so I included several for consideration. Pick the ones that you want, and throw out the others that you don't care about.  If I didn't add the ones that you were looking for, then you can run the test yourself with the included code below and see what the behavior is using your favs. Based on what I found after running my test dozens of times, I consistently get something similar to the above whether I am running on my laptop, my server, or a number of VMs with different RAID configurations.  One thing that really jumps out at me is the number of checkpoint pages/sec.  The file with few VLFs had an average checkpoint pages of 0, while the large VLF file had (in this test) 991 checkpoint pages/sec, which translated to a higher log flush wait/sec as well.  SQL Server rarely "checkpointed" in this test for a small number of VLFs, but otherwise did quite a bit.  This is probably the biggest thing that caught my attention, along with the elapsed time in seconds, of course...I consistently got between 10 and 30% faster write times with the fewer VLFs, this one slightly higher than normal at 34%.  I also note that the processor is much busier with fewer VLFs and seems to be flushing more log bytes as well with a considerable smaller wait time to flush.  Log Flush Waits/sec, or the number of commits waiting on the log to flush, corroborates the overall flush wait time.  Essentially, with fewer virtual log files, the SQL Server is waiting less on the disk; the disk has to do much more work with more VLFs and incurs more waits. After testing this thing over and over, I believe that it is a good practice to run DBCC LOGINFO to capture the number of VLFs that you are dealing with, shrink the file if necessary, and then expand it to a mature size. I understand of various theories on the number of VLFs and sizes, so you'll have to do more testing on this.  What I've found is the fewer the better; in my tests here I maintained a two GB log file with 20 VLFs. Thanks and good luck with your testing, Lee Everest   ------------------------------- Code here...

Posted in: SQL Server Performance  Tags:
I was dorking with Contig for checking fragmentation of SQL Server log files, and it seems to work… and not work at the same time, if that is possible. Here’s the skinny on Contig v1.55, if you haven’t seen this little tool, downloadable from Microsoft: There are a number of NT disk defraggers on the market, including Winternals Defrag Manager. These tools are useful for performing a general defragmentation of disks, but while most files are defragmented on drives processed by these utilities, some files may not be. In addition, it is difficult to ensure that particular files that are frequently used are defragmented - they may remain fragmented for reasons that are specific to the defragmentation algorithms used by the defragging product that has been applied. Finally, even if all files have been defragmented, subsequent changes to critical files could cause them to become fragmented. Only by running an entire defrag operation can one hope that they might be defragmented again. Contig is a single-file defragmenter that attempts to make files contiguous on disk. Its perfect for quickly optimizing files that are continuously becoming fragmented, or that you want to ensure are in as few fragments as possible. Check this – first I am going to pick a database log file, shrink it to remove any fragmentation (VLFs), confirm the fragmentation with Contig and then fragment the hell out of it.  Then, look at it with Contig, defrag with Contig, and then check it again via DBCC Loginfo. DBCC SHRINKFILE (2,0) GO   FileId FileSize StartOffset FSeqNo Status Parity CreateLSN 2 253952 8192 8246 0 64 0 2 253952 262144 8247 0 64 0 2 253952 516096 8248 0 64 0 2 278528 770048 8259 2 64 0 We know the file looks pretty good with only 4 VLFs, so let’s run Contig. It’s gold as well: Let’s run the frag script to frag the hell out of the file (you like that one didn’t you?): SET NOCOUNT ON DECLARE @cntr int = 0 DECLARE @size varchar (20) = '3000' DECLARE @filesize varchar (20) = '3000' DECLARE @incr varchar (2) = 'KB' DECLARE @str varchar (max) WHILE @cntr < 200000 BEGIN SET @str = 'ALTER DATABASE [Test] MODIFY FILE (NAME = N''Test_Log'', SIZE = '+@size + @incr+')' EXEC (@str) SET @cntr+=1000 SET @size = cast(CAST(@filesize as int) + @cntr as varchar (20)) END GO Now run DBCC Loginfo again. It’s fragmented with 730 virtual log files (snippet here only): FileId FileSize StartOffset FSeqNo Status Parity CreateLSN 2 253952 8192 8246 0 64 0 2 253952 262144 8247 0 64 0 2 253952 516096 8248 0 64 0 2 278528 770048 8259 2 64 0 2 458752 1048576 8260 2 64 8.259E+18 2 458752 1507328 0 0 0 8.259E+18 2 458752 1966080 0 0 0 8.259E+18 2 655360 2424832 0 0 0 8.259E+18 2 253952 3080192 0 0 0 8.259E+18 2 253952 3334144 0 0 0 8.259E+18 2 253952 3588096 0 0 0 8.259E+18               ….             Use Contig to see what’s up with the file – something is, because it’s showing a lot of fragmentation: Just for the fun of it, I’m going to run the –v option to try and use the tool to defrag the log file.  From this screenshot, it looks like it did something: But when I run DBCC Loginfo again, the VLFs are still inside of the file.  I’ll need to run the shrink file script from the beginning of this blog to clear out the VLFs.  So what have we accomplished?  Good question. My guess is that this tool appears to actually have moved the file to another block on the disk; it’s not fragmented across the disk, however, we can see that it is still fragmented inside of the log file. In part 3, I’ll continue…have some more geeking with fragmentation inside of transaction log files.  I’ve got a real cool one up next.  Hell yeah! Thanks for reading   Lee Everest   ------------------------- Are we beating a dead horse with all of this transaction log stuff?    http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx

Lee posted on November 16, 2010 14:28
Sankar Reddy had a nice blog awhile back on forward records, and there are some other good ones out on the web about the subject as well.  The first time that I read about this topic was back in 2001 or 2002 in Ken England’s Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook, a really good book back then, and even today still contains some very relevant information (like the chapter where he talks about…forwarded records :)).  While there is much out on the web, as Sankar points out, there is no need to rehash or regurgitate the background info; however, just to make sure everyone is down with what we’re talking about, let’s review: a forwarded record occurs in a heap table (a table with no clustered index) as a result of a change made to the table schema or table data after its original creation.  Because the change can’t be handled by the way the table data is laid down from either the initial or some previous load, a pointer (RID) to a new page for “overflow” data is created, specifically for a varchar column.  This can cause more reads and IO due to this type of traversal whilst performing DML on the table.  Not good in other words.  Slower queries, more CPU required, and more disk I/O can be seen with a table containing forwarded records to one that does not.  The best solution to avoid this scenario would be to, of course, assign a clustered index on the table and be done with it.  If you can’t, you have to remove this type of fragmentation from the table.  There are various scripts out there to do this, so check Google! for one to remove those records. In Sankar’s post, he does an ALTER statement and adds a column to create his forward records. My post today is to remind that while adding a column is certainly a way to create these forward records, I believe that they most often occur by issuing an UPDATE to a variable-length column after the initial data load to the table. Let’s check it out quickly – run the following: USE master GO CREATE DATABASE Test GO USE Test DROP TABLE HeapWForward GO CREATE TABLE HeapWForward (id int ,string varchar (400) ) GO PRINT 'A heap table has been created. Wooo hooo!' GO INSERT INTO HeapWForward SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.id) AS Id, ' ' FROM sys.syscolumns A, sys.syscolumns B GO SELECT forwarded_record_count AS ForwardRecordCount_Before FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'HeapWForward') ,NULL,NULL,'Detailed') as a GO UPDATE HeapWForward SET string = REPLICATE('a', 400) GO SELECT forwarded_record_count AS ForwardRecordCount_After FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'HeapWForward') ,NULL,NULL,'Detailed') as a GO Notice that lots of forward records appear after the update. Sure, an ALTER TABLE with ADD column will produce them, but in online and batch SQL processing, you’re more likely to get them from something such as the above.  For instance, if you use temporary tables and load data and subsequently UPDATE an existing column, you will get forward records. By the way, Microsoft says that read ahead reads will actually decrease with fragmentation, since the read ahead manager can’t read in larger chunks. The same goes for this type of fragmentation as well.  Read-aheads are desirable because this is the mechanism to cache pages and anticipate what the query will need in order to finish in an optimal time frame.  A perfmon counter to use to test this is the Avg Disk Bytes Read: We see this in perfmon very clearly when running a SELECT full scan.  Here’s a scan issued to the table with the data pre-populated - lots of  bytes, and lots of read-aheads: Here’s the same query with the forward records. Notice the great decrease in the Avg. Disk Bytes/Read counter.   Checking STATISTICS IO, in the below image, the first result is from a scan of all records, the second a full scan with forwarded records, and the third with the UPDATE HeapWForward already issued (pre-populated). Notice the read-aheads are much greater with the table already updated; the table with the forward records has many more logical reads, physical reads, with fewer read-aheads due to the fragmentation present.  As important, experiment with sys.dm_db_index_operational_stats; you’ll find that forward records, because SQL Server must traverse more pages, will also cause a greater accumulation of row locks and page locks, and this can be seen as well very clearly. Pretty cool, eh?     Thanks for reading, Lee Everest   ---------------------------- I know. We really didn’t need another forward record post, did we?     Sankar’s blog post - http://sankarreddy.com/2010/03/how-can-i-tell-if-a-sql-server-system-is-affected-by-forwarded-records/ Microsoft SQL Server 2000 Index Defragmentation Best Practices - http://msdn.microsoft.com/en-us/library/cc966523.aspx Microsoft: http://msdn.microsoft.com/en-us/library/ms191475.aspx

Introduction I’m at a client getting ready to give my .02 cents on why their SQL Servers are not running to well, and I happened to notice multiple transaction log files for their databases.  We know that multiple data files for some databases, including tempdb, has advantages in performance and administration. This is well documented. However, I was wondering why their setup included multiple log files, so I started thinking about this; I had not remembered reading about extra throughput or anything when adding multiple transaction log files to a database, so I wasn’t sure what their goal was in having this setup. Actually seemed like a waste of resources to me. So, I decided to find out more about the transaction log (can I call it the “tlog” from here on out? Thanks) as it relates to single vs. multiple files, and exactly what happens, or doesn’t happen, when these additional files present. Ah, yeah. It's just we're putting new coversheets on all the TPS reports before they go out now I thought that I would find some good info on MSDN or around the internet, but was surprised a bit to find little information on this particular subject.  So I called up a friend at Microsoft and his answer was: SQL only writes to one ldf file for a database at a time.  Multiple log files are only useful for working around running out of space on your log volume. [1] What he’s saying here is that extra log files are normally used as a fail safe for the first tlog – SQL Server does not write to all tlog files at the same time, nor does it do round-robin type writes as it does with multiple data files.  Then I did happen to come across some work from Paul Randal on sizing the transaction log, and he also mentions the same thing for the most part: Add more transaction log files. As a general strategy for managing the size of the log, this is not good. Extra log files have zero effect on performance (the myth that SQL Server writes in parallel to the log files is just that - a myth) and make management more tricky. However, if your log fills up for some out-of-the-ordinary reason, then adding another log file may be the only way to allow the database to keep running. If you have to do this though, make sure that you remove the extra log file(s) when you can to keep log management uncomplicated.[2] I’m not certain what management complication he’s referring to, other than you would have multiple log files to back up maybe? Not sure. Anyway, the first part of the paragraph pretty much matches what my buddy offered up. And I agree here – over-engineering anything is worthless, so why do it? And that’s exactly what I’m trying to root-out here. By the way, Paul Randal has got some really great stuff on his blog, very impressive.  I read his bio and see that he was with Microsoft for 1000 years, so I figure that he knows what he is talking about.  I’ve also read some of his articles as a subscriber to SQL Server Magazine for the past decade, so I know he’s good with SQL Server internals as well. Check out his blog postings on the transaction log [2] for some really cool threads. Want to go to Chotchkie's? Get some coffee? Being the curious one that I am, all of this info is good and wonderful, but I wanted to investigate just a bit deeper to see if SQL Server really just ignores these files, whether SQL Server writes to them or not, or whatever else I might be able to dig up. I created a database called Test, and it looks kind of like so: Notice that I’ve shrunk the logs to 1MB, but you can create them this size to start off.  I also have them on different vhds, so I know that I/O won’t be running together in case we find something here in our little investigation. Run the following to verify that the log files are all 1MB: SELECT (size * 1.0 * 8.0)/1024.0 AS size_in_mb,* FROM Test.sys.database_files WHERE data_space_id = 0 I now execute the following script and insert some data into a table called LogTable. It has two columns, one of decimal data type, and the other char data type: SET NOCOUNT ON DECLARE @cnt decimal(10,4)=0 DECLARE @rows int=0 BEGIN TRAN WHILE 1=1 BEGIN INSERT INTO LogTable VALUES (ROUND((RAND()* 1000000),0), SPACE(1024)) SELECT @rows+=1 SELECT @cnt = (size * 1.0 * 8.0)/1024.0 FROM Test.sys.database_files WHERE data_space_id = 0 AND [FILE_ID]=5 IF @cnt>1.0 BREAK END SELECT @rows; GO I randomly choose [FILE_ID]=5, but I already know that I could have used 3 or 4 as well.  When the script complete s, I inserted a total of 23461 rows, but yours will most likely vary. Notice now the size of my files when I run the first script: Interestingly, all of the log files have been written to – obviously the first tlog file has most of the writes, but the others are the same size by the time the script hit the BREAK.  Apparently SQL Server did write to more than one log file, but it’s strange that it wrote only 1MB to 32MB for the first transaction log that was created. Subsequent runs indicate a similar behavior, but I ran a few times and found that it hit the BREAK with considerably fewer rows than 23K from the first run. And then there's Tom Smykowski... He's useless. What is SQL Server doing here, and what’s in these additional tlog files? I can only answer the second question, and may have to ping the storage engine team to explain it I suppose. In order to find out what’s going, I went to ApexSQL and downloaded the ApexSQL Log reader tool;  go download the 14-day trial and check it out if you are following along with me. When I consulted at Rent-A-Center a few years ago by the way, they had the Apex suite of tools and I really liked them. I installed and fired up the tool and here’s what I get when I have it inspect the Test_Log2, 3, and 4.ldf files: I get nothin!  So obviously SQL Server is writing some internals stuff here and not info from our INSERT statement(s).  Cool!  Running the tool with only that first log file, I get this, so we know that the tool is working correctly: And there’s IO as well for those log files for disk drives e, f, and g…as we would expect:   Conclusion We’ve seen that 1) SQL Server does not round-robin multiple transaction log files, 2) additional files do get written to, but not with transactional information per-se, 3) whatever is getting written to these tlog files happens at the same time, and is only a fraction of what is written to the first tlog file created for the database, and 4) there is I/O present on the disk when the additional tlog files are written to, but not much. I’m going to keep looking for some answers here, so let’s call this part 1 and I’ll investigate in some more and see what I can find…definitely interesting to me, and I hope you enjoyed the read. Right now I’m fairly convinced at this point that additional transaction log files are of no use in a normal Microsoft SQL Server database setup. Thanks much, Lee Everest ------------------------- I can't believe what a bunch of nerds we are. We're looking up "money laundering" in a dictionary References [1] Unnamed resource from Microsoft haha [2] http://www.sqlskills.com/BLOGS/PAUL/post/Importance-of-proper-transaction-log-size-management.aspx [3] http://www.sqlskills.com/BLOGS/PAUL/category/Transaction-Log.aspx [4] http://www.apexsql.com/default.aspx

You can never get too many of these things right?  Today I was perusing the internet and found over on Kevin Kline's blog a link to a free perfmon poster from the great folks at Quest.com.  Don’t you notice these days that all of the super-big SQL Server guys have more than one blog now?  How the hell does anyone have time to keep up with twitter, multiple blogs, facebook, etc. and get anything done???  Unknown at this point in time. Anyway, check out these counters – they’re pretty good.  I am an acquaintance of one of the guys on there, Brent Ozar, a super-stud SQL Dude, MVP, MCM, et. al., so you know some decent people authored the list.  I think that you can either get the .pdf, or order a poster to stick on your ceiling in your bedroom as an option. Keep in mind,  on these counters – use them in combination, and don’t take a single counter and make a decision based solely on it’s value. In other words, use them in conjunction with others, and try to create your own correlations. Also, use good judgment by comparing the suggested values here with other values as well as those that you may have developed from experience.  If you can find trends and patterns in the movement of one and its affect on others, you are making great progress in identifying the behavior of a SQL Server. Thanks for reading, Lee Everest   -------------------------------- Here’s a jim-dandy that I ran into today …  SELECT COUNT(*) FROM [tablename] WITH(nolock,readuncommitted)      http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf

Posted in: SQL Server Performance  Tags:
Start reading here, please If you happen to have caught any of my blogs before, I like to test (and challenge) SQL Server’s “sacred cows”;  I have always written code in a number of ways to see what particular design works for a given implementation.  I’ve concluded a long time ago that there are no absolutes in SQL Server development, and just when you think that one way is the right way to do something, you can certainly find another that challenges that method.  This may be due to several factors: different types of data, small or large amounts of data, low to high transaction environment, hardware, server or disk setup, etc.  The list goes on forever.  And, every time that a noob comes to me and asks, “Hey what’s the best way to write this?”, I am always quick to say, “I don’t know, I’d have to go write it. Why don’t you try it several ways and find out for yourself?”.  So, I’m geeking with another of these sacred cows, just for fun, to see what I can come up with – this one is whether explicitly dropping temporary tables is a hindrance to performance. Once Upon a Time About five years ago, I worked on a super-high transaction processing system that was fed by BizTalk. If you’ve ever worked with BizTalk, you know exactly what it can do to SQL Server as I do – kick off a bunch of orchestrations and and you can flat-out flood SQL Server. I talkin’ bring the database server to its knees, bra! And we did it all of the time until we learned the art of fine-tuning locks and blocks with BizTalk, and using query hints in just the right places.  It was grueling but a lot of fun.  At the time, Microsoft in Dallas stated that it was the largest BizTalk setup that they knew about, anywhere.  We fought with our system for hours, days, even weeks, until we had it down. Great learning experience for sure. PITA, but we had some fun times. During that time, a rep from Microsoft came by and looked at my code.  For some reason or another I had gotten into the habit of explicitly dropping temporary (temp) tables, which he quickly pointed out thank you.  Don’t ask me why, I have no idea at the time, I was just doing it.  This was very early on in SQL Server 2005 development.  Anyway, he goes on to tell me that this will cause things to run slower, and may create unnecessary CPU by doing so…essentially, his suggestion was to just go ahead let SQL Server get rid of the temp objects gracefully and without the “Drop Table #xxx”.  He never mentioned anything about GAM or SGAM, allocations, or reuse of temp objects.  And because we were suffering from locks, blocks, and deadlocks, I was willing to try anything to help speed things up.  We also had quite a few nice scripts to look into performance, so we could readily see if things helped or hurt our code. A guru chimes in on the subject This month’s SQL Server Magazine (July 2010), super-stealth Ninja SQL guru Andrew J. Kelly, a SQL Server MVP and practice manger at Solid Quality Mentors, had a paragraph about this very thing – explicitly dropping temp tables. He mentioned that in one situation a query or process decreased in execution time by 50ms from removing drops and/or truncates, however, he didn’t detail the situation much or offer up any test results or cases to speak of, only that he “confirmed that explicitly dropping a temporary table will defeat the ability of using caching mechanisms”.  I don’t doubt him a bit, he’s one helluva SQL Server technician, but wouldn’t it be fun to check this out for ourselves nonetheless? No guru here, but I did stay at a Holiday Inn Express last night I tested this notion of firing off scripts that create hundreds of transactions/second today to see if I could find out for myself whether or not there was any fact-based evidence that explicitly dropping temp tables caused slowdowns in a SQL Server.  As I type I am debating on if I should post my code or not because I don’t really think folks will try it out.  Maybe I’ll just skip that part, but if you want it send me an email. Essentially what I have put together for my test cases is the following: A batch file that fire off several sqlcmd commands using DOS start, so that I get multiple spids at a time running concurrently and hitting the same tables A stored procedure using crossing multiple databases that does a simple fetch of a customer, fetch a product, check to see if product amount >0, create an order, and insert the order into an Order table. All are done randomly by doing ORDER BYs using NEWID() The proc creates a temp table and then inserts some metadata at the end The proc either drops/does not drop the temporary table depending on the test In perfmon, I observe roughly 650 transactions/second in tempdb, and maybe 200 transactions/second on average across the three databases.  I actually added several counters in perfmon, including as work tables created and work table cache, none of which were terribly interesting If you want the code, ping me and I’ll zip it up and email it to you as mentioned, or try your own design out for comparative purposes I”m watching the Bachelorette while writing this, BTW. My meager eight tests are below. Note that I run them with the temp table drop, and without, and the last two are with a large column so that I get close to a page allocated for each insert. (A friend suggested this, why I have no idea. But I thought I’d see if it made any difference because it sounded reasonable). Interesting findings I guess: Notice that with the explicit temp table drop, I get an average execution time of 248 seconds, and without the drop and average time of 285 seconds. Again, this test is executing 600+ transactions/second across three spids running a loop of 10,000 rows per iteration. In all, I end up inserting between 150K and 190K records inserted during a single run, which lasts about three minutes.  In my tests – granted, probably not worth much – show that dropping a temp table is actually faster given my test, hardware, and stored procedure. The end, or just the beginning? What’s fun about what we do as SQL Devs and DBAs is that we are constantly facing change; change in SQL Server and how it performs, changes as new features are added, changes as the database engine is tweaked.  This is what is so interesting about working with this great tool!  One minute, just when you think you have something figured out, a whole new list of ideas opens up from something that someone experiences.  What I have presented here is just dorking with SQL Server…I’ve really not done more than a couple of hours worth of work, and certainly don’t have a well-thought presentation by any stretch. Yet I’m sort of surprised at what I found. And, what’s even more exciting is that you or someone else could take this idea and come up with, given a different set of constraints, the very opposite results that I have. Thanks for reading, Lee Everest   ------------------------ “Land's sakes, Wolfie; ain't you gonna eat me???”   EDIT:  Have some requests for the code.  Run this, and let me know what your results are. Download here Kelly,A (2010). Is Tempdb Affecting your Day-to-Day SQL Server Performance? SQL Server Magazine, pp. 27-28  Flickr Tags: dropping temporary tables,explicitly dropping temp tables,temp table allocation,temp table performance

admin posted on April 12, 2010 22:14
Interesting finding, I reckon. I see some posts on the internet about the cost of updating a column to itself; you might find a statement (or maybe you have used one) that looks like so: UPDATE TABLE SET id = ISNULL(@var,id) Essentially, if the variable is NULL, you update the column to itself. (Real tricky, I know right?).  Someone asked me the other day what exactly happens here, and I told them that I wasn't quite sure to be honest.  Although I've heard of folks doing experiments on and claiming this or that, my best bet is to always test it myself regardless; glad I tested this myself as well. Let's check this out and see what's up. To clarify, the person's exact question was whether or not the transaction log grows during an update such as this.  This is really a good place to start for this action, because if the column updates itself, the log should grow in theory - a DML statement in Transact-SQL (save SELECT) will write to the transaction log by definition.  I first run this to create my table, and then run sp_helpfile: DROP TABLE TLOG_TEST GO CREATE TABLE TLOG_TEST (Id INT IDENTITY (1,1) NOT NULL PRIMARY KEY ,CharValue char (8000) default ' ' ) GO   Figure 1.  sp_helpfile before we've done anything Now, let's run our statement to make the TLog go!  Run the following slick statement to insert a few records: DECLARE @count int=0 START: INSERT INTO TLOG_TEST DEFAULT VALUES SET @count+=@@ROWCOUNT IF (@count <= 50000) GOTO START   Now we run sp_helpfile again.  Figure 2.  sp_helpfile after running the above statement.   Here we see that the datafile grows for sure, nothing yet for the tlog.  Now, run the following statement. UPDATE TLOG_TEST SET CharValue = CharValue For the sake of not pasting the screenshot again (you'll have to trust me on this one) the log did not grow. Somewhat unexpected, but sort of makes sense, really. While a SQL Transaction shows up in Profiler, SQL Server really isn't doing much here.   Just for grins and giggles...try this one. DECLARE @var char(1) = 'a' UPDATE TLOG_TEST SET CharValue = ISNULL(@var,'a')   Figure 3. sp_helpfile after updating the row to something else   Based on my test here, I don't see the log grow when you update a column to itself. To verify, Profiler revealed Reads, CPU, and duration, but no Writes;  I conclude here that the operation takes a toll on the server but that's about it. Test this for yourself under different conditions and see what you can come up with.  A follow-up might be to see what locks SQL Server actually takes out on the table. Thanks for reading, Lee   ----------------------- Sarah Connor: Kyle, the women in your time, what are they like? Kyle Reese: Good fighters.     Flickr Tags: sql update a column value to itself

Admin posted on December 4, 2009 22:15
There is a reason why UDFs kill performance   Saw a great blog post [1] on the MSDN blogs regarding query tuning with UDFs; specifically the problem that was addressed is one that many of us have had to deal with, which is having to overcome the effects of a UDF in the WHERE clause of a TSQL statement.  In this situation, the SQL statement is probably guaranteed to skip any and all indexes, and interrogate many if not all rows from the table or join of tables. The author added: >>The function is invoked for each row in the set, and each invocation has a fixed and not insignificant cost, which is in addition to the cost of the statements in the function body. >>The optimizer cannot accurately estimate the cost of the function (which could vary greatly), therefore it may come up with less than optimal query plans. This is oftentimes evident when the function is on the "left side" of the operator, such as … dbo.fn() = value.  Looking at a query plan from a query similar to his, you can see that the optimizer has placed the filter of the UDF before the join was reduced. This essentially goes against the logical query processing phase that tells us that FROM, ON, OUTER, come before WHERE. Figure 1 shows us that the filter is between the two tables that lead to our join.     Figure 1.  The optimizer moves UDF before row reduction Now notice in Figure 2 how, in this case an aggregate suggested by a reader’s comment, and not a CROSS JOIN, reduces the result set first and then applies the UDF after. In this case my example tables evaluated 225,000 rows rather than over 450,000 rows for which the filter must run against.  The time for the query run was roughly 33% faster using no indexes and 1MM rows in dbo.Table1 and 500,000 rows in dbo.Table2.     Figure 2.  Using an aggregate to push the filter back While you can’t see the rows affected, you can see that Filter has for sure been moved back in the sequence of events, which is then exposed to considerably fewer rows, and results in a faster running query. Lee   ---------------------------- What we're dealing with here is a complete lack of respect for the law       [1] Query Performance, scalar UDFs, and predicate pushdown. Retrieved 12/4/2009 from http://blogs.msdn.com/dfurman/archive/2009/12/02/query-performance-scalar-udfs-and-predicate-pushdown.aspx.

Lee posted on November 23, 2009 11:37
SQL Rx performance evaluations.   John Paul Cook wrote a blog about the greatness SQL Risk and Health Assessment Program (SQLRAP) the other day, which prompted me to write a similar piece about ISI’s SQL Rx practice for performance tuning and server evaluations as an alternative to consider.  I’ve seen a “RAP” report before, and for my money a server review by SQL Rx is by far a better and more thorough report. Having worked for ISI for a year, I understand the nuts and bolts of what goes into these evaluations, and comparing the two…there’s really no comparison. While both have a degree of technical insight, the SQL Rx summary is much greater in depth, breadth, and scope, and is an overall more complete picture, going well into the server, disk, cpu, and even the nics.   Contact Lori Brown at SQL Rx for a sample of their report. Either way you would do no wrong, but the folks there put much more effort into these products.   Lee     -----------------------   They paid me not one red cent for the advertisement of their product, trust me.

Lee posted on October 13, 2009 15:22
You're joking, right?  For a bit of amusement, check out the following KB that came out last week.  I receive emails from the KB alertz folks, and this one cracked me up. SQL Server 2008 976115   The SQL Server 2008 Books Online shortcuts and the SQL Server 2008 Books Online folder disappear from Start menu after you install the May 2009 version of SQL Server 2008 Books Online I could just see someone becoming upset because their BOL shortcut disappeared!!! Lee   ---------------------------------- Probably the most important KB eva!   KBAlertz.  Retrieved on 10/13/2009 from http://kbalertz.com/ http://support.microsoft.com/kb/KB976115

Posted in: SQL Server Performance  Tags:

by Lee Everest, M.S.

Poll

Do you use Azure or cloud in your organization?
Show Results

Ads

Search


Month List

Calendar

«  February 2012  »
MoTuWeThFrSaSu
303112345
6789101112
13141516171819
20212223242526
2728291234
567891011
View posts in large calendar

Tags

Disclaimer
The opinions, code, examples, et.al. expressed herein are my own personal opinions and do not represent my employer's view in any way, shape form, or fashion.  All code for demonstration purposes - no guarantees, either written or implied, are made.

© Copyright 2012 Lee Everest's SQL Server, etc. weblog