The dark side of Fn_dump_dblog

Posted on by
2

I want to show you what happens when you keep using the undocumented fn_dump_dblog function. (For example if you are consistently dumping the contents into a table for analysis)

The below 2 queries shows the current state of my system

SELECT * FROM sys.dm_os_schedulers

SELECT COUNT(*) FROM sys.dm_os_threads

dark.JPG

Now let’s just set the environment up and use fn_dump_dblog and see the effect of using it.

ALTER DATABASE [AdventureWorks2012DR] SET RECOVERY FULL

BACKUP DATABASE [AdventureWorks2012DR] TO DISK = 'C:\SQLSERVER\AdventureWorks2012DR.BAK'

GO
BACKUP LOG [AdventureWorks2012DR] TO DISK = 'C:\SQLSERVER\AdventureWorks2012DRLog.BAK'

GO

SELECT
    [Current LSN],
    [Operation],
    [Context],
    [Transaction ID],
    [Description]
FROM
    fn_dump_dblog (
        NULL, NULL, N'DISK', 1, N'C:\SQLSERVER\AdventureWorks2012DRLog.BAK',
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

Ok so it returns some information – Great.

dark2

Let’s keep running the code (Movement into a table for future analysis). Look at the state of my system. After couple of hours:

dark3

Using this command creates more threads and hidden schedulers (these will only go after a restart). Depending on what version of SQL Server you are on and what Service Pack you may or may not have this issue. It was fixed in SQL 2012 SP2 onwards. So be on the cautious side when running these sorts of commands.

Also I noticed Memory bloat for the sqlservr.exe. Nothing else was running on this server, just my fn_dump_dblog script.  Threads need memory too. (Each thread will use 0.5MB on x86 and 2MB on x64)

capturesdd

 

Microsoft Certifications

Posted on by
1

Every now and again I would navigate to Microsoft’s certification page and see what / if any changes have taken place regarding certification within the Data Platform space. (Check out the link – http://www.microsoft.com/en-us/learning/certification-overview.aspx)

Nothing has changed in terms of the “pyramid” grade structures, you know, going from MTA, MCSA to MCSE which I will get to later.

pyr

However there is one change I have noticed – MCSE: Data Management & Analytics (they still have the MCSE: Data Platform and BI)  where for this certificate you would need to know about Designing and Implementing Cloud Data Platform Solutions and Designing and Implementing Big Data Analytics Solutions.

It makes sense cloud, data, data analysis / mining is becoming more important as data grows, businesses can leverage these techniques to gain advantage so it’s good to see Microsoft adapt to this and change up their certifications.

HOWEVER, I am still slightly disappointed that there is still no tip of the pyramid certificate.

pya2

A bit of background, I used to hold all three MCITP and MCSE: Data Platform certificates (I failed them a few times but I got there in the end). Logically I would then look to the next step; I just feel that there should be a certificate that surpasses the level of questioning asked in the MCSE exams.

I have this one:

MCSE

When I went to the SQLskills training on the last day we were given questions to troubleshoot and fix. They were tough, made me think hard and we had to “apply” what we learned from the last 10 days or so. These are the sorts of questions I want to evaluate myself against.

I won’t be renewing my MCSE certificate, not because of a lack of motivation to learn, if anything it’s quite the opposite. I am hungrier than ever to pick up and improve my Azure (and SQL) skills and get my hands dirty then hopefully share the knowledge that I gain within my blog.

I will say if you are new to this technology I think it’s a great way to help you get started and learn to a set plan so I would encourage you start at the MSCA, just like I did 10 years ago (It was MCTS back then).

Azure SQL Database – TDE

Posted on by
1

I work in the financial space so you can imagine that security is quite high on the agenda. TDE (Transparent Data Encryption – see this article for more details https://blobeater.wordpress.com/category/sql-server-2/tde/ ) is nothing new, setting it up on “earthed” SQL Servers can have some what of an overhead but in the Azure world it is so simple to setup.

The big advantage of TDE in Azure over the earthed flavour is that Microsoft does alot of the work for you. Assuming your database is in a geo-replication partnership it will be protected by a different key on each of the servers. Also Microsoft will rotate your certificate at least every 90 days.

Azure SQL Database does not support Azure Key Vault integration with TDE. SQL Server running on an Azure virtual machine (IaaS) can use an asymmetric key from the Key Vault.

Within the settings section of your Azure SQL Database you will find the TDE option as shown below. To do this successfully you will need to be connected as the Azure Owner, Contributor, or SQL Security Manager.

enc1

Select ON for the data encryption option and click save.

enc2

Seriously, that is all it takes.

I will then move to SSMS (SQL Server Management Studio) and issue the following query to understand the status. (Credit to David Pless: TSP – Microsoft)

 SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
         WHEN '0' THEN 'No database encryption key present, no encryption'
         WHEN '1' THEN 'Unencrypted'
         WHEN '2' THEN 'Encryption in progress'
         WHEN '3' THEN 'Encrypted'
 WHEN '4' THEN 'Key change in progress'
 WHEN '5' THEN 'Decryption in progress'
 WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed'
         ELSE 'No Status'
      END,
percent_complete, create_date, key_algorithm, key_length,
encryptor_thumbprint, encryptor_type  FROM sys.dm_database_encryption_keys

enc3

Naturally tempdb gets encrypted too along with the user database. Azure uses AES-256 bit algorithm.

These sorts of activities get tracked and logged by the Activity log within Azure.

enc4

SQL Server ALTER INDEX….ABORT!

Posted on by
2

I know there are people out there that will be going from older versions of SQL to SQL Server 2016, yes it makes sense it is a great product with a lot of new features such as Query Store, Stretch DB to name  but a few.

However don’t forget about the features that were available in 2014 that are naturally available in 2016, such as the ability to control blocking behaviors when rebuilding indexes.

Classically when you want to use the ONLINE mode of rebuilding indexes the code would look like:

ALTER INDEX [MCD] ON [dbo].[mcdaol] REBUILD WITH(ONLINE = ON)

Looking into the locking you will see that ONLINE operation uses (Sch-M) on the corresponding table as part of the process (actually takes Shared Table Lock (S) at the beginning of the operation, and a Schema Modification Lock (Sch-M at the end)).

lock1

So to be granted a SCH-M lock you can’t have any conflicting locks, so what happens when / if you have a process that is updating the table and you want to use the ONLINE rebuild? Yes you will be blocked. With 2014 onwards we can control what happens if we get into this situation and for this post I am going to abort the other query causing me to wait.

Let’s see what happens.

I have 1 window running an update such as:

— Open Trans

BEGIN TRAN
UPDATE [dbo].[p_dinner]
SET og_status = 1
WHERE og_status = 10

In a different window I am running the Index rebuild:

ALTER INDEX [p_dinner_idx] ON [dbo].[p_dinner]
REBUILD WITH(ONLINE = ON)

Naturally this will be blocked.

lock2

So now we can control what happens here, for this example I want to kill the connection holding the locks that are conflicting with my rebuild i.e. the update (just an example).

So I run:

ALTER INDEX [p_dinner_idx] ON [dbo].[p_dinner] REBUILD
WITH
(
  ONLINE = ON
   (
     WAIT_AT_LOW_PRIORITY
     (
       MAX_DURATION = 1,
       ABORT_AFTER_WAIT = BLOCKERS
     )
   )
)
GO

lock4

From the above image you can see after 1 minute (MAX_DURATION) the query successfully executes because it killed the connection causing the blocking, which is done via the ABORT_AFTER_WAIT = Blockers clause.

ALTER INDEX….ABORT!

Posted on by
2

I know there are people out there that will be going from older versions of SQL to SQL Server 2016, yes it makes sense it is a great product with a lot of new features such as Query Store, Stretch DB to name  but a few.

However don’t forget about the features that were available in 2014 that are naturally available in 2016, such as the ability to control blocking behaviors when rebuilding indexes.

Classically when you want to use the ONLINE mode of rebuilding indexes the code would look like:

ALTER INDEX [MCD] ON [dbo].[mcdaol] REBUILD WITH(ONLINE = ON)

Looking into the locking you will see that ONLINE operation uses (Sch-M) on the corresponding table as part of the process (actually takes Shared Table Lock (S) at the beginning of the operation, and a Schema Modification Lock (Sch-M at the end)).

lock1

So to be granted a SCH-M lock you can’t have any conflicting locks, so what happens when / if you have a process that is updating the table and you want to use the ONLINE rebuild? Yes you will be blocked. With 2014 onwards we can control what happens if we get into this situation and for this post I am going to abort the other query causing me to wait.

Let’s see what happens.

I have 1 window running an update such as:

— Open Trans

BEGIN TRAN
UPDATE [dbo].[p_dinner]
SET og_status = 1
WHERE og_status = 10

In a different window I am running the Index rebuild:

ALTER INDEX [p_dinner_idx] ON [dbo].[p_dinner]
REBUILD WITH(ONLINE = ON)

Naturally this will be blocked.

lock2

So now we can control what happens here, for this example I want to kill the connection holding the locks that are conflicting with my rebuild i.e. the update (just an example).

So I run:

ALTER INDEX [p_dinner_idx] ON [dbo].[p_dinner] REBUILD
WITH
(
  ONLINE = ON
   (
     WAIT_AT_LOW_PRIORITY
     (
       MAX_DURATION = 1,
       ABORT_AFTER_WAIT = BLOCKERS
     )
   )
)
GO

lock4

From the above image you can see after 1 minute (MAX_DURATION) the query successfully executes because it killed the connection causing the blocking, which is done via the ABORT_AFTER_WAIT = Blockers clause.