My Recent encounter to a request from client to split the database data file (.Mdf) into 2 datafiles got me this new challenge and a great learning . I would like to share the experience and the technicality behind this theory. As a DBA we have many times used the command DBCC shrink to get out target results. But there are different parameters that are very useful. So let’s try to shrink the MDF file. Let’s start the steps.
Step 1: Take the backup of the existing database.(Play it
safe..!)
Step 2: Check the disk usage report. Follow steps as per below snapshot:
Right click the
database >reports>Standard Reports>disk usage report.
Note: You need to ignore the free space in the data file. (Every database keeps some free space to
perform some maintenance operations for more follow the blog from Brent.)
Step 3: Now add another file in the database:
Step 4: Now check again the Disk usage report
Step 5:Now we run a DBCC SHRINKFILE with EMPTYFILE parameter and it will automatically start distributing data to the new files.
--Move Data to the blank File Added Recently
use[Adventureworks2014] Go DBCC SHRINKFILE(N’Adventureworks2014_Data’,EMPTYFILE) GO
Step 6: Now check again the Disk usage report:
NOTE; Here you need to ignore the space consumed by the data file because that’s the file space reserved as the initial size. This is how you can split the data file. To be sure of the results and consistency of the database I ran the DBCC CHECKDB command.
For more details about the multiple file database performance gains follow blog from Paul.
‘Working’/problem/Struck/…/…// with ‘tempdb’ in SQL Server #‘Recommendations’ to reduce allocation contention in SQL Server ‘tempdb ‘database #Only One’ tempdb’ Data File (Are you sure..??)
Introduction:
Have you ever been to any summit, lets spare out some useless directions. I ask you again: Have you ever been to
technical summit??. I am sure if you were you must have felt stresses with the only way to reach a destination and then being in the
queue to reach the seminar hall.
Imagine the same with the SQL server,Temp DB and Queries. Limite number of ways to reach to the same destination (TempDB: .mdf)
queries need to be in the queue to get the required data or the required resource
Imagine the same with the SQL server,Temp DB and Queries. Limite number of ways to reach to the same destination (TempDB: .mdf)
queries need to be in the queue to get the required data or the required resource.
So today we are going to make ourselves expert in the existing confusing resourceful world.
Summary:
SQL Server uses database called tempdb to handle a lot of system tasks : a shared workspace (as well as user-created temp tables.) SQL
Server database administrators need to be able to quickly find out what queries and stored procedures are running slow. On a server that
is running Microsoft SQL Server, you notice severe blocking when the server is experiencing a heavy load. Dynamic Management Views
[sys.dm_exec_request or sys.dm_os_waiting_tasks] indicates that these requests or tasks are waiting for tempdb resources.
Additionally, the wait type is PAGELATCH_UP, and the wait.
Detailed Explanation:
During object creation, two (2) pages must be allocated from a mixed extent and assigned to the new object. One page is for the Index
Allocation Map (IAM), and the second is for the first page for the object. SQL Server tracks mixed extents by using the Shared Global
Allocation Map (SGAM) page. Each SGAM page tracks about 4 gigabytes of data.
To allocate a page from the mixed extent, SQL Server must scan the Page Free Space (PFS) page to determine which mixed page is free to
be allocated. The PFS page keeps track of free space available on every page, and each PFS page tracks about 8000 pages. Appropriate
synchronization is maintained to make changes to the PFS and SGAM pages; and that can stall other modifiers for short periods.
When SQL Server searches for a mixed page to allocate, it always starts the scan on the same file and SGAM page. This causes intense
contention on the SGAM page when several mixed-page allocations are underway. This can cause the problems.
Bottom Root cause :
Non Uniform Growth
Insufficient disk I/O response
The regular visit to the SGAM Page for the allocation.
The frequent requirement to visit the PFS page to find the page free space.
Limited number of concurrent GAM, SGAM basically limited number of files.
The Figure below explains the scenario where the TempDb files are not configured to grow equally and this uneven growth enforces
more free file to be utilized more slowing the performance.
Remedy; (Get rid of it..! At Least When you know you can ..Right!!)
• Increase the number of tempdb data files that have equal sizing
As an example, if the single data file size of tempdb is 8 GB, and the Log file size is 2 GB, the recommendation is to increase the
number of data files to eight (8) (each of 1 GB to maintain equal sizing), and leave the log file as is. Having the different data
files on separate disks would be provide additional performance benefit. However, this is not required. The files can co-exist
on the same disk volume.
The optimal number of tempdb data files depends on the degree of contention seen in tempdb. As a starting point, you can
configure tempdb to be at least equal to the number of logical processors that are assigned for SQL Server. For higher-end
systems, the starting number could be eight (8). If the contention is not reduced, you may have to increase the number of data
files.
• Use trace flags:T-1117 & T-1118
-T1117 - When growing a data file grow all files at the same time so they remain the same size, reducing allocation contention
points.
-T1118 - When doing allocations for user tables always allocate full extents. Reducing contention of mixed extent allocations
• Make each data file the same size; this allows for optimal proportional-fill performance.
References:
• Microsoft Knowledge Base:
4099472 PFS page round robin algorithm improvement in SQL Server 2014, 2016 and 2017
• MSSQL Tiger Team Blog:
Files and trace flags and updates in SQL Server tempdb
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:
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
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:
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.
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.
NOTE: Optionally, the database administrator can configure any of the availability replicas to exclude read-only connections when running under the primary role.
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.
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.
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:
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’
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.
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 performancedegradation.
