Performance Tuning In Microsoft SQL Server with SQL Server Management Studio

Tram Ho

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

  1. 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 below

    Method 2 Press Ctrl + M in 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 plan tab 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.

  2. 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 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 Plan to 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.

  3. 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

  4. 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 Advisor Then the login screen will appear, fill in the login information and then select Connect to continue. DETA interface will be displayed as shown below You can choose Start Analysis on the toolbar to start analyzing, or change the options as you like on the General tab and Tuning Options and then click Start 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 the Reports 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.

Share the news now

Source : Viblo