Previous Next

Analyzing SQL Server Performance with Monitor

Monitor available in dbForge Studio for SQL Server is a powerful SQL Server monitoring tool. It allows you to easily track the system load and view SQL performance metrics. With this diagnostic tool, the process of monitoring performance and activity becomes effortless. Analyzing wait statistics and query execution plan helps you detect performance issues and make the troubleshooting process more efficient and easier.

CPU Utilization

The tool with its metrics will allow you to greatly save your time when checking how much CPU resources SQL Server consumes. In addition, it will help you identify the reasons for poor performance. To analyze any issues, simply have a look at the graph and values on the Global Dashboard.

Memory Utilization

It is critical to have enough memory for the optimal performance of SQL Server. On the graph, you can see how much memory is used in total, and how much memory SQL Server is currently using.

Disk Activity

Keep an eye on the disk activity to prevent unnecessary activity and to detect any performance issues. The graph will help you easily get a detailed view of your disk activity by displaying reads and writes (in Mb) of all disks. Optionally, it is possible to track workload on individual disks.

SQL Server Performance Metrics

Metrics on the Global Dashboard refers to the SQL Server performance and provides data of the following diagnostic properties: total CPU, batch requests per second, re-compilation, number of waiting tasks, active connections, deadlocks, transactions performed per second, full and range scans, page reads and writes, page faults, page lookups and splits, page life expectancy, as well as read, write and IO latency in ms. In addition, you can find detailed information about host and SQL Server properties.

This overall information will allow you to get a clear and quick view of the performance within the disk, memory, and CPU system. Having all this data makes the investigation and troubleshooting process much effortless.

Top resource-consuming queries

Slow running queries use much resources, which may lead to poor performance and long query execution. To prevent you from such situations, you can simply use the Top Queries tab to detect the most expensive queries effectively and effortlessly. To do this, check such parameters as execution count, total elapsed time, last elapsed time, min elapsed time, max elapsed time, total logical reads, last logical reads, and last execution time.

In addition, to troubleshoot any potential performance issues, feel free to view and analyze the Query Profiler tool, including query execution plan diagram, top operations, plan tree, as well as SQL queries.

Additional Monitor Panes

Data Input and Output

This section provides information about the number of disk reads and writes, added to the file, as well as the expected time required to complete a process.

Databases

The table displays such properties as database ID, its status, type of recovery model, compatibility with a particular SQL Server version, database total size, data and log size, information on the transaction log space reuse, collation for the database, as well as options to automatically close the connection and to shrink database files.

Wait Stats

If you want to check what impacts on the server performance, you can simply use the Wait Statistics functionality, which includes the following parameters: name of the wait type and its total wait time, resource wait time, and the number of waits. In addition, it allows you to monitor the waiting session between the time when a query was added and when the CPU was ready to run it.

Sessions

This feature will make the process of keeping track for active connections much easier. It provides you with such session components as SQL Server session ID, database, its state, login name, session ID blocking the request, current waiting time, and textual representation of a lock resource, if available. Additionally, you can view cumulative CPU time, as well as disk reads and writes for the process, cache allocated for it, time of logging into the server, and time for the last stored procedure executed remotely.

This overall information will allow you to get a clear and quick view of the performance within the disk, memory, and CPU system. Having all this data makes the investigation and troubleshooting process much effortless.

Backups

Quickly review basic information for database backups, which includes backup type, finish date, size in Mb, and path where the backup is stored.