This report summarizes the conclusions of the database, applications and infrastructure (hardware and operating systems) survey performed over one week at XXXXXXXXXXXX Co., Ltd.
The survey was conducted using the AimBetter system, which performs in-depth, real-time monitoring and analysis of the components mentioned above.
AimBetter is a unique cloud-based solution that enables organizations to identify and address any performance and/or code related issues even before they become critical and impair usability or cause system failures.
The solution combines propriety intelligence modules based on years of expert experience and is being used by over 700 businesses and organizations worldwide.
Executive Summary
The following is a summary of the critical issues that have been detected and require immediate actions:
- SQL Server Setting
- Overly inflated Log Files with a high growth rate
- No correlation between the number of TempDB files and the number of cores (processors)
- Insufficient SQL server memory
- Unoptimized utilization of available disk space
- Data Files (MDFs) and Log Files (LDFs) are on the same disk
- Outdated and unsupported SQL version
- Too many low-performance executions which indicate indexing issues
- High disk usage with potential damage to the server performance
- Lack of proper backups could result in an inability to recover from system failures
- The abnormally high number of blocking queries (there are approximately 70 locks on average per day, which block up to 14 users at a time)
- The low execution performance of many queries (we have counted 100 different queries that waste almost one hour per day)
To resolve the detected issues, we will need to start addressing the most critical issues first and deal with the rest as part of a yearly maintenance contract (to be discussed in the second stage). We will need to work on your server and database using a reliable VPN connection with administrative rights for 20 hours (distributed over one week). This will allow us to get familiar with your system and provide the basis for the yearly maintenance contract Service Level Agreement (SLA).
A fully detailed quotation will be provided upon request.
Performance
Findings – High Pagefile Usage (32GB of paging file!)
Implications – There is not enough memory space for the operating system to perform its current daily operations
Required Actions –
- Identifying the main processes that Pagefile requires
- Setting limitations on SQL memory usage for the benefit of the operating system
- Increasing the memory at the machine level
* For more information, please go to: https://www.aimbetter.com/host-pagefile-use/
Findings – High Disk Usage
Implications – Direct damage to server performance
Required Actions –
- Identifying the cause(s) of the high usage
- Providing a root treatment
MSSQL
Findings – Current SQL version is not up to date and not supported by Microsoft
Implications – No available updates in various areas (i.e. information security, debugging etc.)
Required Actions – MSSQL version update
Findings – Lack of server backups
Implications – Inability to recover in cases of system failure (high data loss risk!)
Required Actions – Create a complete backup plan (full backup inc. log) on the server and route the backups to a network path to maintain high survivability
Findings – There is no correlation between the number of TempDB files and the number of cores (processors)
Implications – Impairment of SQL optimal work capability
Required Actions – Match the number of TempDB files to the number of cores
*One TempDB in comparison to 8 cores
Findings – There is a difference in the size of the Log/Data Files used and their actual size
Implications – Unnecessary utilization of drive space
Required Actions –
- Decrease the size of the Log File
- Check the growth rate setting of the Log/Data Files
Findings – SQL Server memory is not limited
Implications – Unoptimized memory partition between SQL and the operating system can compromise server performance
Required Actions – Setting optimal memory allocation between SQL and the operating system
Findings – Log Files Growth
Implications –
- Lack of Log Files maintenance can cause these files to grow significantly, clog up drive space, and/or cause system failures.
- A decrease in work performance of the database
Required Actions –
- Manually clean and shrink the inflated Log Files
- Create an automatic program to clean up bloated Log Files on the server
Findings – Backups are made to a local route
Implications – Low survivability and possible data loss (in hacking/encrypting modes)
Required Actions – Reroute the backups to the network path
Findings – Data Files (MDFs) and Log Files (LDFs) are located on the same drive
Implications – Unoptimized SQL work performance (SQL reads LDFs in sequential order, while MDFs are read randomly. Since currently there are no disks that know how to work effectively in both configurations simultaneously, it is recommended to separate the drives of these two types of files.)
Required Actions – Separating Data, Log and TempDB files to different drives.
Queries
Findings – High number of blocking queries
Implications – Impaired user experience (users are repeatedly blocked and unable to perform their tasks until the block has been terminated)
Required Actions –
- Analysis of the blocking queries
- Identifying the blocking factors and providing a thorough solution
Findings – Database is not indexed
Implications – Performance degradation (Indexes help MSSQL retrieve data from its tables. Using indexes saves valuable time and makes search processes much more efficient. If no index is defined for a table, queries will cause a change to all the records in it (Table Scan) until the records that meet the search conditions are found. If an appropriate index is defined for a table, searches will run faster because the reference to the database will cause the database search engine to be indexed first, then go to the appropriate records)
Required Actions – Create a program that will improve performance using indexes based on system recommendations (The system displays queries that have an index recommendation which can improve performance by more than 70%)
Findings – A large number of queries that run with the less-than-optimal plan
Implications – Performance Degradation
Required Actions –
- Check index maintenance and statistics on the server
- Examine change plans for these queries
Event Log
Findings – Database backup failures
Implication – Data loss in case of system failure
Required Actions –
- Identifying the cause(s) of backup failures
- Creating and implementing a complete pledge backup plan
Findings – Incorrect syntax (SQL Exceptions due to code errors)
Implications – Queries run failures
Required Actions – Identifying and correcting incorrect syntax
Findings – Login exceptions
Implications – Failures to connect to the database (disabled users)
Required Actions – Identifying the root cause of such failures
Findings – Duplicate key exceptions (failure to insert a value into a table due to primary vital violations)
Implications – Loss of data (information is not stored in the database)
Required Actions – Identifying the cause(s) of such exceptions
