Posted on

How to tune Ad-hoc SQL with Plan Guide for SQL Server ?

Using Plan Guides to tune third-party applications SQL in MS SQL Server can be a useful technique when you need to optimize the performance of a specific query or set of queries generated by the application, without making changes to the application’s source code.

Here are the steps to use Plan Guides to tune third-party applications SQL in MS SQL Server without changing the source code:

  1. Identify the query or queries that are causing performance issues in the application. You can use SQL Server Profiler or Extended Events to capture and analyze the SQL statements generated by the application.
  2. Create a Plan Guide that provides an optimized execution plan for the identified query or queries. This can involve modifying the query text or providing query hints to influence the optimizer’s decisions.
  3. Test the Plan Guide to ensure that it provides the desired performance improvements and does not cause any unintended side effects.
  4. Deploy the Plan Guide to production and monitor the performance of the application to ensure that the Plan Guide is being used and is providing the desired performance improvements.

Before optimizing an ad-hoc SQL statement executed by an application program without modifying the source code, it is crucial to understand how the SQL statement matches the one specified in the Plan Guide, which includes whitespace and comments. Additionally, it is important to match the source of the SQL statement. The following is the system procedure used to create a Plan Guide.

Today, the focus will be on tuning ad-hoc SQL (@type = N’SQL’) using a Plan Guide. There are two types of SQL: standalone SQL (@module_or_batch = NULL) and SQL within a batch text (@module_or_batch = N’batch_text’). For instance, if an application program sends the following SQL, and it is executed independently without any other code, it falls under standalone SQL.
select top 10 * from  employee;
The example below illustrates a batch text that contains one of the SQL statements listed above, which needs optimization by Plan Guide. This SQL statement is located in the middle of the batch text. Since the same SQL statement can originate from a batch text, we must specify the specific batch text by using the variable @module_or_batch = N’batch_text’. Consequently, two Plan Guides must be created for the same SQL statement, one for ad-hoc SQL and one for batch text. To accurately identify the source of an Ad-hoc SQL, it is recommended to use SQL Profiler to capture the SQL statement that requires optimization by Plan Guide.

select count(*) from employee;
select top 10 * from  employee;
where emp_id in (select emp_id id
                             from emp_subsidiary
                             where emp_dept<‘h’)
order by emp_name;

Microsoft SQL Server Management Studio provides a useful tool that assists users in creating a plan guide for a SQL statement without requiring manual execution of the system stored procedure. However, it is crucial to have knowledge about the type of SQL statement being optimized and the meanings of the corresponding parameters that need to be input.

Although the steps to create plan guide may seem complicated for newcomers, they are worthwhile for improving SQL performance without altering the source code or lacking the permission to modify it. However, the most challenging and time-consuming aspect is finding the optimal query hint for the SQL statement (@hints = N’OPTION(query_hint [ ,…n ])). Unless you have an in-depth knowledge of SQL tuning techniques and enough time to experiment, you may require a product that streamlines the process from capturing SQL, identifying SQL source type, automatically tuning query hints, and facilitates easy deployment of Plan Guides.

Tosska DB Ace Enterprise for SQL Server – Tosska Technologies Limited
DBAS Tune SQL PG Standalone – YouTube
DBAS Tune SQL PG Batch – YouTube

Posted on

Backup and Recovery in SQL Server: Understanding the Basics (Part 1)

SQL Server

Taking regular database backups is essential to provide assistance to businesses recovering from an unplanned event. They enable data restoration from when it was previously saved. Moreover, keeping a copy of the information separately is also vital for protection against corruption or data loss.

In this 2-part series, we’ll cover

In this guide, we will discuss SQL Server backup types, recovery models, as well as best practices that you should take into account when putting together your backup strategy.

Various Types of Backups in SQL Server

There are different backup types in SQL Server that users have to consider when constructing their backup strategy. Here, we will briefly explain each of these variants and how they work. Microsoft SQL Server supports the following backup forms:

Full Backup: This implies a complete backup of the SQL Server database. It covers every object in the database. It is the most popular and recommended backup type as it enables users to restore their database to the exact same version it was when the backup was taken.

Differential Backup: It backs up only the data that has undergone changes since you created the last full backup. This is why it takes lesser time than a full backup. However, creating several differential backups over time may eventually lead to greater storage requirements.

The size increases because of the addition of changed data in each subsequent backup, and it can grow to become as large as the full backup. Thus, it is important to schedule new full backups (even if they’re less frequent) to avoid extended backup times and oversized differential backups. Otherwise, these excessively-large backups will cause a negative impact on database performance, requiring optimization.

Transaction Log Backup: It is a form of incremental backup. It backs up the transaction log containing the modifications made since the last t-log backup. Log backups can take place quite frequently – even once every few minutes. This enables users to carry out point-in-time restores to reduce data loss.

File/filegroup Backup: This type of backup involves making separate copies of individual data files or files from a filegroup. Users can backup and restore each database file separately as well.

Copy-only Backup: This is a type of SQL Server backup that doesn’t depend on the sequence of traditional backups. When you create a backup, it generally makes changes to the database.  These impact the manner in which these backups will be restored in the future. On the other hand, it may sometimes be more useful to create backups that don’t affect the comprehensive backup and restore methods for the entire database.  

Creating Backups for SQL Server Databases: What Experts Suggest

Seasoned database professionals recommend a few things when it comes to creating backups of the database. For starters, they suggest using the full recovery method on a daily basis. However, you may create these on alternate days and differential backups every day if the database size exceeds three GB.

Many also advise making daily t-log backups once you’ve created a full or differential backup. You may even schedule one for every four hours, and avoid truncating one manually. If disaster strikes, it’s better to create a backup of the t-log that’s active at the moment. In case there isn’t any t-log backup available, you’ll be unable to restore database activities past the latest available t-log backup. This is likely to hinder point-in-time recovery as well.