Why snapshots should be on your festive checklist

Are you taking some time off over the holiday period? Do you have a change freeze scheduled over the next few weeks?

If so, taking a snapshot of your servers’ configuration is a helpful way of making your work less stressful when you return. That way, when you get back after your time off, you can compare the state of your servers post-holiday with how you left them. You wouldn’t leave your house for the break without properly securing it, so why should your servers be any different?

Comparing snapshots is easy in Aireforge Studio. In the application, take a snapshot of your servers this week. Then when you get back, it’s only a few clicks to compare it against the same servers. You get a clear list of the differences between them, so you’ll immediately be able to see any changes anyone has made. No more hidden surprises, and it makes it so much simpler to revert things if you need to.

Watch our video or read the support page to see how. Then, relax and enjoy your time off, knowing you’ll be prepared for your return.

Tuning SQL Server with Aireforge Studio Advise

Configuring SQL Server to run at peak performance is a skill that can take years to learn. But when you need to configure and maintain tens, hundreds or even thousands of instances, it’s a task that can overwhelm even the most seasoned DBA.

The Advise module for Aireforge Studio helps to combats this. Advise analyzes your SQL Server instances and provides suggestions for optimization and best practices. Here, we’ll take a look at some of its key features and how it can improve the performance, security and stability of your SQL Server estate.

Advise offers support for all versions of SQL Server, as well as Azure SQL Database and Azure Managed Instances. It combines expert database analysis with a focus on ease of use. At Aireforge, our extensive database management experience underpins Advise’s recommendations. We’ve taken fundamental operational checks, along with best practices from our experience working on mission critical and high-transaction databases, and packaged them into dynamically generated scripts you can run on your own estate. The tool provides a reliable and time-efficient alternative to writing and maintaining your own diagnostic scripts.

Feature Highlights

Advise supplies over 50 different warnings and suggestions. Let’s take a look at five key areas.

1. Index Management

Poor indexing can severely hit performance. A lack of useful indexes can lead to excessive scanning of indexes, tables and heaps, potentially crippling the I/O subsystem. Properly optimized indexes will also result in excessive I/O and CPU usage, as fragmented indexes will use a greater number of pages, missing columns will require another table hit / lookup and missing indexes could result in inefficient query plans and / or expensive table scans.

It’s critical to ensure your tables are indexed properly so that data can be quickly accessed. At the other extreme, duplicate indexes or overlapping indexes require more resources to maintain the duplicated data, increasing the amount of processing needed when data is inserted or modified. Duplicate indexes can also result in sub-optimal execution plans as more indexes means more options for the optimizer and a greater risk of timing out before a “good enough” plan is found.

Getting index management right will result in major performance improvements and Advise will suggest changes to speed up your queries and free up resources by removing  duplicates and merging overlapping indexes.

Advise detects duplicate indexes, where the same columns are indexed in the same order, giving you the dynamically generated script to drop the duplicate. It also highlights superseded indexes. This is where index A and index B are indexed on identical columns, but B has additional columns. Where A and B have ‘included columns’, Advise may suggest a creating a third index to replace A and B. Data from the missing index statistics are also factored into these checks, suggesting additions to existing indexes where possible or completely new indexes if required.

Advise also detects disabled, unused or hypothetical indexes, that are consuming resources but simply aren’t used. Dropping these kinds of indexes are quick wins that can save huge amounts of storage and again, give the optimizer an easier time.

Advise fig 1

Screenshot 1: comparing indexes in Aireforge Studio.

Advise fig 2

Screenshot 2: choosing analysis options in Aireforge Studio.

2. Configure SQL Server’s Automatic Behavior and Settings

Many of the default settings for SQL Server haven’t been updated in years, despite the increased speed of new hardware. Outdated default settings can cause poor performance and operational issues, so Advise looks at the SQL Server settings and suggests adjustments. This includes:

  • Recommending min/max memory allowance.
  • Recommended trace flags.
  • Operating system best practices.

Advise also ensures that non-default settings have not been used that could impact the performance, stability and integrity of a server. Including:

  • Checking whether auto shrink and auto close are enabled (both of which can cause massive performance issues if misconfigured).
  • Ensuring auto statistics is enabled to support the optimizer in creating more efficient query plans.

3. Optimize Parallelism Settings

One of the jobs of the SQL Server query optimizer is planning how to run queries on multiple threads. Advise will recommend an optimal parallelism cost threshold. This is the threshold that SQL Server uses to determine when to run a query using multiple threads. A low setting can cause the optimizer to waste time planning multi-threaded queries. A high one can lead to all queries only ever using a single thread. Choosing the optimal cost threshold for parallelism is a delicate balance and Advise helps you tune it correctly.

4. Database File Management

Running out of space when writing to the database file can cause a major service outage. Catching this issue early is critical so Advise warns if a database file is nearly full. Advise will also make best practice suggestions such as an optimum file count per CPU core. It also alerts you if data files in the same group have differing sizes, or if a data file has excessive free space. These checks are applied to all databases, including tempDB.

5. Database Monitoring and Log Analysis

Advise reviews the SQL Server logs to check for possible problems, or opportunities for improvement. This includes alerts if an I/O request takes longer than 15 seconds to complete or if the server is constrained by low memory. These alerts can be the difference between catching performance issues before anyone notices and unplanned downtime that disrupts your entire user base.

Advise Makes Tuning Simple

Advise has many other checks in addition to the ones mentioned above, but common to all of them is that as well as dynamically generated SQL, you’re given human-readable information to understand what the suggested action is, how to take it, and why it’s being shown to you at all. This way, you can not only optimize your database, but have confidence while doing it.

Every check has come from our experience working on real production databases, and we routinely use it in our consulting work in high-performance and high-transaction enterprise solutions. Advise is written by busy DBAs, for busy DBAs. For more information on Aireforge Studio, visit our website.

Advise is included as part of an Aireforge Professional licence, priced at $299 per user, per year. Try it free for 7 days.

An Overview of Aireforge Studio Community Edition

Working with large numbers of SQL Server and Azure instances can be extremely challenging. If you’re like most DBAs and developers responsible for estate management, you’re already juggling other priorities. Since you may have inherited the databases or accidentally stepped into the role, it’s unlikely you have time to manually monitor, trouble-shoot, configure and update, on top of keeping everyone involved happy!

If you can’t easily monitor and configure all those databases, how can you make sure they’re consistently patched and running at peak performance? How can you discover issues such as out of sync user access rights and configuration options, or even just run SQL queries on multiple databases at once?

These are some of the issues we wanted to solve when we created Aireforge Studio. This application supports busy DBAs and developers in charge of database estates, using the same workflow whether you’re working with ten databases or ten thousand. All the features described below are totally free.

Simple Estate Management

One of the major obstacles for DBAs with large estates is simply keeping track of them. The Estate module keeps all of your database instance knowledge in one place. Adding new instances is straightforward with the Import option, which allows you to import SQL Server instances from existing database lists in SSMS or from CSV files. You can also discover instances using SQL Browser.

When the number of database instances runs to hundreds or thousands, quickly navigating the entire list becomes unwieldy. What you need is a way to collect related databases together. The tag manager allows you to group instances and assign informative names such as production, testing, and staging. You can add multiple tags to give further detail, such as regional tags. Then, when you want to perform an action against an entire group (such as comparing their configurations), you can apply the action to a specific tag. With the click of a few buttons, Aireforge Studio allows you to efficiently manage an entire estate.

categorising server
Screenshot: Categorizing a server in Aireforge Studio

Aireforge Studio also includes a way to quickly check the health of database instances and provides several Health Check scripts out-of-the-box for highlighting immediate and potential SQL Server issues. You can also add your own Health Checks, tailored to your specific estate, and run them over hundreds of servers at a time.

health check
Screenshot: health check results in Aireforge Studio

The Power of The Script Module

Many teams use SQL Server’s Central Management Servers for running queries across multiple servers. We did too, but we also found it to be lacking a lot of the functionality needed from a scripting tool, like being able to select individual instances with ease or changing how results are displayed. Using the Script module, you can run queries against the estate you’ve already loaded into your profile, targeting existing groups or individual servers.

run queryScreenshot: running a query across multiple servers in Aireforge Studio.

Discovering Configuration Differences

Aireforge Studio’s Compare module (originally called OmniCompare) makes it easy to find differences between SQL Server and Azure instances. This module compares a variety of configuration and metadata, including build version, users, audit, trace flags, databases, jobs – essentially everything except the data itself. This type of analysis is often a key first step in diagnosing performance issues. As the size of your estate increases, it becomes difficult to determine whether configurations are out of sync. For example, if one SQL server is known to be suffering from poor performance, comparing tuneables such as the default autogrow value in SQL server can help you understand whether the database’s file size is a factor.

The Compare module is a great way to build a fingerprint of the database instance for analysis. Take a snapshot of configuration before making changes, and another after them, so you can easily identify anything that happened unexpectedly as a result. If you have a server you’d like to act as a benchmark for the rest of your estate, take a snapshot, Compare, then you’ll have a list of the changes required to standardize your servers.

comparison results

Screenshot: comparison results in Aireforge Studio.

The Compare module also offers a command-line interface (CLI) for those DBAs who want to drive comparisons from the command line via a comparison file. One of the biggest benefits of the CLI is that you can use it to integrate Aireforge Studio into your deployment pipeline, SQL Server Agent, or other custom scripts.

Customization

Aireforge Studio was designed for database management professionals of all levels. It’s easy to use for accidental DBAs, but there are plenty of features to help power users get their work done faster, such as the ability to add custom Comparisons and Health Check scripts. We’ve been working with SQL Server for over 30 years and use Aireforge Studio routinely in our work as consultants in high-performance and high-transaction enterprise solutions.

Adding these scripts to Aireforge Studio allows us to perform complex tasks at scale. To get the job done, you can customize everything from the Comparison scripts you run to the built-in Health Checks you require. We also provide the scripts in plain text so you can understand, copy and adapt them to better suit your estate.

Try Aireforge Studio Today

Aireforge Studio Community Edition (which is totally free) makes it easy for busy DBAs and developers to manage large, high-performance estates. Whether you’re an accidental DBA or an experienced power user, it keeps you in control of your databases.

Do you have scripts you created, or ideas for scripts, that you believe would be useful for other Aireforge Studio users? Please get in touch! We’re always looking to expand the tools available to make SQL Server estate managers’ lives easier.

Download the latest version of Aireforge Studio Community Edition.

Aireforge Studio 1.1.9

A lot of work has gone into this release but many of the changes won’t be immediately obvious as we’ve;

  • Changed and standardized every button.
  • Relaxed the server limits for Pro versions.
  • Improved the handling of licensing issues.

A good example of the changes can been seen in the splash screen. Not huge changes but they’ve been made everywhere. Another example is the comparison configuration screen.

This slideshow requires JavaScript.

 

Improvements to Advise

  • Added partial support for column store indexes.
  • Ignore fragmented indexes now based on pages rather than rows. Defaults to Microsoft recommended value of 1,000 pages.

New Checks

  • Database owner does not exist.
  • Auto update statistics is disabled.
  • Data files larger than 10GB with more than 90% free.

Improved Checks

  • Cost threshold for parallelism alerts if set to < 15, but suggests setting it to 30.

New Scripts

  • If page verify is NONE or TORN_PAGE, suggest setting to CHECKSUM.
  • If auto shrink is enabled, suggest setting to disabled.
  • If auto create statistics is disabled, suggest setting to enabled.
  • If auto update statistics is disabled, suggest setting to enabled.
  • If auto close is enabled, suggest setting to disabled.
  • If the compatibility level isn’t the latest, suggest setting to the latest but with a warning.

Improved Scripts

  • The suggested size for new tempdb data files are now set to the total space / existing files * number of suggested files.

Multi-server scripting with Aireforge Studio

We’ll keep this short, but we’ve created a scripting tool for running queries against multiple SQL instances via Aireforge Studio. Oh, and it’s FREE.

Script.png

The Microsoft Central Management Servers (CMS) are great (link), but they’re lacking functionality and haven’t had much attention in recent years. So, given that Aireforge users already have their estates loaded into their profiles, we thought it made sense to build a scripting module into Aireforge Studio. You can use the same servers and groups from the estate manager or select individual servers; giving you more control over which scripts are run and where. You can also export to CSV, validate your queries before running them and change how the results are displayed or grouped.

We have a healthy backlog in JIRA for Script, but any feedback is always appreciated. If you think that we’re missing something, please let us know (via Wantoo) and we’ll do our best to implement it.

Enjoy.

New Advise module and Aireforge Studio v1.0.7

What’s new at Aireforge?

We’re pleased to announce that Aireforge Studio is now officially live and supported. Version 1.0.7 ships with hundreds of improvements with a new UI, simpler comparisons with advanced options, 2017 support and our new tuning / best practices module; Advise.
Depending on your version, the application should prompt to update after starting. If not, please download the latest version using the link below.

Introducing Advise

Download images

The new Advise module within Aireforge Studio analyses instances, databases, tables and indexes, before providing recommendations. The growing list of checks vary from best practices and naming convention violations to orphaned users and missing, duplicate or overlapping indexes.

Read more…

Compare v1 & Custom Comparisons

Download images

The Compare module within Aireforge Studio performs a growing list of comparisons, which can be restricted if certain values are known to differ, such as core count or edition (e.g. Enterprise vs Developer).

Common use cases for Aireforge Studio Compare are checking production databases against staging and / or staging against test, auditing the SQL Server estate, sharing configurations with colleagues / support teams.

Read more…

Viewing configuration settings without access to SQL Server

Have you ever been asked to help with a database issue but you can’t get access? Maybe you’re helping out a colleague over Skype, responding to a #sqlhelp request or asking for help yourself and want to provide more information. Quite often, there’s a simple configuration setting that may not be optimal but checking every setting could be time consuming and something could be missed (there’s a lot of settings).

You could start by running some scripts like sp_blitz (really useful) but they don’t always give you the complete picture or you need to extract all settings to store, share or compare them. Aireforge Studio can take an in-depth snapshot of a single instance or the entire estate. This can be really useful to do before or after carrying out any changes or if you need to share your configuration with someone else. You could also attach the saved comparison file to the ticket you’re working on, so you know exactly how it looked before and after any changes were made.

Here’s a quick tutorial on how you could share configuration information about your SQL Server instance or instance(s) with another person or organisation.

Taking a configuration snapshot

If you’re new to Aireforge Studio check out the getting started pages, although it’s a quite simple application. Once downloaded, create a profile and add the server(s) in question.

Go to the Compare tab and select the comparisons you wish to run or select Full to run them all. A full comparison should finish in a few seconds and won’t display any results if you only compared one server or if all of your servers are identical. Now save the results as an .afd (Aireforge Data) file and send it to the other party.

save

Viewing the results

The receiver will also need to have Aireforge Studio installed. Once installed you can either double click on the .afd file to open, or load the file from within the application. To view the results change Differences to All Rows and the View to Grid. If information has been collected from more than one server, you could also use the summary view to quickly see any differences.

results

Aireforge Studio performs a lot of preconfigured checks but if it doesn’t gather the information you require, you can create your own custom comparisons.

Any problems, however small, please contact Aireforge support.

You can also use our Wantoo board to request new features or improvements?