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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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%
Investigate for queries with same query_hash but multiple plan_handles using […]
Today, I am going to show you an interesting thing about SQL Server 2016 – Instant File Initialization. We all knew about the Instant file Initialization feature. For those who are not aware about it can click here.
As a DBA, we know the importance of this feature. Instant File Initialization feature helps SQL […]