locked
Effective GUI for monitoring blocks, memory, cpu,io in SQL server RRS feed

  • Question

  • Hi,

    Is there any GUI/ tool to monitor SQL server 2008 R2 memory, CPU, IO, blocks?

    Tuesday, April 22, 2014 6:48 AM

Answers

  • I use SQLMonitor tool from www.red-gate.com

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Prashanth Jayaram Tuesday, April 22, 2014 2:41 PM
    • Marked as answer by Sofiya Li Thursday, May 1, 2014 1:28 AM
    Tuesday, April 22, 2014 9:54 AM
  • Hi,

    Is there any GUI/ tool to monitor SQL server 2008 R2 memory, CPU, IO, blocks?

    Simply run below query to see SQL Server memory utilization.

    select
    (physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
    (locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
    (total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
    process_physical_memory_low,
    process_virtual_memory_low
    from sys. dm_os_process_memory

    Schedule it through agent job and add database mail to send mail to you.

    I use Quest spotlight it does no gives memory utilization but shows Buffer cache hit ratio.Which is important parameter to mark.SQL utilizes memory heavily so any tool can flash message like memory utilization high which actually is not correct.I have seen it with many tools.

    For others you can go for Quest tool.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Tuesday, April 22, 2014 10:51 AM
    Answerer
  • If you want to rely on any 3rd party tool then you've purchase the license and varieties of database monitoring tools are available in the market.

    As Uri mentioned SQLMonitor  Or Idera are good tools.

    If not,You can also take a look into the script and schedule a job and define an intelligence to send an automated alert in case of threshold breach.

    http://gallery.technet.microsoft.com/SQL-Find-CPUMemoryConnectio-badbf877

    --Prashanth


    • Marked as answer by Sofiya Li Thursday, May 1, 2014 1:28 AM
    Tuesday, April 22, 2014 2:40 PM
  • Use (free) SSMS built-in tools: Activity Monitor, Server Reports, Database Reports.


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    • Proposed as answer by Sofiya Li Wednesday, April 23, 2014 2:55 AM
    • Marked as answer by Sofiya Li Thursday, May 1, 2014 1:28 AM
    Wednesday, April 23, 2014 12:53 AM

All replies

  • I use SQLMonitor tool from www.red-gate.com

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Prashanth Jayaram Tuesday, April 22, 2014 2:41 PM
    • Marked as answer by Sofiya Li Thursday, May 1, 2014 1:28 AM
    Tuesday, April 22, 2014 9:54 AM
  • Hi,

    Is there any GUI/ tool to monitor SQL server 2008 R2 memory, CPU, IO, blocks?

    Simply run below query to see SQL Server memory utilization.

    select
    (physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
    (locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
    (total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
    process_physical_memory_low,
    process_virtual_memory_low
    from sys. dm_os_process_memory

    Schedule it through agent job and add database mail to send mail to you.

    I use Quest spotlight it does no gives memory utilization but shows Buffer cache hit ratio.Which is important parameter to mark.SQL utilizes memory heavily so any tool can flash message like memory utilization high which actually is not correct.I have seen it with many tools.

    For others you can go for Quest tool.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Tuesday, April 22, 2014 10:51 AM
    Answerer
  • If you want to rely on any 3rd party tool then you've purchase the license and varieties of database monitoring tools are available in the market.

    As Uri mentioned SQLMonitor  Or Idera are good tools.

    If not,You can also take a look into the script and schedule a job and define an intelligence to send an automated alert in case of threshold breach.

    http://gallery.technet.microsoft.com/SQL-Find-CPUMemoryConnectio-badbf877

    --Prashanth


    • Marked as answer by Sofiya Li Thursday, May 1, 2014 1:28 AM
    Tuesday, April 22, 2014 2:40 PM
  • Use (free) SSMS built-in tools: Activity Monitor, Server Reports, Database Reports.


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    • Proposed as answer by Sofiya Li Wednesday, April 23, 2014 2:55 AM
    • Marked as answer by Sofiya Li Thursday, May 1, 2014 1:28 AM
    Wednesday, April 23, 2014 12:53 AM