Tackling Large Tables to Improve MySQL Database Performance

improve MySQL database performance

Oftentimes, database professionals make the mistake of jumping to conclusions when trying to improve MySQL database performance. They assume that the database must be the reason why the application has slowed down. 

In most cases, they may be right- which is why it’s important to start looking for possible bottlenecks and removing them to reduce lag. However, make sure you consider multiple forms of diagnostic data when attempting to uncover the root cause behind poor MySQL database performance. Don’t stick to just monitoring CPU usage or disk IO as relying on a single metric has greater chances of leading you to an incorrect diagnosis.

We need to look at the full picture to understand the complex interdependencies among CPU, memory, and IO. It is important to do so before making reactive changes, such as increasing disk capacity or memory. In this blog, we will take a look at one such reason behind performance bottlenecks- large data volumes.

How Large Data Volumes Affect MySQL Database Performance

Statements that cover a wide scope of data or are unrefined may fetch unreasonably large quantities of information from the database. This doesn’t seem like a problem at first when the database is new and has minimal data.

The true issue emerges as it grows in size, gradually leading to the requirement of Database Server. This is because when a statement fetches data, the data must be scanned into memory. The bigger the size of the data that needs scanning, the greater the load on the CPU, resulting in the need for burst mode due to sudden CPU spikes. This kind of usage increases the chances of your database server crashing.

Additionally, in case the data does make it from the database server, your app server may not be sufficiently provisioned to handle it. Known as over-fetching, you can overcome this problem by limiting the scope of data selection to relevant records. One way to do that is to opt for the WHERE clause in such queries- after you find them, of course.

The key to locating them is by searching through the database logs and metrics for tell-tale signs of large-scale data fetching. Although you might be able to spot CPU spikes or burst credit utilization from these metrics, it might not be easy to tell which statements are causing this specifically.

Things You Can Do to Improve MySQL Database Performance

Query optimization is one of the best places to begin when you have to improve MySQL database performance. But it differs from case to case and is far from a one-size-fits-all endeavor. That said, there are certain tasks that help in a lot of cases:

  • As mentioned above, you can prevent large result sets and decrease data volume by limiting the search to relevant records using the WHERE clause.
  • Go through the database schema to uncover ways that decrease complexity. For instance, keep an eye out on queries that contain a lot of joins since they take more time than most queries. You can make them run faster by reducing their relationships.
  • A large number of queries also fetch unnecessary fields from tables. You can set them to return only those fields that are important to keep from over-fetching again.
  • Views can help in some, but not all cases. A view is similar to a table that you can create beforehand by executing a statement to predetermine values that may require on-the-spot calculation otherwise.
  • Change the syntax of the SQL to influence database SQL optimizer to generate a better query plan.

Conclusion

If your application is performing poorly, the problem often lies with the database, with inefficient queries. While there isn’t any solution that works for every single query out there, database experts can hone in on the ones that require optimization using diligent analysis and monitoring, along with the right SQL optimizer tool for sql server.

After they successfully find the queries behind slow database performance, all they have to do is take the right steps to resolve this issue. These include optimization techniques, such as adding indexes, editing out unnecessary fields, and inserting the WHERE clause wherever necessary.

The Importance of Disk Operations in Query Performance Tuning

Query Performance Tuning

DBAs can’t ignore disk operations when working on query performance tuning. When talking about databases, ‘disk’ may be called by one of its many names, such as ‘storage’, ‘I\O’, ‘Reads’, or disk operations.

Although database professionals know all these terms mean the same thing, these might confuse those outside this field. When referring to one of these terms, they usually mean the number of disk operations required to fetch the data from the Disk resource.

Why You Can’t Ignore Disk Operations During Query Performance Tuning

The fact remains, however, that an overwhelming majority of SQL Server databases face the bottleneck issue when it comes to the disk resource. This doesn’t change, regardless of whether you have old-school hard drives or the latest flash storage arrays. Given below are some major reasons behind this, and how these can be affected with MySQL query optimization:

  1. Most slow queries are slow because they have to scan a large amount of data. A lot of the time, this is unnecessary and it’s making your SQL Server perform a lot of unneeded and really sluggish read operations.
  2. When the database reads data, it requires a place to store that information- which it does in the RAM. However, since the RAM has a limited capacity, older information starts getting removed with newer data coming in.
  3. Because RAM is never enough, it is often unable to store all the data that SQL Server fetches. Therefore, the remaining data has to be kept on the disk, which is far slower than the RAM. The information that isn’t present in the RAM has to be fetched from the disk- an operation that is known as the slowest in all of the database operations. Some DBAs even compare data fetching from the RAM and the disk to sprinting and tip-toeing.
  4. So, if we tune a query to read less data than it did before, such as twenty rows instead of twenty thousand, it will help in two ways. Not only will it reduce the workload on the database in terms of disk operations, but it will also require far fewer resources, including CPU and RAM, to process all the data. That said, the end-user is unaware of all these operations- all they know and appreciate is the speed or the time it takes for the query to fetch information. To put it simply, they just want the screen on the app to return as quickly as possible. This is why query performance tuning focuses on decreasing disk reads.
  5. DBAs also perform tuning to lower the other resources, such as CPU or RAM usage. But they only do this in certain special situations where such resources are consistently being overused at dangerous levels. For instance, if the CPU is in constant use of 90% or above, then the DBA will consider CPU tuning.
  6. Tuning queries that fetch large volumes of data to fetch much smaller volumes instead improves SQL Server capacity. This is because when a query takes up fewer resources, it leaves room for more users and queries. This allows the same server to take greater loads than it could. Performing MySQL query optimization also improves the lifespan of the same server, delaying the requirement for a hardware upgrade.

Summing Up

The above-mentioned reasons shed light on the fact that disk operations play a major role in enabling efficient database query performance. You can’t always blame the CPU; in fact, you can rarely do so since 95% of bottlenecks occur on the disk resource.

The CPU, on the other hand, is only a lagging indicator whose use can decrease if the storage reads differ.

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

SQL Server

This blog is the continuation of the 2-part series to explain recovery models in SQL Server.

Each of these aims at different requirements to provide partial or complete data recovery. The Database Administrator selects the recovery model depending on the resource and data requirements of the organization. The goal of the recovery model is to balance the logging overhead with data recovery criticality.

Types of Recovery Models for SQL Server

Given below are the recovery models you can use in your backup and restore strategy:

Simple: This model does not support transaction log backups. SQL Server directly truncates the checkpoint operations log when using this recovery model. This frees up transaction log space to store additional transactions.

Although the Simple Recovery model is the simplest with regards to t-log backup management, it makes the user unable to carry out point-in-time database restores. This can lead to devastating data losses when your data changes on a frequent basis and your backups (full or differential) aren’t run regularly.

In other words, the frequency of your backups will determine the amount of data loss you are likely to experience if you have to restore your database using the Simple Recovery model.

Full: The use of this model will ensure the t-log file holds all your transactions until you run a t-log backup. No automatic truncation will take place here, unlike in the Simple Recovery model. Moreover, the Full Recovery model enables users to restore their database to any point in time as long as the transaction log backup has it. This minimizes data loss but is more likely to affect oracle database performance.

It is important to remember when using this model that the t-log will keep saving information as you make changes to the database. Therefore, you’ll have to carry out transaction log backups on a frequent basis to keep them from getting too large. Creating a t-log backup clears the previously-stored data, making space available to store new transactions.

The amount of disk space the transaction log takes does not change, nor should the user expect it to do so. That said, while you will have to pre-size the transaction log on the basis of expected activity, you can set its size to auto-grow in case it uses up all of its space. However, try to refrain from shrinking these files unless you don’t have a choice. These files are typically shrunk with the help of T-SQL commands in SQL Server.

Bulk-Logged: It shares a lot of similarities with the Full Recovery model, except for its minimal logging feature. In it, certain bulk operations aren’t fully logged in the transaction log, such as TRUNCATE, BULK import, and SELECT INTO. These operations are called minimally logged operations, thanks to which your t-logs won’t grow as much in size as compared to the Full Recovery model.

On the other hand, this type of operation keeps users from carrying our point-in-time restores. This is a disadvantage for many as it increases the chances of critical data loss. Therefore, experts recommend sticking with the Full Recovery model in cases where you’re unsure whether this model is the perfect choice according to your requirements. Despite its performance-affecting results, you will be able to guarantee data availability. You can still use SQL tuning tools to rectify this issue.

In Conclusion

Using the right recovery model, you can recreate and restore the entire database data in one step. This process overwrites the current database or creates it in case the database no longer exists. The ‘new’ database will be identical to the condition of the database when the backup took place, without the proceeding uncommitted transactions. These are rolled back once database recovery has taken place.

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.

Creating an Index in Oracle, and the Best Way to Make Use of It

Create index oracle

Indexes are among the most useful and underutilized components of SQL. The user can create an Oracle index and store values along with their location in it.

Similar to the index at the end of a book, an index enables the user to go straight to the data they are interested in. Indexes are most useful when a user has to find a few rows. Therefore, they can use an index in statements that return a handful of rows – after creating one, of course!

Simple Techniques to Create an Index in Oracle Database

Creating an index is a simple task in MySQL query optimization as you only need to know two things:

  • The columns that require indexing
  • The name you will give the index

Here’s how to create one:

create index <indexname> on <tablename> ( <col1>, <col2>, <col3>, … <coln> );

Eg. create index cars_colour_metallic on cars (colour);

However, there are a few things to know about indexes before you begin:

  • You can place several columns in a single index, which then becomes a composite or compound index.

For instance, in the above example, you could also add the types of cars in the index like this: create index cars_colour_metallic on cars (colour, type);

  • The order in which you set columns in the index affects its use by the optimizer.

Next, let’s take a look at two of the most important index types users create in Oracle.

Two Major Index Types – and When to Pick Each

There are several kinds of indexes in the Oracle database that can improve your SQL. However, one of the most significant decisions you’ll have to make is likely to involve choosing between B-trees and bitmaps.

Create Index Oracle: B-tree Versus Bitmap Indexes

B-trees:– Indexes are in balanced B-tree format by default, which means all the leaf nodes are located at the same depth. It takes equal effort (O(log n)) to access any value, and one leaf index entry contains one row of data.

Bitmap:- Bitmaps also store indexed values, but in a completely different manner as compared to B-trees. In it, one value entry is associated with a range of row values. A bitmap has a series of 1s (yes) and 0s (no) to indicate whether any of the range rows contains the value or not.

One major difference between these two index types is that a B-tree doesn’t include null indexed values; a bitmap does. A bitmap can, therefore, answer some statements during MySQL query optimization, such as targeted index searches in which the column has a null value.

Although this won’t work for a B-tree, the user can add a constant at the end of an index to turn it into a composite index.

Bitmaps are also helpful because compressing the bits is simpler, which is why a bitmap index is generally smaller as compared to a B-tree index with identical data.

Why You Need to Keep a Check on the Indexes You Create

With all the benefits an index provides, it is important to create as few of them as possible. This is because you may end up creating one for every specific requirement and forget about them over time. The same goes for other users who may come and go on your team. And no one will have a clue why Brad needed to create that six-column function-based nightmare.

Since you don’t know if the index in question is only used for year-end reporting or never used, you cannot drop an index whenever you want. This can result in awkward situations where a table contains more indexes than columns!

So, if you’re unsure between two excellent indexes and one “good enough” index, it is better to choose the latter. And don’t forget to test!

The Importance of Query Optimization in SQL Server Plus Tips

query optimization in SQL Server

The database is a vital part of a majority of IT systems as it contains that data that needs to be processed for utilization. SQL is the standard language for fetching data from the database using queries and query optimization in SQL Server helps maintain database performance.

The queries used to communicate with the database are almost always simple and quick. The most common operations include the four major data manipulation techniques (create, select, update, and delete) that execute at quick rates in most cases. However, as you already know, they aren’t the only tasks an administrator has to do in a database.

Why Experts Recommend Query Optimization in SQL

Operational databases may have a requirement to execute statements that take a long time. For instance, queries that need access to several tables or include tasks such as aggregation.

Although statements that operate on data warehouses aren’t time-critical, the ones running on operational databases are. They are necessary to fetch the requested information as quickly as possible – which makes SQL Server performance tuning important. One of the best examples of systems that need optimal SQL is one that has access to a geo server. A geo server contains millions of geographical information spread across countless tables.

Moreover, the database operations taking place on this data is often performance heavy. Such tasks may include the intersection of lands and calculations of area, the distance between two geographical locations, etc.

These involve complex geo operations that need a large amount of data present in multiple tables. If the SQL statement used for these operations isn’t efficient, it could take days or months to fetch the results. This is where the DBA will need query optimization in SQL Server to ensure the users don’t have to wait too long to get a result.

Some Quick Tips for Maintaining Optimal Database Query Performance

Take a look at some time-tested techniques of optimizing queries in SQL that not only save a lot of time and resources but are also useful for a wide range of databases:

  • Check efficiency using LIMIT – Many times, the queries have to run on a huge scale of data. You would not want to wait for your statement to execute completely before finding out that you have used the wrong statement or it was inefficient. Therefore, limiting your statement to a smaller amount of information can help you check its validity and with SQL Server performance tuning. Once you’ve run the query and are satisfied with its efficiency, you can carry it out on the scale you want.
  • Long statements can help – At times, you may have written statements that may seem simple to you as they didn’t take long to understand. However, they took too long to actually execute. In databases involving time-critical operations, the opposite would be preferable. Even though the queries will become complex and take a bit longer to understand, they will save a lot of time.
  • Give preference to quicker data structures – This depends on the programming-based knowledge of the DBA. For example, integer comparisons are far quicker than string comparisons.
  • Don’t be tempted to use IN operator – Checking the existence of certain data in a table may be important. But try not to use the IN operator as it slows down the result.

In Conclusion

As mentioned before, these tips are useful for a majority of databases out there. Since SQL is a declarative language, certain databases are likely to optimize for the cases we have talked about above.

However, test the tips before you try to apply them for query optimization in your database. In case some of these don’t work, you may want to consider using a tuning tool to make things easier. Get in touch with us to know how our tuning tool can simplify database query optimization for you.

Importance of Backup & Recovery in MySQL Database and SQL

MySQL database and SQL

A database is the cornerstone of any application. For this reason, maintaining one or more backup and recovery options remains a priority for every database professional. There are multiple alternatives you can choose from as per the specific needs of your organization’s database.

In this post, we will examine some of the most popular back-ups and restore strategies for MySQL database and SQL. We will also touch upon the reasons why databases require backups on a regular basis.

Why Do We Need Backups for MySQL Database?

As a DBA, you’ll need backup and recovery to support data in multiple cases, such as:

Discrepancies in Data: Users may accidentally delete or update incorrect data in the primary or replica node.

Data Centre Failure: An indefinite power outage or internet connectivity issue can spell trouble for your organization.

Disk Damage: If the disk is stalling for too long due to some kind of damage, it can greatly reduce performance. In cloud services for Oracle database, it translates into a broken DB instance that cuts access.

Broken Data: In case of a power outage, MySQL may fails to write data and close files as usual. There are also instances where MySQL fails to restart and doesn’t work despite the crash recovery process because of corruption in data.

Legislation/Regulation: Backups and recovery options ensure business value and client satisfaction.

Various Kinds of Backups for MySQL Database

Given below are some common backup categories that suit a range of needs:

Physical: These comprise the exact copies of database files and may contain part or all of the MySQL directory. The most common use of this type of backup is to make a new replica node and respond to host failure in a convenient manner. Experts recommend restoring data with the help of the same MySQL database version.

Offsite: This is one of the most recommended backup alternatives as it guarantees an untouched copy in case of data centre or host failure. It involves copying the data to the cloud, an external file server or another external source. However, sometimes it may take longer to download the files from the cloud or server than the recovery process. Therefore, experienced database professionals keep about a week of data locally on a backup server for quick recovery.

Logical: It is useful for smaller quantities of data as it is slower in comparison to physical backup methods. It essentially consists of dumps from INSERT and CREATE statements. It is useful in addressing data corruption or when you must recover a subset of tables. Although the output is greater in logical backups, especially when that data is present in text format, you can perform a quick compression if the software you’re using requires it. For instance, you can use Mydumper and mysqldump to compress and redirect the data to the zip folder.

Incremental: This type of backup contains all the changes made in the organization’s Oracle database and SQL server since the last backup. It is, therefore, quite useful for enormous datasets since it allows you to take small backups (experts recommend this after you’ve taken a full backup) as data comes later.

Differential: It consists of copying the modifications since your previous backup. One advantage of a differential backup is that it saves disk space. This is because the data in these backups mostly remains the same, so the result leads to backups that are substantially smaller in size.

Make Your Oracle Database Work Faster with these 6 Methods

improve performance of sql query

Every Oracle database admin dreads the day when they receive a flurry of complaint calls from users regarding slow online performance.

That’s why, if you’re responsible for managing database performance in your organization, you’ll want to ensure this doesn’t happen. How? By preparing your infrastructure to handle increased loads. Supporting it with performance tuning is also essential, especially when the systems slow down or become entirely unresponsive.

In this post, we’ll consider six important (yet often overlooked) methods and tips to improve Oracle database performance. These will also help you gain the most from your efforts and make sure your environment stays fully operational.

6 Ways to Optimize the Performance of Your Oracle Database

Take a look at the following tricks and tactics to improve Oracle database performance, explained in a simple manner: 

1. Maintain Optimum CPU Utilization Levels

It may be necessary to carry out manual tuning processes if, for some reason, you cannot apply the automatic diagnostic features Oracle provides. For this, you must make sure of complete CPU utilization in the user space. Check it to determine whether any non-database tasks are taking up CPU and restricting shared CPU resources.

2. Have a Firm Grasp of System Capabilities

Understanding the importance of baselines is essential to improve performance of SQL query. It helps to preserve a set performance baseline that you can refer to in case of performance issues.

You can do this by keeping an eye out for peak usage periods while measuring current performance. This will enable you to manage bottlenecks easily.

3. Make Use of an AWR (Automatic Workload Repository) Tool

An AWR is extremely useful as it gathers, processes, and maintains performance stats. It also provides a clear picture of your database’s health. The Automatic Database Diagnostic Monitor (ADDM) can help you gain actionable insights from the information you gain here. Together, these tools will save you time and money while enhancing the performance tuning process.

4. Tune Inefficient SQLs Using the Right Tools

The quickest and the least risky way to get rid of Oracle database problems is to tune the SQL using the right query plan. You can also use tuning tools for the Oracle database, such as those provided by Tosska Technologies Limited.

Tosska’s SQL Tuning Expert (TSE™) for Oracle® is ideal for all levels of experience and expertise. Its professional version, Tosska SQL Tuning Expert Pro (TSE Pro™) for Oracle®, can improve performance of SQL query with or without source code. However, these tools work best when you define and measure system issues correctly first.

5. Boost Storage Efficiency

It is possible to improve database performance by enhancing the performance of the storage platform. Review the existing storage for your Oracle database server and see if you can opt for alternatives.

For instance, flash technology offers considerably greater bandwidth and decreased latency to IO-bound workloads. It allows targeted provisioning, thereby boosting performance while eliminating the need to refresh the whole database. The key is to use flash storage for database objects that will benefit the most, leaving the rest in traditional storage.

6. Opt for a Dedicated Database Infrastructure

Sometimes, a common infrastructure platform can be the reason behind reduced performance results. No amount of tuning efforts proves sufficient in such cases. One of the most effective solutions is to provide a dedicated infrastructure to the database. This helps stabilize performance for database workloads and convenient management as well as effective monitoring and security. 

Ready to improve Oracle database performance in the simple ways mentioned above? You may choose to seek assistance from Tosska’s professional tuning tools for Oracle. They are designed to help you get the most out of your database and maintain a fully operational environment.

SQL Performance Tuning: 5 Things to Do for Faster Results

query tuning in sql

Is your SQL Server falling behind in terms of performance? Are poorly-written queries slowing down your applications? Before you set out in search of professional help, make sure you’ve tried everything you could to resolve the issues you’re facing on your database. 

Many problems related to SQL Server can be handled easily with preventative maintenance, patches, and a few activities performed on a regular basis. You can always depend on our SQL performance tuning tools if nothing works for a particular situation. But before that, read the five important things you can do to fix database performance.

5 Things to Do for Effective SQL Performance Tuning

Given below are five simple things you can do to improve database performance:

Check if your SQL Server is up-to-date

An older query engine out of active development is bound to get you in performance-related trouble every now and then. Moreover, the newer versions have much better diagnostic support and will make things faster in multiple ways.

For starters, they come with new versions of the query optimizer. Although Microsoft provides a few tweaks here and there in its service packs, major version releases contain all the best improvements. Other advantages include:

  • Bug fixes
  • New CPU instruction sets
  • Latest software development techniques

Even a 32-bit to 64-bit upgrade can go a long way in improving database performance. This will help regardless of whether you are performing SQL tuning for Oracle or SQL Server.

Increase the Memory

Maxing out its memory will make a difference in its performance. This is because the database utilizes it to cache data instead of making additional trips to disk. Additionally, you gain more memory for cache query plans and can use it for larger sorts and joins. Another advantage is a potential decrease in disk and CPU utilisation, which further helps with SQL performance tuning. Just remember to raise the memory configuration in your SQL Server so it actually makes use of the new RAM.

Open Task Manager

If things remain slow after you’ve increased memory and upgraded your SQL Server,  it’s time to open Task Manager. Sort by CPU, followed by memory, and close any running apps, processes, or software that’s eating up space and you’re unaware of. Configure exceptions for antivirus software, if you have anything installed.

Windows may also be caching data for file system access, stealing RAM from server-side apps such as SQL Server. This can be checked by looking at the Cached number in the “Physical Memory (MB)” tab.

Check the Event Log

This includes both – the Windows Event Log and the SQL Server Log – as they both have potentially useful information. In case Windows or SQL Server are facing any sort of issue, these logs will certainly have more details about it.

You’ll know whether SQL Server is lagging due to hardware-related problems, facing long disk wait times, or dumping core. There may be other services with issues on the server that you can find out about here.

You can also read our post on SQL tuning for Oracle for some useful tips.

See if SQL Server alerts are set up

It is important to ensure these alerts are configured. They will, in turn, help you make sure you’re updated on everything that’s happening in SQL Server. As the person in charge of the database, you need to be aware in case the storage is falling short or other serious errors in SQL Server.

SQL Query Performance Tuning: A Look at Various Plan Formats

SQL query performance tuning

Database professionals are often familiar with the fundamental maintenance tasks for SQL Server. However, they may have to perform the optimization of SQL queries and keep an eye on query plans from time to time.

This may lead to confusion as to which type of query plan they should use. Here, we will cover the different types and formats of query plans and how to obtain them for SQL query performance tuning.

Query Plan Types and Formats for Efficient SQL Query Performance Tuning

To start with, there are two major kinds of query plans: the estimated and the actual execution plan. Users can obtain these in three distinct formats – text, graphical, and XML.

Keep in mind that users who wish to create these execution plans will require SHOWPLAN permission first. This is also true in the case of query plans and SQL tuning for Oracle.

Text-Based Query Plans

This type of plan can be procured using one of the three methods given below:

  • Set SHOWPLAN_Text – The query will not be run by SQL Server, but this method should fetch information about the manner in which queries run. In short, this statement will display information regarding the Estimated Execution plan.
  • Set SHOWPLAN_All – Again, SQL Server won’t run the query but it will fetch thorough details regarding query execution, i.e the way it executes them and which resources it uses for this purpose. You can also get more details about the Estimated Execution plan.
  • Set Statistics Profile – SQL Server will run the statement and display comprehensive data regarding query execution. This information includes the precise number of rows that were actually processed and all the resources that have been utilised for executing these queries. This command will also fetch details regarding the Actual Execution plan.

Graphical Query Plans

This format allows users a look at numerous sources of information and plenty of tooltips in SQL Server Management Studio.

Note that if you want to view the Estimated Execution Plan, you can do so once you press Ctrl+L in the query window. To view the Actual Execution Plan in the same results set, press Ctrl+M.

XML Based Query Plans

This type of query plan gives the most comprehensive details of the plan in the extremely portable XML format. You can obtain query plans in this format using two methods:

  • Set SHOWPLAN_XML – The query doesn’t run but returns detailed information about how the statements execute and the resources used for the query execution. SQL Server also displays a detailed XML document that has the Estimated Execution plan.
  • Set Statistics XML – SQL Server runs the statement and shows information regarding query execution in exhaustive detail. This includes information on the actual number of rows processed as well as the resources applied in the query execution. It fetches a properly created XML document consisting of the Actual Execution plan, helping in SQL query performance tuning.

How these Query Plan Formats Differ in Use

The graphical format is usually the simplest to read which is why beginners usually start with them. The best way to read graphical query plans is from right to left moving upwards from the bottom while following the arrows. Additionally, you can make things easier with the Zoom In and Zoom Out functions.

You can view further details from Graphical plans with the help of ToolTips. All you have to do is point your cursor at the icon you want to know more about. While this is an extremely useful feature, it can get complicated to view every detail on a complex query with the help of this tool.

This format also forms the link between the Text and the XML formats. This is because you can save Graphical Plans in XML format. If you’re trying to perform SQL tuning for Oracle, you may consider a SQL tuning tool depending on your requirements.

Text plans, on the other hand, are more difficult to read and lack simpler rules to understand them. However, they prove useful for those who have experience with execution plans and know what and how to look for.