TSQL Archives - Page 2 of 5 - Performance Tuning And Infra Automation

Heavy-SqlServer-Workload-against-StackOverflow-database

Time conversion from local to UTC and vice-versa

Recently I have been working on setting up baselining for my SQL Server lab environment. As part of baselining, I capture all the required Perfmon counters both at SQL Server…

Ajay Dwivedi September 6, 2020

Baseline-SQL-Server TSQL

Get Time Duration in [DD hh:mm:ss] format

As DBA, there are various scenarios where I display duration in results. More often, for visual effects, I like to convert the same duration into [DD hh:mm:ss] format. For example,…

Ajay Dwivedi August 29, 2020

TSQL

Create & Populate StackOverflow.dbo.PostTags table

As a DBA & instructor, very often I like to generate heavy workload on my SQL Server instances to re-produce scenarios like 100% CPU, zero PLE, high IO, etc. For…

Ajay Dwivedi August 22, 2020

SQL SERVER – Blocking Tree – Identifying Blocking Chain Using SQL Scripts

Health Check TSQL

SQL SERVER – Blocking Tree – Identifying Blocking Chain Using sp_WhoIsActive

As a DBA, I often receive user requests to check server slowness. Quite often, slowness is directly linked with active blocking on the server. I find many scripts on internet…

Ajay Dwivedi August 15, 2020

SQL Server - Index Maintenance for Replication Databases

Performance Tuning TSQL

SQL Server – Doing Index Maintenance when HADR solutions are in Place

When I joined my current organization 2 years back, the biggest problem I faced as DBA was whenever there was Replication, log shipping, Mirroring, or AlwaysOn, the Index Maintenance jobs…

Ajay Dwivedi February 22, 2020

TSQL

Some useful Scripts

As a DBA/Developer, there have been many occasions where I had to write my own scripts/automation to fulfill specific business requirements. Below are some of those useful scripts that I…

Ajay Dwivedi September 8, 2019

Baseline-SQL-Server SQL Alerts TSQL

Setup Long-Running Jobs Alert

In my organization, we have many SQL Agent jobs that are expected to execute within a particular time window. But there are times when some of them take longer than…

Ajay Dwivedi September 7, 2019

Performance Tuning TSQL

Remove High Virtual Log File (VLF) (@OptimizeLogFiles) This generates TSQL code for removing huge virtual log files (VLFs) from SQL Server database log files. By default, @vlfCountThreshold is 500. So any log file with VLF count greater than @vlfCountThreshold will be considered for SHRINKFILE operation, Regrow to original Size in appropriate size chunk, and finally reset the AutoGrowth settings. Space-Capacity-Automation url:- https://github.com/imajaydwivedi/Space-Capacity-Automation

Ajay Dwivedi March 14, 2019

SQL Alerts TSQL

How to Setup Blocking Alerts on SQL Server

It's very common for Database professionals to experience blocking. Even though blocking is a usual activity, it can convert into an issue anytime. And becomes pain point when DBAs are…

Ajay Dwivedi August 18, 2018

Execution Plan Performance Tuning TSQL

Question/Answer – Should I filter the data in Sub Queries??

Recently a good friend of mine asked me question whether is there any performance benefit in filtering the data out in subqueries, and then joining them. Well, answer is NO.…

Ajay Dwivedi May 28, 2018