Log in | Register

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

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

SQL Server – ROW_NUMBER versus TOP N

Hello Friends,

This blog post is the result of question asked by some professionals working on databases. While writing T-SQL code they generally use TOP N in the code to get the top n number of rows in the result set. They knew the another way to achieve the same result by using ROW_NUMBER. Now the […]

Database Scoped Configurations and Trace flag 4199

Those who are working on query performance whether you are a DBA or Developer I am sure you guys must have used trace flag 4199.  TF 4199 enables all query optimizer related fixes.Microsoft used to have separate TF for each hotfix applied to query optimizer prior to SQL Server 2005 Service Pack 3 (SP3) Cumulative […]

By |April 16th, 2017|Categories: Database Administration, Database Development, Performance, SQL Server, Transact-SQL|Tags: , , , |Comments Off on Database Scoped Configurations and Trace flag 4199

Rightly Estimating the Azure DTU

As the move to Azure getting popular for SQL Server, the adoption involves a step to understand the costs associated with the migration – The measure of an Azure DTU. Yes, the most under-explained term of the Azure world. Azure DTU is the term, heard so often and yet, unless shown on your bill, very […]

By |April 3rd, 2017|Categories: Database Administration, Performance|Tags: , , , , |Comments Off on Rightly Estimating the Azure DTU

CAST function doesn’t change execution plan for Datetime type column

I have been reviewing code from last couple of years. I found some myths are followed very strictly by developers. Yesterday while reviewing, I found developers are hesitating to use TRY_CAST or CAST function on column in where clause to convert date to datetime or datetime to date. When I asked the reason they […]

DBCC CloneDatabase

This blog was due from a long time.  With the release of SQL Server 2014 Service pack 2, a new DBCC command included that is DBCC CloneDatabase. My primary job role is to do query tuning and I was looking for this kind of functionality. Using this command, it is quite easy   to copy […]

By |March 15th, 2017|Categories: Database Administration, Performance, Transact-SQL|Tags: , , |Comments Off on DBCC CloneDatabase

Trace Flag 634 – Disable Background Columnstore Compression

Hi Friends,

In Columnstore Technology, we knows that Column data stores in segments and segments groups together to form the rowgroup. One of the main reason of using Columnstore technology is compression of rowgroups. By default, SQL Server perform compression of closed delta stores. This tasks is done by tuple mover. Which invokes after a […]

By |December 26th, 2016|Categories: Compression, Database Administration, Indexes & Statistics, Performance|Tags: , |Comments Off on Trace Flag 634 – Disable Background Columnstore Compression

SQL Server 2014 – SELECT INTO Statement

Hi Friends,

Microsoft introduced so many new features and improvements with SQL Server 2014. In addition to these features, one of the improvement provided in SQL Server 2014 is about SELECT INTO statement. The SELECT INTO  generally used to copy the data from one table and inserts that data into a new table.

Before SQL Server […]

By |December 20th, 2016|Categories: Database Development, Database Engine, Performance|Tags: , |Comments Off on SQL Server 2014 – SELECT INTO Statement

SQL Server 2016 – Sort Operator with Batch Mode Processing

Hello Friends,

There are lots of features and improvements announced by Microsoft in SQL Server 2016, one of them is providing the sort operator with batch mode processing. In the programming world, we know that  performing row by row processing take longer time as compared to perform the same processing in batch mode. If the […]

SQL Sever 2016 – DBCC CHECKDB WITH MAXDOP

Hello Friends, There are lots of feature and improvements announced by Microsoft in SQL Server 2016, one of them is restricting the number of processors for DBCC CHECKDB. We know that MAXDOP setting is generally taken care by Instance level setting ‘MAX Degree of Parallelism’ under advanced tab of server property. I’ll show you the […]

By |September 18th, 2016|Categories: Database Administration, Database Maintenance, Performance, SQL Server|Tags: , |Comments Off on SQL Sever 2016 – DBCC CHECKDB WITH MAXDOP

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
Newsletter_Footer