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 […]
My today’s blog post is focused on trace flag 1117 changes in SQL Server 2016. We all know that there are multiple trace flags in SQL Server. These trace flags are generally used to change the default behavior of SQL Server. One of these trace flag is 1117. This trace flag is generally […]
My today’s blog post is focused on the correct cardinality estimation for table variable using trace flag 2453. In one of my previous blog post, we have seen the use of OPTION (RECOMPILE) query hint for correct cardinality estimation in case of table variable. Now the question is, Is there any way to […]
Various new features and improvements are coming with SQL Server 2016. Various performance improvements are also added to the CTP versions. Today, I’ll show you one of them i.e. Alter Table … Alter Column online operation.
Blocking is one of the major problems when you work on performance tuning for SQL Server. We knows […]
In my previous blog post, I have shown you the working of nested loop join in SQL Server. Today, I am going to show you the Nested Loop Join – Temp Table versus Table variable.
Before executing a query, SQL Server generates an estimated execution plan on the basis of various server settings and […]
We know about parallelism in SQL Server i.e. SQL Server uses multiple threads for the processing of a request to make it faster. Two main important settings for the decision of going with parallelism are:
MAXDOP: Maximum degree of parallelism, This value represents, how many threads can be used for parallel processing.
The Cost threshold […]
First of all wish you all a very Happy New Year 2016. I hope this new year will bring lots of joy and happiness in your life. Today, I’ll show you the Forward and Backward Scan.
We all knew that whenever we create an index (Clustered or Non-Clustered) on a table in SQL Server […]
There is a mechanism known as Read-Ahead Reads in SQL Server to read the pages in a buffer before getting the request from query. By default, this feature is enabled in the SQL Server. Have you ever tested the performance impact in terms of IO and Execution time? So today I am going […]