All You Need to Know About VLF files:

All You Need to Know About VLF files:

Every SQL Server database transaction log records all transactions and the database modifications made by each transaction. The transaction log is a critical component of the database and, if there is a system failure, the transaction log might be required to bring your database back to a consistent state. This guide provides information about the physical and logical architecture of the transaction log. Understanding the architecture can improve your effectiveness in managing transaction logs.

VLF stands for Virtual Log File and the transaction log file is made up of one or more virtual log files. The number of virtual log files can grow based on the autogrowth settings for the log file and how often the active transactions are written to disk.

Request: Do read the Article Some downloadable query collection will be provided to you at the last :

DEBATE:

There is always been the debate since the SQL server evolved on the face of earth about the optimized number of VLF files. But then its totally dependent on the database you host the nature of transaction it serves.

But yes many of the most reputed Intellects have tried to solve this mystery and tried to find out the science behind it few are mentoned below do check out for more details.(I personally recommend these for your better understanding of concept)

1: Transaction Log VLFs – too many or too few? -: by Kimberly L. Tripp
2: Important change to VLF creation algorithm in SQL Server 2014 -: Paul S. Randal
3: Bug: log file growth broken for multiples of 4GB -: Paul S. Randal
4: Guide To manage VLFs -: David Levy

So basic formula for the number of VLF files will be created next time the database log file is at the edge of next growth. Follow below stats:

· Up to 64 MB: 4 new VLFs, each roughly 1/4 the size of the growth

· 64 MB to 1 GB: 8 new VLFs, each roughly 1/8 the size of the growth

· More than 1 GB: 16 new VLFs, each roughly 1/16 the size of the growth

The transaction log is a wrap-around file. For example, consider a database with one physical log file divided into four VLFs. When the database is created, the logical log file begins at the start of the physical log file. New log records are added at the end of the logical log and expand toward the end of the physical log. Log truncation frees any virtual logs whose records all appear in front of the minimum recovery log sequence number (MinLSN). The MinLSN is the log sequence number of the oldest log record that is required for a successful database-wide rollback. The transaction log in the example database would look similar to the one in the following illustration.




When the end of the logical log reaches the end of the physical log file, the new log records wrap around to the start of the physical log file.




This cycle repeats endlessly, as long as the end of the logical log never reaches the beginning of the logical log. If the old log records are truncated frequently enough to always leave sufficient room for all the new log records created through the next checkpoint, the log never fills. However, if the end of the logical log does reach the start of the logical log, one of two things occurs:

· If the FILEGROWTH setting is enabled for the log and space is available on the disk, the file is extended by the amount specified in the growth_increment parameter and the new log records are added to the extension. For more information about the FILEGROWTH setting, see ALTER DATABASE File and Filegroup Options (Transact-SQL).

· If the FILEGROWTH setting is not enabled, or the disk that is holding the log file has less free space than the amount specified in growth_increment, an 9002 error is generated. Refer to Troubleshoot a Full Transaction Log for more information.

If the log contains multiple physical log files, the logical log will move through all the physical log files before it wraps back to the start of the first physical log file.

Caution: Virtual log file (VLF) creation follows this method:

· If the next growth is less than 1/8 of current log physical size, then create 1 VLF that covers the growth size (Starting with SQL Server 2014 (12.x))

· If the next growth is more than 1/8 of the current log size, then use the pre-2014 method:

o If growth is less than 64MB, create 4 VLFs that cover the growth size (e.g. for 1 MB growth, create four 256KB VLFs)

o If growth is from 64MB up to 1GB, create 8 VLFs that cover the growth size (e.g. for 512MB growth, create eight 64MB VLFs)

o If growth is larger than 1GB, create 16 VLFs that cover the growth size (e.g. for 8 GB growth, create sixteen 512MB VLFs)

Conclusion:

This is a very critical and unending topic sur to multiple dependencies and hence I rest this here …do check the list of useful links provided here since I don’t think I will be ever writing the blog more cleare and precise like big names in industry.

Thanks Regards
Ajeet Singh

DOWNLOAD:
Find Out VLF count
Click Here

To find the DB with High VLF
Click Here

Determing the position of the last VLF in transaction log before shrinking the log file
Click Here

References disclaimer:

All the details I could bring to you, is because of the great personalities in this profession. I have mentioned links to their blogs. Do visit share your feedback and queries.

Please post queries and Doubts and topics you would like me to blog:

Read Routing Not working on primary:Fix read routing

Secondary to primary replica read/write connection redirection ……!!!

As the topic itself is shouting the description we hardly require an explanation on it.But yes for those who are new to this concept emerged in SQL 2016 and later versions of SQL server with some interesting value added feature is covered in this article to add a new confidence in you as a DBA.

Read routing is basically to forward the read only requests to the server’s stand by or secondary replica (As named in AG group). Read-only routing refers to the ability of SQL Server to route qualifying read-only connection requests to an available Always On readable secondary replica (that is, a replica that is configured to allow read-only workloads when running under the secondary role). 

Mandatory:

The client’s connection strings must specify the application intent as “read-only.” That is, they must be read-intent connection requests. Follow below procedure to configure while making connection:

NOTE:

You must set the read-only routing URL of a replica before configuring its read-only routing list.

Prerequisites

·         The availability group must possess an availability group listener. For more information.

·         One or more availability replicas must be configured to accept read-only in the secondary role

·         Performance considerations of read routing:

     Load-balancing across read-only replicas: SQL 2016 introduced load balancing feature in which the read only connections can be modified to any replica replacing the old method in which the connections were forwarded to the first available replica.

NOTE:

One more interesting feature came from this functionality the we can force to rout the connection between set of replicas using SINGLE PARENTHESES: ” ( )”

Code 1:

READ_ONLY_ROUTING_LIST = (‘Server1’,'Server2’, 'Server3’,…,…,…,…, 'Server8’) Modified to make replica set as below:

Code 2:

READ_ONLY_ROUTING_LIST = (('Server1’,'Server2’), 'Server3’,…,…,…,…, 'Server8’)

Note: Each entry in the routing list can itself be a set of load-balanced read-only replicas. The above example demonstrates this.

Code 2:

represents the configuration that it will rout the read only connection to server1/2, in case if both are not online the connections will be forwarded to next provided replica.

Example (Transact-SQL)

The following example modifies two availability replicas of an existing availability group, AG1 to support read-only routing if one of these replicas currently owns the primary role. To identify the server instances that host the availability replica, this example specifies the instance names—COMPUTER01 and COMPUTER02.

ALTER AVAILABILITY GROUP [AG1]

MODIFY REPLICA ON  

N'COMPUTER01’ WITH  

(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));  

ALTER AVAILABILITY GROUP [AG1]

MODIFY REPLICA ON  

N'COMPUTER01’ WITH  

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433’));  

ALTER AVAILABILITY GROUP [AG1]

MODIFY REPLICA ON  

N'COMPUTER02’ WITH  

(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));  

ALTER AVAILABILITY GROUP [AG1]

MODIFY REPLICA ON  

N'COMPUTER02’ WITH  

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433’));  

ALTER AVAILABILITY GROUP [AG1]  

MODIFY REPLICA ON  

N'COMPUTER01’ WITH  

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02’,'COMPUTER01’)));  

ALTER AVAILABILITY GROUP [AG1]  

MODIFY REPLICA ON  

N'COMPUTER02’ WITH  

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01’,'COMPUTER02’)));  

GO  

SPECIAL CASE

when all the replicas are offline and only primary is online:

ALTER AVAILABILITY GROUP AG_Name MODIFY REPLICA

ON N'Replica1’

WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('Replica2’, 'Replica3’), 'Replica1’)));

Acknowledgement:

!! Details of  SPECIAL CASE:!!

In one of our encounter with the failover of AG group the read routing was not working since after failover the database took time to come online on secondary. This caused the read only connections to fail for reporting purpose.

That’s what struck us that we did not consider this situation where only primary is online, so the read request must reach to primary then we configured this condition and resolve the issue.

REFERNCES:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-routing-for-an-availability-group-sql-server?view=sql-server-2017#Prerequisites

https://msdn.microsoft.com/en-us/library/hh213002.aspx

Code level configurations for Read Routing : (T-SQL and Power shell)

1.       Performance considerations of read routing:

NOTE: Optionally, the database administrator can configure any of the availability replicas to exclude read-only connections when running under the primary role.

2.      Calculating read_only_routing_url for Always On

3.      Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server)

Transparent data Encryption:TDE

Database Encryption: is Transforming the data into 'Cipher Text' using the algorithm(AES128/AES256) to make it incomprehensible if don't decrypt before use
EXAMPLE: 

So this is what exactly is happening in encryption all the data is bind with the encryption key (Prism) unless you have the Key the data is useless.

There are multiple ways to encrypt the data but today we will look at the one of the best encryption !

                         Transparent data encryption: TDE

Below is the transparent data encryption Architecture. 

IMAGE SOURCE:

The TDE made its debut with the SQL server 2008 where Microsoft extended the data protection capability of the SQL server. TDE is inbuilt feature of SQL server that encrypts the data at rest or the data available in the drive not in the buffer or any other active transacting place. This only protects the data at the SQL server OS end not the application end. You must balance the pros and cons of Encryption.

You must refer below article for more:

1. ·         All About TDE:
2. ·         Side Effect:

TDE Hierarchy:

Create the DMK in the master database, if it doesn’t already exist.
Create a certificate in  the master database for securing the DEK.
Create the DEK in the user database to be encrypted.
Enable TDE on the user database.
The following example illustrates encrypting and decrypting

The Below example illustrates TED on AdventureWorks2012 database using a certificate installed on the server named MyServerCert.Verify the encryption status:To verify which databases are encrypted on the instance or database use below methods.A: To verify the database; 

USE master;

GO  

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ’<UseStrongPasswordHere>’;  

go  

CREATE CERTIFICATE 

MyServerCert WITH SUBJECT = ‘My DEK Certificate’;  

GO  

USE AdventureWorks2012;  

GO  

CREATE DATABASE ENCRYPTION KEY  

WITH ALGORITHM = AES_128  

ENCRYPTION BY SERVER CERTIFICATE MyServerCert;  

GO  

ALTER DATABASE AdventureWorks2012  

SET ENCRYPTION ON;

GO  

Verify the encryption status:

To verify which databases are encrypted on the instance or database use below methods.

A: To verify the database;

Use database properties to check encryption.

B:   Alternatively use the Query below for the Instance level enquiry:

SELECT    name,DEK.*

FROM      sys.databases D

JOIN      sys.dm_database_encryption_keys DEK

ON        DEK.database_id = D.database_id

ORDER BY  name;

Output will be like below Snap:  (refer ‘Encryption_state’ Column)

is_encrypted = 0 I believe reflects the fact that DB was encrypted automatically, not by issuing ALTER command. Here is what MSDN says about this parameter: Indicates whether the database is encrypted (reflects the state last set by using the ALTER DATABASE SET ENCRYPTION clause).

percent_complete = 0 is expected when there is no ongoing encryption. From MSDN: Percent complete of the database encryption state change. This will be 0 if there is no state change.

encryption_state = 3 looks to be the ultimate hint in answering the question. MSDN: Indicates whether the database is encrypted or not encrypted, 3 = Encrypted.

Database Encryption Struck in progress:

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

You can check which certificates exist using the following query :

SELECT * FROM sys.certificates;

Removing the database Encryption;

– Remove encryption
USE TDETest;
ALTER DATABASE TDETest SET ENCRYPTION OFF;
GO
– Remove DEK
DROP DATABASE ENCRYPTION KEY;

                                              !! Take aways:

Reference ;

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/sql-server-encryption?view=sql-server-2017

https://en.wikipedia.org/wiki/Database_encryption

https://technet.microsoft.com/en-us/library/gg592949(v=sql.110).aspx

SCRIPT : https://www.sqlshack.com/how-to-monitor-and-manage-transparent-data-encryption-tde-in-sql-server/

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/back-up-the-service-master-key?view=sql-server-2017

#Database encryption #All I know About TDE

Universal PSSDIAG:

Universal PSSDIAG:   Run PSSDIAG on any version of SQL server: #Magic PSSDIAG!!

PSSDIAG is a tool used by Microsoft to diagnose a production problem.The collection of data depends on the scripts used and the scripts can be customized.There are many more tools available to DBA but more often we see PSSDIAG in action.

Today I will show you how we can use a PSSDIAG for almost all versions of SQL server.

(Note: I am again saying for almost all versions because this can modify the SQL sever details but the scripts used to collect the data with the PSSDIAG will be the main cause of limit. )

I am not describing the PSSDIAG I assume that you have a base knowledge of using it and its consequences.

If you don’t know and have a crave to learn more about it  check out the amazing documentation by Microsoft itself.

All about PSSDIAG:

so here we start:

when you have all the PSSDIAG package. Follow  below steps:

Step 1: Open the PSSDIAG folder provide by Microsoft. Extract all the files.

Step 2: Locate the file “pssdiag” in XML format. Refer snapshot below.

Step 3: Open the file in notepad and locate the machine name in the code Refer below snapshot.

Step 4: In place of server mention the server name on which Pssdiag to be executed.

Step 5: Next is to modify the instance name and version number.

·         > Instance name will be the name of instance (Default / Named).

·         > Version number will be the SQL server version E.g.: SQL 2016 has version as ‘13’

Refer below snapshot.

 Save the above modifications run the pssdiag this will create a custom Pssdiag for any version of SQL server.

Note: Do not change the file name or place it in any other folder other than pssdiag itself.

Author: 

References:

> Microsoft & working experience.

Page life expectancy is too LOW..?

Intro: 

Being a production DBA means you must have ability to quickly look at the system and figure out the issues. A quick look at perfmon or Task manager can tell us of high CPU usage, but what is a good way to quickly look at proper memory usage.

Checking out the Page Life Expectancy (PLE) counter is a great way to check for memory pressure. A low value for your system indicates that system is flushing pages from the buffer pool too quickly. The longer a page can stay in the buffer pool and be read from memory the better. As fetching the data from the disk is the resource intense operation that slows down the system that can perform way better if reads from buffer.

Before we go extremely technical let’s have a brief introduction on the topic:

·         What is page Life expectancy:

Page Life Expectancy; is the number of seconds the average page of data has been in the buffer pool.  Keeping the data in memory gives SQL Server quicker access to it instead of making the long, slow trip to disk.  While none of the counters in SQL Server means everything on its own, this one can open your eyes and lead you towards issues that can be resolved.

·         Detailed Information;

The commonly cited value to aim for is 300 seconds or more. This translates to five minutes, meaning a page will remain in memory for only five minutes. Therefore, all of the contents of the buffer pool will be renewed every five minutes, given the current pressure on the buffer pool to find new buffers for pages. For example, if you have a buffer pool that’s 60GB, your SQL Server system is reading 60GB of data every five minutes, which is a pretty large amount.

The value of 300 seconds is a Microsoft recommendation in a white paper that was written five years ago. It’s not a relevant guideline nowadays, and even back in 2006, aiming for a set value isn’t a valid recommendation.

T-SQL Query to find the page life expectancy:

SELECT [object_name],
[counter_name],
[cntr_value] FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ’%Manager%’
AND [counter_name] = 'Page life expectancy’

The problem is that many people see the 300 value for Page Life Expectancy you can still find documented is wrong, very wrong. If you have a server with 64 GB of memory with 56 GB allocated to SQL Server that means you’re reading about 56 GB of data from disk every 300 seconds. If you look at Page 36 of Troubleshooting SQL Server – A Guide for the Accidental DBA by Jonathan Kehayias and Ted Krueger you’ll see an actual scalable version of this; PLE should be 300 for every 4 GB of RAM on your server. That means for 64 GB of memory you should be looking at closer to 4,800 as what you should view as a critical point.

The reason you see 300 written everywhere is because we were limited by 32-bit servers for so long while at the same time SQL Server documentation was really being developed well.  Those servers could only have 4 GB of memory, thus making this number make sense as well as the Buffer Pool / 4 GB * 300 formula.  I’ll go so far as to say that you should be cautious of anything that doesn’t have a formula to it because our servers keep getting faster, our databases keep getting bigger, and static values…don’t.

Formula to be used as per today’s technology advancements:

PLE threshold = ((MAX BP(MB)/1024)/4)*300

One of the options we have is to enable the Microsoft feature that locks pages in the memory to avoid frequent and easy flush of pages from memory.

Lock pages in memory:

This policy setting determines which accounts can use a process to keep data in physical memory, which prevents the computer from paging the data to virtual memory on a disk.

Normally, an application running on Windows can negotiate for more physical memory, and in response to the request, the application begins to move the data from RAM (such as the data cache) to a disk. When the pageable memory is moved to a disk, more RAM is free for the operating system to use.

Enabling this policy setting for a specific account (a user account or a process account for an application) prevents paging of the data. Thereby, the amount of memory that Windows can reclaim under pressure is limited. This could lead to performance degradation.

 References:

·         Lock pages in memory;

https://docs.microsoft.com/en-us/windows/security/threat-protection/security-policy-settings/lock-pages-in-memory

·         How to enable this option;

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-the-lock-pages-in-memory-option-windows?view=sql-server-2017

·         MS BLOG Detailed:

https://blogs.msdn.microsoft.com/mcsukbi/2013/04/11/sql-server-page-life-expectancy/

https://simplesqlserver.com/2013/08/19/fixing-page-life-expectancy-ple/

https://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/

Page life expectancy is too low:T-SQL Query to find the page life expectancy