UPGRADING TO SQL SERVER 2016: POST-UPGRADE TASKS : After upgrading SQL Server you will need to perform a series of tasks to verify that database is ready to be handed over to the end users for further testing. This post will provide you a checklist o...

SQL Server 2016 Sort Order Batch Processing Mode : SQL Server 2016 comes with many new features and enhancements. One of these enhancements in the query optimizer is the batch processing mode for the sort operator, which was processed row by row in p...

Consolidating SQL Server Error Logs from Multiple Instances Using SSIS: SQL Server hides a lot of very useful information in its error log files. Unfortunately, the process of hunting through all these logs, file-by-file, server-by-server, can cause...

How to Find the Statistics Used to Compile an Execution Plan: In this post, I show you how to determine exactly which statistics objects were used by the query optimizer to produce an execution plan.We will need three undocumented trace flags. The f...

SQL Server 2016 AlwaysOn: Add DBs in AGs with Direct Seeding: In this blog I would like to talk about two new features shipped with SQL Server 2016 for AlwaysOn availability groups. The first one concerns the new direct seeding capability and the sec...

If a SQL query has parameters, SQL Server creates an execution plan tailored to them to improve performance, via a process called 'parameter sniffing'. This plan is stored and reused since it is usually the best execution plan. Just occasionally, it...

Comparison Tool released with latest SSMS : The ability to compare plans is something that a lot of people have to do for troubleshooting reasons. Maybe it’s to find why a query or batch suddenly slowed down; to understand the impact of a rewrite;...

Working with the bcp Command-line Utility : Even though there are many other ways to get data into a database, nothing works quite as fast as BCP, once it is set up with the right parameters and format file. Despite its usefulness, the art of using t...

Configure SQL Server AlwaysOn Availability Group on a Multi-Subnet Cluster : Recently we had a project to configure SQL Server AlwaysOn between three nodes which were hosted on a multi-subnet environment. A “multi-subnet” environment is defined w...

While troubleshooting the performance issues of SQL server, you might have seen the sessions are in different state like SUSPENDED,RUNNING,RUNNABLE etc. In this post let us try to explain this by going through the threading model of SQL server.SQL se...

How to Interpret Query Execution Plan Operators: SQL Server provides a variety of query execution plan formats like a graphical plan, text plan and XML plan. A graphical plan is the most preferred way to understand the query execution by developers a...

Understanding a SQL Server Query Execution Plan: To communicate with any RDBMS we use Structured Query Language (SQL), which is a declarative querying language and supported by the American National Standards Institute (ANSI). Microsoft extended ANSI...

Top 10 Methods to Improve ETL Performance Using SSIS : Extraction Transformation Load (ETL) is the backbone for any data warehouse. In the data warehouse world data is managed by the ETL process, which consists of three processes, Extraction-Pull/Acq...

Step by Step Configuring AlwaysOn with Log Shipping on SQL Server 2016: One of my clients came up with a requirement to use log shipping along with an AlwaysOn Availability Group. They cannot extend an AlwaysOn replica to the disaster recovery site f...

How much memory does my SQL Server actually need? : Traditionally questions about how much memory SQL Server needs were aimed at how to appropriately set the 'max server memory' sp_configure option in SQL Server, and in my book the recommendation tha...