Azure SQL Database – Automatic Index Tuning

Introduction: -

We live in an era of “Automation”.  Planes run on auto pilot mode, cars will soon be auto driven, so why not have auto indexed databases? This article will discuss the automatic index tuning feature available in Azure SQL Database.

Problem Statement:

Application development is moving at a rapid pace and its very common for application teams to develop a template database design and deliver it to multiple customers.  It is even more common in cloud where solutions are delivered as “software as a service” and it is way too challenging for application development team to analyze the workload of individual database, design specific indexes for them and tune them.

Automatic tuning in Azure SQL Database

Azure SQL Database, the cloud Platform as a service (PAAS) offering of SQL Server has attempted to address the above problem by introducing “Automatic tuning”. As of date, “automatic tuning” has the following features

·         Automatic plan correction – Plan regressions or sudden change in query plan resulting in performance degradation are tracked and last good plan available is forced. Automatic plan correction is already well explained over here

·         Automatic Index Tuning:  Automatic index tuning feature of Azure SQL Database will study the workload executed against the database, identify useful indexes and create them automatically. Automatic index tuning will also drop indexes that are unused over 93 days or any duplicate indexes present.

How to enable automatic index tuning

Once logged in to Azure Portal (portal.azure.com) one can enable the same by navigating to SQL Databases-> Click on the database -> Automatic tuning

·         Force plan -> Enables Automatic Plan Correction

        ·         Create / Drop Index -> enables automatic index tuning

Features can be enabled at server level and can be inherited at the database level as well.

TSQL Script for enabling automatic index tuning provided below

ALTER DATABASE DBName

          SET AUTOMATIC_TUNING (CREATE_INDEX = ON, 
      DROP_INDEX = ON)

Example:

To test how Auto Indexing works, following things were performed

·         A sample database with 5 tables with no indexes was created

·         10 to 15 select queries against the tables were executed repeatedly few thousand times over 3 to 4 hours

·         After one day, automatic indexing found the recommendations and rolled them out when there was less or no workload on the database.  Auto Indexing requires minimum one day of monitoring to identify recommendations

 This can be verified on Azure Portal by clicking on the “Database name” -> “Performance Recommendation”

The same can also be verified via the DMV sys.dm_db_tuning_recommendations, documented clearly over here

Key pointers of Automatic Index Tuning

·         Indexing recommendations are obtained using advanced artificial intelligence rules applied on the monitored workload. The recommendations are expected to be lot more accurate than earlier missing index recommendations from SSMS

·         The best part of the feature is it is non-blocking during the rollout of new indexes. 

·         Index changes wouldn’t be rolled out and will be postponed, if DTU usage was above 80% anytime in the last 20 minutes

·         Automatically created indexes don’t enforce any dependency on the columns. When the column with automatically created index is dropped, index is dropped as well

·         Auto created indexes can be tracked via sys.indexes – auto_created column

Learn – Adapt - Verify

One of the key features of Automatic Index tuning is the ability to monitor the impact of the changes done via automatic tuning. Azure SQL Database closely monitors the automatically created or dropped indexes and verifies if it has achieved the desired performance improvement. If it doesn’t then the change is automatically reverted to prevent any negative impact to the database. The reverted changes can be tracked from sys.dm_db_tuning_recommendations DMV as well.

Suggested enhancements

·         Automatic indexing is available only as an Azure SQL Database feature and is not available on SQL Server on premise. If would be wonderful if the feature could be extended to on premise SQL Server.

·         Automatic Index tuning should be extended to fixing fragmentation issues of the indexes as well.

Conclusion

 Automatic Index Tuning does simplify one of the major tasks of a database administrator and a developer. Automatic Index Tuning may not be 100% perfect to get the choices correct all the time but it is certainly a step in the right direction. Expect Microsoft to release more of such features in the days to come not just in indexing but also all areas of database maintenance

Data Channel Interview - 09 - Mr.Madhivanan on Best Practices in TSQL Programming

Dear All,

Very happy to share the 9th interview of #DataChannel. Mr.Madhivanan Ramachandran, 10 time Microsoft MVP Award winner and a Data Platform expert from India discusses "Best Practices for TSQL Programming".  Mr.Madhivanan demystifies quite a few concepts with his to the point answers in this interview. Happy Learning!!!





Regards,
Nagaraj 

Residual Predicate and "Number of Rows Read" in Execution Plans

What is Residual Predicate?

Lets say one gets a query to fine tune. While checking the execution plan, if there is a Index seek, it is common for DBAs to think that the query performance is good and acceptable. Though in most of the scenarios, the idea of looking for index seeks is acceptable, there are quite a few scenarios where index seeks simply doesn't mean optimal performance. One such scenario is explained below. Consider the following query

SELECT *
FROM [Production].[TransactionHistory]
WHERE Productid = 801
AND TransactionID % 3 = 0

Table has a non clustered index on "ProductID" and clustered Index on "TransactionID".

 

 

 

The picture above as expected indicates an Index seek.

 

 



Screenshot of Index Seek Operator's details provided above.  The seek predicate section at the bottom indicates that "Index Seek" operator was used for "ProductID = 801" filter alone.

Observe the section marked in red. "Predicate" section shows

"[Production].[TransactionHistory].TransactionID % 3 = 0 ".

 

What it implies is the index seek filtered only for "ProductID = 801" filter condition. Additional filtering (outside the index) had to be done  for "TransactionID % 3 = 0", after the Index seek operation. This additional filters for the rows that are extracted from "Index Seek" are termed as "Residual Predicates". If the work done by residual predicate is too high then it implies that the Index is not effective.  

 

"Number of Rows Read" n "Residual Predicates"

On the last post, I wrote about "Number of Rows Read". Just to recap, "Number of Rows Read" indicates the number of read by the operator. "Actual Number of Rows" is the rows returned by the operator. Observe the section highlighted in Green in picture above.

Number of Rows Read: 519
Actual Number of Rows:171

The above numbers imply that Index seek operator's seek predicate ( "ProductID = 801" ) filtered 519 rows. The additional filter " "TransactionID % 3 = 0" filtered it further to 171 rows.

The difference in "Number of Rows Read" and "Actual Number of Rows" is due to the additional rows filtered for "Residual Predicate". "Number of Rows Read" information on execution plans has made it much easier to track the additional costs incurred due to "Residual Predicates"

Hekaton Part 10: Things Hash Indexes Can do

Hash Indexes on "In memory" tables are best suited for equality searches. Reason is obvious, as they are based on hash algorithm which doesn't sort data, hash indexes aren't useful for sorting or non equality searches ( Refer here). But, hashing being a fast and a effective technique, hash indexes are perhaps the best bet for handling equality searches ( where conditions using "=" ). Note that, equality searches are by far the most common filter condition you would see in queries. So, it is one of those things Microsoft has done to address the obvious with best possible solution ignoring the not so common problems :) Besides equality filters other areas where hash indexes can be useful are

Group by clause:

Consider the following query

SELECT count(*),data
FROM [HK_tbl]
GROUP BY Data

Query plan indicates a index scan but no additional sorting or grouping required, as even though data is not sorted by hash index, it is grouped / categorized and hence it is very effective for processing "group by" queries.

Inner Join:

HK_tbl2 is another "in memory" table with hash index on data column.

SELECT HK_tbl.data,HK_tbl2.dt
FROM HK_tbl
INNER JOIN HK_tbl2
ON HK_tbl.data = HK_tbl2.data

Outer table is scanned as there is no filtering condition while the inner table makes use of the "Hash Index" . Query plan shown below

 
Left Outer Join:

SELECT HK_tbl.data,HK_tbl2.dt
FROM HK_tbl
LEFT OUTER JOIN HK_tbl2
ON HK_tbl.data = HK_tbl2.data

Semi equijoin condition like left outer join also benefits from hash index

 

Hekaton Part 9: Things Hash Indexes can't do

Last few posts have explored hash indexes in depth. This one will deal with the limitations on Hash Indexes. What are things hash indexes can't do?
Hash indexes are based on hash functions, which doesn't sort the data at all like conventional (binary tree indexes). Hash Indexes just categorize the data into hash buckets which help in fetch the required row faster.

 "HK_tbl" is a in memory table, with column "data" having a hash index.

Order by:

Consider the following query using Order by:

SELECT TOP 1000 [ID]
,[Data]
,[dt]
FROM [HKDB].[dbo].[HK_tbl] WITH (SNAPSHOT)
ORDER BY data desc;

Query plan shown below:

Observe the "Sort" operator. Sort operator indicates that the data was sorted and index didn't avoid sort operation.

Pattern match using like and '%'

SELECT TOP 1000 [ID]
,[Data]
,[dt]
FROM [HKDB].[dbo].[HK_tbl] WITH (SNAPSHOT)
WHERE data like 'C%'  

Observe the filter operator. Filter operator indicates that the predicate ( or the filtering condition ) was applied after the index was scanned. The number of rows which came out of index scan operator would also confirm that index scan operator read the entire table.

Similarly, non equality wouldn't be using hash indexes. For handling range scans, sort operations, the other index type called range index ( also called as in memory non clustered index ) in "in memory" tables would help. 

On next post, we will see the scenarios, which explore scenarios where hash indexes are most useful.
 

Hekaton Part 8 - Hash Collisions - Hash_Index_Stats DMV

Continuing from previous post , this post will discuss a few scenarios on hash collisions.
 

The following "in memory" table with a hash index is created. Please note that the hash bucket count has been set to 1024. Hash bucket count 1024 implies that hash collisions are certain after row count increases 1024.

CREATE TABLE dbo.hash_collision

(
[ID] Int identity(1,1) PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024) NOT NULL ,
[Data] uniqueidentifier DEFAULT newsequentialid() ,
[dt] datetime NOT NULL ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

 

Step 1: Load 500 rows

 Insert into hash_collision(dt) select getdate()

GO 500

 

dm_db_xtp_hash_index_stats dmv can be used to check statistics on hash indexes n hash collisions. At 500 rows, the hash index stats indicates that the buckets are partially filled in.

 

SELECT
* FROM

sys.dm_db_xtp_hash_index_stats

WHERE object_name(object_id) = 'hash_collision'

 

 

•  "Total Bucket count" indicates the number of buckets in the hash index which is fixed.

 

• "Empty Bucket count" indicates the number of buckets that are empty. In this case, close to 50% are empty as we have inserted only 500 rows.

 

• "Average Chain Length" indicates average length of a hash chain. In other words, average number of hops one may need to take to find a row.

 

 

 

Step 2: Add another 500 rows

 

Let us add another 500 rows and check the index status from dm_db_xtp_hash_index_stats

 

Insert into hash_collision(dt) select getdate()

go 500

 

select * FROM sys.dm_db_xtp_hash_index_stats WHERE object_name(object_id) = 'hash_collision'

 dm_db_xtp_hash_index_stats indicates that 80% of the buckets are full but the average hash chain length is still at 1 as there are still a few empty hash buckets.

Step 3: Add 9000 rows

Let us add few more thousands - say 9000 rows. Now table contains 10,000 rows but only 1024 hash buckets

Insert into hash_collision(dt) select getdate()
go 9000

SELECT * FROM sys.dm_db_xtp_hash_index_stats
WHERE object_name(object_id) = 'hash_collision'

  Notice a sharp increase in "Average Chain Length" as there more values than the number of buckets. The number of empty buckets is obviously zero.

So does longer hash chain affect the performance? Longer hash chains cause reads or the index scan to be slower. So, it is important to pick the hash bucket count carefully. General recommendation is at least 2 times the number of distinct values in the table. Also, it is always better to over size the hash bucket count instead of under sizing it.