Optimizing You Log File

Optimizing your transaction log could easily fill a 50 page white paper, or a week's consultancy, but here are a few points to get you started:

How many physical log files?
This one is easy...One! Exactly one, always one, the answer is one! There is an urban myth that having multiple log files will improve performance, but this is just a myth. Log files are written sequentially, so if you have multiple files, it fills one, then fills the next one, etc. Pointless!

How many virtual log files?
This one is a bit more complicated. Your physical log file is managed internally with a series of virtual log files. See this post for more details. If you have too few, then the active portion of your log may be too large, leading to longer backup times. If you have too many VLFs however, this leads to "log fragmentation" and cause performance issues.

The general rule of thumb is to have VLFs sized at around 500MB. To achieve this, grow your log in 8000MB chunks. If you already have log fragmentation, the way to resolve it, is to shrink your log file (thus deleting the existing VLFs) and then grow it again, using the desired chunk size.

I/O Subsystem
This breaks down into two parts. Firstly, make sure that you put the log file on a separate physical disc to your data files. Otherwise you will get I/O contention.

The second part is a little more complex, and that is what RAID level should you use? Well, this depends on what features of SQL you are using. Before we begin, it may be beneficial to bring some context, by looking at the standard RAID levels, and how many IOs they generate.

RAID 1 - IOs = R + (W*2) / 2
RAID 5 - IOs = R + (W*4) / No Of Discs
RAID 10 - IOs = R + (W*2) / No Of Discs

If you are just using SQL Server at a basic level, then RAID 1 is a good choice. This is because almost all activity on the log will be write activity. The only read activity will be backups and restores. In this scenario, RAID 1 is better than RAID 5 because for every one write, there will be two physical writes to the volume with RAID 1, as opposed to 4 writes with RAID 5. It is also better than RAID 10 in this scenario, because RAID 1 will perform the operations as sequential IO, where as RAID 10 will perform them as random IO.

However, this recommendation changes if you are using some of the more advanced feature of SQL, especially the HA features. If you are using Database Mirroring, Replication or Log Shipping, then you are liable to have a much higher read to write ratio, and hence need better read performance, whilst maintaining write performance. In this scenario, RAID 5 is still a no-go, because you will double the number of physical writes for every write operation, from 2 to 4, as discussed above, so your best choice would be RAID 10. RAID 10 will maintain the 2 writes per write operation ratio, but also give you improved (and more scalable) read performance. This is because the the overall IOs will be divided by the number of discs in both RAID 1 & RAID 10, but in RAID 1, you always have exactly 2 discs, where as with RAID 10, you will have a minimum of 4, and as many as you require.

Log Manager Bottlenecks
One reason that your log can become a bottleneck, is because in SQL Server 2008, the Log Manager will only allow you to have 3840K of data that has been sent to be written, but where no acknowledgement of the write has been received back. Before SQL 2008, this limit was even lower.

Monitoring Log Performance
Within SQL, keep an on the sys.dm_os_wait_stats dmv. If you see lots of WRITELOG waits, then the log is probably coming under pressure.

In Perfmon, you should be monitoring the obvious disc counters for you log drive, such as Avg Disk Queue Length, Disk Writes/Sec, etc, but you should also keep track of the Log specific SQL counters. In particular, pay attention to Log Flushes/Sec and Log Flush Wait Time.

Together, these counters should give you an all-round idea of how your log is performing. From a performance aspect, the Log tends to be the forgotten relative, but be careful, as forgotten relatives have a nasty habit of turning up unexpectedly, and making a nuisance of themselves!

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

Livelocks - Do they exist?

I have been involved in an interesting discussion over the last couple of days around livelocks, and whether or not they exist in SQL Server. The question arose because somebody had been asked the difference between a deadlock and a livelock in a job interview and had not been able to answer the question, and in fact, if you Google livelocks in SQL Server, 90% of the results are sites offering standard SQL Server interview questions.

So first off, what is a livelock? Basically, it is where a resource has an exclusive lock is blocked by a shared lock, but then another shared lock is granted before the exclusive lock, and then another, and another, and so on, meaning that the exclusive lock is never granted.

But do they exist? Well, my initial reaction was no, because SQL's lock manager queues lock requests and grants them in order, and there are not any scenarios where locks can "jump the queue" but I never like to state something as a fact unless I can prove it, and it is very difficult to prove a negative.

However, I though that I would do my best to replicate this behavior, and see what happened. So the first thing I did was create a table and populate it with the following script...

CREATE TABLE livelock (col1 int)
INSERT INTO livelock
VALUES(1)
GO 10000

...The next step was to try and create a live lock, so I opened three query windows and in the first, I ran the following query...

BEGIN TRANSACTION
SELECT col1
FROM livelock WITH (TABLOCK, HOLDLOCK)

...I used TABLOCK to reduce the number of locks, to make it easier to monitor and HOLDLOCK to create a queue.

Then in the second windows I ran...

BEGIN TRANSACTION
DELETE FROM livelock
COMMIT TRANSACTION

...And then in the third I ran the original query...

BEGIN TRANSACTION
SELECT col1
FROM livelock WITH (TABLOCK, HOLDLOCK)

...Now, in a separate query window, I ran a query against the DMV sys.dm_tran_locks, which looks at lock manager, and lets you see the lock waits in progress...

resource_type   resource_associated_entity_id    request_mode     request_type request_status
OBJECT           1927677915                                S                          LOCK GRANT
OBJECT           1927677915                                IX                        LOCK WAIT
OBJECT           1927677915                                S                          LOCK WAIT

...Following this, MVP Tibor Karaszi confirmed that LiveLocks had been removed back in version 6 of SQL Server, so in this context, they no longer exist.

This was where I was planning to end the post, but whilst writing this article, I stumbled across this post by Bob Ward at Microsoft, which talks about the relaxing of the strict queuing within SQL Server from 2005 onwards. However, it also mentions that the logic has been designed to avoid starvation.

So in conclusion, SQL has logic to avoid Livelocks. It may be possible to force one to happen, but fundamentally they are very unlikely to occur.

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

Nested Transaction? No such thing!

My post about transaction isolation levels has prompted me to have a good old rant about one part of the T-SQL syntax that drives me mad is the ability to create nested transactions, despite the fact that no such thing exists in SQL! Don't believe me? Check this out...

CREATE TABLE NestedTransactions
(Col1 int)

BEGIN TRAN --Outer Transaction
     INSERT INTO NestedTransactions
     VALUES(1)
     BEGIN TRAN --Inner Transaction
          INSERT INTO NestedTransactions
          VALUES(2)
     COMMIT TRAN --Inner Transaction
ROLLBACK --Outer Transaction

SELECT * FROM NestedTransactions

...So in the script above, we have committed the inner transaction, so even though we rollback the outer transaction, the NestedTransaction table should still include the value 2 from the inner transaction, right? Wrong...

...Ok, so what about if we try this script...

BEGIN TRAN --Outer Transaction
     INSERT INTO NestedTransactions
     VALUES(1)
     BEGIN TRAN --Inner Transaction
          INSERT INTO NestedTransactions
          VALUES(2)
     ROLLBACK --Inner Transaction
COMMIT --Outer Transaction

SELECT * FROM NestedTransactions

...This time, we have rolled back the inner transaction, but committed the outer transaction, so the NestedTransactions table should have one value, of 1, right? Wrong...

...And check out the message I received...

(1 row(s) affected)
(1 row(s) affected)
Msg 3902, Level 16, State 1, Line 9
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
(0 row(s) affected)
...As you can see, when it rolled back the transaction, it rolled back everything, so when I tried to commit the outer transaction, there was nothing to commit.

The bottom line, is that nested transactions do not exist!

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

The World's Worst Maintenance Plan

Below is the world's worst Maintenance Plan. Please try to ignore the fact that it is in Denali, and hence looks sexy!

This is a pretty standard maintenance plan. You might even have one like it in your environment, so what's so bad about it? Well, there are 3 issues, and they get progressively worse.

1) The first, is the fact that the Index Rebuild Task in SQL Maintenance Plans in "dumb". Or in other words, it blindly does through and rebuilds every index, regardless of whether it needs to be rebuilt or not. This is Ok if you have a nice big maintenance window, but if you in something approaching a 24/7 environment, as lots of us are these days, then you are likely to want to reduce your maintenance window as much as possible. Also, if you have Database Mirroring set-up (especially in SQL 2005) you might be flooding the network with your log stream. Even if you you don't have either of these considerations, I am still a great believer in not using more resources than you need to.

2) The second issue relates to the Update Stats Task. Now, not many people realise this, but when you rebuild your indexes, SQL Server takes advantage of having 100% stats at it's disposal, and updates the index stats based on a 100% sample. So at best, your Update Stats Task will be set to use 100% sample, and you are merely doing the work twice. In the worst case, you will be using a 10% or 20% sample to update your stats, and not only will you be doing the work twice, but you are going to end up with worse stats than you had originally.

3) Shrink is evil - Do not use! I mean it. The smallest problem with shrinking your database is that you should be managing your space pro-actively, and shrinking the files will just mean they have to grow again. The much worse side effect is that shrinking your files will cause hugh amounts of fragmentation, thus undoing all of the work your Index Rebuild has done.

In my next post, I will do a demo of the fragmentation caused by shrinking a database.

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

Logical Order NOT Physical Order

There is a common misconception, that your data will physically be stored in the order determined by your Clustered Index, but this is not true. A CI in SQL will logically order your data, but not physically order it. Take the table below as an example...

CREATE TABLE LogicalOrderDemo
(
Entry_ID  INT,
Large_Column NCHAR(500)
);

CREATE CLUSTERED INDEX CI__LogicalOrderDemo ON LogicalOrderDemo(Entry_ID);

INSERT INTO LogicalOrderDemo
VALUES (2,'This is a large column'),
(3,'This is a large column'),
(4,'This is a large column'),
(5,'This is a large column');

...If we now use sys.fn_physlocformatter (see this post for more details) to find the page number, and then look inside the page using DBCC PAGE, to view the row offset table...

SELECT sys.fn_PhysLocFormatter(%%physloc%%), *
FROM LogicalOrderDemo;

DBCC TRACEON(3604)

DBCC PAGE(Adventureworks2008,1,29012,1);

...If you scroll to the bottom of the output, you will see a row offset table, similar to the one below. The row offset table tells SQL the physical offset of each slot, from the beginning of the page. A slot is a logical container for a row. The offsets are in reverse order, so Slot 0 holds the first row (With an Entry_ID of 2)...

...So now, lets go ahead and insert another row. This time, we will insert a row with an Entry_ID value of 1. As we have a Clustered Index on this column, then we know that SQL will insert it into Slot 0, in order to enforce a logical order to the rows, but will it physically re-order the data on the page? Lets see...

...No! You can see from the offset table that it has inserted the row at the end of the page. You can interpret this because Slot 0 will contain our new data, but its offset value is the largest in the table, meaning that it is furthest away from the beginning of the page.

In reality, it has inserted it in the first free row space on the page, but as we have not deleted any data, in our case, that is at the end.

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

Count(*) Confusion

There still seems to be an amount of confusion regarding the use of COUNT(1) and COUNT(*). Since SQL Server 2000, query optimizer has produced identical query plans for both of these queries, but many people still seem to think that COUNT(*) is more expensive.

To settle the debate once and for all, I issued the following query against the Adventureworks database...

 

SELECT COUNT(*) FROM Person.Contact

...and it unsurprisingly produced the following query plan...

                      ...<DefinedValue>
                        <ColumnReference Column="Expr1004" />
                        <ScalarOperator ScalarString="Count(*)">
                          <Aggregate AggType="countstar" Distinct="0" />
                        </ScalarOperator>
                      </DefinedValue>...

                   

...I then issue this query against Adventureworks

SELECT COUNT(1) FROM Person.Contact

And this is the plan it produced...

                  ... <DefinedValue>
                        <ColumnReference Column="Expr1004" />
                        <ScalarOperator ScalarString="Count(*)">
                          <Aggregate AggType="countstar" Distinct="0" />
                        </ScalarOperator>
                      </DefinedValue>...

...Now, the sceptics amongst you, are probably thinking, "Oh yes, very good, but surly optimizer just re-used the original plan, because it was good enough..." and that would be a logical assumption, so to test this, I ran the following...

 

DBCC FREEPROCCACHE

...This command flushes the query cache, without the need to re-start the Instance. So at this point, SQL Server was not holding any cached plans, and hence it would HAVE TO create a new plan for the next query, and would obviously choose the plan that it found optimal. So now I ran the query again...

SELECT COUNT(1) FROM Person.Contact

 

And once again, the following plan was used...

                      ...<DefinedValue>
                        <ColumnReference Column="Expr1004" />
                        <ScalarOperator ScalarString="Count(*)">
                          <Aggregate AggType="countstar" Distinct="0" />
                        </ScalarOperator>
                      </DefinedValue>...

...The reason for this happening, is because if you use a constant, as a value in the expression of a COUNT(Exp), optimizer is intelligent enough to know that you are actually doing a COUNT(*), and uses the same plan...So despite popular belief, there will never be any difference in cost between a COUNT(1) and a COUNT(*).