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.

Index Types

Index Type	Details
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,