Query Hint and Plan Guide

Query execution plan generated for a SQL query significantly affect the query performance. Parameter sniffing, indexes, statistics and other factors are taken into consideration for SQL Server query optimizer to produce an optimal query plan for the execution within a timely manner. Sometimes, due to certain scenarios and limitation, the query plan generated may be suboptimal. There are multiple ways to address this problem, like modifying the way the query is written, or using query hint or even plan guides under certain circumstances. This blog post discuss an example of using a query hint as well as plan guide.

Query Performance Tuning Example

As DBA, application performance issue and complaints often thrown our way. It is often fun to troubleshoot those performance issue. When it has been identified that the problem lies on SQL Server, it get even more exciting. Here is one of the example of how I tuned a query a while ago and also some lessons learned.

Transaction Logging - Table Variable and Temporary Table

You may have already known some of the difference between table variable and temporary table like ability to create statistics, execute in parallel plan (for update query), transaction rollback and other limitations. This post will be focusing on their transaction logging resources.

SQL Transaction Modes

There are different ways of starting transactions in SQL Server. Each transaction modes differ from one another in their own behavior and it is crucial to understand how they work.

Faster Way To Check If Database Exist

Sometimes we need to check if the database exist before perform certain task. There are a few ways to be done. Today, let's evaluate these options to see which may give us a better performance.

Faster Wait Statistics for SQL Server 2012

Since SQL Server 2005, waits and queues statistics have been implemented in SQL Server to provide important information and effective analysis to identify the root cause and for performance tuning. Here is some script to aggregate this information for performance tuning and analysis.