Speed improvements have always been a problem for medium and large projects, and most of the reason for this delay is the problem of accessing data from the database. Today we will learn together the methods to find the cause and improve the speed in the database management system of Microsoft SQL Server (called SQL Server) by means of SQL Server Management Studio (abbreviated) is SSMS).
SQL Server
SQL Server is a database management system (DBMS) developed by Microsoft to store and manage data. It also provides other useful tools such as report writing, data import / export, and data analysis.
SQL Server Management Studio
SSMS is an advanced development environment that allows us to configure and manage DBMS. SSMS includes many tools for manipulating SQL Server.
Tools to tune DB
Execution Plan
The first step to understanding and analyzing a query is to look at its execution plan. The Execution plan is a form of diagram showing the activities performed by the database engine when running a query. To enable query execution with execution plan attached, you can follow one of the methods below
Option 1 Click the
Include Actual Execution Plan
icon as shown belowMethod 2 Press
Ctrl + M
in SSMS interfaceMethod 3 Select
Query -> Include Actual Execution Plan
After only enabled, the execution plan will be displayed with the results of the query on the
Excution plan
tab as shown belowYou can hover on each item of the plan to see detailed information
The execution plan tells us what types of objects the query uses (table, index, etc.) and how they are used (the order in which objects are called, how they are called – seek or scan, join types, etc.). ). Based on that we can analyze what is the cause (or part of the query) that causes perfomance problem.
Activity Monitor
Slow and time-consuming processes on a database often consume a lot of processor, memory and disk. So we can rely on that to detect queries that need speed improvement. To open the activity monitor, you can click on the
activity monitor
icon as shown belowQueries that have been recently performed are taking up a lot of resources and execution time will be displayed in the
Recent Expensive Queries
You can right-click on a line and select
Show Execution Plan
to view execution details of that queryIn addition activity monitor also has a lot of other useful information to support the monitoring and management of db activity. I will go into more detail in this article in another article.
Dynamic Management View
Dynamic Management View (DMV) is the view and function made available in SQL Server. These views and functions return database server information, help monitor server status, diagnose problems, and improve performance.
References on the homepage of Microsoft doc:
Details on how to use DMV to see perfomance statistics
List of available DMVs in SQL ServerDatabase Engine Tuning Advisor
Database Engine Tuning Advisor (DETA) is a tool used to analyze one or more sql statements, then gives suggestions on how to improve performance (add index, add partitioning) to analyze one (or more) queries, We select that query, right click, and then select
Analyze Query in Database Engine Tuning Advisor
Then the login screen will appear, fill in the login information and then selectConnect
to continue. DETA interface will be displayed as shown below You can chooseStart Analysis
on the toolbar to start analyzing, or change the options as you like on theGeneral
tab andTuning Options
and then clickStart Analysis
.After successful analysis, if DETA has improved, the content will be displayed on the
Recommendations
tab, while detailed reports on analysis will be displayed on theReports
tab.Above are a few methods to monitor, analyze, and find ways to improve the speed of SQL Server queries with SQL Server Management Studio. In the next article, I will go into more tips and tricks that can be used to improve performance.