Performance

Esat Erkec
Performance comparison Batch mode vs Row mode

SQL Server 2019 new features: Batch Mode on Rowstore

May 27, 2020 by

In this article, we will explain batch mode on rowstore feature, which was announced with SQL Server 2019. The main benefit of this feature is that it improves the performance of analytical queries, and it also reduces the CPU utilization of these types of queries. Behind the scene, this performance enhancement uses the batch mode query processing feature for the data, which is stored in row format. Also, this feature has been using by columnstored indexes for a long time.

Read more »
Jignesh Raiyani
Rebuild Index

How to identify and resolve SQL Server Index Fragmentation

May 27, 2020 by

In this article, we will learn how to identify and resolve Index Fragmentation in SQL Server. Index fragmentation identification and index maintenance are important parts of the database maintenance task. Microsoft SQL Server keeps updating the index statistics with the Insert, Update or Delete activity over the table. The index fragmentation is the index performance value in percentage, which can be fetched by SQL Server DMV. According to the index performance value, users can take the indexes in maintenance by revising the fragmentation percentage with the help of Rebuild or Reorganize operation.

Read more »
Esat Erkec
SQL Server Query Tuning: Use the UNION ALL operator instead of to UNION operator

Getting started with SQL Server Query Tuning

April 21, 2020 by

This article will cover some essential techniques for SQL query tuning. Query tuning is a very wide topic to talk about, but some essential techniques never change in order to tune queries in SQL Server. Particularly, it is a difficult issue for those who are a newbie to SQL query tuning or who are thinking about starting it. So, this article will be a good starting point for them. Also, other readers can refresh their knowledge with this article. In the next parts of this article, we will mention these techniques that help to tune queries.

Read more »
Jignesh Raiyani
Thread distribution with SQL Server Degree Parallelism

Importance of SQL Server Max Degree of Parallelism

April 21, 2020 by

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.

Read more »
Jignesh Raiyani

SQL Server Hardware Performance Tuning

April 14, 2020 by

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?

Read more »
Ben Richardson
Example Of Records Using A Clustered Index

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

January 10, 2020 by

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.

Read more »
Jignesh Raiyani
Ranking Function Query with Result

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

December 19, 2019 by

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.

Read more »
Jignesh Raiyani

Query Performance Issues on VARCHAR Data Type Using an N Prefix

December 6, 2019 by

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.

Read more »
Ed Pollack
Execution plan when using a system DMV instead of COUNT.

SQL Server Database Metrics

October 2, 2019 by

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.

Read more »
Ed Pollack
Row Count Metrics

Using SQL Server Database Metrics to Predict Application Problems

September 27, 2019 by

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.

Read more »