Performance counters

Question

Performance counters

Himanshu Sinha 11

Hello ,
We are seeing the slow performance of the queries and wanted to know which performance counetsr we should focus on to get more info avout the the issues

Thanks

HImanshu

2 answers

Your answer

Answer 1

Erland Sommarskog 122K MVP Volunteer Moderator

I tempted to say: None. Personally, I very rarely look at performance counters for troubleshooting bad performance.

Your best tool for troubleshooting query performance is Query Store where you find a wealth of information. Provided, that is, you have it enabled. It is on by default in Azure SQL Database, and also for new databases in SQL 2022. But for databases in older versions, you need to enable it.

Once you have enabled it, you will find a node Query Store in Object Explorer for the database. There are a couple of reports, that I am sure that you will find useful.

Naomi Nosonovsky 8,431 Reputation points

2025-03-03T23:35:21.82+00:00

I don't see this node in Object Explorer. I see the following for the database:

Does it mean it enabled? Do I have to have certain permissions to view it?

Also, is it bad that we have Optimizer Fixes = Off?
Erland Sommarskog 122K Reputation points MVP Volunteer Moderator

2025-03-04T22:12:28.2133333+00:00

To access the Query Store data, you need the permission VIEW DATABASE STATE. I don't know if SSMS checks for this, but maybe.

This is how it looks for one of my databases:

Also, is it bad that we have Optimizer Fixes = Off?

With this flag on, you live a little more adventurously. If Microsoft makes fixes to the optimizer you can benefit from this and get performance. But with some bad luck, you can also get performance regressions.

Answer 2

ZoeHui-MSFT 41,496

Hi @Himanshu Sinha，

This article describes how to handle a performance issue that database applications may experience when using SQL Server: slow performance of a specific query or group of queries. The following methodology will help you narrow down the cause of the slow queries issue and direct you towards resolution.

Troubleshoot slow-running queries in SQL Server

Regards，

Zoe Hui

If the answer is helpful, please click "Accept Answer" and upvote it.

Share via

Performance counters

2 answers

Your answer