Performance tuning

Ahmad Yaseen

SQL Server 2016 Trace flags modifications

August 9, 2016 by

SQL Server Trace Flags are special switches that are used to customize and control specific behaviors of the SQL Server Engine. Trace Flags can be defined in two forms; Session Trace Flags that are activated and visible at the current connection level only, and Global Trace Flags that are enabled and visible at the SQL Server Instance level and applied to all connecting sessions in that SQL Server. Global Trace Flags should be enabled globally in order to take effect, where some Trace Flags that can be either Global or Session Trace Flags can be enabled in the appropriate scope, and its effect will appear on the defined level.

Read more »
Derik Hammer

Measuring Availability Group synchronization lag

August 9, 2016 by

With all of the high-availability (HA) and disaster recovery (DR) features, the database administrator must understand how much data loss and downtime is possible under the worst case scenarios. Data loss affects your ability to meet recovery point objectives (RPO) and downtime affects your recovery time objectives (RTO). When using Availability Groups (AGs), your RTO and RPO rely upon the replication of transaction log records between at least two replicas to be extremely fast. The worse the performance, the more potential data loss will occur and the longer it can take for a failed over database to come back online.

Read more »
Ahmad Yaseen

Force query execution plan using SQL Server 2016 Query store

July 29, 2016 by

SQL Server Query Store is a new feature introduced in SQL Server 2016 that is used to automatically and asynchronously capture query execution history, statistics and plans, with minimal impact to overall SQL Server Performance. The Query Store feature makes performance problem troubleshooting simple; you can view the query execution plans changes and compare its performance to decide which execution plan the SQL Server Query Optimizer should use for that query.

Read more »
Daniel Tikvicki

SSRS ReportServer: Service performance counters guide

July 27, 2016 by

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.

Read more »
Ahmad Yaseen

The SQL Server 2014 Resource Governor

July 15, 2016 by

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.

Read more »
Daniel Tikvicki

SSIS Pipeline performance counters guide

July 14, 2016 by

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.

Read more »
Nikola Dimitrijevic

Handling excessive SQL Server PAGEIOLATCH_SH wait types

July 5, 2016 by

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

Read more »
Daniel Calbimonte

Is this the end of SQL Profiler?

June 13, 2016 by

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.

Read more »
Nikola Dimitrijevic

Troubleshooting the CXPACKET wait type in SQL Server

June 8, 2016 by

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 parallel plan.

Read more »
Timothy Smith

Monitoring changes in SQL Server using change data capture

May 27, 2016 by

Background

In multi-user environments, changes may occur frequently to the architecture, data, or overall structure that creates work for other users. In this series, we look at some ways that we can track changes on the data and architecture layer for pin-pointing times, changes, and using the information for alerting, if changes should be kept to a minimum. SQL Server comes with some built-in tools that allow us to monitor changes, and depending on the architecture, we can create tools that allow us also to monitor and identify changes near the time that they occur.

Read more »
Timothy Smith

Troubleshooting some waits issues

May 10, 2016 by

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.

Read more »
Ahmad Yaseen

SQL Server 2014 Columnstore index

April 29, 2016 by

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.

Read more »
Ahmad Yaseen

SQL Server indexed views

March 17, 2016 by

SQL Server Views are virtual tables that are used to retrieve a set of data from one or more tables. The view’s data is not stored in the database, but the real retrieval of data is from the source tables. When you call the view, the source table’s definition is substituted in the main query and the execution will be like reading from these tables directly.

Read more »
Ed Pollack

Insight into the SQL Server buffer cache

February 18, 2016 by

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.

Read more »
Ed Pollack

Searching the SQL Server query plan cache

February 8, 2016 by

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!

Read more »
Page 5 of 10« First...34567...10...Last »