In this blog, we will cover Optimize Query Performance In SQL Server is potentially the most critical aspect of database performance. It also provides details on analyzing individual query performance and determining where improvement is needed.
Topics we’ll cover:
- What is Optimize Query Performance
- What are Execution Plans
- Types Of Execution Plans
- Database Normalization
- Table Indexes
- Data Compression
- Dynamic Management Groups
What is Optimize Query Performance
Optimize Query Performance in SQL Server are potentially the most critical aspect of database performance. It also provides details on analyzing individual query performance and determining where improvement is needed. And the query performance also depends on the volumes of data and transaction concurrency. Executing the same query on a table with only thousands of records requires more time than performing the same task on the same table with only a hundred thousand records.
Execution Plans For Optimize Query Performance
- In the Execution plan, the query optimizer is the component used to analyze SQL Statements and determines the most efficient way to access the data in the underlying table.
- The Query Optimizer takes in the query the database schema and the database statistics.
- The Query Optimizer then outputs the query execution plan. This is also referred to as the execution plan.
The way the Query Execution Plan gets defined depends on certain factors
- The sequence of the source tables – sometimes a query could be used to access several tables. Hence based on the query, the execution plan would need to determine which table needs to be accessed first.
- The method used to extract the data – sometimes there could be indexes in the table. Based on the indexes, the data can be extracted in a much more efficient manner.
- Sometimes there are methods in the query itself that are used to compute calculations or make use of filters, aggregates, and sorting of your data.
Check Out: Our blog post on Azure Database Migration Service Online.
Types Of Execution Plans For Optimize Query Performance
1.) Estimated Execution Plans
This is compiled plan based on just estimations. This Query plan will estimate that how many rows are returned from each table.
2.) Actual Execution plan
This is the complied plan plus the execution content. This would become available after the query is executed. This also includes actual runtime information. This would include execution warnings, elapsed, and CPU time for the execution.
3.) Live Query Statistics
This is the compiled plan plus the execution content. This also includes runtime information, such as the number of rows flowing through the operators. It also includes the elapsed time and the estimated query progress.
Database Normalization
The process of organizing data in the database is known as normalization. During the process of normalization, you develop the relationship between the tables in the database. With the help of normalization, you can also reduce redundant data waste.
Database Normalization Normal Forms
1.) First Normal Form
In the first standard form, you need to eliminate the repeating groups in individual tables. You create a separate table for each set of related data and then identify each related data with a primary key.
For example, you have a course table that stores information about the courses and then stores customers who have purchased the course. So you can have the same customer name purchase multiple courses. Instead of specifying the same customer name multiple times, ensure to create a separate customer table.
2.) Second Normal Form
In the Second standard form, you have to create separate tables for a set of values that apply to multiple records. And then relate these tables with the help of foreign keys.
3.) Third Normal Form
In the third standard form, you need to eliminate fields that do not depend on the key.
You have a customer table. This contains information about a customer that places orders on an e-commerce site. If the customer table has a field called course name so this is probably not required in this table and could be better to be in the course table
Table Indexes
A table index helps to find information within a table quickly. It is like finding a piece of page information using a book index similar to a table; a SQL Server index is an on-disk or an in-memory structure associated with a table or view. And helps to speed up the retrieval of rows from a table or a view. The Index is based on a column or multiple columns in the table. And based on the selected column, the data will be stored accordingly.
1.) Clustered Index
The clustered Index is used to sort and store the rows of the table based on their key values. Basically, you can have one clustered Index per table. This is because the data rows can only be sorted in one order.
With the help of clustered Index, you can use the indexes in the queries when it comes to the retravel of data, and when you create a primary key for a table, it will automatically create a clustered index. For Example: If you have a table and you go ahead and create a clustered index based on the let’s see the customer Id so when it comes to the pages when it comes to the actual disk which is storing the information, it is stored in sorted order.
2.) Non-Clustered Index
In the non-clustered Index, this provides the extra view of the index values are created. These index values point to the rows in the actual table, and you can create multiple non-clustered indexes for the table.
For Example: If we have the table and go ahead and create a non-clustered index based on the customer Id here, now an extra view is created, and you will have the values pointing on the rows in the actual table.
3.) Column Store Index
In the column store index, the data is physically stored in the column-wise format. In the traditional way of storing data in a table, the data is stored as a row store where the data is stored in a row-wise data format.
Column store indexes are normally suitable for the tables in a SQL data warehouse, so loading the data into the table becomes easier. So it allows for compression of data that helps reduce the storage size and improve the overall I/O performance when it comes to accessing table data.
Data Compression
- SQL Server version 2019 and Azure SQL database has support for row and page compression for row store tables and indexes. And the compression can be used to reduce the size of the database.
- It also helps to increase the overall performance when it comes to I/O workloads because the data gets stored on fewer pages on the disk. But extra cycles will be required to compress and decompress the data when accessed by an application.
- Rows and page compression include supports for the following database objects, like entire tables that are stored in the clustered Index, tables that are stored as a heap, an entire non-clustered index, and an entire viewed index.
Dynamic Management Groups
- These views are inbuilt in the SQL Server; these views and functions are available to return server state information. This information can be used to monitor the health of the underlying server instance.
- Using dynamic management views, you can diagnose problems and tune performance when it comes to your underlying server instance; these views are available for SQL Server, Azure SQL Database, Azure SQL managed Instance.
- There are dynamic views available at the server level. These require you to have the VIEW SERVER STATE permission on the server.
- There are dynamic views available at the database level. These require you to have the VIEW DATABASE STATE permission on the server.
- You also need to select permission on the objects referenced by the views.
Some Important Dynamic Views
Sys.dm_db_resource_stats
|
This view returns the CPU Usage and storage data for an Azure SQL database. The data gets collected and aggregated over five-minute intervals. Here historical data is retained for approximately 14 days. |
Sys.dm_tran_locks
|
This view gives you more information on the locks required on resources. If you have fewer locks, you can increase the concurrency of your requests. |
Sys.dm_exec_requests
|
This view returns the information about each request that is executing on the SQL Server. |
Sys.database_scoped_configurations
|
These are the settings available at the database level. |
Related/References
- Exam DP-300: Microsoft Azure Database Administrator Associate
- Microsoft Certified Azure Database Administrator Associate(Hands-On Labs)
- Azure SQL Deployment Options | SQL Managed Instance | SQL Database| SQL On VM
- Migrate SQL Server To Azure SQL Database
- Implement A High Availability And Disaster Recovery Environment
Next Task For You
We will cover all the exam objectives related to performing migrations, Hands-On Labs, and practice tests in our Azure Database Administrator training program if you want to begin your journey towards becoming a Microsoft Certified: Azure Database Administrator Associate by checking our FREE CLASS.
Leave a Reply