Symptoms: SQL server engine is responding too slowly to connection requests, reporting a long response time to test probes from AimBetter agent
Impact: Medium
This metric measures the length of time between a request to SQL Server for a connection and receipt of the response. If the response is slow, it can indicate several possible issues, relating both the SQL engine and to the rest of the environment.
Expected behavior:
Typically, this value should average below 2000 ms for a well-tuned network and server. Average values in excess of 2000 ms indicate either an excessively busy network segment or a stressed SQL Server.
Possible causes
Slow Network Priority: Medium
Failure of some hardware component handling data traffic is the most likely reason for this error.
Recommended action :
Identify the faulty component and repair/replace it.
Operating system conflict Priority: Medium
Besides SQL database functions, the server performs functions relating to other operating system activities, such as anti-virus scan, disk clean-up, OS updates, etc. If an unusually high level of these coincides with high database activities, there may be an excessive load on CPU from competing elements.
Recommended action :
If possible, schedule backups and scans to occur at periods of lowest user activity.
Running out of disk space Priority: Medium
If the program calls for output to the disk ( I/O ) and the disk is nearly full (generally the optimal threshold is below 90% of total capacity), the disk will start to slow down as it searches for free space. This will cause the program to wait for progressively longer periods.
Recommended action :
Examine the disk free-space reporting from AimBetter Dashboard > Resources > Disk. If necessary, working with operating system reports, identify whether there is sufficient space in unnecessary files (for example, old or redundant copies of data), to delete these files and run a disk clean-up. If there is still not enough, further disk capacity must be added.)
SQL deadlocks and blocks Priority: Medium
See our explanation of coding problems causing deadlocks here.
Recommended action :
Optimize code. Possible actions :
- Redesign program to maximize the use of indexed data.
- Redesign table structures to match the requirements of the programs by building indexes.
- Make use of temporary tables.
Missing or corrupt indexes Priority: Medium
Missing indexes will cause extensive data searching
Recommended action :
See our explanation for missing/corrupt indexes here.
Background
High response times generally occur when sessions are blocked or when either there is heavy network traffic or network problems or if the computer the SQL Diagnostic Manager Repository or the SQL Diagnostic Manager Console is hosted on is running slow due to various problems.
