DX Unified Infrastructure Management

  • 1.  Need out of box Nimsoft sql queries

    Posted Nov 29, 2018 12:35 PM

    Hi  can anyone share the sql query for alloc_space which used in out of box Nimsoft in sql server probe ?

     

    Regards,

    Sowmiya



  • 2.  Re: Need out of box Nimsoft sql queries

    Broadcom Employee
    Posted Nov 29, 2018 12:37 PM

    If you raise a case with Support they can pass this to one of the developers to supply.

     



  • 3.  Re: Need out of box Nimsoft sql queries

    Broadcom Employee
    Posted Nov 29, 2018 01:40 PM

    What Queries (updated ) are used by the sqlserver - CA Knowledge 

     

    I am unclear which query you are looking for from the sqlserver probe.

    is this what you are looking for:

    QOS_SQLSERVER_alloc_spacePercent

    Monitors allocated space.

    this is part of the Press space query:

    free_space,        
        
    sp_MSforeachdb 'select ''?'' db_name, (select convert(bigint,sum(size))* 8192 from [?].sys.database_files where type <> 1) allocated,
    ISNULL((select convert(bigint,sum(max_size))*8192 from [?].sys.database_files f where ((f.type <> 1) and f.growth > 0 and size > 0 ) having ((select count(*) from [?].sys.database_files where max_size = -1 and type <> 1 and growth > 0) = 0)),-1) max,
    ISNULL((select convert(bigint,sum(size))*8192 from [?].sys.database_files where type <> 1 and growth = 0 and size > 0),-1) max2,
    ISNULL((select convert(bigint,sum(a.used_pages))*8192 from [?].sys.allocation_units a),-1) used,
    (select count(*) from [?].sys.database_files cg where max_size = -1 and type <> 1 and growth > 0) unlimited

     

     

    The allocated is from the below line:

    sp_MSforeachdb 'select ''?'' db_name, (select convert(bigint,sum(size))* 8192 from [?].sys.database_files where type <> 1) allocated

     

    Hope this helps

     



  • 4.  Re: Need out of box Nimsoft sql queries

    Broadcom Employee
    Posted Nov 29, 2018 01:43 PM

    Here are all the queries for future reference.

     

    What Queries (updated ) are used by the sqlserver - CA Knowledge 



  • 5.  Re: Need out of box Nimsoft sql queries

    Broadcom Employee
    Posted Nov 29, 2018 02:41 PM

    Sowmiya -

     

    For the alloc_space checkpoint, the sqlserver probe actually executes the same query it executes for the free_space checkpoint.  This can be determined by checking the sqlserver probe logs at loglevel 5 where you will see lines similar to the following for these 2 checkpoints:

     

    sqlserver: (doQuery) Profile: <profile name>/alloc_space, doQuery free_space starting

    sqlserver: (doQuery) Profile: <profile name>/free_space, doQuery free_space starting

     

    There is a difference in how these two checkpoints handle "Unlimited Growth" in the monitoring sqlserver. 

     

    alloc_space
    This checkpoint monitors the amount of free space in database in allocated data files in %. This checkpoint doesn't consider "unlimited" growth.

    free_space
    This checkpoint monitors the amount of free space in database data files in %. If there is at least one file with "unlimited" growth, the space in the whole database is considered as 100% free. If you are using file groups, this could be misleading, therefore you should deactivate this checkpoint and use only the "fg_free_space" checkpoint.
    alloc_space tells you how much of the currently allocated space is free whereas free_space will always report 100% if there is "Unlimited Growth".

    The choice of which checkpoint to use would depend on how your SQL Server is configured.

     

    The following knowledge document provides the actual queries used by the sqlserver probe for all of the checkpoints:

     

    KB000103841 : SQL Server Checkpoints queries



  • 6.  RE: Re: Need out of box Nimsoft sql queries

    Posted Aug 29, 2019 03:56 AM
    Hi Kathy,

    Not able to download to attached in the kb article. It seems link has been removed. Can you please help me with some other one where i can download or check for queries behind the checkpoints ?

    ------------------------------
    System Analyst
    DXC Technology
    ------------------------------



  • 7.  RE: Re: Need out of box Nimsoft sql queries

    Broadcom Employee
    Posted Aug 29, 2019 03:02 PM
      |   view attached
    Siddharth -

    The inability to download documents attached to published Knowledge Document has been a known issue that our IT support team has been working on getting resolved.  To get you moving on this, I have attached a copy of the attached document from the KB103841 knowledge document here for you to download.

    ------------------------------
    Kathy Maguire
    Technical Support Engineer 4
    Broadcom
    ------------------------------

    Attachment(s)



  • 8.  RE: Re: Need out of box Nimsoft sql queries

    Posted Aug 29, 2019 03:23 PM
    Thanks a lot for this Kathy..
    One more question 
    do we have same mongodb & mysql also? IF yes can you please attach them also?

    ------------------------------
    System Analyst
    DXC Technology
    ------------------------------



  • 9.  RE: Re: Need out of box Nimsoft sql queries
    Best Answer

    Broadcom Employee
    Posted Sep 03, 2019 05:46 PM
    Siddharth -

    We do not have a corresponding document for the mongodb probe.

    For the mysql probe, take a look at the KB136531 : mySQL probe Checkpoint Queries knowledge document.  This document is not as complete as the one for the sqlserver probe, but will at least give you a starting point.

    ------------------------------
    Kathy Maguire
    Technical Support Engineer 4
    Broadcom
    ------------------------------