In this post, you will read about the differences between index tuning in SQL Server (on-premises) and SQL Azure (cloud). But what is an index?
Consider an index as a contents page. When comparing a SQL database index and a Contents page in a physical book, you note there are no differences between the two.
For example, if there is a need to find a specific topic in a book, you can refer to the book’s contents page. Now, for the same example in a SQL database, when need to search for a value, a database index is used to find the result faster.
|Clustered||Index can be assigned only to a PRIMARY KEY, There can be only One clustered Index per Table, By default a clustered index is created with a primary key, Stores and Sorts Data in a table based on the Key Value.|
|NonClustered||Index sorts data on a table can be more than one NonClustered Indexes per Table, Key Value stored in a different place that data.|
|Unique||index ensures the uniqueness of data on a table.|
|Columnstore||Columnstore index is suggested for data warehouses, query performance is 10x faster and data compression 7x better over the standard index.|
|Filtered||A filtered index is a NonClustered index which allows defining a filter in the Where clause.|
|XML||XML index gives the ability to create an index on XML data type column.|
|Spacial||A spacial index can store spatial data type, like geometry etc.|
|Full-Text||Full-Text index use the Full-Text Engine for full-text queries, only one full-text index is allowed per table|
|Hash||Hash index allow data accessed via an in-memory table.|
SQL Server Tools
Database Engine Tuning Advisor: This tool embedded in SSMS. The main job is to analyze SQL queries and recommend indexes, statistics, etc.
This tool provides two user interfaces, a GUI (Graphical User Interface) and a command prompt utility, called dta.
For more details see here.
SQL Server Profiler: A very useful profiling tool which stores SQL queries in various ways ( Trace File, Template, Table, XML File, etc.) [Deprecated Feature]
This is a user-friendly GUI tool which provides trace analysis results, this tool can help find and diagnose slow-running queries, audit actions etc.
For more detail see here.
Activity Monitor: A tool that displays information about SQL Server Processes, Resource Waits, I/O, Expensive Queries.
This tool gives the ability to dba’s by open panes to monitor a SQL Server system. The panes are expandable and allow to see more details about SQL Server workloads.
For more details see here.
SSMS: Provides abilities like Estimated Execution Plan, Live Execution Plan, Live Query Statistics.
SQL Server Management Studio is an IDE for SQL Server service management. Provides a lot of tools for some of which are mentioned above. And also allow interacting with SQL Server engine via T-SQL.
More details and Tool Download from here.
DMV (Dynamic Views): These views & functions are able to help with health monitoring, diagnose and tune queries.
There are two types of Dynamic Management Views, server-scoped and database-scoped. Both of them required different permission on the server.
For the server-scoped, requires VIEW SERVER STATE and for the database-scoped requires VIEW DATABASE STATE permission. [Details about SQL Server Permissions see, here.]
For System Dynamic Management Views, please check in this Microsoft document.
An example of DMV query is :
SELECT TOP (10) [session_id] ,[wait_type] ,[waiting_tasks_count] ,[wait_time_ms] ,[max_wait_time_ms] ,[signal_wait_time_ms] FROM [sys].[dm_exec_session_wait_stats]
SQL Azure Tools
Microsoft Azure offers databases, and intelligent insights for table indexes. Based on these results it provides recommendations to improve better performance.
This can be accomplished using: Automatic index management, Automatic plan correction, Adaptive query processing.
For more details please check here.
Automating tuning: By set ON the state of automating tuning options, is like you turn on the auto-pilot on an airplane. It sounds a little strange but it’s true!
Performance Recommendations: This feature provides tuning recommendations for the database/s to the end-user.
Query Performance Insight: This dashboard displays the TOP 5 queries ordered by CPU, Data IO, Log IO.
And get the Query details,