I found out this week that a couple of my colleagues were unfamiliar with Allocation Units, so I thought I would knock-up a quick post explaining what they were, and how you can use them to optimize performance. So first off - what are they?
Well, traditionally, SQL Server has had a fixed limit of 8060-Bytes for a row and 8000-Bytes for a column, as a single row can not span more than one page. So, in more recent versions of SQL Server, it has been possible to push some of this data off-row, in order to let developers overcome this fixed limitation.
So now, every index, including the clustered index, or heap can have it's data split across multiple allocation units. There are 3 types of allocation unit. The first is for your standard data, and this is called the IN_ROW_DATA Allocation Unit. This is the allocation unit that will hold the majority of your data, in most cases. The second Allocation Unit is know as the LOB_DATA allocation unit. This is used to store the older type LOBs (Large Object Blocks) such as Text, NText and Image. The final Allocation Unit, which is the most recent addition, is the ROW_OVERFLOW_DATA Allocation Unit. Also know as the SLOB (Small-LOB) Allocation Unit, this is used to store newer Large Objects, such as Varchar, NVarchar, SQLVariant (although this should be avoided!) and the .NET based data-types, such as Geospatial data-types.
So, are there three allocation units per table? Oh no! There is a lot more than that! Every index can be partitioned, with a maximum of 1000 partitions, and in SQL Server 2008, you can have up to 1000, indexes, and the Allocation Units propagate down to the partition level, so it is actually possible to have 3 Million Allocation Units. In SQL Server 2005, the limit is 750,000 Allocation Units, because you are limited to 250 indexes per-table.
By default, LOBs (Text, NText and Image) are stored out of row. You can move them in row, by changing the value of 'Text In Row' by using sp_tableoption. Obviously, this will only bring in LOBs that will physically fit on the page. (i.e. They still can not be more than 8000-Bytes each, and 8060-Bytes in total), and you can also specify the maximum size for values to bring in-row. If you do not set this limit, the default is 256-Bytes.
By Default, SLOBs are stored in-row (providing they will fit in the row, using the same calculations as above) but you can push them off-row by setting the 'Large Value Types Out Of Row' option using sp_tableoption. Unlike the 'Text In Row' option, it is not possible to specify a custom size limit. Either all rows that will fit are stored in-row, or all values are stored out of row. The size limit is 8000-Bytes, and this can not be changed.
So the next question, is when should you push data off-row? Well, before we tackle that question, we need to think about how much overhead is caused by pushing data off-row, and this calculation is fairly simple. If LOBs are stored off row, a 16-Byte pointer is stored in the row. If SLOBs are pushed off-row, then a 24-Byte pointer is stored in-row. This can get slightly confusing however, because if you specify the (MAX) operator for a Varchar or NVarchar, then SQL treats it as a LOB, rather than a SLOB, and only requires a 16-Byte pointer.
So the first, and most obvious considerations are (from an SLOB perspective) are 1) Are the majority of SLOBs less than 24-Bytes? If so, there would be no benefit in pushing them off-row. In fact, it would be detrimental. 2) Are the SLOBs more than 8000-Bytes? If so, you have no choice, but to store them off row. 3) Do your SLOBs add-up to more than 8060-Bytes? If so, again, you have no choice, they must be off-row.
But what about the cases in the middle? What about if your SLOBs are all between 24 and 8000-Bytes, and together, they all fit within the 8060-Byte limit? How should you store them? Well, it depends on how you are using your data. If these SLOBs are going to be frequently queried, then you should store them in-row. If you do not, then you are increasing IO, and also forcing that IO to be random, as opposed to sequential, and random IO is not your friend!
If on the other hand, the SLOBs are essentially just a data store, and you are only querying them infrequently, but the other columns in the table are being queried, then you should store the SLOBs off-row. This is because you will be able to fit more rows on a page, and this give you two big advantages. Firstly, queries that need to read this table will have to read less pages (because there will be less pages to read). And the second reason is simply that you will use less memory. If you read less pages, then less pages will be read into the Buffer Cache, and memory pressure will be reduced.
Showing posts with label IO. Show all posts
Showing posts with label IO. Show all posts
Saturday, 2 April 2011
Sunday, 6 February 2011
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
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
Sunday, 30 January 2011
How Clever Is your Index?
If you Seek an index, would you expect SQL to seek the index once for each query, or once for each operator, or once for each value? You would hope that it was once for each query, but is that true? Well, it depends. I've created a rather contrived example, to demonstrate SQL's behavior.
Firstly, I created and populated a table, and then create an index on it using the following script...
CREATE TABLE LogicalReadsTbl (Col1 INT)
DECLARE @i INT
SET @i = 1
WHILE @i < 100000
BEGIN
INSERT INTO LogicalReadsTbl
VALUES(@i)
SET @i = @i + 1
END
CREATE NONCLUSTERED INDEX IX1 ON LogicalReadsTbl(col1)
...I have created a nonclustered index, but for this particular example, the behavior would be identical with a clustered index (although I will let you prove that for yourself)
Next, I need to make sure that my queries are going to span rows that are stored in multiple pages, so I ran a simple query, and included %%physloc%% (see this post) to see how my rows were organised.
Based on this, I choose to use values between 619 and 626, as the page break was on 623/623.
Next, I ran sys.dm_db_index_physical_stats against my table, to double check the number of levels in the index. As suspected (because of size), there were 2 levels.
So the next thing to do, was turn on IO statistics. (Please bare in mind that because I ran a query including %%physloc%%, I am already expecting all pages of this table to be in the buffer cache, and hence I should see no physical reads, only logical reads) Just for fun, I also turned on Include Actual Execution Plan, then I ran the following query, and checked out the results...
SELECT COL1
FROM LogicalReadsTbl
WHERE Col1 BETWEEN 619 AND 626
Table 'LogicalReadsTbl'. Scan count 1, logical reads 2
...Perfect! SQL performed an index seek, and then, as the pages were next to each other, it read them as one block.
Ok, so now lets look at another query. Now this query will only return 2 rows, not 8, and read exactly the same pages, so we should have an identical query plan, and identical IO stats...right???
SELECT
COL1
FROM LogicalReadsTbl
WHERE Col1 = 619 OR Col1 = 626
Table 'LogicalReadsTbl'. Scan count 2, logical reads 4
...Ok, that's not so good, despite the same execution plan, and the same environmental conditions, and despite the fact that this time, we are reading 1/4 of the number of rows, the IO has doubled. SQL has performed 1 seek, for each page it required.
So be careful, sometime SQL is not as clever as we like to think it might be!!!
Do you find this kind of thing interesting? Please let me know, as it's good to know how to use my blogging time!!!
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Firstly, I created and populated a table, and then create an index on it using the following script...
CREATE TABLE LogicalReadsTbl (Col1 INT)
DECLARE @i INT
SET @i = 1
WHILE @i < 100000
BEGIN
INSERT INTO LogicalReadsTbl
VALUES(@i)
SET @i = @i + 1
END
CREATE NONCLUSTERED INDEX IX1 ON LogicalReadsTbl(col1)
...I have created a nonclustered index, but for this particular example, the behavior would be identical with a clustered index (although I will let you prove that for yourself)
Next, I need to make sure that my queries are going to span rows that are stored in multiple pages, so I ran a simple query, and included %%physloc%% (see this post) to see how my rows were organised.
Based on this, I choose to use values between 619 and 626, as the page break was on 623/623.
Next, I ran sys.dm_db_index_physical_stats against my table, to double check the number of levels in the index. As suspected (because of size), there were 2 levels.
So the next thing to do, was turn on IO statistics. (Please bare in mind that because I ran a query including %%physloc%%, I am already expecting all pages of this table to be in the buffer cache, and hence I should see no physical reads, only logical reads) Just for fun, I also turned on Include Actual Execution Plan, then I ran the following query, and checked out the results...
SELECT COL1
FROM LogicalReadsTbl
WHERE Col1 BETWEEN 619 AND 626
Table 'LogicalReadsTbl'. Scan count 1, logical reads 2
...Perfect! SQL performed an index seek, and then, as the pages were next to each other, it read them as one block.Ok, so now lets look at another query. Now this query will only return 2 rows, not 8, and read exactly the same pages, so we should have an identical query plan, and identical IO stats...right???
SELECT
COL1
FROM LogicalReadsTbl
WHERE Col1 = 619 OR Col1 = 626
Table 'LogicalReadsTbl'. Scan count 2, logical reads 4
...Ok, that's not so good, despite the same execution plan, and the same environmental conditions, and despite the fact that this time, we are reading 1/4 of the number of rows, the IO has doubled. SQL has performed 1 seek, for each page it required.
So be careful, sometime SQL is not as clever as we like to think it might be!!!
Do you find this kind of thing interesting? Please let me know, as it's good to know how to use my blogging time!!!
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Subscribe to:
Posts (Atom)