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 TypeDetails
ClusteredIndex 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.
NonClusteredIndex sorts data on a table can be more than one NonClustered Indexes per Table, Key Value stored in a different place that data.
Uniqueindex ensures the uniqueness of data on a table.
ColumnstoreColumnstore index is suggested for data warehouses, query performance is 10x faster and data compression 7x better over the standard index.
FilteredA filtered index is a NonClustered index which allows defining a filter in the Where clause.
XMLXML index gives the ability to create an index on XML data type column.
SpacialA spacial index can store spatial data type, like geometry etc.
Full-TextFull-Text index use the Full-Text Engine for full-text queries, only one full-text index is allowed per table
HashHash 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,

 

 

 

 

Share This