sys.dm_os_performance_counters Resource Pool Stats returns zero

  • 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?

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • I  am trying to find reason for below error

    "There is insufficient system memory in resource pool 'internal' to run this query."

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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?"

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

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