Log in | Register

Performance Tuning Archives - SQL Server Blogs, Events, Webcasts, Videos, SQL Server learning & education Performance Tuning Archives - SQL Server Blogs, Events, Webcasts, Videos, SQL Server learning & education

Performance Tuning

SQLServerGeeks Annual Summit 2016 IS HAPPENING IN BANGALORE. HAVE YOU REGISTERED?

SQL Server Trace Flags Information in Execution Plans

Hello Friends,

SQL Server trace flags are important consideration for a query plan compilation and execution. Trace flag can impact the execution of a query and might hit the performance badly if not used appropriately. Sometimes during performance troubleshooting by using execution plan is not easier because we don’t have the information about the trace […]

By |March 14th, 2017|Categories: Performance Tuning|Tags: , |Comments Off on SQL Server Trace Flags Information in Execution Plans

SQL Sever – Compare Showplan

Hello Friends,

During performance troubleshooting, It was not easy to compare the execution plan for the queries. We generally save the execution plans and then compare them by opening in different SQL Server Management Studio windows but it was not easy to compare the various things about two operators from the plan. In new SSMS […]

By |March 5th, 2017|Categories: Performance Tuning, SQL Server|Tags: |Comments Off on SQL Sever – Compare Showplan

SQL Server – Join Operation

In this blog we’ll find how SQL Server works when we do join operation. SQL Server optimizer chooses one of the below physical operator to perform logical join operation.

Hash Match
Merge Join
Nested Loop Join

As far as performance is concern let me tell you, we can’t say which one will best.Each operator has its own advantages […]

SQL Server 2016 – Batch Mode Processing with Serial Plan

Hello Friends,

There are lots of features and improvements announced by Microsoft in SQL Server 2016, one of them is providing batch mode processing with 900 rows batch size. SQL Server 2016 uses batch mode processing even if the execution plan is a serial plan which was not possible with previous versions. Due to this […]

By |October 17th, 2016|Categories: Database Administration, Performance Tuning, SQL Server Internals & Architecture|Tags: , , |Comments Off on SQL Server 2016 – Batch Mode Processing with Serial Plan

SQL Server 2016 – Trace Flag 9453 – Disable Batch Mode Processing

Hello Friends,

In my previous blog post, we have seen the SQL Server 2016 Sort operator with batch mode processing. To compare the performance for Batch mode versus Row mode for Sort operator, I used a trace flag 9347 which disables the batch mode processing for sort operator. Now the question is: Is there any […]

By |October 10th, 2016|Categories: Database Administration, Performance Tuning, SQL Server Internals & Architecture|Tags: , , |Comments Off on SQL Server 2016 – Trace Flag 9453 – Disable Batch Mode Processing

SQL Server 2016 – New Columns in sys.dm_exec_query_stats

Hello Friends,

There are lots of features and improvements announced by Microsoft in SQL Server 2016, one of them is adding new columns in sys.dm_exec_query_stats. These new columns capture information about threads and memory. First, I’ll show you the new added columns for collecting threads and Degree of parallelism level information.

Here total_dop represent the total […]

How to resolve High Compilations/Second problem in SQL Server

Hi Friends and Geeks!

Sharing an experience on resolution of High Compilations/Second performance counter in SQL Server from one of my consulting assignments.

The threshold for Compilations/Second is mentioned as 10% of Batch Requests/Second by various MVPs and in our case it was around 40 to 50%

Investigation Process

Investigate for queries with same query_hash but multiple plan_handles using […]

By |August 24th, 2016|Categories: Performance, Performance Tuning, Query Tuning, SQL Server||Comments Off on How to resolve High Compilations/Second problem in SQL Server

SQL Server Index Hint

SQL Server query optimizer decides which index to use, when a query is requested. SQL Server optimizer is a cost based optimizer so it picks the index which has low cost based on performance. When we design an index for a query, optimizer will utilize the defined index to generate the plan for requested […]

By |July 2nd, 2016|Categories: Performance Tuning, Query Tuning|Tags: , , |Comments Off on SQL Server Index Hint

Clustered Index seek doesn’t guarantee optimal Query plan

I was working on removing duplicate indexes from a table. Before removing an index usually I check whether that index is used by any query or not, is there any index hint is used, looking at index usage statistics and many other factors.

If you search in web you will get many ways to identify […]

By |June 16th, 2016|Categories: Indexes & Statistics, Performance Tuning, Programming, Query Tuning, SQL Server|Tags: , , , |Comments Off on Clustered Index seek doesn’t guarantee optimal Query plan

SQL Server 2016 – Database Scoped Configurations

Hi Friends,

As a DBA, we know about various Instance level settings in SQL Server like MAXDOP, Cardinality Estimator related changes and various Trace Flags etc. There may be some scenarios where you want to enable these kind of settings at the database level rather than instance level. For Example, I want to set MAXDOP […]

By |April 24th, 2016|Categories: Database Administration, Database Engine, Database Maintenance, Performance Tuning|Tags: , |Comments Off on SQL Server 2016 – Database Scoped Configurations
Newsletter_Footer