When you have many database servers and instances running in your environment, then naturally the complexity of monitoring them increases. Each user group or business units will want to use a variety of databases for specific business operations. In order to give them continuous access to what they want, you must be proactive in identifying the most useful information that will make all the difference. Here is a list of the top 6 reports which will help you proactively uncover performance issues and optimize your SQL servers.
Top 6 Reports to Be More Proactive
- Clustered/Non-clustered Indexes by Fragmentation: SQL uses indexing to make data searches faster. Proper indexing helps to ensure that data retrieval or modifications to the database occurs faster. Poor index fragmentation not only slows down database searches, but also requires more disk space usage causing performance degradation. This report will show you which indexes need to be rebuilt or reorganized. You should run this report prior to your regular maintenance schedule for rebuilding indexes.
- Top 10 Expensive Queries: One bad query from an application using the SQL database will affect the performance of the whole database server itself, and not just the individual databases belonging to an application. Looking at the query text, CPU time, physical and logical reads, time it has taken to complete the query, and database name will alert you to troubleshoot before these issues affect the whole server.
- Database Backup: Check if your backups are working properly. This includes all the backups that were successful, failed, and those that have reached critical and warning stages. A quick glance at this report will help you identify if all the database backups are as you wanted them to be.
- Memory: Run this report to find out what is hogging memory. Look at key statistics, such as buffer manager and paging, and drill down to see if there are any issues. If there are issues with these performance counters, then you know that you probably need more memory. You can also generate reports for physical and virtual memory that are currently being used by processes and services.
- Connections: If you have users connecting to your database from multiple locations, it’s important to know how many connections were successful and how many failed, and how many logins and logouts occurred for any given time interval. This will tell you which locations are having issues and whether or not it’s due to a network problem or whether there are connection issues to the database. User connections can also lock tables in the SQL server. If you can identify unnecessary user connections, you can prevent application slowdowns.
- Database Size/Transaction Log Size by File: As your database size grows, the SQL server will require more memory and CPU to read data from the tables. When the size of the table increases, it slows down database operations. So it’s good to look at the size of the current database or transaction, percentage growth, and remaining space.
As DBAs, you can run these reports to find out if there are any niggling issues before they become bigger and start affecting end users. It will also show you meaningful insights in your environment that you thought were faultless. Ultimately what you get is not just reports, but a reality check of your existing environment – as it happens, in real-time. And remember, it’s always better to be proactive than reactive!
Karthik Ramachandran is a Product Marketing Specialist at SolarWinds who has extensive IT management expertise in systems management including application, server and remote IT management.