The article How to detect whether index fragmentation affects SQL Server performance explains how and in what cases the Index fragmentation affects SQL Server performance, and when a DBA should and should not have to deal with fragmented indexes. This article will deal with the situation when Index fragmentation affects SQL Server performance and has to be dealt with
May 5, 2017
Insight on replication agent status can be collected manually or programmatically. This article will consider both cases, and also when a replication agents’ status will be determined in relation with all publications, or a particular one.
February 24, 2017
Every database in a SQL Server environment has two basic states: full availability (online state) or full unavailability (offline state).
February 20, 2017
In general, the term “failover” refers to switching from a previously active machine, other hardware component, or network to a passive (or unused) one, to sustain high availability and reliability. In most cases, a failover event is an automatic process, while a the similar event, switchover, requires manual intervention in switching between the active/passive elements.
January 13, 2017
The core component of SQL Server is the Database Engine service. It is identified with the Windows service named MSSQLSERVER (sqlservr.exe) for the default instance, and for the named instance, the customized instance name is appended to the name of the service, e.g. MSSQLTESTDEPT.
January 5, 2017
Overall stability and availability of a machine in a specific time range is usually called system uptime. This measurement represents a period (sometimes percentage) when the system is stable and performing without unattended reboots, except for maintenance and administrative purposes, and works without issues. The opposite, system downtime is a period when machine is turned off (on purpose), or encounters experiences problems that result is the system being unavailable to users and processes. The combination of these two measurements is called system availability which is both identified and tracked with Windows Event Viewer (System Log).
December 23, 2016
SQL Server’s built in abilities to track query execution via wait statistics is critical to resolving performance problems. Wait statistics or response time analysis are metrics that provide the ability to measure the time needed for the database to respond to executed queries. It doesn’t simply represent the time needed for a query to complete execution, but the wait statistic is also the measurement of the time the query has taken for each step in its execution. That information becomes the basis for identifying bottlenecks that are affecting the execution time.
December 20, 2016
Blocked processes caused by locks on database objects are a common issue. Locking ensures the integrity of data stored within databases by forcing each executed SQL Server transaction to pass the ACID test. which considers that every transaction must meet the following requirements:
December 12, 2016
The main purpose of SQL Server Agent is executing administrative tasks within SQL Server, mostly as on-demand user actions. It is Windows service which runs continuously in the background, but it stores necessary information within SQL Server itself, in the msdb system database.
December 6, 2016
The following recommendations are part of an effective strategy for investigating SQL Server performance issues caused by slow queries:
November 23, 2016
While Performance Monitor can be used for monitoring performance parameters of SQL Server, it doesn’t provide an option to get notifications when certain performance metrics values breach specified thresholds. The main reason why this is important is to be able to identify SQL Server performance issues as soon as possible, and react before they affect too many users. In addition, notifications on potential SQL Server performance issues could help avoid more serious problems by forecasting when they are likely to occur in order to avoid them. Therefore, one of the primary tasks for the database administrator is to establish a system that will ensure that he/she is going to be notified in case when certain performance metrics values are beyond the expected/desired range
September 8, 2016The AlwaysOn Availability Group was introduced as a new feature in the SQL Server 2012 Enterprise edition and is designed to ensure a more advanced and reliable option for SQL Server high availability and disaster recovery.
May 19, 2016
The ability to successfully solve a problem and pinpoint the root cause of an issue that affects SQL Server performance depends on knowledge of the particular SQL Server system and environment, but also on personal experience which can help in determining where to start SQL Server performance troubleshooting.
May 20, 2015To create a custom system for performance base lining we need the following:
In the following series of articles, we will discuss what baselines are, how they work and how to apply them to everyday SQL Server performance monitoring. This article will provide a brief overview of baselines and the statistical calculations behind them. Later we’ll apply this to real information gathering techniques to allow DBAs to create their own baseline.
May 4, 2015Even a superficial reading of books and articles on SQL Server performance monitoring will tell you that SQL Server memory pressure is the first thing you should monitor. Not the only thing, far from it, and some CPU, I/O and internal SQL Server counters are critically important to get the complete picture but it’s the memory counters that often show us the first indication of suboptimal performance
March 3, 2015The existence of a deadlock may be indicated by application slowness, error messages or cases when a SELECT statement takes a long time to finish or terminates prematurely with the message:
“Transaction (Process ID xx) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction”
All this indicates that SQL Server encountered a deadlock. A deadlock is a common issue with SQL Server. It is a circular blocking chain when two or more processes holding locks on data where each process wants to lock the data already locked by another process, and each process is waiting on the other process to complete before moving forward
March 2, 2015In the previous part of this series, we showed how to use SQL Server profiler to create a SQL trace that provides enough information to find expensive SQL queries. In this part, we will show how to create a SQL trace without SQL Profiler and how to analyze the information captured.
October 6, 2014In the previous parts of this series, we described how to find the most expensive recent queries using SQL Server Management Studio native features and dynamic management views and functions. In this part, we will show how to do that using SQL Server Profiler.
October 1, 2014In the previous part of this series, we described why tracking most expensive queries is important and how to use SQL Server Management Studio native solutions. In this part, we will focus on dynamic management views and functions.
September 30, 2014
© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy
