Performance

Importance of SQL Server Max Degree of Parallelism

April 21, 2020 by Jignesh Raiyani

In this article, we will discuss how the Max Degree of Parallelism works in SQL Server and how does it improve the query performance. SQL Server Degree of Parallelism is the processor conveyance parameter for a SQL Server operation, and it chooses the maximum number of execution distribution with the parallel use of different logical CPUs for the SQL Server request. Microsoft SQL Server allows setting this Degree of Parallelism parameter value at the SQL Server instance level or Query level. If you do not specify the SQL Server Degree of Parallelism value at the SQL Server instance, then each request or operation has to rely on SQL Server default value and random CPU allocations.

SQL Server Hardware Performance Tuning

April 14, 2020 by Jignesh Raiyani

SQL Server Performance Tuning can be a difficult assignment, especially when working with a massive database where even the minor change can raise a significant impact on the existing query performance. Performance Tuning always plays a vital role in database performance as well as product performance. A query can be optimized with the proper evaluation of a piece of code or SQL Statements. The important thing in Query Optimization is to understand, which process or expression needs evaluation in the piece of code. Everyone expects to get a quick response from the query on the production, but How?

SQL Server Update Statistics using database maintenance plans

April 13, 2020 by Rajendra Gupta

This article explores SQL Server Update Statistics using the database maintenance plan.

Understanding the deadlock definition in SQL Server

April 7, 2020 by Esat Erkec

This article explains the deadlock definition in SQL Server, and it also mentions how to capture deadlocks with extended events.

Table-Valued Parameters in SQL Server

April 6, 2020 by Esat Erkec

Table-Valued Parameters aka TVPs are commonly used to pass a table as a parameter into stored procedures or functions. They are helpful in a way, we can use a table as an input to these routines and we can get rid of dealing more complex steps to achieve this process.

Database Design and Logical Asseveration for SQL Query Optimization

March 11, 2020 by Jignesh Raiyani

Database design and Logical Asseveration play a vital role in database performance and SQL Query optimization. Both have different parameters to make your database and the query accurate.

Overview of Non-Clustered indexes in SQL Server

January 16, 2020 by Rajendra Gupta

This article gives an introduction of the non-clustered index in SQL Server using examples.

How to detect and prevent unexpected growth of the TempDB database

January 13, 2020 by Ahmad Yaseen

In this article, we will discuss the best practices that should be followed in order to keep the TempDB database in a healthy state and prevent any unexpected growth of the database, in addition to the procedure that can be followed to detect this unexpected growth once occurred.

Using SQL CREATE INDEX to create clustered and non-clustered indexes

January 10, 2020 by Ben Richardson

The SQL CREATE INDEX statement is used to create clustered as well as non-clustered indexes in SQL Server. An index in a database is very similar to an index in a book. A book index may have a list of topics discussed in a book in alphabetical order. Therefore, if you want to search for any specific topic, you simply go to the index, find the page number of the topic, and go to that specific page number. Database indexes are similar and come handy. Particularly, if you have a huge number of records in your database, indexes can speed up the query execution process. There are two major types of indexes in SQL Server: clustered indexes and non-clustered indexes.

Overview of SQL Server Clustered indexes

December 23, 2019 by Rajendra Gupta

This article targets the beginners and gives an introduction of the clustered index in SQL Server.

Replace a SQL While loop and a cursor with ranking functions in SQL Server for better query performance

December 19, 2019 by Jignesh Raiyani

SQL While loop and cursor are the most common approach to repeat a statement on condition-based or determined limits. Loop and cursor can be utilized in a circumstance to deal with row-based processing in T-SQL. We generally observe moderate execution of old made procedures, which are composed of using loop and cursors. Those procedures take time, especially when the number of iteration count is big for the execution.

Query Performance Issues on VARCHAR Data Type Using an N Prefix

December 6, 2019 by Jignesh Raiyani

In this article, we’ll discuss data type VARCHAR and query performance issues associated with utilizing the lower level VARCHAR data type. CHAR, VARCHAR and NVARCHAR are data types that support storing information in text format in a SQL Server database. These data types allow a wide assortment of character sets in the defined field or column in the database table.

Forwarded Records Performance issue in SQL Server

December 5, 2019 by Rajendra Gupta

This article discusses the Forwarded Records and its performance issues for heap tables in SQL Server.

SQL Table Variable Deferred Compilation in SQL Server 2019

November 25, 2019 by Rajendra Gupta

In an article, An overview of the SQL table variable, we explored the usage of SQL table variables in SQL Server in comparison with a temporary table. Let’s have a quick recap of the table variable:

SQL Server Database Metrics

October 2, 2019 by Ed Pollack

Summary

There is a multitude of database metrics that we can collect and use to help us understand database and server resource consumption, as well as overall usage.

This data can include hardware statistics, such as measures of CPU or memory consumed over time. We can also examine database metadata, including row counts, waits, and deadlocks.

Using SQL Server Database Metrics to Predict Application Problems

September 27, 2019 by Ed Pollack

Summary

Database metrics can be collected, maintained, and used to help predict when processes go awry so problems can be resolved before they become severe. Understanding when an application or process misbehaves is not always easy. We are often left waiting until a server, application, or service breaks or enters an undesirable state before we know something is wrong. At that point, we are forced to rush and resolve a production problem quickly, before its impact becomes severe.

An Overview of Instant File Initialization in SQL Server

September 25, 2019 by Rajendra Gupta

This article gives an overview of Instant File Initialization and its benefits for SQL Server database creation, restoration and file growth.

SQL Server Index Fill factor with a Performance Benchmark

September 9, 2019 by Jignesh Raiyani

In this article, we will study in detail about the how SQL Server Index Fill factor works.

SET NOCOUNT ON statement usage and performance benefits in SQL Server

August 26, 2019 by Rajendra Gupta

Have you ever noticed SET NOCOUNT ON statement in T-SQL statements or stored procedures in SQL Server? I have seen developers not using this set statement due to not knowing it.

sp_updatestats overview and usage

August 20, 2019 by Esat Erkec

In this article, we will learn usage details of the sp_updatestats built-in store procedure which helps to update all statistics in a SQL Server database. First of all, we will take a glance at the statistics concept in SQL Server.

Performance Monitoring via SQL Server Query Store

August 19, 2019 by Ben Richardson

SQL Server Query Store is a performance monitoring tool that helps us evaluate the performance of a SQL query in terms of several different performance metrics such as CPU and Memory Consumption, execution time and the I/O cycles consumed by the query. Query store is similar to the windows “Task Manager”. A task manager provides information about the CPU, Memory, Network and Disc consumption of a process. Similarly, the Query Store provides insight to similar information.

Memory-Optimized TempDB Metadata in SQL Server 2019

July 10, 2019 by Ed Pollack

Introduction

In-memory technologies are one of the greatest ways to improve performance and combat contention in computing today. By removing disk-based storage and the challenge of copying data in and out of memory, query speeds in SQL Server can be improved by orders of magnitude.

Optimizing SQL Server index strategies

July 2, 2019 by Bojan Petrovic

Index strategies overview

This article is about techniques for optimizing the SQL Server indexes strategy. It is an appendix of the SQL index overview and strategy article in which I covered different areas like what indexes actually do, how to create them, and I briefly mentioned some index design guidelines. Furthermore, I also presented an example of how to design them by tuning and optimizing queries, so I’ve really tried to cover all but there is always more when it comes to SQL Server indexes.

Clustered Index vs. Heap in SQL Server

June 21, 2019 by Ed Pollack

Summary

There are few topics so widely misunderstood and that generates such frequent bad advice as that of the decision of how to index a table. Specifically, the decision to use a heap over a clustered index is one where misinformation spreads quite frequently.

SQL Server Statistics in Always On Availability Groups

May 27, 2019 by Rajendra Gupta

Introduction to SQL Server Statistics

SQL Server Statistics are an essential part of query performance in SQL Server. They help the query optimizer to prepare a query plan using the distribution of values in participating rows. If we do not have updated statistics, it might lead to resource intensive query execution plan. For example, for a large table having a billion records, SQL may choose to have an index scan instead of an index seek.