Accelerated Database Recovery. How did we get here?

Advanced Database Recovery. How did we get here?
Credit

While Accelerated Database Recovery (ADR) is new, the need and purpose of improving the time to recover is always relevant.   There are many ways that a database can crash and need to recover. Maybe you find yourself here from something more severe like hardware failure, perhaps it was only a reboot and when things started to come back you see databases stuck in an “In Recovery” state. I personally have been bitten by a log running transaction rolling back a few times. Regardless, the scenario is scary because you need to wait for the recovery process to finish or restore from back ups to get out of this situation.

Recovery happens in 3 phases in SQL Server.

Analysis Phase

SQL Server is taking a minute to go through the database files to determine what, if anything, needs to be fixed. It will go through the mdf and ldf files, create some working tables (Dirty Page Table and Active Transaction Table) for itself to track what needs done and go forward from there.

Redo Phase

SQL Server is going through and applying every valid modification tracked in the transaction log file/s (how many files needed would be shown in the DPT (Dirty Page Table) as the minimum LSN required). This process is cleaning out the DPT so that all dirty pages that belong to a committed transaction are being applied to disk. In my experience this part takes the longest, but that heavily depends on your transaction log size and number of VLFs.

Undo Phase

Anything that was uncommitted and put into the ATT (Active Transaction Table) is now being reviewed and rolled back so long as it doesn’t affect database integrity. Once these items are rolled back, the database would enter the online state again and become available for reads and writes.

What’s so great about Accelerated Database Recovery?

Advance Database Recovery. What is so great about Advanced Database Recovery?
Credit

What if I told you that long running transaction wait times could be eliminated and we could still process the 3 phases of recovery, but at a much faster pace? That is the high level benefit of ADR. ADR brings us the sLog, persisted version store (PVS), and logical reverts. sLog tracks non-versioned operations in memory and the latter 2 phases of the recovery process use this log to process things from the oldest uncommitted transaction up to the latest checkpoint. Since everything is in memory and we are only concerned with non-versioned operations (DDL operations, bulk queries), these steps can process at a much quicker pace. PVS is similar to the version store but gets stored in the target database of the transaction instead of inside tempdb, which helps out the other new concept of logical revert. Logical revert is using PVS to avoid lengthy rollback wait time and locks and instead aborting the transaction all together and just using the previous row version in PVS.

The new 3 phases in SQL Server

Analysis Phase

SQL Server still processes this phase in the same manner it did before, but adds in the step of constructing the sLog for processing in the next 2 steps.

Redo Phase

The accelerated database recovery redo phase is now broken into 2 parts

Part 1

In the first accelerated database recovery redo phase part we complete our redo transactions required in the sLog starting at the oldest uncommitted transaction and go up to the latest database checkpoint. This part typically completes very quickly because we are still only looing at non-versioned operations.

Part 2

After reaching the latest database checkpoint, the engine then swaps over to the transaction log and continues performing redo transactions until it gets to the current time in the log.

Undo Phase

The undo phase is the big winner in accelerated database recovery as everything done in undo can happen from the sLog and also perform quick rollbacks using the PVS and logical revert functionality.

Things to watch out for when using Accelerated Database Recovery

While accelerated database recovery does have many positives, there are some costs associated to them. The largest cost is the increase in size for all data files for databases that have this feature enabled as you are now storing previous versions of changed rows in the user database. Another cost to consider is the additional compute power that will be consumed maintaining the rows in PVS.

The post Accelerated Database Recovery appeared first on dbWonderKid.

SQL Server 2022 was announced yesterday at Microsoft’s Ignite Conference. Here are some of the items that caught our attention. This includes SQL Server 2022 Announcements and other announcements around the Microsoft Data Platform.

Bob Ward blessed the community with this presentation over his three favorite features..

SQL Server 2022 First Thoughts

The following is a summary of some of my favorite announcements with some initial thoughts.

Bi-directional HA/DR to Azure SQL – With SQL Server 2022, you can use Distributed Availability Groups to have HA/DR replica in Azure Managed Instance. You can failover your Read/Write Traffic. You have to do a backup and a restore into SQL Server 2022 to go back from the Azure Managed Instance. Hopefully, Distributed Availability Group failovers will be bi-directional in the near future.

Query Store Enhancements (QDS) – SQL Server 2022 allows you to have separate data for your readable replicas. QDS will now be on by default for new databases. QDS provides the ability to utilize hints to easily shape query plans with no code changes.

Intellectual Query Processing Improvements (IQP)- IQP has new enhancements to memory grant feedback to persist data instead of requiring changes only to happen when queries are executed frequently.IQP has more improvements without changing your code. These improvements include changes with MAXDOP and CE Feeback. The query can detect and change how many threads are used so large tables can have more threads as needed. Saving the best for last. Progress to help smooth out parameter sniffing will also be included in SQL 2022. Looking forward to seeing how this is like Automatic Tuning Automatic Plan Correction introduced in SQL Server 2017.

Free SQL Assessments for Azure VMs – If you are using an Azure VM to host an instance of SQL Server you might be interested in the new free assessment offering coming out of Ignite.

New Class Ebdsv5 Virtual Machines – Now 30% better price on performance compared to the previous greatest option. Remote I/O Throughput goes up to 2,500 MBPS so less memory could be required due to higher read throughput.

SQL Server 2022 integrates with Azure Synapse Link and Azure Purview – Being able to manage data discovery within both on-premise and the cloud is a great win! The same could be said for breaking down silos to help you get the most value out of your data.

SQL Server Ledger – Looks like the Azure SQL Database Ledger which was announced in May of this year is coming to SQL Server 2022. Always excited to see the most secure relational database platform increase its security features., Microsoft is bringing the same blockchain capabilities to SQL Server.

SQL Server 2022 What Is Next?

If you want to get an early jump on SQL Server 2022 you can apply to enter the Early Adoption Program (EAP). I also recommend signing up for PASS Member Summit Conference that starts next week. I will be giving a 101 Fundamentals talk on Performance Tuning. It’s 100% FREE to attend.

What is your favorite feature of SQL Server 2002? What do you wish was added to the feature list? Let us know in the comments.

What is a heap?

Heaps are unorganized just like this deck of cards
Heaps are unorganized just like this deck of cards

Heap was actually named pretty accurately. It is a “pile” of unsorted/unorganized data. A heap will exist on any table that lacks a clustered index. Heaps can exist even if you have a non clustered indexes. The main advantage to a heap is the speed at which you can insert data into your tables, as it doesn’t need to follow a logical order like inserting identity values into the last page of a table.

What is a clustered index?

Clustered Indexes are sorted and organized unlike table heaps
Clustered Indexes are sorted and organized unlike table heaps

A clustered index is used as the main method of logically sorting data in a table. It can be done on a key-value defined in the table, but it doesn’t require an actual primary key to exist. Clustered indexes should be created on the most common column(s) used in the predicates of your most frequently executed statements. All data will be sorted to match the index and you can only have 1 clustered index per table. The main advantage to having a clustered index is increased speed on data reads.

How should I identify when a clustered index should be used instead of a heap?

As noted above, the main advantage of a clustered index is an increase in the speed of your reads. To get an idea of where you might need to increase read speed, you should first identify all of your tables that do not have a clustered index. I found it very helpful to also place lower limits on the number of rows in a table and the number of reads against that table (by checking dynamic system views). The below query is a great start into looking at those things.

DECLARE @type_of_index INT = 0 /*0 = heap, 1 = clustered, 2 = non clustered*/ 
	, @min_num_of_reads INT = 10
	, @min_num_of_rows INT = 100

SELECT OBJECT_NAME(IX.OBJECT_ID) AS Table_Name
   ,SCH.name AS SchemaName
   ,IX.name AS Index_Name
   ,IX.type_desc AS Index_Type
   ,PS.row_count AS TableRowCount
   ,SUM(PS.[used_page_count]) * 8 IndexSizeKB
   ,IXUS.user_seeks AS NumOfSeeks
   ,IXUS.user_scans AS NumOfScans
   ,IXUS.user_seeks + user_scans AS NumOfReads
   ,IXUS.user_lookuPS AS NumOfLookuPS
   ,IXUS.user_updates AS NumOfUpdates
   ,IXUS.last_user_seek AS LastSeek
   ,IXUS.last_user_scan AS LastScan
   ,IXUS.last_user_lookup AS LastLookup
   ,IXUS.last_user_update AS LastUpdate
FROM sys.indexes IX
	INNER JOIN sys.objects O 
		ON ix.object_id = O.object_id
	INNER JOIN sys.schemas SCH 
		ON SCH.schema_id = O.schema_id
	INNER JOIN sys.dm_db_index_usage_stats IXUS 
		ON IXUS.index_id = IX.index_id 
		AND IXUS.OBJECT_ID = IX.OBJECT_ID
	INNER JOIN sys.dm_db_partition_stats PS 
		ON PS.object_id = IX.object_id  
		AND PS.index_id = IX.index_id
WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1
	AND IX.index_id = @type_of_index 
	AND PS.index_id = @type_of_index 
	AND IXUS.index_id = @type_of_index
	AND IXUS.database_id = (DB_ID())
	AND IXUS.user_scans + IXUS.user_seeks > @min_num_of_reads
	AND PS.row_count > @min_num_of_rows
GROUP BY OBJECT_NAME(IX.OBJECT_ID), IX.name, IX.type_desc, IXUS.user_seeks, IXUS.user_scans, IXUS.user_lookuPS, IXUS.user_updates, last_user_seek, IXUS.last_user_scan, IXUS.last_user_lookup, IXUS.last_user_update, SCH.name, PS.row_count
ORDER BY TableRowCount DESC, NumOfReads DESC

Now that you see what tables are on the larger side that also has a lot of reads, we can dive deeper into the MSSQL system dynamic management views to see how these tables are being used in the system by looking at query stats and plans. Enter your table name in the variable and you can see the number of times a plan has been used, the text associated to that plan, and the plan itself to validate if the query is using a table scan due to the heap or if there is a non clustered index that is being used instead. The 2nd result set will give you object names if the table is being used in a SQL object like a stored procedure or function

DECLARE @TableName NVARCHAR(50) = 'ExampleTableName'

SELECT TOP 10 databases.name AS DBName
	, dm_exec_sql_text.text AS TSQL_Text,
	, dm_exec_query_stats.creation_time, 
	, dm_exec_query_stats.execution_count,
	, dm_exec_query_stats.total_worker_time AS total_cpu_time,
	, dm_exec_query_stats.total_elapsed_time, 
	, dm_exec_query_stats.total_logical_reads, 
	, dm_exec_query_stats.total_physical_reads, 
	, dm_exec_query_plan.query_plan
FROM sys.dm_exec_query_stats 
	CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
	CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
	INNER JOIN sys.databases
		ON dm_exec_sql_text.dbid = databases.database_id
WHERE dm_exec_sql_text.text LIKE '%'+@TableName+'%'
ORDER BY ;

SELECT * 
FROM sys.sql_modules M 
	INNER JOIN sys.objects O 
		ON M.object_id = O.object_id
WHERE M.definition LIKE '%'+@TableName+'%'

After reviewing use cases and their query plans, it’s time to decide if the table in question meets the above recommendations for a clustered index or if it’s better served as a heap, and what columns to include in the index. If the table has multiple use cases but they all typically share the same column/s, create the clustered index on that table with those column/s.

Now don’t go throwing clustered indexes around like Oprah to the studio audience

The clustered index is helpful in a lot of situations, but there are some instances where it is not necessary or can actually do some harm to your performance.

The main example of this would be logging tables. These tables typically have a lot of insert operations, but not necessarily a lot of reads or even updates as they are just logging everything that is happening and are referenced infrequently. Putting an index on a table like that could cause hot latches which are caused by data waiting to be inserted into the last available page as the key is typically sequential and other data is frequently being inserted into that page. The only exception to this rule would be if your key column of the index is a GUID, as the GUID is random and not sequential.

Watch out for making your clustered index on columns/data that is typically not static. You would update these key values frequently. Updating key values on an index will likely cause performance issues. The clustered index is used as a lookup point for all non clustered indexes.  By updating the key values, you can cause page splits. Page splits will require index maintenance to fix your problem and return performance to where it belongs.

Another example of a common mistake is making the clustered index with too many columns. A clustered index is defining your default sort of all data in the table. Too many columns in an index could slow down data operations by requiring resorting for additional use cases and it will also increase the size of all non clustered indexes on that table as well (and there is a limit to how wide a key can be for an index).

Finally, never forget the most important part of adding indexes: TEST TEST and TEST.

Post a comment below if you have any other tips or tricks when it comes to fixing your heap woes below!