Performance tuning

SSRS ReportServer: Service performance counters guide

July 27, 2016 by Daniel Tikvicki

SSRS performance counters

Measurements of the Reporting Services service (SSRS) monitoring cycle show which resources the reporting process consumes, and also, specific sets of counters show the particular type of the reporting process deployment in use, Native and SharePoint mode. The entire reports processing occurs in the Report Server, which is the core element of SSRS architecture, and among all features, collaboration with SharePoint platform is the most crucial, because of advantages of report processing and generating reports for SharePoint components.

The SQL Server 2014 Resource Governor

July 15, 2016 by Ahmad Yaseen

SQL Server Resource Governor was introduced in SQL Server 2008. This feature is used to control the consumption of the available resources, by limiting the amount of the CPU, Memory and IOPS used by the incoming sessions, preventing performance issues that are caused by resources high consumption.

SSIS Pipeline performance counters guide

July 14, 2016 by Daniel Tikvicki

SSIS Pipeline performance counters

SSIS Pipeline performance counters monitor the processes which are related to the execution of packages and the Data flow engine’s the most crucial feature, the (Data) Pipeline. Their measurements reveal in which way the memory resources are acquired during the execution of the SSIS packages, and also, show the amount of memory used during that events. The proficient monitoring of the SSIS Pipeline memory usage can mitigate the potential issues of memory and data leakage, data transformation interruptions and overall, avoid data integration damage.

Handling excessive SQL Server PAGEIOLATCH_SH wait types

July 5, 2016 by Nikola Dimitrijevic

One of the most common wait type seen on SQL Server and definitely one that causes a lot of troubles to less experienced database administrators is the PAGEIOLATCH_SH wait type. This is one of those wait types that clearly indicates one thing, but which background and potential causes are much subtler and may lead to erroneous conclusions and worse, incorrect solutions

SSIS Service performance counter guide

July 4, 2016 by Daniel Tikvicki

SSIS performance counters

Although there is no real standardized classification of SQL Server Integration Services performance counters, the two main types are:

Query optimizer changes in SQL Server 2016 explained

June 17, 2016 by Ed Pollack

Description

SQL Server 2016 includes a variety of query optimizer enhancements. Some of these have existed since the first previews while others were added later. This is an opportunity to discuss, test out, and validate the behavior and benefits of these changes!

Best practices to configure the index create memory setting in SQL Server

June 15, 2016 by Ashish Kumar Mehta

Introduction

The Index Create Memory setting is an advanced SQL Server configuration setting which the SQL Server database engine uses to control the amount of maximum memory which can be allocated towards the creation of an index. In this article, we will take a look at the steps to resolve the below mentioned error message.

Is this the end of SQL Profiler?

June 13, 2016 by Daniel Calbimonte

Introduction

SQL Server Profiler is still a tool used to monitor our relational databases and our multidimensional ones. We used for performance and security purposes. However, in the SQL Server 2016, they announced that the SQL Profiler will be deprecated in future versions.

Troubleshooting the CXPACKET wait type in SQL Server

June 8, 2016 by Nikola Dimitrijevic

The SQL Server CXPACKET wait type is one of the most misinterpreted wait stats. The CXPACKET term came from Class Exchange Packet, and in its essence, this can be described as data rows exchanged among two parallel threads that are the part of a single process. One thread is the “producer thread” and another thread is the “consumer thread”. This wait type is directly related to parallelism and it occurs in SQL Server whenever SQL Server executes a query using the parallel plan.

How to handle excessive SOS_SCHEDULER_YIELD wait type values in SQL Server

June 8, 2016 by Nikola Dimitrijevic

The SQL Server SOS_SCHEDULER_YIELD is a fairly common wait type and it could indicate one of two things:

Troubleshooting some waits issues

May 10, 2016 by Timothy Smith

Background

On occasion, I’ll see waits that exceed what I expect well above normal and a few of them have some architecture and standards to consider following when troubleshooting, though like most waits’ issues, there can be other underlying factors that are happening as well. In this article, I investigate the three waits ASYNC_NETWORK_IO and WRITELOG. In general, waits vary by environment and server, so before reading this article an immediate question to ask is, “Do you know what’s normal for yours?” When a wait suddenly spikes, or if the architecture is designed in a manner that should prevent a specific wait from consuming time, and yet you see that the wait does, I would be concerned. In addition, because applications and environments differ by architecture, you may want to consider other troubleshooting steps, as these may not apply to your situations.

How to build better alerting

May 4, 2016 by Timothy Smith

Background

One of the most popular complaints from developers to DBAs involves alerting, whether from third party tools or alerting built by other developers or DBAs in the environment. Building or using alerts for important applications, data layers, or processes within a SQL Server environment offer everyone benefits, but can become noisy if they’re architected poorly, or the purpose isn’t considered. In this article, we look at considerations for building effective alerts that tell us when something is wrong without creating situations where we learn to disregard them. We want to make sure that we respond when we need to, and not always be on high alert when there is no issue.

SQL Server 2014 Columnstore index

April 29, 2016 by Ahmad Yaseen

By default, SQL Server stores data logically in the tables as rows and columns, which appear in the result grid while retrieving data from any table and physically in the disk in the row-store format inside the data pages. A new data store mechanism introduced in SQL Server 2012, based on xVelocity in-memory technology, in which the data is stored in the column-store data format. This data store mechanism called the Columnstore index.

Using SQL Server cursors – Advantages and disadvantages

March 23, 2016 by Sergiu Onet

Intro

In relational databases, operations are made on a set of rows. For example, a SELECT statement returns a set of rows which is called a result set. Sometimes the application logic needs to work with a row at a time rather than the entire result set at once. In T-SQL, one way of doing this is using a CURSOR.

Insight into the SQL Server buffer cache

February 18, 2016 by Ed Pollack

When we talk about memory usage in SQL Server, we are often referring to the buffer cache. This is an important part of SQL Server’s architecture and is responsible for the ability to query frequently accessed data extremely fast. Knowing how the buffer cache works will allow us to properly allocate memory in SQL Server, gauge accurately how databases are accessing data, and ensure that there are not inefficiencies in our code that cause excessive data to be cached.

Searching the SQL Server query plan cache

February 8, 2016 by Ed Pollack

Whenever a query is executed in SQL Server, its execution plan, as well as some useful execution data are placed into the plan cache for future use. This information is a treasure trove of metrics that can allow some very useful insight into your server’s performance and resource consumption. Much of this information would be difficult or impossible to acquire otherwise.

Understanding how to access and use the metadata about query execution will provide us the tools we need to answer questions about our server and gain fascinating performance data. I’ve found myself spending more and more time writing, tweaking, and using queries against the plan cache lately and look forward to sharing these adventures with you!

What is causing database slowdowns?

February 2, 2016 by Kimberly Killian

Why is my database so slow? This query used to be so much faster. Why does it take so long to rebuild my index? How come it was fine last month? Every day I am asked these types of questions by clients. Every day! A lot of database developers and application developers do not realize that indexes are ever changing entities within your database or rather they need to be monitored closely and managed periodically to remain efficient. I cannot even count the times someone tells me “but we have index’s on this or that column and it was fine last month” and so on. All while they fail to realize or even tell me that the database just took on, updated or deleted 1,000,000 records for example, which would definitely change the footprint of the data, making the index’s unsound or in need of help. Even adding 50 new users that use the data differently could require new indexes. That being said, I decided to automate a quick and easy data gathering and reporting job that helps to answer these questions. Most of the time query performance questions can be answered by determining the fragmentation levels of index’s, if there are missing index’s, duplicate index’s, unused index’s and what are the heavy hitters in regards to queries and are queries running in memory or to disk and how many executions. My favorite thing to do with SQL Server is automate, automate and automate the tasks that are asked of me over and over.

In-Memory OLTP Series – Data migration guideline process on SQL Server 2016

January 29, 2016 by Luan Moreno M. Maciel

On the last article about the best modes to move the disk-based tables to using the In-Memory feature we covered all the aspects and styles available on SQL Server 2014. Continuing on the migration process now we’re going to look at some of the new enhancements of makes SQL Server 2016

How to run a local SQL Report from an Azure VM Database

January 28, 2016 by Daniel Calbimonte

Introduction

In this new article, we will create a SQL Server Report using SQL Server Reporting Services from a SQL Server machine installed in an Azure Virtual Machine. We will show step by step how to connect from a local machine to a VM Azure Database and generate a SQL report.

In-Memory OLTP Series – Data migration guideline process on SQL Server 2014

January 28, 2016 by Luan Moreno M. Maciel

In this article we will review migration from disk-based tables to in-memory optimized tables. This article assumes that you already understand the pros and cons about In-Memory Technology, for more articles about this, please refer here.

There are some options available on SQL Server 2014 and SQL Server 2016 that will help you to identity, discover and track the tables and stored procedures that can be good candidates to be ported to memory. Furthermore it’s possible to check if the table meets to all the pre-requisites established. Here is the list of the features that we will take a look into.

SQL Server non-clustered indexes with included columns

January 11, 2016 by Ahmad Yaseen

SQL Server indexes are created to speed up the retrieval of data from the database table or view. The index contains one or more columns from your table. The structure of these keys are in the shape of B-tree distribution, enabling SQL Server to find the data quickly.

Tips and tricks for SQL Server database maintenance optimization

January 11, 2016 by Miroslav Dimitrov

Database maintenance is very important, a critical part of our database administrators’ daily tasks. However, this aspect is frequently underestimated which could lead to performance problems and respectively angry, unhappy customers. In this article, we will take a look at the different maintenance operations we have in SQL Server and how we can optimize them and take the maximum out of each.

Monitor the Query timeout expired message from a SQL Server Agent job

January 11, 2016 by Ahmad Yaseen

SQL Server provides you with a good solution to automate a lot of your administrative tasks using the SQL Server Agent jobs. These jobs are handled from the operating system side by a Windows service that is responsible for executing these jobs and feeding the SQL Server systems tables with the metadata about these jobs. The system database that is used by the SQL Server Agent for the job management called the msdb database. All information related to the job steps, schedules and the history can be found in the msdb database tables. The msdb system database is also responsible for the SQL Server Mail, Service Broker, SQL Server Maintenance Plans and the databases backup history.

The tempdb database, introduction and recommendations

January 5, 2016 by Daniel Calbimonte

Introduction

Many times the people focus on the user databases to improve the performance, but sometimes the problem is not the user database itself. Sometimes the problem is the tempdb.

In this article, we will give a brief introduction about the tempdb database, show how to create some temporary objects there and show how to improve and monitor it.

Construct a special multi-statement table function for checking SQL Server’s health

December 24, 2015 by Eli Leiba

The Problem

Checking the many SQL Servers health conditions is one of the DBA basic tasks. Monitoring many servers and databases, day after day can be a very tedious job. There are many aspects to look for when checking the server performance figures. Such aspects include among others: CPU, Read and Write Disk counters, Various memory counters and locks performance counters. The DBA needs a tool that checks the most important counters and output a report that states whether the server is considered to be healthy or not. For every aspect, a counter is chosen to represent it to be included in the overall report.