execution plan Archives - Performance Tuning And Infra Automation

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

Performance Tuning TSQL

Should we check [context_switches] for SQL Server Query from Performance Point

Hi Friends. If you are wondering if we should check [context_switches] counter for SQL Server Queries while performance tuning, then here is the official verdict from Paul Randal. Thanks Friends!…

Ajay Dwivedi May 27, 2018

Execution Plan Health Check

[sp_HealthCheck] – Get SQL Server Health Details

Hi Friends, Ever wanted to check the heartbeat of SQL Server. It is a very common activity for a SQL Server user to check CPU, SQL Memory Utilization, Available Memory…

Ajay Dwivedi March 31, 2018

Execution Plan Health Check Performance Tuning

What’s Running – Find All Details of Running Queries

I often see DBAs/developer struggling to find what's wrong with query. The first step in troubleshooting queries is finding out information like batch text, query text, batch execution plan, individual…

Ajay Dwivedi March 30, 2018

Execution Plan Performance Tuning

Procedures with RECOMPILE Option

Hi Friends, I often work on Performance Issues, and I found that developers use RECOMPILE option without even understanding it thoroughly. Instead of resolving the performance issue by query tuning,…

Ajay Dwivedi January 7, 2018

Internals Performance Tuning TSQL

Non – Trusted Foreign Keys in SQL Server

Today, I was working with one user to troubleshoot their query timeout issues. Analyzing wait stats on server, I found 'LCK_M_X' wait to be most prominent wait on server. This…

Ajay Dwivedi July 17, 2017

Performance Tuning

Log all Activities using sp_WhoIsActive – 2

Hi Friends, in my previous blog post Log all Activities using sp_WhoIsActive, I shared how easy it is to log all sql server sessions using sp_whoIsActive and SQL Server Agent…

Ajay Dwivedi May 4, 2017

Performance Tuning TSQL

Optimize for Value – TSQL Query Hint

Today, I was working on an incident of slow running procedure. Procedure was running fine for most of the parameter values but, was getting stuck for few values. I could…

Ajay Dwivedi January 3, 2017

Execution Plan Performance Tuning TSQL

Query Optimization 03 – Users statistics (individual user) [StackOverflow]

Hi Friends, this is my 3rd blog post of series Query Optimization. In last blog post Questions and Answers per Month, we took optimization decision by analyzing warnings suggested by…

Ajay Dwivedi November 27, 2016

Execution Plan Performance Tuning

Query Optimization 02 – Questions and Answers per Month [StackOverflow]

Hi Friends, this is my 2nd blog post of series Query Optimization. In 1st blog post What is my accepted answer percentage rate, we took optimization decision based on Logical…

Ajay Dwivedi November 21, 2016