Showing posts with label Internals. Show all posts
Showing posts with label Internals. Show all posts
Tuesday, 7 June 2016
How to Calculate Binary Bit Masks
Ok, so I know this post is a bit geeky, but there is nothing wrong with a little geeky fun from time to time.
SQL Server uses bit maps for various thinks, such as CPU affinity and Server Agent Operator's pager availability, but how do you work with bit maps? Well, it is a case of calculating the integer expresentation of the bit map value. This is made more complex because the INT data type is a 32-bit signed integer, meaning that some of the representations will be negative numbers.
Using CPU affinity as an example, the table below shows the processor number, bit mask and signed integer representation for processors 0 - 31.
Processor Number Bit Mask Signed Integer Representation
0 0000 0000 0000 0000 0000 0000 0000 0001 1
1 0000 0000 0000 0000 0000 0000 0000 0010 2
2 0000 0000 0000 0000 0000 0000 0000 0100 4
3 0000 0000 0000 0000 0000 0000 0000 1000 8
4 0000 0000 0000 0000 0000 0000 0001 0000 16
5 0000 0000 0000 0000 0000 0000 0010 0000 32
6 0000 0000 0000 0000 0000 0000 0100 0000 64
7 0000 0000 0000 0000 0000 0000 1000 0000 128
8 0000 0000 0000 0000 0000 0001 0000 0000 256
9 0000 0000 0000 0000 0000 0010 0000 0000 512
10 0000 0000 0000 0000 0000 0100 0000 0000 1024
11 0000 0000 0000 0000 0000 1000 0000 0000 2028
12 0000 0000 0000 0000 0001 0000 0000 0000 4096
13 0000 0000 0000 0000 0010 0000 0000 0000 8192
14 0000 0000 0000 0000 0100 0000 0000 0000 16384
15 0000 0000 0000 0000 1000 0000 0000 0000 32768
16 0000 0000 0000 0001 0000 0000 0000 0000 65536
17 0000 0000 0000 0010 0000 0000 0000 0000 131072
18 0000 0000 0000 0100 0000 0000 0000 0000 262144
19 0000 0000 0000 1000 0000 0000 0000 0000 524288
20 0000 0000 0001 0000 0000 0000 0000 0000 1048576
21 0000 0000 0010 0000 0000 0000 0000 0000 2097152
22 0000 0000 0100 0000 0000 0000 0000 0000 4194304
23 0000 0000 1000 0000 0000 0000 0000 0000 8388608
24 0000 0001 0000 0000 0000 0000 0000 0000 16777216
25 0000 0010 0000 0000 0000 0000 0000 0000 33554432
26 0000 0100 0000 0000 0000 0000 0000 0000 67108864
27 0000 1000 0000 0000 0000 0000 0000 0000 134217728
28 0001 0000 0000 0000 0000 0000 0000 0000 268435456
29 0010 0000 0000 0000 0000 0000 0000 0000 536870912
30 0100 0000 0000 0000 0000 0000 0000 0000 1073741824
31 1000 0000 0000 0000 0000 0000 0000 0000 -2147483648
On a 32-core server, there are 2.631308369336935e+35 possible combinations for processor affinity, but a few examples are included below.
Aligned Processors Bit Mask Signed Integer Representation
0 and 1 0000 0000 0000 0000 0000 0000 0000 0011 3
0, 1, 2, and 3 0000 0000 0000 0000 0000 0000 0000 1111 15
8 and 9 0000 0000 0000 0000 0000 0011 0000 0000 768
8, 9, 10, and 11 0000 0000 0000 0000 0000 1111 0000 0000 3840
30 and 31 1100 0000 0000 0000 0000 0000 0000 0000 -1073741824
28, 29, 30, and 31 1111 0000 0000 0000 0000 0000 0000 0000 -268435456
Because an integer has a a maximum range of 2^32 then the Affinity Mask can only accommodate values for up to 32 CPUs. If your server has between 33 and 64 processors, then SQL Server relies on a 2nd value, called the 64 Bit Affinity Mask, to store the details of the next 32 CPUs.
There are a number of calculators available online, for calculating the signed integer representation of a bit mask. I tend to use www.binaryconvert.com/convert_signed_int.html
Wednesday, 13 April 2011
SQL Server Southampton User Group 13/04/2011
I was speaking at the Southampton SQL UG tonight, and as promised, here are links to the code samples that I used, plus some more in depth articles on the subjects I spoke about. Thanks to everybody for a good night...!
Shrinking Databases
Why not to shrink a database here
The world's worst maintenance plan? here
Using %%physloc%%
How to find what page your data is stored on here
Logical Order NOT Physical Order here
Allocation Units
What Are Allocation Units here
Querying Allocation Units here
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Shrinking Databases
Why not to shrink a database here
The world's worst maintenance plan? here
Using %%physloc%%
How to find what page your data is stored on here
Logical Order NOT Physical Order here
Allocation Units
What Are Allocation Units here
Querying Allocation Units here
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Wednesday, 6 April 2011
The Curious Case Of The Phantom Last Log Backup!
I was extremely "busy" at work the other day. So much so, I was sat reading Paul Randal's Blog, when I came across an interesting article on how to tell if your database is really in Full Recovery, see here.
The general premise is that when you switch from Simple to Full recovery model, the model does not change until the log backup chain has been established, with a backup. This is called pseudo-simple mode, and can be recognised by a database that is in Full recovery model showing a NULL value for last_log_backup_lsn in sys.database_recovery_staus
I was a little board, so I though that I would have a look and make sure that our OLTP databases were really in Full Recovery Model, and they were, but much to my surprise I found that some of our VLDs that were in Simple Recovery Model were showing a non-NULL value in the last_log_backup_lsn column in sys.database_recovery_status catalogue view.
This confused me and I ask Paul Randal if there was a pseudo-full mode, but there is not, so why was there a last_log_backup_lsn? Well, intrigued, I kept an eye on the transaction logs of the databases involved, buy using SELECT * FROM sys.fn_dblog(NULL,NULL) which shows all log records in the active VLFs.
Day 1 - 7436 entries, day 2 - 762 entries.
This was good news! It meant that the log had been truncated, so the databases were behaving as if the were in Simple recovery model. This was a relief, as my initial fear was that they were behaving as though they were in Full, and of course, we were not taking Transaction Log backups, as we thought they were in Simple!
So the next test - Was this record of a last LSN only in the sys.database_recovery_status catalogue view or was it embedded more internally? To check this, I ran DBCC PAGE on the boot page of the database. The database ID happened to be 8, so the syntax was
DBCC TRACEON(3604)
DBCC PAGE(8,1,9,3)
Here, I turned on trace flag 3604, so that the DBCC output would be to the messages window, rather than the event log. The parameters for DBCC Page represent the Database ID, The file number, the page number and then the level of detail. The database boot page is always at page 9 of file 1 see here for more info on system pages.
The results showed that the boot page also contained the lsn and time stamp of the last log backup, so the mystery continued. Where did it come from? Unfortunately, it was a production system that I was looking at, so I was not able to play around with it too much, but after a few hours of trying different things, that I new had occurred on the production system, I managed to reproduce the symptom, with the following script:
CREATE DATABASE [lsn_test] ON PRIMARY
USE [master]
GO
ALTER DATABASE [lsn_test] SET RECOVERY FULL WITH NO_WAIT
GO
The results were as expected...
name recovery_model_desc last_log_backup_lsn
lsn_test FULL NULL
So now...
BACKUP DATABASE [lsn_test]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER=MSSQL\Backup\lsn_test.bak'
WITH NOFORMAT, NOINIT, NAME = N'lsn_test-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Again, results as expected...
name recovery_model_desc last_log_backup_lsn
lsn_test FULL 25000000006200064
Next, was to switch the Database to Simple...
USE [master]
GO
ALTER DATABASE [lsn_test] SET RECOVERY SIMPLE WITH NO_WAIT
GO
Once more, predictable results...
name recovery_model_desc last_log_backup_lsn
lsn_test SIMPLE NULL
So now lets backup the database again...
BACKUP DATABASE [lsn_test] TO DISK = N'C:\Program Files\Microsoft SQL=erver\MSSQL10.MSSQLSERVER\MSSQL\Backup\lsn_test.bak'
WITH NOFORMAT, NOINIT,
NAME = N'lsn_test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, ST=TS = 10
GO
Once more, the correct results...
name recovery_model_desc last_log_backup_lsn
lsn_test SIMPLE NULL
So finally, what happens if we restore the database...?
RESTORE DATABASE [lsn_test] FROM DISK = N'C:\Program Files\Microsoft =QL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\lsn_test.bak'
WITH FILE = 2, NOUNLOAD, STATS = 10
GO
Ah, this doesn't look so straight forward...!
name recovery_model_desc last_log_backup_lsn
lsn_test SIMPLE 25000000011900001
So basically, SQL has picked up the last log backup LSN from the restore, but it is meaningless, and essentially a bug. So in the end, it turned out not to be a major issue, but it was certainly interesting, tracking it down, and a big thanks to Paul Randal for helping me get my head around it!
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
The general premise is that when you switch from Simple to Full recovery model, the model does not change until the log backup chain has been established, with a backup. This is called pseudo-simple mode, and can be recognised by a database that is in Full recovery model showing a NULL value for last_log_backup_lsn in sys.database_recovery_staus
I was a little board, so I though that I would have a look and make sure that our OLTP databases were really in Full Recovery Model, and they were, but much to my surprise I found that some of our VLDs that were in Simple Recovery Model were showing a non-NULL value in the last_log_backup_lsn column in sys.database_recovery_status catalogue view.
This confused me and I ask Paul Randal if there was a pseudo-full mode, but there is not, so why was there a last_log_backup_lsn? Well, intrigued, I kept an eye on the transaction logs of the databases involved, buy using SELECT * FROM sys.fn_dblog(NULL,NULL) which shows all log records in the active VLFs.
Day 1 - 7436 entries, day 2 - 762 entries.
This was good news! It meant that the log had been truncated, so the databases were behaving as if the were in Simple recovery model. This was a relief, as my initial fear was that they were behaving as though they were in Full, and of course, we were not taking Transaction Log backups, as we thought they were in Simple!
So the next test - Was this record of a last LSN only in the sys.database_recovery_status catalogue view or was it embedded more internally? To check this, I ran DBCC PAGE on the boot page of the database. The database ID happened to be 8, so the syntax was
DBCC TRACEON(3604)
DBCC PAGE(8,1,9,3)
Here, I turned on trace flag 3604, so that the DBCC output would be to the messages window, rather than the event log. The parameters for DBCC Page represent the Database ID, The file number, the page number and then the level of detail. The database boot page is always at page 9 of file 1 see here for more info on system pages.
The results showed that the boot page also contained the lsn and time stamp of the last log backup, so the mystery continued. Where did it come from? Unfortunately, it was a production system that I was looking at, so I was not able to play around with it too much, but after a few hours of trying different things, that I new had occurred on the production system, I managed to reproduce the symptom, with the following script:
CREATE DATABASE [lsn_test] ON PRIMARY
USE [master]
GO
ALTER DATABASE [lsn_test] SET RECOVERY FULL WITH NO_WAIT
GO
The results were as expected...
name recovery_model_desc last_log_backup_lsn
lsn_test FULL NULL
So now...
BACKUP DATABASE [lsn_test]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER=MSSQL\Backup\lsn_test.bak'
WITH NOFORMAT, NOINIT, NAME = N'lsn_test-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Again, results as expected...
name recovery_model_desc last_log_backup_lsn
lsn_test FULL 25000000006200064
Next, was to switch the Database to Simple...
USE [master]
GO
ALTER DATABASE [lsn_test] SET RECOVERY SIMPLE WITH NO_WAIT
GO
Once more, predictable results...
name recovery_model_desc last_log_backup_lsn
lsn_test SIMPLE NULL
So now lets backup the database again...
BACKUP DATABASE [lsn_test] TO DISK = N'C:\Program Files\Microsoft SQL=erver\MSSQL10.MSSQLSERVER\MSSQL\Backup\lsn_test.bak'
WITH NOFORMAT, NOINIT,
NAME = N'lsn_test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, ST=TS = 10
GO
Once more, the correct results...
name recovery_model_desc last_log_backup_lsn
lsn_test SIMPLE NULL
So finally, what happens if we restore the database...?
RESTORE DATABASE [lsn_test] FROM DISK = N'C:\Program Files\Microsoft =QL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\lsn_test.bak'
WITH FILE = 2, NOUNLOAD, STATS = 10
GO
Ah, this doesn't look so straight forward...!
name recovery_model_desc last_log_backup_lsn
lsn_test SIMPLE 25000000011900001
So basically, SQL has picked up the last log backup LSN from the restore, but it is meaningless, and essentially a bug. So in the end, it turned out not to be a major issue, but it was certainly interesting, tracking it down, and a big thanks to Paul Randal for helping me get my head around it!
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Saturday, 2 April 2011
How To Query Allocation Units
Following my post explaining Allocation Units see here I though that I would do a quick follow-up post, demonstrating how you can interrogate these structures. There are two ways of doing this.
The first (and documented way) is to use the catalogue view called sys.allocation units. This view will display one row per allocation unit, for every table and index. This can be joined back to sys.tables, via sys.partition, in order to retrieve the table name, as demonstrated below...
SELECT t.name, au.* FROM sys.allocation_units au
INNER JOIN sys.partitions p
ON au.container_id = p.partition_id
INNER JOIN sys.tables t
ON p.object_id = t.object_id
ORDER BY t.name
...A portion of the output (run against AdaventureWorks2008) is detailed below...
...This query is interesting, and shows you how large each of the allocation units is, in terms of pages. What is even more interesting, however, is the undocumented way to interrogate allocation units. This is with the use of sys.system_internals_allocation_units. Let re-run the last query, using this catalogue view...
SELECT t.name, au.* FROM sys.system_internals_allocation_units au
INNER JOIN sys.partitions p
ON au.container_id = p.partition_id
INNER JOIN sys.tables t
ON p.object_id = t.object_id
ORDER BY t.name
...Lets have a look at the results of this query...
...The results of this query are even more interesting. Here, alongside the page counts, we can also see the location of the first IAM Page in the IAM chain, the indexe's root page, and the first page of the index. We can crack these results by using the sys.fn_physloc_formatter() function, as below...
SELECT
t.name,
sys.fn_PhysLocFormatter(au.first_page) AS 'First Page',
sys.fn_PhysLocFormatter(au.root_page) AS 'Root Page',
sys.fn_PhysLocFormatter(au.first_iam_page) AS 'First IAM Page'
FROM sys.system_internals_allocation_units au
INNER JOIN sys.partitions p
ON au.container_id = p.partition_id
INNER JOIN sys.tables t
ON p.object_id = t.object_id
ORDER BY t.name
...The results are shown below...
...Pretty cool hey? Be warned, however, that the sys.system_internals_allocation_units catalogue view is both undocumented and unsupported! This means that I can not vouch that the page locations it gives you will always be accurate. It is, however, a useful and interesting starting point.
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
The first (and documented way) is to use the catalogue view called sys.allocation units. This view will display one row per allocation unit, for every table and index. This can be joined back to sys.tables, via sys.partition, in order to retrieve the table name, as demonstrated below...
SELECT t.name, au.* FROM sys.allocation_units au
INNER JOIN sys.partitions p
ON au.container_id = p.partition_id
INNER JOIN sys.tables t
ON p.object_id = t.object_id
ORDER BY t.name
...A portion of the output (run against AdaventureWorks2008) is detailed below...
...This query is interesting, and shows you how large each of the allocation units is, in terms of pages. What is even more interesting, however, is the undocumented way to interrogate allocation units. This is with the use of sys.system_internals_allocation_units. Let re-run the last query, using this catalogue view...
SELECT t.name, au.* FROM sys.system_internals_allocation_units au
INNER JOIN sys.partitions p
ON au.container_id = p.partition_id
INNER JOIN sys.tables t
ON p.object_id = t.object_id
ORDER BY t.name
...Lets have a look at the results of this query...
...The results of this query are even more interesting. Here, alongside the page counts, we can also see the location of the first IAM Page in the IAM chain, the indexe's root page, and the first page of the index. We can crack these results by using the sys.fn_physloc_formatter() function, as below...
SELECT
t.name,
sys.fn_PhysLocFormatter(au.first_page) AS 'First Page',
sys.fn_PhysLocFormatter(au.root_page) AS 'Root Page',
sys.fn_PhysLocFormatter(au.first_iam_page) AS 'First IAM Page'
FROM sys.system_internals_allocation_units au
INNER JOIN sys.partitions p
ON au.container_id = p.partition_id
INNER JOIN sys.tables t
ON p.object_id = t.object_id
ORDER BY t.name
...The results are shown below...
...Pretty cool hey? Be warned, however, that the sys.system_internals_allocation_units catalogue view is both undocumented and unsupported! This means that I can not vouch that the page locations it gives you will always be accurate. It is, however, a useful and interesting starting point.
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
What Are Allocation Units?
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.
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.
Sunday, 13 March 2011
How Do Join Operaters In SQL Server Work?
I was asked a question this week, that I could only answer at a very high level, so of course my instant reaction was to research a more detailed answer and blog about it! The question was "How do the join operators in SQL Server work?"
So first for a little background... In SQL Server, if you join two table together, in the old days, there was only one way that the optimizer could perform this task, and that was with an operation called Nested Loops (or loop join). In more recent versions however, in order to support larger databases, with larger tables, two additional operators have been added. These are Merge Join or Hash Match (Hash join). But how do these operators work?
Well, first up is Nested Loops. This operator will use statistics to decide which is the smallest of the two tables (in terms of rows) and will mark this table as the "outer table", and mark the larger table as the "inner table". For each row in the outer table, it will loop through every row in the inner table, looking for a matching key, based on the join column(s), and join the tables based on this value.
Next is Hash Match. This operator works by choosing one of the tables and running a Hash Function against the key column(s). It then puts these hashed values into an in-memory hash table, although if this becomes to large, it can be written to TempDB. It then runs the Hash Function against the second table, and joins the rows based on a match of the two hashed values.
Lastly is Merge Join. This operator relies on both of the tables being sorted by the join key. Because both keys are sorted in the same way, SQL can look at the first value in the first table and the first value in the second table. If they match, then it can output them to the result set, if not, then it can take the next row from the first table and repeat the process, until all of the rows from the first table have been matched.
So when is each operator best? Well Nested Loops is generally better for smaller tables, and also needs to be able to perform an index seek on the "inner table", to ensure any kind of performance.
Hash Match can be better for larger tables, because it requires less IO. This is because each key only needs to be read once, rather than multiple times. However, the hash function causes a fair bit of CPU, and hash table can require a lot of memory, and if you are coming under memory pressure, it may need to write the table to TempDB, which can cause more IO, as well as the memory pressure, making it a less desirable option.
Merge Join is generally regarded to be the most efficient of the operators for larger datasets, because it only needs to read the keys once, and also does not require a lot of CPU cycles. However, it does require both tables to be sorted. If they are (especially by the Clustered Index) then the operator is very fast, but if they are not, then SQL needs to perform a sort operation on them, before they enter the join operation. This, of course, is very inefficient.
It is possible to "override" the optimizer's choice of operator, by using OPTION(Loop Join), OPTION(Hash join) or OPTION(Merge Join) after the join in your query. However, remember that optimizer is very smart, and does not make that many mistakes. Updating Statistics and rebuilding indexes is usually the best way to ensure the optimizer uses the correct option. If you really do need to use these hints, however, then you should always work with SQL, rather than against it.
What do I mean by that? Well, if we take the example that SQL is deciding to use a Merge Join, but we know that this will never be the most effective option, because one of the tables (for some reason) will never be sorted by the join key, then instead of telling the optimizer that it must use a Hash Match, we should tell it to use anything it wants except a Merge Join. We can do this, by comma separating the list of operators that it can use. In this example, we would say OPTION(Hash join, Loop Join)
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
So first for a little background... In SQL Server, if you join two table together, in the old days, there was only one way that the optimizer could perform this task, and that was with an operation called Nested Loops (or loop join). In more recent versions however, in order to support larger databases, with larger tables, two additional operators have been added. These are Merge Join or Hash Match (Hash join). But how do these operators work?
Well, first up is Nested Loops. This operator will use statistics to decide which is the smallest of the two tables (in terms of rows) and will mark this table as the "outer table", and mark the larger table as the "inner table". For each row in the outer table, it will loop through every row in the inner table, looking for a matching key, based on the join column(s), and join the tables based on this value.
Next is Hash Match. This operator works by choosing one of the tables and running a Hash Function against the key column(s). It then puts these hashed values into an in-memory hash table, although if this becomes to large, it can be written to TempDB. It then runs the Hash Function against the second table, and joins the rows based on a match of the two hashed values.
Lastly is Merge Join. This operator relies on both of the tables being sorted by the join key. Because both keys are sorted in the same way, SQL can look at the first value in the first table and the first value in the second table. If they match, then it can output them to the result set, if not, then it can take the next row from the first table and repeat the process, until all of the rows from the first table have been matched.
So when is each operator best? Well Nested Loops is generally better for smaller tables, and also needs to be able to perform an index seek on the "inner table", to ensure any kind of performance.
Hash Match can be better for larger tables, because it requires less IO. This is because each key only needs to be read once, rather than multiple times. However, the hash function causes a fair bit of CPU, and hash table can require a lot of memory, and if you are coming under memory pressure, it may need to write the table to TempDB, which can cause more IO, as well as the memory pressure, making it a less desirable option.
Merge Join is generally regarded to be the most efficient of the operators for larger datasets, because it only needs to read the keys once, and also does not require a lot of CPU cycles. However, it does require both tables to be sorted. If they are (especially by the Clustered Index) then the operator is very fast, but if they are not, then SQL needs to perform a sort operation on them, before they enter the join operation. This, of course, is very inefficient.
It is possible to "override" the optimizer's choice of operator, by using OPTION(Loop Join), OPTION(Hash join) or OPTION(Merge Join) after the join in your query. However, remember that optimizer is very smart, and does not make that many mistakes. Updating Statistics and rebuilding indexes is usually the best way to ensure the optimizer uses the correct option. If you really do need to use these hints, however, then you should always work with SQL, rather than against it.
What do I mean by that? Well, if we take the example that SQL is deciding to use a Merge Join, but we know that this will never be the most effective option, because one of the tables (for some reason) will never be sorted by the join key, then instead of telling the optimizer that it must use a Hash Match, we should tell it to use anything it wants except a Merge Join. We can do this, by comma separating the list of operators that it can use. In this example, we would say OPTION(Hash join, Loop Join)
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Sunday, 6 March 2011
System Pages In SQL Server
As you know, SQL Server's data files are made up of 8KB pages, that are organized into 64KB Extents. There are several different page types, for Data, Indexes, LOB data and IAMs (Index Allocation Maps), which are the root pages for partition/allocation unit combinations, but there are also several other pages, that SQL Server maintains internally, in order to operate quickly and correctly.
The first of these page types, is the Database Boot Page. This is always Page 9, in file 1 of your database, and includes information such as the current and original database versions, the LSN (Log Sequence Number) of the last Checkpoint, and the Database Mirroring ID.
The second page type is the File Header Page, and this is always page 0 in every file. This also contains a raft of useful information, with some of the highlights being the auto growth size, the minimum file size, the HDD sector size and the LSNs/GUIDs required for restore operations.
Page 1 of every file is a PFS (Page Free Space) page, that is a byte map, detailing how full each page is. Obviously, there is a limited number of pages that can have there space recorded within an 8060-byte byte map, so this page is repeated on every 8088th page. The information stored for each page recorded in the byte-map includes:
The amount of free space on a page
Are there Ghost records (deleted rows) on the page?
Is the page an Index Allocation Map?
Is the page part of a mixed extent?
Has the page been allocated?
On pages 2 & 3 we begin the GAM Interval. This consists of a GAM (Global Allocation Map) and a SGAM (Shared Global Allocation Map) page. These two pages are bit-maps, that record the status of the following 64000 extents, which equates to around 4GB of data.
The GAM page is a record of which extents have been allocated. 0 = Allocated, 1 = Unallocated.
Then, the SGAM page is a record of if there are free pages within a mixed extent or not. 0 = The extent is either full of mixed pages, or is a uniform extent, 1 = There is one or more pages free in the mixed extent.
It is then possible for SQL to combine the bits from each of these bit-maps to determine if an extent is uniform, mixed or unallocated, so that it knows which mixed extents can be used for new or small objects and which extents are free to be assigned uniformly to larger objects.
Just taking a step back, SQL Server (by default) uses both mixed extents (where different pages are assigned to different objects) and uniform extents (where all pages within the extent are assigned to the same object). The rule it uses, is that when an object is first created, it will use a mixed extent, but once the object has grown to a point where it would fill an entire extent, it switches to uniform extents. It never switches back however, even if you truncate a table.
It is possible to change this behaviour, by turning on Trace Flag 1118. This is generally used to alleviate contention for system pages in TempDB, and will force SQL to only use uniform extents, even when a small object is first created.
Anyway, back to the point - The last two system pages of note, are the MLM and DCM pages. These pages can be found at pages 6 and 7 within a file, and contain information that helps with Disaster Recovery. Like the other bitmap pages, they are repeated every 511,230th page.
The MLM (Minimally Logged Map) page is a bitmap, that specifies whether a page has been modified by any minimally logged transactions, since the last transaction log backup. This helps SQL quickly determine what changes have occurred since the last transaction log backup.
Finally, the DCM (Differential Change Map) page is a bitmap that specifies if a page has changed since the last full backup. This means that when you take a Differential backup, SQL can easily determine which pages is needs to backup.
The first of these page types, is the Database Boot Page. This is always Page 9, in file 1 of your database, and includes information such as the current and original database versions, the LSN (Log Sequence Number) of the last Checkpoint, and the Database Mirroring ID.
The second page type is the File Header Page, and this is always page 0 in every file. This also contains a raft of useful information, with some of the highlights being the auto growth size, the minimum file size, the HDD sector size and the LSNs/GUIDs required for restore operations.
Page 1 of every file is a PFS (Page Free Space) page, that is a byte map, detailing how full each page is. Obviously, there is a limited number of pages that can have there space recorded within an 8060-byte byte map, so this page is repeated on every 8088th page. The information stored for each page recorded in the byte-map includes:
The amount of free space on a page
Are there Ghost records (deleted rows) on the page?
Is the page an Index Allocation Map?
Is the page part of a mixed extent?
Has the page been allocated?
On pages 2 & 3 we begin the GAM Interval. This consists of a GAM (Global Allocation Map) and a SGAM (Shared Global Allocation Map) page. These two pages are bit-maps, that record the status of the following 64000 extents, which equates to around 4GB of data.
The GAM page is a record of which extents have been allocated. 0 = Allocated, 1 = Unallocated.
Then, the SGAM page is a record of if there are free pages within a mixed extent or not. 0 = The extent is either full of mixed pages, or is a uniform extent, 1 = There is one or more pages free in the mixed extent.
It is then possible for SQL to combine the bits from each of these bit-maps to determine if an extent is uniform, mixed or unallocated, so that it knows which mixed extents can be used for new or small objects and which extents are free to be assigned uniformly to larger objects.
Just taking a step back, SQL Server (by default) uses both mixed extents (where different pages are assigned to different objects) and uniform extents (where all pages within the extent are assigned to the same object). The rule it uses, is that when an object is first created, it will use a mixed extent, but once the object has grown to a point where it would fill an entire extent, it switches to uniform extents. It never switches back however, even if you truncate a table.
It is possible to change this behaviour, by turning on Trace Flag 1118. This is generally used to alleviate contention for system pages in TempDB, and will force SQL to only use uniform extents, even when a small object is first created.
Anyway, back to the point - The last two system pages of note, are the MLM and DCM pages. These pages can be found at pages 6 and 7 within a file, and contain information that helps with Disaster Recovery. Like the other bitmap pages, they are repeated every 511,230th page.
The MLM (Minimally Logged Map) page is a bitmap, that specifies whether a page has been modified by any minimally logged transactions, since the last transaction log backup. This helps SQL quickly determine what changes have occurred since the last transaction log backup.
Finally, the DCM (Differential Change Map) page is a bitmap that specifies if a page has changed since the last full backup. This means that when you take a Differential backup, SQL can easily determine which pages is needs to backup.
Tuesday, 15 February 2011
When Is Data Written To Disc?
When you run a query in SQL, the first thing that happens, is SQL checks to see if the data pages that it needs to modify are currently in the Buffer Cache or not. It does this by reading an in-memory hash table, that records the dbid-fileno-pageno of each page that is in the cache at that time. You can see for yourself what pages are currently in your cache, by sys.dm_os_buffer_descriptors. If the pages are not in the cache, then it reads them in from disc.
Once all the required pages are in cache, SQL will begin an Implicit Transaction (if an Explicit Transaction is not already open for the statement). It will then perform the required operations on the page(s). If this was a DML operation, it will also mark them as being dirty. (This just means that they have changed in memory, without being written to disc). It also writes the transaction records to the log cache.
When the transaction (either implicit or explicit) commits, all transaction records, up to and including the commit, will be written to the log. There is a caveat here however; It is not just the log records from this specific transaction that are written. In order to make the transaction durable, SQL will also have to flush other log records that are intermingled with this transaction. It is also worthy of note, that SQL never writes individual log records, it always writes in blocks of between 512 bytes and 60 kb. This is called the "Log Block" and it's size is managed dynamically, internally.
This process is called write-ahead logging, or WAL, and ensures database consistency, but can cause a bottleneck, because SQL caps the amount of transactions that can be "in-flight". (This means that the Log Manager has issued a write, but an acknowledgement that the write has completed). The cap is different depending on your version of SQL, but in 2008, it is 3840KB, regardless of whether you are using a 32-bit or 64-bit version.
If this is causing a bottleneck, then you will notice lots of WRITELOG waits, when you query sys.dm_os_wait_stats. If this is the case, then you should try to optimize your transaction log. Details can be found in this post.
Once the log records have been committed to disc, then SQL is free to flush the "Dirty Pages" to disc. It does not do this straight away however. These dirty data pages are written to disc when the LazyWritter kicks in, or when a Checkpoint occurs.
A checkpoint occurs in a number of circumstances. Firstly, and most obviously, if you manually issue a checkpoint, when a backup starts, when the server is shut down (gracefully), or when an ALTER DATABASE statement is run. Also, if you are in Simple Recovery Model, a checkpoint will occur when your log is 70% full, or if you are in Full Recovery Model, a Checkpoint will run periodically, depending on your setting for recoveryinterval, which allows you to specify a target recovery time.
The LazyWriter process is a SQLOS thread, and it's role in life is to keep the Buffer Pool clean, with enough space to add new pages. It does this by periodically scanning the buffer cache and keeping track of how often a page is referenced. If it decides that it is no longer useful to keep a page in the cache, (using an algorithm that tracks how often a page is used and even what type of page it is) it checks to see if the page is "Dirty", if it is it writes it to the data file. It then marks the page as being "free" in a linked list. When a worker thread needs a page, it will choose one from this list.
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Once all the required pages are in cache, SQL will begin an Implicit Transaction (if an Explicit Transaction is not already open for the statement). It will then perform the required operations on the page(s). If this was a DML operation, it will also mark them as being dirty. (This just means that they have changed in memory, without being written to disc). It also writes the transaction records to the log cache.
When the transaction (either implicit or explicit) commits, all transaction records, up to and including the commit, will be written to the log. There is a caveat here however; It is not just the log records from this specific transaction that are written. In order to make the transaction durable, SQL will also have to flush other log records that are intermingled with this transaction. It is also worthy of note, that SQL never writes individual log records, it always writes in blocks of between 512 bytes and 60 kb. This is called the "Log Block" and it's size is managed dynamically, internally.
This process is called write-ahead logging, or WAL, and ensures database consistency, but can cause a bottleneck, because SQL caps the amount of transactions that can be "in-flight". (This means that the Log Manager has issued a write, but an acknowledgement that the write has completed). The cap is different depending on your version of SQL, but in 2008, it is 3840KB, regardless of whether you are using a 32-bit or 64-bit version.
If this is causing a bottleneck, then you will notice lots of WRITELOG waits, when you query sys.dm_os_wait_stats. If this is the case, then you should try to optimize your transaction log. Details can be found in this post.
Once the log records have been committed to disc, then SQL is free to flush the "Dirty Pages" to disc. It does not do this straight away however. These dirty data pages are written to disc when the LazyWritter kicks in, or when a Checkpoint occurs.
A checkpoint occurs in a number of circumstances. Firstly, and most obviously, if you manually issue a checkpoint, when a backup starts, when the server is shut down (gracefully), or when an ALTER DATABASE statement is run. Also, if you are in Simple Recovery Model, a checkpoint will occur when your log is 70% full, or if you are in Full Recovery Model, a Checkpoint will run periodically, depending on your setting for recoveryinterval, which allows you to specify a target recovery time.
The LazyWriter process is a SQLOS thread, and it's role in life is to keep the Buffer Pool clean, with enough space to add new pages. It does this by periodically scanning the buffer cache and keeping track of how often a page is referenced. If it decides that it is no longer useful to keep a page in the cache, (using an algorithm that tracks how often a page is used and even what type of page it is) it checks to see if the page is "Dirty", if it is it writes it to the data file. It then marks the page as being "free" in a linked list. When a worker thread needs a page, it will choose one from this list.
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
Tuesday, 11 January 2011
How many Virtual Log Files do I have?
As you may know, a physical log file is made up of multiple Virtual Log Files (or VLFs). This allows SQL to manage space and transactions effectively. In fact, when you truncate a log, you are not actually physically deleting any data, instead, you are simply marking a VLF as inactive, so that it can be reused, and if you shrink a log, the smallest size you can shrink it to, is the size of the active VLFs. By active VLF, we mean VLFs that contain open transactions.
This has the effect that when the end of the transaction log is reached, SQL moves back to the start of the log (The first inactive VLF) and begins to recycle. There is a misconception that if the loop of VLFs catches up with itself, you will receive a 9002 (Log full) error. This is incorrect. SQL will actually grow the log, thus creating additional VLFs, and "skip" the active VLFs. When the loop next goes round, the "skipped" VLFs we be reused as normal (Providing it has now been marked as inactive). The only exception to this rule is quite an obvious one...If the maximum size you have set for the log file has been reached, or the drive is full. Obviously, if either of those conditions are met, you will receive a 9002 error as you would expect.
So, back to the point of this post...How many VLFs do you have? Well, this depends on the initial size of the log when you created it, how many times it has grown and by what size, also if you have shrunk the log file.
The math for the number of VLFs is as follows:
When the log is first created, if the log is less than 64MB, there will be 4 VLFs, if the log is larger than 64MB and smaller than 1GB then there will be 8 VLFs, and if the log is larger than 1GB, then there will be 16 VLFs.
Then, every time the log grows, the same rules will be applied. If you grow the log by less than 64MB, SQL will create an additional 4 VLFs, if you grow the log by between 64MB and 1GB, then SQL will add 8 new VLFs, and growing the log by more than 1GB, will result in 16 VLFs being added.
As a performance consideration, it is generally recommended that you have less than 50 VLFs and it is recommended that you grow your log file 8000MB chunks, in order to keep your VLFs at around 500MB in size. You should specifically not grow you log by exactly 8GB (or exactly 8192MBs), as this exposes a bug, that calculates the size incorrectly if it grows in multiples of 4GB, that makes it grow less than 1MB at a time, and kills your database!
So, the big question...Is it possible to actually see how many VLFs you currently have, and how many are active, etc? Well, of course it is! You can do exactly that by using DBCC LOGINFO.
Firstly, I created the database with the following script...
...If we then examine DBCC LOGINFO
...As you can see, 8 VLFs have been created, as you would expect. In this output, FileID is the ID of the physical log file, FileSize is the size of the VLF, StartOffset marks the beginning of the VLF in relation to the beginning of the physical Log, FSeqNo is the sequence number of the VLF, Status is set to 0 for inactive and 2 for active, (so if you were troubleshooting a log that would not truncate, one point of call would be to look in this columns for lots of 2s!), Parity is a parity bit column, and CreateLSN is the first LSN of the VLF. You may know that a description of RecoveryUnitID is conspicuous by it's absence. This is because is has only appeared in Denali, and tonight is the first time I have seen it, so more details to follow, but I suspect it ties in with the new HADR features!?! Watch This Space for updates!
Anyway, I digress...If we now increase the log size by an additional 8GB, (This will not expose the bug for me, as I am running Denali, and the bug is fixed in this release. If I were running on SQL 05 or 08, I would have increased by 8000MB), it will add 16 new VLF, giving us a total of 24. This means that we see the following results...
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
This has the effect that when the end of the transaction log is reached, SQL moves back to the start of the log (The first inactive VLF) and begins to recycle. There is a misconception that if the loop of VLFs catches up with itself, you will receive a 9002 (Log full) error. This is incorrect. SQL will actually grow the log, thus creating additional VLFs, and "skip" the active VLFs. When the loop next goes round, the "skipped" VLFs we be reused as normal (Providing it has now been marked as inactive). The only exception to this rule is quite an obvious one...If the maximum size you have set for the log file has been reached, or the drive is full. Obviously, if either of those conditions are met, you will receive a 9002 error as you would expect.
So, back to the point of this post...How many VLFs do you have? Well, this depends on the initial size of the log when you created it, how many times it has grown and by what size, also if you have shrunk the log file.
The math for the number of VLFs is as follows:
When the log is first created, if the log is less than 64MB, there will be 4 VLFs, if the log is larger than 64MB and smaller than 1GB then there will be 8 VLFs, and if the log is larger than 1GB, then there will be 16 VLFs.
Then, every time the log grows, the same rules will be applied. If you grow the log by less than 64MB, SQL will create an additional 4 VLFs, if you grow the log by between 64MB and 1GB, then SQL will add 8 new VLFs, and growing the log by more than 1GB, will result in 16 VLFs being added.
As a performance consideration, it is generally recommended that you have less than 50 VLFs and it is recommended that you grow your log file 8000MB chunks, in order to keep your VLFs at around 500MB in size. You should specifically not grow you log by exactly 8GB (or exactly 8192MBs), as this exposes a bug, that calculates the size incorrectly if it grows in multiples of 4GB, that makes it grow less than 1MB at a time, and kills your database!
So, the big question...Is it possible to actually see how many VLFs you currently have, and how many are active, etc? Well, of course it is! You can do exactly that by using DBCC LOGINFO.
Firstly, I created the database with the following script...
...If we then examine DBCC LOGINFO
RecoveryUnitId
|
FileId
|
FileSize
|
StartOffset
|
FSeqNo
|
Status
|
Parity
|
CreateLSN
|
0
|
2
|
134152192
|
8192
|
34
|
2
|
64
|
0
|
0
|
2
|
134152192
|
134160384
|
0
|
0
|
0
|
0
|
0
|
2
|
134152192
|
268312576
|
0
|
0
|
0
|
0
|
0
|
2
|
134152192
|
402464768
|
0
|
0
|
0
|
0
|
0
|
2
|
134152192
|
536616960
|
0
|
0
|
0
|
0
|
0
|
2
|
134152192
|
670769152
|
0
|
0
|
0
|
0
|
0
|
2
|
134152192
|
804921344
|
0
|
0
|
0
|
0
|
0
|
2
|
134668288
|
939073536
|
0
|
0
|
0
|
0
|
...As you can see, 8 VLFs have been created, as you would expect. In this output, FileID is the ID of the physical log file, FileSize is the size of the VLF, StartOffset marks the beginning of the VLF in relation to the beginning of the physical Log, FSeqNo is the sequence number of the VLF, Status is set to 0 for inactive and 2 for active, (so if you were troubleshooting a log that would not truncate, one point of call would be to look in this columns for lots of 2s!), Parity is a parity bit column, and CreateLSN is the first LSN of the VLF. You may know that a description of RecoveryUnitID is conspicuous by it's absence. This is because is has only appeared in Denali, and tonight is the first time I have seen it, so more details to follow, but I suspect it ties in with the new HADR features!?! Watch This Space for updates!
Anyway, I digress...If we now increase the log size by an additional 8GB, (This will not expose the bug for me, as I am running Denali, and the bug is fixed in this release. If I were running on SQL 05 or 08, I would have increased by 8000MB), it will add 16 new VLF, giving us a total of 24. This means that we see the following results...
RecoveryUnitId
|
FileId
|
FileSize
|
StartOffset
|
FSeqNo
|
Status
|
Parity
|
CreateLSN
|
0
|
2
|
134152192
|
8192
|
34
|
2
|
64
|
0.00
|
0
|
2
|
134152192
|
134160384
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134152192
|
268312576
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134152192
|
402464768
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134152192
|
536616960
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134152192
|
670769152
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134152192
|
804921344
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134668288
|
939073536
|
0
|
0
|
0
|
0.00
|
0
|
2
|
536870912
|
1073741824
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
1610612736
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
2147483648
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
2684354560
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
3221225472
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
3758096384
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
4294967296
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
4831838208
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
5368709120
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
5905580032
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
6442450944
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
6979321856
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
7516192768
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
8053063680
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
8589934592
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
9126805504
|
0
|
0
|
0
|
34000000006600000.00
|
...Finally, if we try to shrink the log file back down to 950MB, because we are just deleting inactive VLFs, we will end up with our original 4 VLFs, with the same original sizes, totaling 1GB, not 950MB...
RecoveryUnitId
|
FileId
|
FileSize
|
StartOffset
|
FSeqNo
|
Status
|
Parity
|
CreateLSN
|
0
|
2
|
134152192
|
8192
|
34
|
2
|
64
|
0.00
|
0
|
2
|
134152192
|
134160384
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134152192
|
268312576
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134152192
|
402464768
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134152192
|
536616960
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134152192
|
670769152
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134152192
|
804921344
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134668288
|
939073536
|
0
|
0
|
0
|
0.00
|
...Hope you found this useful, and I will post an update, with the meaning of RecoveryUnitID column, as soon as I have it, unless of course you already know, in which case, please post a comment!
CREATE DATABASE LogInfoTest
ON PRIMARY
( NAME = N'instantinittest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\loginfotest.mdf')
LOG ON
( NAME = N'instantinittest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\loginfotest_log.ldf' , SIZE = 1GB)
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Subscribe to:
Posts (Atom)