Performance

Esat Erkec
Read I/O and Write I/O sections of the performance test

Using Diskspd to test SQL Server Storage Subsystems

July 28, 2020 by

In this article, we will learn how to test our storage subsystems performance using Diskspd. The storage subsystem is one of the key performance factors for SQL Server because SQL Server storage engine stores database objects, tables, and indexes on the physical files. Therefore, the storage engine always interacts with the disk subsystem because of data processing. In this context, when a bottleneck occurs on the storage subsystems, it causes a negative impact on SQL Server performance. It would be the right approach to measure the performance of the disks to be used before the SQL Server installation based on their usage purposes. For example, OLTP databases have to complete delete, insert, and update processes in a short time but OLAP databases handle a huge amount of batch data. In this case, the storage requirement of these two database systems should differ from each other. In short, it is a best practice to test and analyze the performance of the storage subsystems according to their usage purposes so that we can eliminate the I/O problems in advance.

Read more »
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 »