SSMS Tips and Tricks - Part 02 - Query Elapsed Time

When we running a query on SSMS, one of the most important thing we first look at is elapsed time. I.e. how much time this query took to complete.

We can easily see this in the status bar of the Query Result window.

However this is only accurate to seconds. What if you want to see more accurate result, specially with very quick queries but very critical to performances.

That's when you can use properties window for query. In order to see this right click on the query window (see below) and select "Properties Windows".

In the properties window, you can see a property called "Connection elapsed time" and many other properties.

This shows elapsed time up to closest milli-second.

RDP to AzureAD Laptop

My work laptop is AzureAD joined and I'm using AzureAD account (from work place) to login to it. For easy access I regularly RDP to it from my home laptop (when working from home in unusual times). My home laptop just use my personal Microsoft account and not joined to any domain.

Suddenly on a day (very recently), my home laptop refused to log me into the work laptop. I was pretty sure I was typing the correct password and accessing the correct IP, because I didn't change a thing. I was puzzled.

I was sure this is something Microsoft has suddenly changed via a update. I Googled around and for my luck I was able to find following article:

Remote Desktop to Azure AD Joined Computer

This was a life saving article written by a person called Bradley Schacht around August 2019.it showed exactly the issue I was having and very clear step by step instructions to resolve it. 

Basic summary of the article as follows:

• First you need to add two new entries if they are not already there in your RDP file. Those entries are:
• enablecredsspsupport:i:0
• authentication level:i:2
• Then you need to access your account in following manner:
• .\AzureAD\yourazureaduser@company.com

I'm not going to spell out the instructions here again, if you are interested, please visit the above link and you will be guided very clearly through the whole process.

Thanks again for Bradley Schacht for amazing article.

SQL Server management Views - Part 1

SQL Server Management views (DMVs) are system views which provide insight into internal working of the SQL Server. When you are going to trouble shoot SQL Server or performance of it DMVs plays major part giving us very useful information.

There are hundreds of DMVs in SQL Server today. However this series of blogs going to look into interesting ones.

One of the DMVs I came across recently is called -> dm_exec_query_optimizer_info.

This DMV provide us with statistical data for SQL Server's Query Optimizer. It returns a table with following columns:

Counter	Occurrence	Value
Name of the counter/event which is measured	No of times event has occurred	Average value of the property (per occurrence)

Data Collection

Note that these values and occurrences are get reset, when SQL Server restarts and when they are collected values are occurrences are cumulative (add up for each event). If query was executed using a cached plan, stats in here doesn't get change (i.e. only update when optimizer starts working).

Permission

SQL Server 2019 and earlier -> VIEW SERVER STATE

SQL Server 2022 and later -> VIEW SERVER PERFORMANCE STATE

Execution

SELECT * FROM sys.dm_exec_query_optimizer_info

Data

This view collects data for about 40 events (might vary from version to version). Some of the most interested are listed below:

Counter	Occurrence	Value
optimizations	Total Number of optimizations	N/A
elapsed time	Number of times time elapsed	Average time taken
final cost	Number of times final cost calculated	Average final cost
trivial plan	Number of times trivial plan used	N/A
no plan	Number of times optimizer didn't find a plan	N/A
timeout	Number of times optimizer time out	N/A

Total List (in 2019)

optimizations

elapsed time

final cost

trivial plan

tasks

no plan

search 0

search 0 time

search 0 tasks

search 1

search 1 time

search 1 tasks

search 2

search 2 time

search 2 tasks

gain stage 0 to stage 1

gain stage 1 to stage 2

timeout

memory limit exceeded

insert stmt

delete stmt

update stmt

merge stmt

contains subquery

unnest failed

tables

hints

order hint

join hint

view reference

remote query

maximum DOP

maximum recursion level

indexed views loaded

indexed views matched

indexed views used

indexed views updated

dynamic cursor request

fast forward cursor request

Note that optimizer counts are not updated if optimizer choose to use cache plan.

Two Digit Year Cut-off Setting in SQL Server

I have recently come across that SQL Server has setting which controls how it interpret year number when specified as two digits.

Since year number contains 4 digits (e.g. 2024), if user specify year as two digit (e.g. 24) SQL server need to know a way to identify how to interpret 2 digit year as a full 4 digit year. For example 24 can be year 1924 or year 2024 or even year 2124.

Default setting for this is 2049, which means two digit years are interpret as years from 1950 to 2049.

00 => Interpret as => 2000

20 => Interpret as => 2020

49 => Interpret as => 2049

50 => Interpret as => 1950

78 => Interpret as => 1978

This setting can be change in Server Properties.

Right click on the SQL server node, and select properties. Then go to "Advanced" section.

Or Using TSQL.

USE AdventureWorks2022;  

GO  

EXEC sp_configure 'show advanced options', 1;  

GO  

RECONFIGURE ;  

GO  

EXEC sp_configure 'two digit year cutoff', 2030 ;  

GO  

RECONFIGURE;  

GO

Note setting is in advanced settings section.

Above code will set the two year cut off setting to 2030, which means from year 1931 to 2030.

It is always recommended to use 4 digit in your code to avoid ambiguity.

Reference -> Microsoft Books Online