Test DML-DDL performance
Job details
| Name: | Test DML-DDL performance | |
| Platform: | Sqlserver | |
| Category: | Performance | |
| Description: | Runs performance test on the database. The procedure executes SELECT, INSERT, UPDATE, DELETE (and TRUNCATE) statements on the test table. | |
| Long description: | Runs performance test on the database. The procedure executes SELECT, INSERT, UPDATE, DELETE (and TRUNCATE) statements on the test table. | |
| Version: | 1.7 | |
| Default schedule: | 10 22 5 1,30 | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘1999′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| records number | -9 | Number of rows to insert into the test table. |
Job Summary
- Purpose: The purpose of this job is to assess the performance of DML (Data Manipulation Language) and DDL (Data Definition Language) operations on a database by executing SELECT, INSERT, UPDATE, DELETE, and TRUNCATE statements on a test table.
- Why: This job is important as it helps to determine the efficiency and speed of these operations, which are crucial for the overall performance and responsiveness of the database. It enables database administrators to identify potential issues and areas for optimization.
- Manual checking: You can check this manually in the database by issuing SQL commands to perform similar DML and DDL operations and measuring their execution time.
Job Details
- “dbwatch-task”: This encapsulates the entire job configuration for dbWatch Control Center.
- “category”: Performance – Indicates that the job is related to database performance monitoring.
Specific Task Details
- “astask”: Represents a single task within the dbWatch job.
- “name”: Test DML-DDL performance – This is the name given to the task.
Parameters
- “parameters”: Defines specific parameters used by the task.
- “records number”: Defines the number of rows to manipulate during performance tests. Special values control different sets of tests, for example, -9 runs very large tests while -1 runs smaller scale tests.
Dependencies
- “dependencies”: Lists other database objects that the task depends on. These include tables and procedures that should be present for the task to run properly.
- “dbw_dml_performance”, “dbw_test_tab”, “dbw_performance”, “dbw_performance_histr”, etc.
Implementation & Upgrade
- “implementation”: Describes the SQL to be executed as part of this task’s setup, such as creating tables and stored procedures.
- “upgrade”: Contains code to update the procedure from previous versions like 1.5 and 1.6 to the current version, modifying some entities or logic.
- “pre-implementation” & “post-implementation”: May contain scripts that prepare the environment before and clean up after the main implementation.
Reporting
- “dbwatch-report-template”: Defines how the results of the task should be reported.
- “title”: Indicates the title of the report.
Job Execution
- The job is normally triggered based on a scheduled time and checks various DML and DDL performance metrics by dynamically inserting, updating, deleting, selecting, and truncating data against the “dbw_test_tab” table. The results are used to analyze the database performance under different loads and operations, providing crucial insight into how the database might perform under various real-world conditions.
- Information from these tests can be used to fine-tune the database and optimize query performance leading to better application responsiveness and user experience.