SQL Server monitoring - some more issues with missing information

JesperM
JesperM
in XORMON NG

Hi,

I found some more issues with missing information:

In the Overview section of each SQL Server instance the graph that should contain information about deadlocks does not contain any values (I guess it should report 0 if no deadlocks where found).

In the Instances/<DB name>/Capacity section of each database the tab Relation is missing information and the Filegroups tab reports size of filegroup as 0.0

In the Instances/<DB name>/Locks the Locks and Deadlocks tabs has no values.

When looking at which queries that xormon executes I can see that I queries for tables and file groups but due to the permissions given it cannot see information in sys.tables.

When assigning the monitoring user VIEW ANY DEFINITION permission it is able to get the table information. The file group information does not seem to be related to permissions as the query return the size.

About the locking information it seems to be related to that the counters for locks are not available in sys.dm_os_performance_counters for each database just at system level.

Should the monitoring user have VIEW ANY DEFINITION permission?

Maybe the graphs related to locks and deadlocks should be moved from database level to system level?

Thanks

Comments

  • damerva
    damerva

    1.

    In the Overview section of each SQL Server instance the graph that should contain information about deadlocks does not contain any values (I guess it should report 0 if no deadlocks where found).

    If there isn't a 0 the metric wasn't returned at all

    2.

    In the Instances/<DB name>/Capacity section of each database the tab Relation is missing information and the Filegroups tab reports size of filegroup as 0.0

    It shows 0.0 GiB, is is possible that the filegroup size is not big enough to show? Perhaps MiB would be more suitable for filergroup sizes?

    3.

    Should the monitoring user have VIEW ANY DEFINITION permission?

    VIEW ANY DEFINITION shouldnt be required and we have not encountered any issues with permissions for Relations tab as of yet

    but if there is a permission issue we'll look into a grant for the specific view/table. What version is your SQLServer?

    4.

    Maybe the graphs related to locks and deadlocks should be moved from database level to system level?

    locks definitely should be moved and we can get deadlocks for system level, but deadlocks should be attainable per instance too


    if there is any queries/metrics you would like to see added let us know

  • JesperM
    JesperM

    Thanks for the reply.

    1. Yes, I seems to be that no value is returned for that metric. The query I can find that tries to collect locking and deadlock information is joining sys.dm_os_performance_counters with sys.databases on I.instance_name = d.name and that will not have any matches as instance_name for the locking information in SQLServer:Locks does not contain the database names at all.
    2. It does not seem to matter how big the files are, they are reported as 0.0. If I execute the query manually it returns the size. It think it would be a good idea to show the value in MiB instead.
    3. I have tested against SQL Server 2019 and 2022. It seems that these ones does not return any values: sys.tables, sys.indexes, sys.objects without more permissions.
    4. There seem to be different values for locking on many levels but it does not seem to be possible to relate them to a specific database. At least not the ones in the SQLServer:Locks category.


Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Register

Categories

In this Discussion