sys.dm_os_performance_counters Resource Pool Stats returns zero

Question

sys.dm_os_performance_counters Resource Pool Stats returns zero

IT researcher

SSCertifiable

Points: 7602
More actions
November 30, 2020 at 9:35 am

#3815066
I am using SqlServer 2008 R2 Express edition. When i execute below query i will get all 0 .
```
select * from sys.dm_os_performance_counters where object_name = 'SQLServer:Resource Pool Stats'
```
By referring this Link - In express edition I can't use this query to get resource statistics.
But is there any other method to get the resource statistics in SQL Express edition?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398904 More actions · Answer 1

Resource Pool Statistics are specific to Resource Pools which are how the Resource Governor works. You don't have a Resource Governor in Express Edition. Therefore, no need to look at Resource Pools.

What is it you're actually trying to find? It isn't Resource Pools because you don't have those. What else is it you're thinking you need?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

IT researcher SSCertifiable Points: 7602 More actions · Answer 2

I am trying to find reason for below error
"There is insufficient system memory in resource pool 'internal' to run this query."

Grant Fritchey SSC Guru Points: 398904 More actions · Answer 3

That one is simple. Not enough memory in the system for the query in question. That means also, not enough disk space to swap stuff out. Sounds like the data involved is at the limit of what Express can handle, or, the query is complicated enough that there's not enough memory. I'd focus on the query, not the system. Express has hard limits on the system, so you have to stay within them.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 4

How much memory is actually in this particular system? I ask because, while I agree that Express is pretty limited, it seems to me that it should be able to run this particular query without running it out of memory.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

IT researcher SSCertifiable Points: 7602 More actions · Answer 5

Is it possible to find the query causing problem?

Because error comes randomly(once per week) and too many queries/programs run daily and continuously.

I am using SQL Server 2008 R2 in Windows Server 2012 R2 with 40.0 GB RAM. "How much memory is actually in this particular system?"

Grant Fritchey SSC Guru Points: 398904 More actions · Answer 6

2008R2? Ugh. Well, that limits your choices for monitoring down to one, Trace Events. These things do not filter well. However, you can set up a server side trace to capture queries and filter based on memory use, but this isn't going to be easy or quick to set up and you'll have to deal with the data generated. If you at least know the time of day when this is likely to occur or other mechanisms for limiting the time frame that you have to run it in, that'll help a lot.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning