Posted on

How to build indexes for multiple Max() functions for SQL Server?

For some SQL statements with multiple Max() functions in the select list and nothing in the Where clause, we have different methods to create new indexes to improve the SQL speed.

Here is an example SQL, it is to retrieve the maximum name and age from the employee table.
select   max(emp_name),
     max(emp_age)
from  employee

The following is the query plan that takes 9.27 seconds.

The SQL cannot be tuned by SQL syntax rewrite or hints injection, and the SSMS cannot recommend any index to improve the SQL.

For this kind of SQL that we can consider building a composite index or two individual indexes for emp_name and emp_age. A new composite of these two columns (emp_age, emp_name) can improve the SQL around 7 times. The following is the query plan shows that the new composite index is used, but it has to scan the entire index for these two stream aggregate operations before getting the max(emp_name) and max(emp_age).

How about if we build two individual indexes for emp_name and emp_age. The following is the result and query plan of these two indexes created. A Top operator selects the first row from each index and returns to the Stream Aggregate operation, and then a Nested Loops join the two maximum results together. It is 356 times much faster than the original SQL.

This kind of indexes recommendation can be achieved by Tosska SQL Tuning Expert Pro for SQL Server automatically:
Tosska SQL Tuning Expert Pro (TSES Pro™) for SQL Server – Tosska Technologies Limited

Posted on

How to build indexes for slow first execution SQL – SQL Server?

You may suffer from SQL statements with a slow first execution time due to the long data cache process. The following SQL is simple that retrieves records from the EMPLOYEE table that if EMP_SALARY < 500000 and the result set is ordered by EMP_NAME.

Select emp_id,
    emp_name,
    emp_salary,
    emp_address,
    emp_telephone
from    employee
where  emp_salary < 500000
order by emp_name;

The following is the query plan that takes 9.51 seconds for the first execution and takes 0.99 seconds for the second execution without data cache.

The SQL cannot be tuned by SQL syntax rewrite or hints injection for both the first execution and the second execution, it is because SQL Server has selected the best query plan for this simple SQL statement. But the problem is that if the condition “where emp_salary < 500000” is changed; say from 500000 to 510000 or the EMPLOYEE data is flushed out from the memory, the execution time will then be prolonged up to 9.51 seconds.

Let’s see if we can build indexes to improve this situation. There is a common perception that a good index can help to improve both the first execution time and the second execution time. So, I use a tool to explore a lot of indexes configurations, but none of them can improve both executions’ performance. Here the following is the performance of the second execution with data cached for different indexes proposed by the tool. You can see the performance of “Index Set 1” is close to the original SQL performance with a little performance variation due to the system’s loading status and all other indexes sets are worse than the original SQL. Normally, we will give up the tuning of the SQL statement without even trying to see whether those recommended indexes are good for the first execution time.

I did a test for those recommended indexes to see whether they are helpful to improve the first execution time, it surprises me that the “Index Set 1” is tested with a significant improvement and improves the first execution time from 9.51 seconds to 0.65 seconds. It is a 14 times improvement that can make my database run more efficiently. So, you should be very careful to tune your SQL with new indexes that may not be good for your second execution with all data cached, but it may be very good for your first execution without data cached.

This kind of indexes recommendation can be achieved by Tosska SQL Tuning Expert Pro for SQL Server automatically.

Tosska SQL Tuning Expert Pro (TSES Pro™) for SQL Server – Tosska Technologies Limited

Posted on

How to build indexes for multiple Max() functions for SQL Server?

For some SQL statements with multiple Max() functions in the select list and nothing in the Where clause, we have different methods to create new indexes to improve the SQL speed.

Here is an example SQL, it is to retrieve the maximum name and age from the employee table.

select max(emp_name),
     max(emp_age)
from  employee

The following is the query plan that takes 9.27 seconds.

The SQL cannot be tuned by SQL syntax rewrite or hints injection, and the SSMS cannot recommend any index to improve the SQL.

For this kind of SQL that we can consider building a composite index or two individual indexes for emp_name and emp_age.  A new composite of these two columns (emp_age, emp_name) can improve the SQL around 7 times. The following is the query plan shows that the new composite index is used, but it has to scan the entire index for these two stream aggregate operations before getting the max(emp_name) and max(emp_age).

How about if we build two individual indexes for emp_name and emp_age. The following is the result and query plan of these two indexes created. A Top operator selects the first row from each index and returns to the Stream Aggregate operation, and then a Nested Loops join the two maximum results together. It is 356 times much faster than the original SQL.

This kind of indexes recommendation can be achieved by Tosska SQL Tuning Expert Pro for SQL Server automatically.

Tosska SQL Tuning Expert Pro (TSES Pro™) for SQL Server – Tosska Technologies Limited

Posted on

Do not undermine your SQL Server’s potential ability

For some SQL statements that are failed to be tuned by syntax rewrite, hints injection, and all necessary indexes are built, people may think that hardware upgrade is the only way to resolve the performance problem. But, please don’t undermine your SQL Server’s SQL optimizer which can provide you with the ultimate performance solution that you may not have imagined before. What you need to do is to provide SQL Server with a set of proper new indexes.

Here is an example SQL, it is to retrieve the minimum employee’s salary and the emp_id that with salary greater than all salary of the emp_subsidiary table with subsidiary’s employees’ department = “AAA”.

SELECT emp_id,
    (SELECT min(emp_salary)
     FROM  employee)
FROM  employee
WHERE emp_salary > (SELECT max(emp_salary)
           FROM emp_subsidiary
           where  emp_dept = ‘AAA’)

Although all columns that show in the SQL are indexed, the following query plan takes 44 seconds.

The SQL cannot be tuned by SQL syntax rewrite or hints injection, and the SSMS can recommend only one index on one table for a SQL statement, it is failed to recommend any good index. So, the SQL cannot be tuned in any traditional way.

Let’s use our new A.I. index recommendation engine to see if there are any good index solutions. A set of indexes is recommended listed in the following. It takes only 0.55 seconds.

Example: 80 times faster A.I. SQL index recommendation

The query plan shows that two new indexes are used at the same time that the SSMS is not able to provide.

Tosska SQL Tuning Expert Pro is in-built with an A.I. engine to recommend indexes for multiple tables at the same time for a SQL statement. The new technology is so powerful to recommend multiple tables’ new indexes for a SQL at the same time, it means that how each new table’s indexes affect each other in the query plan will be considered by the engine. It is very helpful for SQL Server’s SQL optimizer to explore more potential query plans that could not be generated before. So, don’t undermine your SQL Server’s ability. Instead, use the right tool to tune your SQL statements before you are planning to upgrade your hardware.

Tosska SQL Tuning Expert Pro (TSES Pro™) for SQL Server – Tosska Technologies Limited

Posted on

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.

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.

Posted on

Selecting a SQL Server Database Version for Your Company

SQL Server database and SQL

Choosing the right version of SQL Server is important for the performance you desire. If you’re installing an older one because your organization’s management prefers an older build or the vendor is unable to support newer versions, it is important to let them know which version your company needs, and why.

For this reason, we will discuss some popular versions of SQL Server from older to newer and mention their advantages in this blog.

Which SQL Server Version Works Best with SQL Performance Tuning?

Knowing the versions that support this task is extremely important because it will give you the ability to improve the SQL Server database and SQL performance.

To that effect, we will discuss the SQL Server 2016, 2017, and 2019 versions here.

SQL Server 2016

This version was chosen by a lot of independent software vendors or ISVs for one reason – 2016’s Service Pack 1 edition came with Enterprise features in Standard mode. These helped create a single application version that worked simultaneously for both Standard as well as Enterprise clients.

Advantages of Choosing this Version:

  • It is easy to find support material online as this version is quite popular and numerous database professionals are well-versed with this version’s tools.
  • Standard Edition users may find this version appealing since it supports 128GB RAM and additional space for internal functions such as query plans.
  • Support for this version ends after 2026 – longer than the older versions (2012/2014).
  • Newer applications that have additional compliance requirements will benefit from features in this version such as Always Encrypted, temporal tables, and Dynamic Data Masking. These will make it somewhat easier to protect and monitor sensitive information.
  • You can have both row store and column store indexes in this version, unlike the earlier ones that only had row store indexes.
  • If you need query plan monitoring to help with SQL performance tuning, you can use the Query Store’s features provided in SQL Server 2016 for this purpose.

SQL Server 2017

Being a newer release, it is one of the most regularly updated versions with patches coming in almost every other month. These patches are important because they resolve significant problems. It also comes with a minimum commit replica configuration to ensure commits are accepted by several replicas.

Advantages of Choosing this Version:

  • The upgrades are easier to get from this version onward due to a Distributed Availability Group that contains multiple SQL Server versions in it. Before this, we had AG version upgrades that were not as convenient, leading most users to construct a new cluster and migrate to it rather than opt for an upgrade.
  • This version contains batch mode execution plans, which gives those who require high-performance column store statements an advantage.
  • If you must run your SQL Server on Linux, you may consider SQL Server 2017 as several bugs have been resolved in the Cumulative Updates.
  • It’s a newer version so support will last longer than that of its predecessor.

SQL Server 2019

Released on November 4, 2019, this version is the latest in the SQL Server series. Naturally, it comes with the longest support lifespan, i.e. it will be supported until 2030. This version also receives regular patch updates to fix many significant issues in the form of Cumulative Updates.

Changes and Features in this Version:

  • Patch contents aren’t documented anymore. Moreover, you are likely to receive updates with undocumented new features – something to consider in case you require it for mission-critical production environments.
  • There is a bit of a learning curve thanks to some cutting-edge features in this version, so be prepared to perform some experimentation as you learn.
  • Some of the best performance features are included in the 2019 compatibility mode. However, you will have to keep a close eye on all SQL Server databases and SQL queries – even the ones running fast at present – as these will alter your current execution plans. In other words, you will have to test both slow and fast queries to make sure the slow ones speed up and the fast ones don’t fall behind in performance.
  • Table variables have gotten better in this version along with user-defined functions.
  • Additional features to watch out for including Big Data Clusters, Java support, and high container availability, so you may want to explore this version if you’re looking for perks like these in the SQL Server you want.

In Conclusion

At this point, SQL Server 2017 might seem like the best version to go with, thanks to a balance of features, stability, and support lifespan. Furthermore, you’ll receive plenty of help with SQL performance tuning – a lifesaver for overworked professionals who may not have the time or resources to upgrade every server every year.