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 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
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 Planicon as shown below
Method 2 Press
Ctrl + Min SSMS interface
Method 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 plantab as shown below
You 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.
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 monitoricon as shown below
Queries 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 Planto view execution details of that query
In 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 Server
Database 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 AdvisorThen the login screen will appear, fill in the login information and then select
Connectto continue. DETA interface will be displayed as shown below You can choose
Start Analysison the toolbar to start analyzing, or change the options as you like on the
Tuning Optionsand then click
After successful analysis, if DETA has improved, the content will be displayed on the
Recommendationstab, while detailed reports on analysis will be displayed on the
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.