Troubleshooting SQL Server performance issues using wait statistics – Part 2

In the previous article we described native SQL Server capabilities for troubleshooting SQL Server performance issues using wait statistics and especially the query wait statistic.

In this part, we’ll focus on a 3rd party solution – ApexSQL Monitor. ApexSQL Monitor is a SQL Server and system performance monitoring tool designed for monitoring of operating system, SQL Server, and database performance metrics in real time including wait statistics as well as query wait statistic on cumulative and individual query level. Besides being able to track wait statistics, ApexSQL Monitor allows a database administrator to configure and receive alert notifications about wait statistics as well.

Troubleshooting SQL Server performance issues using wait statistics – Part 1

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.

Using regular expressions (RegEx) in SQL Server to generate randomized test data

A regular expression (abbreviated regex or regexp and sometimes called a rational expression) is a sequence of characters that forms a search pattern, mainly for use in pattern-matching and "search-and-replace" functions. They can be also used as a data generator, following the concept of reversed regular expressions, and provide randomized test data for use in test databases.

How to detect SQL Server performance issues using baselines – Part 2 – Collecting metrics and reporting

To create a custom system for performance base lining we need the following:

  • A metric to track
  • A database and tables to store our collected data and processed information
  • A means to collect performance data
  • A means to process the performance data into meaningful information e.g. Mean, Standard deviation
  • A line chart of the data
  • A query to create a report of the measurements that exceeded the threshold, we set, for raising alerts

Manage multiple database backups across different SQL Server instances

One of the most common ways to ensure that the recovery will be possible if a data-file corruption or any other disaster occurs is to create a recovery plans for this scenario. The most popular recovery plans include regular creation of database backups which can later be used to restore a database to a nearest available point in time, prior to disaster.

In order to create and apply successful recovery plan, it is important to create a solid backup schedule and to manage backups of multiple databases across different SQL Server instances.

In this article we will create a SQL Server scheduled backup by using a SQL Server Agent job and ApexSQL Manage.

How to detect SQL Server performance issues using baselines – Part 1 – Introduction

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 statistics and we’ll also show how to baseline performance “out of the box” with a 3rd party tool

How to ensure continuous auditing of SQL Server with zero audited data loss

An optimal continuous SQL Server auditing approach must include:

  1. Continuous auditing
  2. Real time data collection
  3. Ability to generate meaningful reports
  4. Alerting on unwanted activities
  5. Tamper proof store of audit data

In many cases, the primary requirement that must be fulfilled is that auditing must be performed with zero auditing data lost.

How to implement check out and lock policies in the shared database source control development model

In a multi-user database-development environment, avoiding conflicts and overwrites with edits, and ensuring all changes are audited and recorded is important. Until recently however, effective tools for SQL development management have lagged well behind their client developer equivalents, like Visual Studio. In this article, we will look on specific database source control challenges and a way to address them use new SQL developer tools that make meeting these straightforward and easy

How to analyze SQL Server database dependencies in client code e.g. C#

ApexSQL Clean has, among others, a useful feature that most people are not even aware of. It provides a client SQL code analysis in C#, Delphi, VB.NET, XAML, XML, ASP.NET, HTML, CSS code etc., and detects which SQL objects are actually being used and which ones aren’t in the referenced database. The SQL code analysis is performed by inspecting the code file for objects in the SQL database. This feature helps keeping SQL databases clean and organized

How to automate batch SQL object scripting and committing to source control

Many development teams have the need for a quick and easy but effective solution to committing SQL Server database objects to source control, but aren’t yet ready to move to full source control integration at check in/check out level.

In order to get objects under a source control in a “Poor man’s” approach, creation scripts need to be produced for each table, stored procedure, and any other object in a database. Afterwards, the scripts need to be committed to a source control system.