SQL 2019 – Database Snapshot for In-Memory OLTP table

Traditionally Database snapshots have been used mainly to get Database mirroring secondary’s to act like Readable secondary. As you might be aware in mirroring etc. the databases are restored with no recovery. Since the transaction log file isn’t in a consistent state the database engine prevents users from querying them. However in the interest if…

Backup and restore of encryption keys and restoring

USE master; /************************************************************* CREATE YOUR MASTER KEY AND YOUR CERTIFICATE FOR TDE AND ALL OTHER TYPES OF ENCRYPTION *************************************************************/ OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘Isthis@securePa$$word?’; BACKUP MASTER KEY TO FILE = ‘c: \work\exportedmasterkey’ ENCRYPTION BY PASSWORD = ‘abcd@1234’; GO BACKUP CERTIFICATE MyServerCert TO FILE = ‘c: \work\MyServerCert’ WITH PRIVATE KEY ( FILE =…

SQL 2019- Max DOP and Memory Configuration

Probably the most useless recommendation and as far as I am concerned not a feature. Why? If you are a DBA you know that DOP and Memory limits are fine-tuned based on the workload and system details. Things like concurrency OLTP vs OLAP, usage of in memory vs traditional tables Buffer pool extensions, number of…

SQL 2019- Optimize for sequential key

Last page contention is one of the textbook examples for latch contention. This frequently occurs on tables that are narrow and have high inserts happening against a sequential primary key column such as an identity column. Simply put on tables that have high concurrency you might have multiple users inserting data and generating some kind…

SQL 2019 – Custom Capture of Query Store stats

Query store is a feature of SQL server that tracks and captures information about queries. Database administrators will find query store useful in trouble shooting performance issues. Most database scenarios query store is perfectly fine under default configuration. However every once in a while a DBA might want to specifically crack metrics where the default…

SQL 2019- Scalar UDF inlining

Scalar UDF are notorious for their performance issues. The most common reason they cause issues is due the fact that the logic is applied on each row. So queries which use Scalar UDF for a large number of rows often face significant execution times due to the context switch with each row. There are other…

SQL 2019 – Worker Migration

Worker migration in SQL 2019 is the process by which workers that are bound to a scheduler can be unbound and run on another scheduler in order to complete long running transactions sooner. Once again I find the example of a car wash very handy here. Assume you have a query (car) and a team…

SQL 2019 – Indirect Checkpoint

Checkpoint in SQL server is the process by which dirt pages are written to disk. However it had a flaw that cause issues and the solution of which is indirect checkpoint. In the beginning we had automatic checkpoints. Here we assume a recover interval of 60 secs. Then in a very crude explanation we measure…

Accelerated database recovery – And how it’s like a car wash

    Accelerated Database Recovery is a feature that DBAs will find very useful. Dba’s are often stuck in a situation where the transaction log file cannot be truncated because of a long running query or the database recovery takes longer than usual after shut down and restart. In such scenarios Accelerated database recovery is…

SQL 2019-Data Classification and Vulnerability assessment

Data Classification With GDPR security has become a high priority for a large number of organizations the cost of not implementing robust security has been significantly increased and therefore companies that previously used to think security wasn’t a key part of their architecture now has to revisit that approach. But complying with GDPR and all…

Error when installing SQL 2019 – Developer Edition

  Error Message Cannot insert the value NULL into column ‘CustomContext.ClusterId’, table ‘DWDiagnostics.dbo.pdw_component_health_data_lock’; column does not allow nulls. INSERT fails. Affected Components DB Engine , Replication , Polybase , Full Text Search , Java Connectors for HDFS , Data Quality Services Resolution Currently Under investigation   Notes Post installation DB engine components are installed but…

SQL 2019 – Lightweight Query profiling

Lightweight query profiling is a feature that was introduced in SQL 2014. Before we can start talking about lightweight query profiling we need to understand the problem that it was solving. Prior to the introduction of lightweight query profiling database administrators had to rely on profile or running a trace in order to identify performance…

SQL 2019 – TDE SUSPEND RESUME

In this video we are going to talk about transparent data encryption. Transparent data encryption is a feature in SQL server that protects data at rest. Data at rest means your MDF and LDF files. These files reside on the hard disk and are easy prey to anyone with access the file system. By simply…

SQL 2019 -Batch Mode on row store

With the introduction of Columnstore indexes we got Vertipaq Compression and Batch mode as the execution mode. The latter results in much faster query execution where large number of rows need to be processed e.g aggregation in a DW query. Naturally the question then becomes why not use a Columnstore index if any way you…