Performance tuning

Esat Erkec

Load testing for SQL Server Reporting Services (SSRS)

September 25, 2018 by

Performance monitoring is a very significant and essential topic for database or system administrators. This is no different with SSRS load testing. In some cases, performance problems can lead to significant system downtime. Most of the time in the background of the problem is having insufficient resources or improper settings. Therefore, before experiencing performance issues, we have to take some time to review preventive measures related to current system configurations or new installations. The load test (or stress test) is the most commonly used approach to tune systems before real-life staging conditions. Now we will discuss the details of load test, and in particular SSRS load testing.

Read more »
Ahmad Yaseen

SQL Server Execution Plan Operators – Part 1

September 12, 2018 by

In the previous articles of this series, SQL Server Execution Plans overview , SQL Server Execution Plans types and How to Analyze SQL Execution Plan Graphical Components, we discussed the steps that are performed by the SQL Server Relational Engine to generate the Execution Plan of a submitted query and the steps performed by the SQL Server Storage Engine to retrieve the requested data or perform the requested modification operation.

Read more »
Ahmad Yaseen

How to Analyze SQL Execution Plan Graphical Components

September 7, 2018 by

In the previous articles of this series, SQL Server Execution Plans overview and SQL Server Execution Plans types we went through the different stages that the submitted SQL Server query followed and how it processed by the SQL Server Relational Engine that generates the Execution Plan and the SQL Server Storage Engine that performs the requested data retrieval or modification operation. In addition, we described deeply the different types and formats of the SQL Server Execution Plans that can be used for queries performance troubleshooting purposes. In this article, we will discuss the graphical query plan components and how to analyze it.

Read more »
James Rhoat

Performance troubleshooting when the query plan from the application is different than SSMS

August 24, 2018 by

Troubleshooting performance issues in a database is one of the main jobs of DBAs and by now most can trace the problem back to a query which is either running to slow or is causing a blocking issue on a key table. However, what is often not known is why this doesn’t cause problems in SSMS or why you don’t get the same query plan as what is inside the app. For example, in your extended event trace you see the query running longer from the application when compared to SSMS.

Read more »
Bojan Petrovic

How to collect performance and system information in SQL Server

August 16, 2018 by

In this article, we’re going to through many of the tools we can use for monitoring SQL Server. SQL Server is chock-full of lots of good reports that allows a DBA to quickly spot whether there is any current performance bottleneck on the SQL Server. Many of these sit on top of DMVs but they give us a visually interactive way to look and work with the data. We’re going to start with SQL Server Performance Dashboard Reports.

Dashboard Reports

Let’s jump into SQL Server Management Studio (SSMS) and the first thing we’re going to take you through out-of-the-box dashboard reports of all levels. They can be found by right-clicking the SQL Server instance in Object Explorer, and from the context menu, you’ll find Reports > Standard Reports:

Read more »
Ahmad Yaseen

SQL Server Execution Plans types

July 23, 2018 by

In the previous article, we described, in detail, the different stages that a submitted SQL Server query goes through and how it processed by the SQL Server Relational Engine. The SQL Server Relational Engine generates the Execution Plan and the SQL Server Storage Engine performs the requested data retrieval or modification process. In this article, we will discuss the different types and formats for SQL Server Execution Plans.

Read more »
Aamir Syed

Use cases for Query Store in SQL Server

July 18, 2018 by

Query store was introduced in SQL Server 2016. It is often referred to as a “flight data recorder” for SQL Server. Its main function is that it captures the history of executed queries as well as certain statistics and execution plans. Furthermore, the data is persistent, unlike the plan cache in which the information is cleared upon a server restart or reboot. You can customize, within Query Store, how much and how long the query store can hold the data.

Read more »
Esat Erkec

Main Concepts of SELECT operators in SQL Server execution plans

July 9, 2018 by

One of the main responsibilities of a database administrator is query tuning and troubleshooting query performance. In this context, SQL Server offers several tools to assist. But among them, query execution plans are essential for query optimization because they include all of the vital information about the query execution process. At the same time as it provides this valuable information “under the hood”, SQL Server creates a graphical description of the execution plan. Read more »

Ahmad Yaseen

SQL Server Execution Plans overview

July 4, 2018 by

In this series of articles, we will navigate the SQL Server Execution Plan ocean, starting from defining the concept of the Execution Plans, walking through the types, components and operators of Execution Plans analyze execution plans and we’ll finish with how to save and administrate the Execution Plans.

When you submit a T-SQL query, you tell the SQL Server Engine what you want, but without specifying how to do it for you. Between submitting the T-SQL query to the SQL Server Database Engine and returning the query result to the end user, the SQL Server Engine will perform four internal query processing operations, to convert the query into a format that can be used by the SQL Server Storage Engine easily use to retrieve the requested data, using the processes assigned to the SQL Engine from the Operating System to work on the submitted query.

Read more »
Ahmad Yaseen

SQL Server indexes – series intro

June 29, 2018 by

Description

In this series, we will dive deeply in the SQL Server Indexing field, starting from the surface by understanding the internal structure of the SQL Server tables and indexes, going deeper by describing the guidelines and best practices that we can follow to design the most efficient index and what operations can be performed on the created indexes. Having these knowledges about the SQL Server indexes, we have all the tools that help us in testing the lower part of the ocean and dive deeper with the two main types of the SQL Server Indexes; the Clustered and Non-Clustered, and the other types of indexes that can be customized to serve us improving your environment. After that, the adventure becomes more interesting when learning how to use this knowledge to tune the performance of our queries and touch the bottom of the ocean. In our way back to the surface, and before celebrating our achievements, we will collect statistical information about these indexes and use this information to maintain the indexes to take benefits from it continuously and gain the best application performance.

Read more »
Ahmad Yaseen

Top 25 SQL interview questions and answers about indexes

June 25, 2018 by

Q1: What is the difference between a Heap table and a Clustered table? How can we identify if the table is a heap table?

Heap table is a table in which, the data rows are not stored in any particular order within each data page. In addition, there is no particular order to control the data page sequence, that is not linked in a linked list. This is due to the fact that the heap table contains no clustered index.

Read more »
Nikola Dimitrijevic

How to handle the SQL Server WRITELOG wait type

June 13, 2018 by

The WRITELOG wait type is one of those wait types that can often be seen quite frequently on SQL Server, and that can cause a lot of headaches for DBAs. The WRITELOG wait time represents the time that accumulates while waiting for the content of the transaction log cache to be flushed to the physical disk that stores the transaction log file. To understand better the WTITELOG wait type, there are some basics of SQL Server mechanism for storing the data in the transaction log file is to be explained first

Read more »
Nikola Dimitrijevic

SQL Server performance counters (Batch Requests/sec or Transactions/sec): what to monitor and why

June 5, 2018 by

When maintaining SQL Server, it is essential to get an accurate perception of how busy it is. Two metrics that are often considered as indicators of how busy SQL Server is are Batch Requests/sec and Transaction/sec. When those metrics trend higher, they often affect all other metrics and make them go higher as well. While they could look similar, they are using a different type of starting point for measurement; the batches and transactions. So, to correctly understand those important metrics, lets first try to understand what the batches and transactions in SQL Server are and what are the differences between the two

Read more »