<img height="1" width="1" style="display:none;" alt="" src="https://dc.ads.linkedin.com/collect/?pid=204641&amp;fmt=gif">

dbWatch Control Centers' SQL performance pack for investigating your database performance

Posted by Chad Pabalan on Nov 7, 2022 2:30:00 PM

SQL Performance package 

SQL Performance pack is the latest feature of dbWatch Control Center that allows for historical monitoring and playback analysis of SQL Statements in the database instance. It helps thoroughly investigate any problematic queries that have been executed at a specific time. 

It supports all versions newer than MS SQL Server 2014, PostgreSQL 9.4, and Oracle 11g. 

 

Requirements for installation:

  • - dbWatch Control Center's latest September 2022 release 
  •  
  • - A license that includes SQL Performance pack. It can be as a separate "SQL Performance Package" license or as part of a larger license package. It is included in the DEMO license type. 
  •  
  • - Database platform and version that is supported 
  •  
  • - Space on the database instance, around 5 GB depending on the amount of SQL queries, configured thresholds and desired history requirements. 

Adding the license 

If the database instance uses the DEMO license type, you can skip this step, as it is already enabled. 

Right-click on an instance and choose Configure instance. If you pull down the license list, you must ensure a license that includes the SQL Performance package is chosen. In this case, both Demo and SQL Performance Package are valid choices. After you have made your choices, click "Save" to save changes. 

 

Installing the SQL Performance package repository job 

Once the correct license is enabled, you can install the job that collects SQL performance data for the repository. 

Right-click on the instance name and choose "Configure jobs." 

Graphical user interface, text, application, email

Description automatically generated

Scroll down to the Performance group and select the "SQL statistics" job. If unavailable, you have not enabled the correct license, or the platform/version is not supported. Once selected, click "Install" to install it on that database instance. A popup will appear saying that the SQL statistics job was installed. Close the popup and the "Manage jobs" window. 

Graphical user interface, text, application, email, website

Description automatically generated

It should now be available in the Performance group on that database instance. It will run automatically every 5 minutes by default and can be triggered if you right-click and choose "Run." Allow it to gather data for some time, such as one day. 

 

Navigating around the SQL Performance dashboard 

Once the SQL statistics job is installed and you have collected enough data, you can navigate to the dashboard. 
It is located in the management interface for that database instance. 

There should be a "SQL performance" node in the tree structure. Click on it to open the SQL Performance dashboard. 

Using the SQL Performance dashboard 

Navigating time 

The structure of the SQL Performance dashboard is a top graph showing historical data, such as Logical/physical reads and writes (SQL Server) or Buffergets, direct writes, and disk reads (Oracle). 

Graphical user interface, application

Description automatically generated

The logical/physical reads and writes graph is used to select the timeframe you want to look at. 

You can select predefined time selections, such as Last 8 hours, last 24 hours, previous two days, last week, and all statistics: 

Graphical user interface, application

Description automatically generated

You can also use the mouse to select a timeframe from the graph. Click on your start time, hold the mouse button, drag it to the desired end time, and release the mouse button. 

This will change the selection on the SQL Handle statistics table and the additional information graphs. 

SQL handle statistics (SQL Server) or SQL statistics (Oracle) 

The SQL handle statistics/SQL statistics will list SQL handles or statements that have been active in the selected period. 

The fields and their description 

Records: 
The number of times this SQL statement has been detected by dbWatch Control Center 

DB/Schema: 
The database where this procedure is located (SQL Server) or schema executing the SQL or procedure (Oracle) 

 

DBW: 
Is this a SQL or procedure associated with dbWatch. (Specify 1 for dbWatch Control Center (CC) related queries, 0 to remove all queries associated with dbWatch CC) 

First execution: 
The first time in the selected time frame that this SQL or procedure was executed 

Last execution: 
The last time in the selected time frame that this SQL or procedure was executed 

Executions: 
The number of times in the selected time frame when this SQL or procedure was executed 

Elapsed time: 
The total elapsed time (in milliseconds) in the selected time frame where this SQL or procedure was executed 

Rows: 
The total number of rows in the selected time frame where this SQL or procedure was executed 

Logical reads/Buffer gets: 
The total number of logical reads/buffer gets in the selected time frame where this SQL or procedure was executed 

Logical writes/Direct writes: 
The total number of logical writes/direct writes in the selected time frame where this SQL or procedure was executed 

Physical reads/Disk reads: 
The total number of physical reads/disk reads in the selected time frame where this SQL or procedure was executed 

 

Navigating SQL handle statistics / SQL statistics 

You can investigate the SQL handles further by right-clicking on one of the lines. Show statistics will open a new dashboard focusing on one SQL handle or SQL statement. 

This dashboard will show logical/physical reads and writes statistics for this statement, the statistics for the SQL handle, the SQL code that has been executed, and individual SQL statements within the SQL handle if it is a procedure with multiple SQL statements. 

Graphical user interface, application

Description automatically generated

You can also open each SQL statement within the SQL handle to get statistics for that statement. 

Additional graphs 

In addition to the logical/physical reads and writes and the SQL handle statistics overview, there are additional graphs to visualize other performance statistics from the same selected timeframe. 

Examples: 

Elapsed time: 
Graphical user interface, application

Description automatically generated with medium confidence

Waits history: 

Transaction log – KB flushed per minute: 
A picture containing chart

Description automatically generated

Data cache memory usage: 
Graphical user interface, application

Description automatically generated

Session load: 

Checkpoints: 

Lazy writes: 
Chart

Description automatically generated with low confidence

 

With this latest feature SQL performance pack of dbWatch Control Center, it will be much easier for DBAs to investigate, troubleshoot, and pinpoint problematic queries executing in their environment. DBAs can now replay their historical performance to drill down and identify performance during a specified period in time. 

dbWatch CC offers the following: 

  • Farm overview - consolidated views for performance, capacity, and maintenance for your entire database farm 
  • Monitor in-depth all your instances and platforms 
  • Performance – identify performance bottlenecks within the database farm 
  • Scalability – ability to adapt to your growing database farm 
  • Alerting – alerting and third-party extensions tailored to your business requirements 
  • Security – role-based access controls, Kerberos integration encrypts connections and supports security certificates.
  •  
  • You can try dbWatch Control Center today. Use this link to download a version of Control Center and try it for 30 days:  Download dbWatch Control Center 

If you have any questions or you would like to know more about how dbWatch Control Center can assist you in your current enterprise database monitoring and database management situation, feel free to contact me directly at chadwick@dbwatch.com    or visit our customer portal page.

 

For more information, visit www.dbWatch.com or the dbWatch wiki pages   

 

#DatabaseMonitoring #OracleDatabase #OracleDatabaseMonitoring #DatabasePerformance #OracleDatabasePerformance #DatabaseManagement #OracleDBA #SQLperformance #SQLServer #performancetuning #queryperformance 

 

 

Topics: sqlmonitor, sqlperformance, sqlmonitoring

dba-guide-ebook
how-to-be-smarter-dba-ebook
control-center-cta