How to set automated database schema validation

In situations where deployment of database script might be delayed due to review process or when multiple project branches are automatically deployed to a single stage environment there is always a possibility that target schema was changed in relation to prepared deployment script. This might be the cause for script execution failure and consequential database issues.

How to achieve high quality auditing trail with the minimum number of auditing events

Configuring auditing in ApexSQL Auditing is a quick and easy task. One simply needs to define which specific SQL Server events should be audited on both server and database levels, as can be seen in the Auditing SQL Server databases – initial configuration article and then apply the configuration to complete the setup process. Auditing will immediately commence, and user can then query their central repository databases to create comprehensive reports and use various filters to ensure they can produce high quality and precise reports.

Nevertheless, even though excess information can be filtered out in the reports, the better practice is to not audit events with zero or minimal auditing value at all, since excess auditing may result in various additional expenses, such as:

  • Longer processing time when storing audited information into the central repository
  • Higher disk space requirements – which can take especially heavy toll in the high-traffic SQL Server environments, especially in cases when traces created by ApexSQL Audit have to be temporarily stored on the local drive in case of higher priority SQL Server processing
  • Slower report creation due to more complex filters
  • Time investments when creating a new report every time, due to the requirement to remove excess data

With the above in mind, there is no reason why ‘all’ events should be audited in any case and add unnecessary encumbrance on their auditing or production environments. In this article, we are going to show how to use different ApexSQL Audit assets, including auditing filters and features which will allow high-end auditing jobs to be put in motion over different SQL Server instances with minimal auditing trails ensuring best performance and lowest requirements.

Server filters

Once ApexSQL Audit is installed, auditing does not commence immediately – the auditing filters must first be configured.

First, add SQL Server instance for auditing by choosing SQL Server for auditing from the drop menu in the Server pane in the Configuration tab (or typing the name manually) and clicking on the ‘Add’ button.

Also, provide appropriate credentials and choose connection options before completing the process.

Now, specific server-level events can be added by checking the checkboxes next to each server event in the Operations tab.

Next, on the Applications tab, it is generally advised to exclude all changes made through the applications which are not required for auditing. For instance, if you have an application performing frequent logins, checks, or changes which is not part of the auditing requirements, be sure to exclude those in this filter by checking them in the applications tab

Quick tip icon

Quick tip:

If the application, login or object name is not shown in the displayed filter lists, type its name in the ‘custom entry’ field, click ‘Add’ and ensure item is checked in the list to add this specific filter

Another important filtering tool is the ‘Logins’ filter. Here, we can choose to include or exclude any SQL logins which do not need to be audited per existing requirements. As before, simply check specific logins, or add custom ones, and opt to either include or exclude them by choosing the appropriate radio button.

Database filters

Adding databases for auditing is also simple and easy. As before with the SQL Servers, in the Server pane, first select the SQL Server instance which hosts the database, and click ‘Add database’ button.

Now, in the ‘Add databases’ pane, check databases for auditing and click on the ‘Add’ button.

As before, it is required to check the database events which will be included in the auditing task.

One of the most frequent mistakes made when auditing job is being set is skipping the ‘Object’ filter on the database levels. More often than not, there are hundreds or thousands of tables, triggers, stored procedures and other database objects with a vast amount of traffic which is frequently irrelevant for the auditing requirements and should be hence excluded to minimize the trail and traffic size. Choosing to include only a specific object in the auditing task can decrease auditing trail tenfold or more in most environments.

To configure object filters, as was the case with already mentioned filters, check appropriate objects and opt for a radio button which will allow you to include or exclude them from the auditing job.

Quick tip icon

Quick tip:

A great starting point for auditing configuration are predetermined compliance templates based on the most existing compliance standards such are GDPR, HIPAA, PCI and more. These include minimal auditing events to meet compliance standards, making them ideal starting points before adding more operation, application, login or object filters, both on server and database levels

Advanced filters

Now that we’ve seen how to use various filters to control the auditing output using ApexSQL Audit simple filters, let’s take this job one step further, straight to the advanced auditing filter.

Advanced filters in ApexSQL Audit take a different approach and combine both server and database levels together. They are created by combining an unlimited number of different conditions to filter the auditing trail. They can be accessed by clicking on the ‘Advanced’ button and cannot be used together with the simple filters – these will be converted and overwritten.

Conditions are added by clicking on the green ‘plus’ sign and can be combined in various ways. Different operators, grouping and more can be used to achieve pinpoint precision and ensure that the auditing trail is indeed minimal. With the advanced filter, filters can be configured to audit specific database or server events for one login, and completely different operations for another one, why monitoring others only for the third set of events etc.

  • More on how ApexSQL Audit advanced filters can be used and configured for highest auditing precision is described in great details in the ApexSQL Audit feature highlight: Advanced filtering article which also offers several great tips on how to group and combine conditions

Before-after auditing

In the Before and after auditing in SQL Server article we show how to setup and configure before-after auditing on a table column level to capture changes before and after the insert, update or delete operation has been executed. DML changes audited by before-after auditing are usually the most frequent changes in the databases since they actually feed and change the data in the database tables. With this in mind, it is important to give some special attention when configuring auditing of DML operations since incorrect configuration can get out of control pretty quickly.

The first thing to keep in mind when configuring before-after auditing is that it goes all the way up to the column-level, which means that we can include or exclude only specific columns in our auditing job, minimizing the output this way. So, first use ‘Add table’ button to include specific tables in the configuration, and then dive deep down to the column levels and choose which one will be part of the auditing job.

The second thing to keep in mind regarding before-after auditing is that it can combine advanced filter with the table filter above. So, once the column-level configuration is completed, we can add conditions to further filter our auditing. For example, we can choose to only audit operations on our included table columns in case when a SQL Server Management Studio is used by a particular SQL login connecting from a specific client host

With this, we conclude our auditing configuration recommendations and best practices.

Keep in mind that you may not be able to achieve minimal auditing trails immediately and that some tweaking will be needed. So, a good practice would be to:

  1. Configure auditing filters for minimal auditing trail as per known requirements
  2. Run auditing reports to investigate auditing trail and isolate any events which may not actually be required and can be removed
  3. Keep a watch for any ApexSQL Audit alerts which may indicate huge growth of the central repository database and alert you on potential flaws.
  4. Manually create or schedule regular auditing reports to be automatically created on a predetermined frequency to keep track of your auditing trail. If the latest report is several time larger than those before it, some changes have probably occurred in your SQL Server environment which could be a flag to check and appropriately adjust your auditing filters to accommodate for the latest changes

FAQ

How can I ensure my auditing job does not impose impactful performance hinderance on my production environment?

Aside from checking or keeping track on any potential CPU or memory usage in low-performance environments, it is a good practice to check if traces (including audited data) created by ApexSQL Audit are timely processed by SQL Server, or if they are piling up in the temporary folder (default: c:\ProgramData\ApexSQL\ApexSQLAudit\Auditing\<SQL Instance Name>\ActiveTraces\), which can be a flag that the audited data is being added faster than it can be processed which suggests an immediate reevaluation of the auditing configuration.

What is the expected performance impact of ApexSQL Audit on central and audited instances?

ApexSQL Audit has a minimal performance overhead, equivalent to running the default trace which means it can audit huge amounts of events with minimal/no performance impact.

 

How to configure and use SQL Server Wait statistic monitoring

Applies to
ApexSQL Monitor

Summary

This article explains how to use ApexSQL Monitor to investigation the SQL Server wait statistics, as well as what data related to wait statistic it collects, how and how those data should be interpreted and used in the analysis of the SQL Server performance monitoring.

Description

The article will address the following questions:

 

What is a SQL Server Wait statistic?

Every SQL query that is executed against the SQL Server has to wait during its execution. Theoretically, it is possible to execute a query without waiting, but that never occurs in a real world. To be able to track what the query waits for, SQL Server has implemented a mechanism to track and log all states where the query is waiting for something, including the information about the reasons why a query is forced to wait. The information that SQL Servers collect about the reasons a query has to wait during its execution is the so-called Wait statistic.

  • The waits in SQL Server occurs due to external and internal reasons. External reasons are called Resource Waits, while internal reasons are called Cooperative Scheduling Waits.
  • A Resource Wait occurs when execution is forced to wait on external resources, such as reading the locked data from the table, waiting on the buffer pool to become available or physical or logical storage during I/O operations are just some of the examples.
  • A Cooperative Scheduling Wait occurs due to working mechanisms of SQLOS (SQL Server Operating System). To maximize the SQL Server efficiency, SQLOS does not use preemptive Windows scheduling, but instead, it schedules the threads by itself. SQLOS Scheduler (sometimes called SOS Scheduler) decides when the query will be executed on CPU, for how long and when it will be taken off the CPU to allow to another query to executes. The time an SQL Query can be actively executed on CPU is set to 4ms by SQLOS Scheduler and that 4ms time slice is the so-called Quantum. It is exactly this SQLOS Scheduler mechanism which is an integral part of SQL Server is the reason why the query has to always wait in practice during the execution.

It is important to understand that, when it comes to Wait statistics, that although we look to provide the precise information on symptoms and reasons of the waits, that can be further used by the DBA for analysis and determining of the root causes, but wait statistics, in and of themselves does not pinpoint the actual root causes.

What is the difference between Wait statistics and Query waits?

While both deal with wait statistics, the primary difference between the Wait statistic feature and the Query waits feature is that Wait stats deal with Wait statistics globally at the SQL Server level, while Query waits deal with Wait statistic at an individual query level.

Wait stats provide the break down over the time of various wait types that occurs on SQL Server. They allow insights into the wait types that are source of what SQL Server is waiting on during query execution and at what level various wait types affects the SQL Server work.

What Wait types I can monitor?

ApexSQL Monitor is capable of tracking all Wait types. The number of wait types that can be monitored depends only on the version of SQL Server that is monitored and what wait types that particular version supports.

Do I have to monitor all wait types?

No. While ApexSQL Monitor is capable of tracking all Wait types, by default, it tracks the most important ones to prevent collecting and storing unwanted data.

Why are some wait types not monitored by default?

Not all Wait types are equal. There are some wait types that predominate in the real-world work with SQL Server, and those wait types are the cause of almost all problems. Furthermore, tracking the wait types that wouldn’t be helpful in performance troubleshooting or that are even not related to performance troubleshooting would increase the noise, making focusing on the real problem to be a challenging job. Therefore, to prevent collecting, storing and processing of less important or unnecessary wait types, ApexSQL Monitor tracks by default only the important wait types from the SQL Server performance troubleshooting standpoint.

Can I configure what wait types to monitor, or to exclude from monitoring?

Yes. While ApexSQL Monitor tracks the predefined number of important Wait types, it still allows you to retain the full control on what Wait types you might want to turn on or turn off for monitoring.

To configure what wait types, you want to monitor:

  1. Open the Configuration page using the Configure link in the main menu


  2. Select the Wait types tab


  3. It will open the list of the Wait types that ApexSQL monitor tracks


All wait types are split into two groups: Common and Other.

The Common group contains all Wait types that ApexSQL Monitor tracks by default and they are all selected. The Other group contains the rest of the Wait types and wait types contained within this group are not monitored by default.

The wait types are listed in alphabetical order within the groups, and for each wait type listed, the associated performance category that it belongs to is displayed in the Category column.

Since there are over 900 wait types that SQL Server tracks and that can be listed in this page, the appropriate “live” search filter is included for easy and fast locating of needed wait type. The results will start to appear as soon as you start to type into the search box.

The filter works using the “contain” condition, so it is enough to type in any known part of the wait type name, and it will list all wait types that contain the types string as part of the name.

For those that need more information about a specific wait type, suggested solutions for the issue caused by that wait type and additional links to the more detailed article for gaining a more in-depth knowledge, just click on the name of the wait type, and the appropriate helper dialog will pop up.

Once the wait types are configured press the Save button, and the current settings are saved.

In case that there is a need to restore all changes to a factory setting, press the Restore defaults button.

When disabled, will wait types not be monitored at the database or SQL Server level?

When disabled, the application will not catch that wait type at all, including the queries that are waiting for that wait type. The only exception is when the query accumulated the excluded wait type alongside with some other wait type during the execution. In that case, the wait type will be collected as the part of that query to provide full information about that particular query execution. If the excluded wait type is the sole wait type accumulated during the query execution, the query will not be collected at all.

Besides the above case, when the wait type is collected as the part of the individual query execution, it will not be collected or processed anyhow in all other scenarios, and therefore it will not be stored in the repository database or displayed in the Wait stats page.

Does the application store the wait stats data?

Yes. All data regarding the wait types are stored in the repository database. That allows historical research and analysis of the collected data.

How can I see the collected wait stats?

To view the collected wait stats data:

  • Select the SQL Server in the server explorer or select the server name in the server pane from the main dashboard

  • That opens the Instance dashboard page
  • Select Details in the Waits stats section of the instance dashboard


  • The Wait stats page is now displayed

What will wait type data be displayed in the Wait stats page?

The Wait stats page is divided into three related segments, where each displays the different aspect of the collected wait statistic. Each of those segments serves to provide more comprehensive information and help to gain the better perception and understanding of potential issues and where the issue might be.

The page contains the Wait stats chart, the Wait stats by top 5 categories pie chart and the Wait stats table where wait types are listed per specific category, they belong to and the precise wait time accumulated for each wait type.

Wait stats chart – The chart displays the top 20 wait types that are accumulated the highest wait time over the period specified by the user. The names of top 20 wait stats listed in the chart’s legend are displayed in descending order based on Wait stats time each wait type accumulated in the given period of time. That means that Wait stats with the highest accumulated wait time are listed first.

For those who want to inspect the wait types in more details, the chart provides the option to turn off specific wait type from being displayed in the chart. If needed, the user can leave just one wait type for analysis and troubleshooting. By clicking on the name of the wait type in the chart legend, it can be turned on or off in the chart.

Bellow is an example of how that looks like when wait types are excluded partially and how when a single one is retained for display in the chart.

Quick tip icon

Quick tip:

The scale of the Y-axis of the chart will be scaled dynamically depending on the selected wait types to ensure the better preview of displayed wait types.

Wait stats by top 5 categories pie chart – Displays the ratio between the accumulated wait time on the server among the top 5 wait categories with highest wait time accumulated.

Wait stats table – Displays the total accumulated wait time for wait types over a specified period as well as the average wait time.

Can I drill in the chart to see more precise data?

Yes. Just click on the specific bar, and the chart will be scaled down to display the shorter period of time for data contained within that bar. Drilling down in the chart allows you to see data with a more detailed view, broken down across the shorter period of time, down to 30 minutes.

How frequently are wait stats data collected? Can I change the interval and/or can I turn off Wait statistic collection entirely?

By default, ApexSQL Monitor collects the wait stats data in 30 minutes’ interval. The collecting frequency is configurable in the SQL Server section of the Metrics configuration page.

To turn off Wait statistic monitoring, just uncheck the checkbox next to the Wait stats [ms]. Each configuration change made here must be saved using the Save button.

Can I calculate the baseline for the wait stats data and can I see how the wait type relates to calculated baseline?

Yes. the baseline can be calculated for Wait stats data based on the historical data. Calculating baseline requires at least 7 days of data to be collected. ApexSQL Monitor calculates the baseline for each collected wait type independently. That allows the wait stats data to be tracked via baseline per individual wait type.

To turn on/off displaying of the baseline for wait types in the chart, click on the baseline icon in the top right part of the chart.

To check the baseline for different wait type, just click on the name of the wait type in the legend of the chart.

Can I set alerting for Wait stats?

Yes. Just check the Alerting checkbox for the Wait stats [ms] and set the appropriate values for the low, medium and high thresholds.

The threshold time is defined per individual wait type. So, what that means in practice? When the data are collected on 30 minutes, if in the example, 5 wait types have a wait time that is higher than some of the predefined thresholds, the appropriate alert will be triggered for each of those five wait types. Therefore, the alerting system is not based on the global wait time accumulated, but rather granularly on every wait type for its time accumulated between the two reading periods.

Quick tip icon

Quick tip:

It is highly advisable to turn on alerting based on the calculated baseline. This is the most reliable method for alerting for wait statistic. For most wait types, using baseline-based alerting is the only recommended alerting method, as due to hardly predictable nature of wait types, baselining is the closest to what could be considered a valid alerting and the way to prevent massive false positive and false negative alerting that could occur as a consequence of manually defined thresholds.

Use manually defined thresholds in exceptional cases only, when specific troubleshooting is required, and switch back to the baseline-based alerting as soon as possible.

Can I print or save as a file the Wait stats chart?

Yes. use the hamburger button in the top left corner of the chart and select from the menu whether you want to print the chart or save it in PNG, JPEG image format, PDF document format or SVG vector-based drawing file format.