3 settings to improve SQL Server performance

There are a number of dials and knobs in SQL Server that allow the DBA to fine tune almost every aspect of the server. In this post I highlight five quick wins that every DBA can enable on their server to improve performance. The best part is they will take all of three seconds of…

DB options: – Recovery intervals and Checkpoint

In this video we talk about recovery intervals. Specifically how checkpoint allows recovery intervals to be maintained. Checkpoint is a feature that allows SQL server to recover faster during an unexpected shutdown. It is important for DBAs to understand how this functionality works. We explore different type of checkpoints and the role they play in…

DB Options: – Parameterization

In this video we cover the two options available under parameterization in MSSQL database properties. Ad hoc queries are queries that aren’t stored procedures. Since the code is ad-hoc in nature we see that the WHERE condition often has hard coded values. Each time the hard coded value changes we see a new execution plan…

DB options: – Auto Shrink = OFF

The use of AUTO SHRINK I think needs no explanation. Almost anybody who has worked on SQL Server understands the way databases can quickly grow. In these cases we can see growth happens in two places, the database file and the log file. The database file gets highly fragmented when it is shrunk. The log…

DB Options: – AUTO UPDATE STATS / ASYNC

https://youtu.be/8ugpVfPzvz4 In this video we are going to talk about auto update statistics. This is a feature in the database engine that automatically triggers a stat update when data has been updated beyond a threshold. In previous versions of SQL server this thresh hold was 500 rows followed by every 20% increase in the count…

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- 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 – 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…