Top Free SQL Server Tools & Performance Tuning options from Microsoft
(A Benefit to the company)
I am writing this blog to share the importance of the tools provided by Microsoft along with SQL Server to use to monitor the health of the databases, performance related issues and troubleshoot the same. Microsoft has provided several tools and other options for such scenarios related to SQL Server Databases and these are absolutely free therefore it is better to use these tools instead of procuring third party tools. By using these free tools we can reduce the cost which cast to the company. However there are several free third party tools available in the market but these are not secure and not recommended as per the company policy as well.
Therefore how to prevent these issues and run the SQL environment secure and smooth, it is recommended to use free tools provided by Microsoft.
Free Tool from Microsoft
- Perfmon (Performance Monitor)
- Activity Monitor
- Extended Events
- DTA (Database Tuning Advisor)
- DMV (Dynamic Management Views)
- Trace Flags
- DBCC (Database Consistency Check)
Profiler is a user interface tool which can be used to monitor overall health of your database and instance by tracing inside the SQL Server. It gather the complete information about the event which we have selected to monitor e.g. deadlock info etc. and give us the result in form of a trace file which we can use and check thoroughly and find the cause of the issue.
Path: Run — type Profiler — Profiler window will open
Performance monitor or Perfmon is another great tool which can be used to set the counters and see the result graphically. This tool provides the overall picture of the server health which includes the counters information as Processor, Memory,Buffer etc.
Path: Run — type Perfmon — Perfmon windows will open
This tool is available in the SQL Server Management Studio and can be used to monitor the processes, recent expensive queries, resource wait etc. It displays the information graphically as well. It is easy and very much handy tool to find the quick information about the system along with the processes running.
Path: Open SSMS (SQL Server Management Studio) —connect to the instance —Object Explorer — Activity Monitor
Extended events is a more useful tool which can be used to monitor server in below cases:
- Application Tuning
- Troubleshooting Performance
- T-SQL Query Performance Analysis
There are predefined template available in SQL Server Management Studio which can be used while configuring the extended events while we can also design a custom event. Mainly available template focus on Locks & Blocks, Query Execution & System Monitoring. Always On monitoring can also be done through it.
We can configure the Extended event, captures the event which we want and troubleshoot the problems.
DTA (Database Tuning Advisor)
It is another tool which is used to tune the query and advise us about the specific requirement of the indexes. We can find this tool in Tools menu options in SQL Server Management Studio and we can open it in search programs in window.
DTA can be useful in such cases:
- Tuning Queries
- Adding indexes (clustered, non-clustered and indexed views)
- Adding partitioning
- Adding statistics
Do not start DTA when SQL Server is running in single user mode.
Trace flags plays an important roles in database administration. These are frequently used to diagnose performance issues or to debug stored procedures. As I have already written a blog on this so please go through the link
DMV (Dynamic Management Views)
DMVs are used to fetch the server state information and which can be used to monitor the health of the instance, diagnose the problem and tune the performance.
There are several types of DMVs available.
E.g. OS Level, Index Level etc.
DBCC (Database Consistency Checker)
DBCC is used to check the consistency of the databases. These commands are used for maintenance of databases, tables, file group and indexes.
DBCC sqlperf (logspace)
DBCC loginfo ()
Note: Hope you have got the valuable information, I have just provided a brief about the tools however if you want detailed description please go through Microsoft blogs.