After visiting to many customer locations I have found there is a great set tools , which many DBAs are not aware of. They struggle a lot while troubleshooting performance issues. So, I decided to blog on the subject matter so that it could help DBAs to troubleshoot the performance issues faster. This blog targets the audience with the knowledge level 100-200. All the tools discussed in this article are either free or inbuilt in SQL server installation:
There are majorly two sections of this blog:
Database and OS configuration
Database and OS configuration : There are 2 major tools which we have available :
- BPA(Best Practice Analyzer): BPA tool is used to find the discrepancies in the system and SQL level configuration. It helps to unveil the critical issues before it causes serious outage. It has some great set of rules which it runs against the collected data. This tool is deprecated in SQL 2012 as it’s taken over by system center advisor which we will discuss in the next section:
It shows critical errors : If consistency checks are not being done on regular basis, If backups are outdated and there are some critical issues in the server etc. Please check the demo in the links mentioned below to get familiarity in depth:
This tool can be great help to understand the issues proactively and take necessary actions before it’s too late.
Here is the screenshot of the recommendations by BPA:
For more details check the link:
Download and Install MBCA –http://www.microsoft.com/en-us/download/details.aspx?id=16475
Download and install BPA – http://www.microsoft.com/en-us/download/details.aspx?id=15289
Steps to run BPA – http://www.microsoft.com/en-us/download/details.aspx?id=436
Demo of how BPA works – http://www.microsoft.com/en-us/download/details.aspx?id=18882
System Center Advisor(SCA) : This tool , which is now released for public , helps to identify the discrepancies in the SQL configuration. Not only this, it can help to find out the unsupported features and patch levels of SQL server instances. This tool can be of great help for the customers who can’t purchase the Microsoft premier services.
It pretty much comes under the proactive work for the SQL environment. As more than 30% approx. the performance issues in the SQL environment are due to the wrong configuration. It also helps to proactively find out, if the backups are outdated or consistency checks are not done etc.
How it works:
There are two major components of this tool :
1. Gateway: – This component has to be installed on the machine from where you will upload the logs to the MS cloud service. It can be a centralized place from where the data is uploaded to the cloud service or can be installed on every individual machine to upload.
2. Agent : – This component must be installed on the machine on which you want to collect the data.
This is how the reports look like :
In most of the environments, the production servers can’t be exposed to internet and the ports can’t be opened without critical purpose. For this tool to upload the data to cloud service ,we need to open a port (port no. 80) . To avoid any complications there is a provision to setup a remote gateway to which all the data will be provided and it will upload the logs to the cloud service. As shown the first screenshot under this section, multiple agents can report to one gateway and it can be given internet access to upload the logs.
To read more about it , please check this post: http://sql-blogs.com/2013/03/21/system-centre-advisor-is-now-free/
Performance monitoring, I will discuss in my next post. Please click here to read about it