Author Archives: opsadmin

About opsadmin

This post was written by Jayanth Kurup. A Microsoft SQL Server Consultant and Trainer based out of Bangalore, India. Jayanth has been working on MS SQL Server for over 15 years. He is a performance tuning and Business Intelligence expert. Having worked with companies like Microsoft, DELL, Wells Fargo, Thomson Reuters and many other fortune 100 companies. Some other technologies Jayanth works on include Microsoft Azure, PowerBI, Python and AWS. When he isn’t consulting or training, Jayanth like to travel, paint and read. He is also very active in social causes and the founder of Enabled Business Solutions. Visit his company by clicking the link in the menu or email him directly.

DB Options: Auto Create Incremental Statistics ON

By | February 18, 2020

In this video we cover the next database option which is AUTO CREATE INCREMENTAL STATISTICS. This option is useful when the database has partitioned tables. It allows the DBA to update stats by merging the updated stats for the partition in question with existing stats. In this way DBA are able to reduce the overhead… Read More »

Using Bookmarks in PowerBI

By | February 7, 2020

https://www.youtube.com/watch?v=SftUYP6nchk PowerBI is a robust reporting tool that has a number of easy to use and configurable options when it comes to reporting. One of the most frequently used features in PowerBI is the bookmarks capability. A bookmark as its name suggest provides way to snapshot a report page keeping in mind the filters and… Read More »

Temp table Owner

By | January 13, 2020

Recently I came across a post on LinkedIn which required identifying the user who created a temp table. This is an interesting use case and thought I’d write a query of my own. The below script identifies currently logged in users who are executing queries and anytime tables that they might have created. I figured… Read More »

SQL 2019 – Christmasql

By | December 4, 2019

Early November I had posted I would be conducting a series of sessions on MS SQL server. The response was really great and while I intended to do a classroom session the interest was primarily for online delivery. So I will be arranging the first half of it this in the coming 10 days. Here… Read More »

SQL 2019 – Database Snapshot for In-Memory OLTP table

By | November 25, 2019

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… Read More »

Backup and restore of encryption keys and restoring

By | November 19, 2019

  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 = ‘c:\work\MyServerCertKey’… Read More »

SQL 2019- Max DOP and Memory Configuration

By | November 17, 2019

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… Read More »