Performance tuning

Memory-Optimized Table Variables in SQL Server

May 6, 2020 by Esat Erkec

This article will cover the usage details and performance advantages of the memory-optimized table variables.

View Execution Plans in Azure Data Studio

April 24, 2020 by Rajendra Gupta

This article gives an overview of viewing execution plans in the Azure Data Studio.

Getting started with SQL Server Query Tuning

April 21, 2020 by Esat Erkec

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.

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.

Monitoring activities using sp_WhoIsActive in SQL Server

March 16, 2020 by Hadi Fadlallah

In this article, we will talk about sp_WhoIsActive stored procedure and how we can use it to monitor currently running activities in SQL Server.

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.

Execution Plans in SQL Server

March 10, 2020 by Aveek Das

Introduction

In this article, I’m going to explain what the Execution Plans in SQL Server are and how to understand the details of an execution plan by reading the various metrics available once we hover over the components in the plan.

SQL AVG() function introduction and examples

March 2, 2020 by Esat Erkec

In this article, we will learn the SQL Average function which is known as AVG() function in T-SQL. AVG() function is an aggregate function that calculates the average value of a numerical dataset that returns from the SELECT statement.

SQL Server Always On Availability Groups Interview Questions & Answers

February 28, 2020 by Ahmad Yaseen

In this article, we will discuss a number of interview questions that you could be asked about the SQL Server Always On Availability Groups feature when attending an interview for the SQL Server database administrator role.

A quick overview of In-Memory OLTP in SQL Server

February 12, 2020 by Prashanth Jayaram

This is in continuation of the previous articles How to monitor internal data structures of SQL Server In-Memory database objects and SQL Server In-Memory database internal memory structure monitoring.

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.

Top five considerations for SQL Server index design

January 13, 2020 by Ahmad Yaseen

In this article, we will discuss the most important points that we should consider when designing an optimal SQL index. Before going through the index design procedure, let us revise the SQL Server index concept.

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.

An overview of sp_getapplock and sp_releaseapplock stored procedures

January 9, 2020 by Nisarg Upadhyay

This article explains about the sp_getapplock and sp_releaselock stored procedures and their usage with example.

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.

An overview of the SQL Server Profiler

December 9, 2019 by Hadi Fadlallah

In this article, we will describe SQL Server Profiler and how it can be used. In addition, we will mention the tools that are integrated with it- plans about its deprecation, and what are the alternatives?

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.

Audit and Alert SQL Server Jobs Status Changes (Enabled or Disabled)

December 4, 2019 by Jignesh Raiyani

In this article, we will talk about how to track enabled or disabled SQL jobs in SQL Server using T-SQL. Users with the Sysadmin role have the default permissions to modify the information of any jobs in SQL Server. If a user is not in this role and wants access to this activity, then the user needs to be given the SQLAgentOperatorRole in the msdb database.