Best MSSQL database performance Tools

1. Built-in SQL Server Tools (You Already Own These)

1.1 Query Store ✅ (must-use)

What it does

• Captures history of queries, execution plans, runtime stats (CPU, duration, reads, etc.).
• Shows top resource-consuming queries and plan regressions.
• Lets you force a good plan if a bad one appears.

Why it’s great for you

• Perfect for your “before vs after” experiments.
• Central to any serious performance engineering workflow.

How to enable (quick refresher)

ALTER DATABASE YourDbName
SET QUERY_STORE = ON;

Then, in SSMS:

• Database → Query Store → Top Resource Consuming Queries, Regressed Queries, etc.

1.2 Execution Plans (Actual Execution Plan)

What it does

• Shows how SQL Server runs your query (scans, seeks, key lookups, joins, spills, etc.).
• Helps you see missing indexes, bad joins, parameter sniffing issues.

How to use

In SSMS:

• Click Include Actual Execution Plan (Ctrl+M) and run the query.
• Look for:
  • Index Scan / Table Scan
  • Key Lookup
  • Hash Match + tempdb spills
  • Big estimated vs actual row differences

1.3 Dynamic Management Views (DMVs)

What they do

• Internal system views that expose:
  • Top CPU / IO queries
  • Missing indexes
  • Index usage
  • Plan cache

Examples

• sys.dm_exec_query_stats
• sys.dm_exec_sql_text
• sys.dm_exec_query_plan
• sys.dm_db_missing_index_details
• sys.dm_db_index_usage_stats

(We already built a mini script library for you in Appendix B of the “book” above – that’s basically your DMV toolkit.)

1.4 SQL Server Profiler / Extended Events

SQL Server Profiler

• Real-time stream of:
  • Queries
  • Duration
  • CPU
  • Reads
  • Parameters
• Very handy when you want to see exactly what your .NET app is doing.

Extended Events

• The modern replacement for Profiler.
• More lightweight and production-friendly.

Use cases:

• Catch long-running queries from ASP.NET.
• Validate N+1 issues.
• See which endpoints hammer the DB.

1.5 Database Engine Tuning Advisor (DTA)

What it does

• Takes a workload (trace / query file) and recommends:
  • Indexes
  • Indexed views
  • Partitions

It’s good as a second opinion. Don’t blindly apply everything, but it often gives useful index hints.

1.6 Performance Monitor (PerfMon) + SQL Server Counters

Windows + SQL Server performance counters:

• SQLServer:Buffer Manager (Page life expectancy, etc.)
• SQLServer:SQL Statistics (Batch Requests/sec)
• SQLServer:Locks (deadlocks, lock waits)
• Processor, Memory, PhysicalDisk

Use them to see if the bottleneck is:

• CPU
• IO
• Memory
• Locks

2. SSMS/Azure Data Studio Extensions & Helpers

These are tools that live inside or alongside SSMS / Azure Data Studio and make tuning easier.

2.1 Azure Data Studio + Extensions

• Built-in notebooks, integration with Query Store, and optional Copilot/AI features (depending on setup).
• Nice for:
  • Query analysis
  • Lightweight SQL development
  • Cross-platform work

2.2 Redgate Tooling (commercial but widely used)

• SQL Monitor – monitors SQL Server performance, alerts on long-running queries, blocking, etc.
• SQL Prompt – helps rewrite queries, detect bad patterns, format SQL neatly.
• SQL Profiler-like functionality & monitoring dashboards.

These are extremely popular in SQL Server shops for ongoing performance monitoring and tuning.

2.3 dbForge / ApexSQL / Devart toolsets

Vendors like Devart (dbForge) and ApexSQL provide:

• Query profilers / analyzers
• Index analyzers
• Execution plan visualizers
• Monitoring dashboards

These are GUI-heavy tools that make analysis simpler when you don’t want to live in raw DMVs.

3. Application Performance Monitoring (APM) for .NET + MSSQL

These tools see end-to-end: HTTP request → .NET code → SQL query.

3.1 Azure Application Insights

• Deep integration with ASP.NET / .NET.
• Shows:
  • Slow requests
  • SQL dependencies (with timings)
  • Call stacks

Very good if you’re already on Azure / App Service / AKS.

3.2 Other APMs

• New Relic
• Dynatrace
• Datadog
• AppDynamics

They provide:

• Transaction traces
• SQL call breakdowns (per endpoint)
• N+1 detection clues
• Throughput & latency dashboards

These are great when you want to say “this specific API route is slow because of this specific SQL”.

4. Performance & Benchmarking Tools for .NET + MSSQL

4.1 BenchmarkDotNet

• For micro-benchmarks on .NET code paths.
• You can:
  • Benchmark different EF Core queries.
  • Compare Dapper vs EF Core vs raw ADO.NET.
  • See allocations & timing.

Perfect for lab-style experiments like we designed earlier.

4.2 Load Testing Tools

To stress-test your ASP.NET + MSSQL combo:

• k6
• JMeter
• Locust
• Vegeta / wrk (HTTP-level)

Use them to:

• Hit endpoints
• Watch SQL load via Query Store + DMVs
• Validate your “before vs after” improvements with data.

5. AI-Assisted Tools (Already Lining Up With Your Training Content)

On top of all the above, you can layer AI-based tools:

• GitHub Copilot / Copilot Chat – helps rewrite LINQ and SQL, explain performance issues from plans.
• Azure Data Studio Copilot – explains queries, suggests improvements.
• Generic AI (like me) – you can paste:
  • query text
  • execution plan XML (or summary)
  • table schema
    and ask for “index and query optimization suggestions”.

The key is: AI + Query Store + Execution Plans + DMVs = extremely powerful combo.

6. What I’d Recommend You Use First (Concrete Path for You)

Given everything you’re already doing (training, labs, and deep .NET work), I’d prioritize:

1. Query Store
   • Turn it on for your key DBs.
   • Use it as your main “top slow queries” dashboard.
2. Actual Execution Plans + DMVs
   • For each slow query, always:
     • Get the actual plan.
     • Run the DMV scripts we built in Appendix B.
3. SQL Profiler / Extended Events
   • Use them when debugging real-time issues or verifying EF Core behavior (N+1, too many round-trips).
4. PerfMon + basic APM
   • To see if the bottleneck is SQL, CPU, or something else.
5. Optional 3rd-party
   • If you want a polished view: Redgate SQL Monitor or a full APM like Datadog / New Relic.

Rajesh Kumar

I’m a DevOps/SRE/DevSecOps/Cloud Expert passionate about sharing knowledge and experiences. I have worked at Cotocus. I share tech blog at DevOps School, travel stories at Holiday Landmark, stock market tips at Stocks Mantra, health and fitness guidance at My Medic Plus, product reviews at TrueReviewNow , and SEO strategies at Wizbrand.

Do you want to learn Quantum Computing?

Please find my social handles as below;

Rajesh Kumar Personal Website
Rajesh Kumar at YOUTUBE
Rajesh Kumar at INSTAGRAM
Rajesh Kumar at X
Rajesh Kumar at FACEBOOK
Rajesh Kumar at LINKEDIN
Rajesh Kumar at WIZBRAND

Rajesh Kumar DailyLogs