SQL Server Performance Optimization

According to the different studies, about 70 to 80% of the Microsoft SQL Server performance problems are due the bad code, and rest 20-30% is infrastructure problems, often due the poor capacity planning.

SQL Governor is the only software on the market to offer both performance management and capacity planning features. It is ideal for helping to proactively alert about problems in order to fix them.

What is SQL Server Performance Optimization?

”SQL Server performance optimization targets to long-term well-being of your Microsoft SQL Server estate. Without continuous nurturing of your SQL Servers and databases, SQL Server performance starts to degrade, typically resulting into response slowness and even availability issues. With right set of  tools and features, SQL Governor makes it easy and straightforward to identify such bottlenecks allowing a DBA to pinpoint and fix the issue. SQL Server Performance monitoring and optimization is typically targeted to following technical areas:

  • Operating System level configurations
  • SQL Server instance configurations
  • SQL Server database options
  • SQL Server database table indexing
  • SQL Server performance counters
  • Physical volumes on storage
  • Wait statistics and plan cache
  • Locking and blocking issues
  • T-SQL queries

 

BOOK A FREE DEMO

MS SQL performance tuning with SQL Governor

  • Standalone server monitoring
  • Always On availability groups monitoring
  • Reactive alerting
  • Predictive alerting
  • Wait Statistics analysis
  • Plan cache analysis
  • Blocking queries
  • Volume monitoring
  • SQL Server diagnostics
  • T-SQL query tracing

Standalone server monitoring

SQL Governor software allows you to monitor data platform 24/7 in real-time to effectively identify the root cause of performance problems. SQL Governor supports 64 performance counters to give you essential information.

Standalone server monitoring

 

Always On availability groups monitoring

 

Always On availability groups monitoring

SQL Governor provides profound support for Microsoft SQL Server Always On availability groups monitoring. With this functionality it is easy to monitor any number of Availability Groups and their states in real-time.

Reactive alerting

Let SQL Governor automatically identify your monitoring baselines. In addition to that, you are able to individually adjust performance counter alert and warning threshold levels and automate these alerts to be sent to desired e-mail recipient(s).

Reactive alerts are the ones that are thrown when a certain threshold point is exceeded real-time. SQL Governor has built-in following reactive alerting mechanisms:

  • Basic warnings and alerts
  • Critical alerts
  • Consecutive alerts
  • Custom alerts

thumbnail_Reactive alerting

 

basic alerts and warnings

 

Basic warnings and alerts

Basic warnings and alerts are bound to 64 performance counters. You can have an individual warning and alert threshold level set for each server, instance and database performance counter or let the software decide that. For example, if the CPU usage % performance counter alert limit for a certain server is set to 80% and SQL Server is over that threshold level for a 1-minute sampling interval, an alert is triggered.

Critical alerts

A critical alert can be tagged for any server, instance or database performance counter. Critical alerts are prioritized and shown in the SQL Governor alerting dashboard with red exclamation mark.

1. critical alerts

 

2. consecutive alerts

 

Consecutive alerts

The idea of consecutive alerts is to decrease the alerting overload in most efficient way. A consecutive alert defines how many consecutive sampling intervals needs to exceed the alert threshold level before an alert is thrown for such performance counter. For example, if the consecutive alert limit for individual SQL Server’s CPU Usage % performance counter is set to 5 on 80% threshold level, and then if this performance counter is consecutively over 80% for at least 5 minutes, it will give just one consecutive alert instead of 5 individual alerts for each given minute sampling interval.

Custom alerts

Custom alerts are a powerful mechism to create almost any kind of an alert that returns a value, based on SQL Server DMV’s (Dynamic Management Views). Custom alerts can be queried on instance or database level and are very configurable.

3. custom alerts

 

thumbnail_Predictive alerting

 

Predictive alerting

The idea of predictive alerting is to foresee potential ms sql performance bottlenecks before they even occur. In this way, DBA has more time to react and in best case DBA is able to identify and fix the problem before its potential occurrence, resulting in better SLA’s and even potential uptime.

Predictive alerts in SQL Governor are:

  • Regression-based alerts
  • Machine Learning-based pattern alerts
  • Time-series-based anomaly detection for monitoring and T-SQL query plans
  • Early Warnings

To read more from SQL Governor predictive alerts, see our CTO’s blog post: Predictive monitoring features in SQL Governor

Wait Statistics analysis

When SQL Server processes any task, It is always waiting some resource. These resources can be such as:

  • CPU
  • Memory
  • Storage IO
  • Locks
  • Other

SQL Governor collects these waits automatically and visualizes them on one-minute granularity. By visualizing these waits, we are able to identify different kinds of resource bottlenecks and to drill down on individual T-SQL query batches and see which statements are using the most resources. This way it is easy for a DBA to pinpoint potential problem in T-SQL code, indexing etc.

thumbnail_Wait Statistics analysis

 

thumbnail_Plan cache analysis

 

Plan cache analysis

Plan cache analysis complements wait statistics analysis by bringing another dimension into analysis: The absolute T-SQL query performance. SQL Server Plan cache is essential resource for finding inefficient T-SQL queries. SQL Governor collects the T-SQL query plan cache and aggregates time series data into 10-minute level granularity for visualization. From this visualization, it is easy to drill down and pinpoint which T-SQL query batches and statements consume the most resources over time. Measured T-SQL query resources are:

  • Executions
  • Physical reads
  • Logical reads
  • Logical writes
  • Worker time (CPU)
  • Elapsed time
  • Rows returned

Blocking queries

Sometimes T-SQL queries can block each other. If only one query (A) blocks another (B), B will wait until the A is executed. This causes query B to be a blocking victim an therefore being processed slower than normally. By fixing such a blocking statement in a T-SQL query batch, the blocking situation would be most likely prevented. A worst form of a blocking situation is a deadlock, because then to T-SQL statements from different T-SQL queries try to update same record(s) in the database table in SQL Server at same time leading to an cyclic event that can only be eliminated by SQL Server selecting a one of the processes as a deadlock victim and rolling back the T-SQL statements.

SQL Governor tracks and visualizes the blocking statements and collects essential metadata for each blocking / blocked T-SQL batch:

  • Session Id
  • Start time
  • Object
  • Wait type
  • Wait time
  • Request mode

thumbnail_Blocking queries

 

thumbnail_Volume monitoring-1

 

Volume monitoring

Monitoring physical volumes is important. Just think of a situation where drive space is full or disk IO latencies peak up suddenly. With SQL Governor you are able to forecast when drive space is full and to identify following idsk counter evolvment over time:

  • Transfers / sec (IO)
  • Milliseconds / transfer (disk latency)
  • Disk time %
  • Megabytes / sec (throughput)
  • Used space %

SQL Server diagnostics

SQL Governor has profound set of diagnostics features helping you out to keep up the optimal SQL Server settings and indexing all the time and in between the migrations as well. You can configure different diagnostics jobs to run at once or in recurrence and see trends over time. SQL Governor software itself is able to prioritize and give some recommendations on most critical settings and indexing. SQL Governor diagnostics features include:

  • Instance configurations
  • Database options
  • Missing indexes
  • Unused indexes
  • Index fragmentation analysis
  • Wait stats analysis (including signal vs. resource waits ratio)

thumbnail_SQL Server diagnostics

 

thumbnail_T-SQL query tracing

 

T-SQL query tracing

SQL Governor uses classic lightweight T-SQL query tracing. With this feature you are able to schedule one-time or recurring query tracing jobs. Trace job results can be seen from different kinds of reports. These reports are:

  • Trace charts of overall resource consumption over time
  • Trace logs for detailed analysis
  • Trace analysis with time series visuals similar to plan cache analysis
  • Overall trace statistics
BOOK A FREE DEMO
gray-background-image

Frequently Asked Questions

The rate at which data is refreshed in the UI depends on the feature and the collection intervals. Generally, the UI fetches a new set of data from the database every 2 minutes and caches it to reduce network traffic. The performance counter and availability group monitoring pages refresh automatically every 30 seconds; other pages refresh their data (from cache or database) when you click on something.

The data collection intervals are:

  • Server-level counters: 1 minute
  • Instance-level counters: 1 minute
  • Database-level counters: 1 to 60 minutes, depending on database count
  • Availability groups: 2 minutes
  • Standard alerts: 1 minute
  • Pattern-oriented alerts. 5 minutes
  • Predictive alerts: 5 minutes
  • Anomaly detection: 24 hours
  • Plan cache: 10 minutes
  • Wait statistics: 1 minute
  • SLAs: daily
  • Blocks: 30 minutes
  • Trace: milliseconds

Put simply, the limit value defines how many consecutive alerts are required to raise an actual alert. For example, if the limit is 3, the first one or two alerts won't cause any action; i.e. they won't be shown as alerts in the monitoring page and no emails will be sent. Only if there is a third consecutive value that is also above the threshold (or below in case of reversed performance counters) will an alert be raised. Thus, the first alerts before the limit are not really alerts but simply values that exceed the threshold. If the recorded values stay above (or below) the threshold after the limit is reached, then the alert will remain active.

Consider the following example: the performance counter is CPU usage %, the threshold is 80 and the limit is 3. The recorded performance counter values are shown in the top row and the resulting action in the bottom row:

76 %

82 %

85 %

86 %

81 %

79 %

no alert, below threshold

no alert, threshold exceeded

no alert, threshold exceeded

alert raised

alert raised

no alert, below threshold

 

That is, with a limit of three, the four consecutive threshold crossings cause two alerts in total. 

Instance waits are collected from sys.dm_os_wait_stats and include all waits generated on the instance, whether they have a SQL query batch associated with them or not. Query waits on the other hand are collected from sys.dm_exec_requests and always have a SQL query associated with them. For example, a backup might cause instance waits but not have any SQL query and thus not show up in query waits.

Why is the continuous maintenance and optimization of the database important?

In IT architecture, the SQL Server database server is the same as the engine for a racing car: It is the most central component for the operation of applications. If the database server or one of its central databases is unavailable, the application cannot run on its own. If the database server and databases are not regularly maintained, the probability of unavailability situations and slowing down of the databases increases significantly and can cause service interruptions and poor usability for the application. Just as a racing car engine requires regular maintenance and optimization measures, so does a database server with its databases.

 

BOOK A FREE DEMO

Book a demo

SQL Governor is a leading-edge software for the planning, forecasting and optimization of Microsoft SQL Server platforms. Please submit this form to request a free online demo of SQL Governor with one of our experts. The efficient run-through will give you more thorough understanding on how the solution works and can help you, and we will answer any questions you may have.

 
LinkedIn
YouTube

©2021 DB Pro All Rights Reserved  |  Mechelininkatu 1a, 00180 Helsinki, Finland  |  sales@sqlgovernor.com | +358 10 4110 470