Top Free SQL Server Tools & Performance Tuning options from Microsoft

Top Free SQL Server Tools & Performance Tuning options from Microsoft

(A Benefit to the company)

microsoft-tools-training

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

  1. Profiler
  2. Perfmon (Performance Monitor)
  3. Activity Monitor
  4. Extended Events
  5. DTA (Database Tuning Advisor)

Other Options

  1. DMV (Dynamic Management Views)
  2. Trace Flags
  3. DBCC (Database Consistency Check)

Profiler:

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

Perfmon:

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

Activity Monitor:

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.

activity monitor

Path: Open SSMS (SQL Server Management Studio) —connect to the instance —Object Explorer — Activity Monitor

activity monitor -ssms

Extended Events:

Extended events is a more useful tool which can be used to monitor server in below cases:

  1. Application Tuning
  2. Troubleshooting Performance
  3. 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.

extended events

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:

  1. Tuning Queries
  2. Adding indexes (clustered, non-clustered and indexed views)
  3. Adding partitioning
  4. Adding statistics

Don’t Do:

Do not start DTA when SQL Server is running in single user mode.

Trace Flags

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

https://mirzahusain.wordpress.com/2017/12/31/trace-flags-in-sql-server/

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.

E.g.

DBCC sqlperf (logspace)

DBCC checkdb

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.

Please Share it

Advertisements

About Mirza Husain

Mirza Husain is a senior consultant-database and having more than 10+ years of experience in the IT industry with different domains. He is fond of writing and speaking about SQL Server and also keen to learn new technologies. He is holding MCA degree and having Microsoft certifications as MCP & MCTS. He is also ITIL certified. In his past years, he worked with many clients as Microsoft, Bank of America etc. Currently he is associated with a France based client and offering his best services. You may reach him on his email id -- mirza_dba@outlook.com. Thanks!
This entry was posted in Database Administration, Other, Sql Server and tagged , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s