sqlserver metrics to capture

• mtz676

  SSCrazy Eights

  Points: 9239

  More actions

  February 13, 2025 at 7:26 pm

  #4536984

  Can this baselining and benchmarking sqlserver be done using extended events ?

  what are the various events from underneath which can be captured as metrics to benchmark/baseline a sqlserver ?

  SELECT xp.[name], xo.* FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp WHERE xp.[guid] = xo.[package_guid] AND xo.[object_type] = 'event' ORDER BY xp.[name];

  What are the free options to baseline/benchmark sqlserver ?

  We are doing this to get some metrics for sql server consolidation.

  Please advise. Thanks

  • This topic was modified 1 month, 2 weeks ago by  mtz676.

• David Wiseman

  SSCommitted

  Points: 1720

  More actions

  February 13, 2025 at 9:41 pm

  #4537086

  Extended events tend to be more for diagnostic purposes than monitoring resource utilization.  You can get some info like CPU utilization from XE. Perfmon could be used to capture resource utilization and there are DMVs like sys.dm_os_performance_counters, dm_io_virtual_file_stats that you can use.  Instead of building something, you might want to look at DBA Dash - a free and open-source monitoring tool I created.  It will capture CPU, IO, waits, memory, running queries, slow RPC/batch completed XE events, and a ton of other stuff.  As it's open-source, you can also see the collection scripts used which might help if you are trying to build something.  Glen Berry's diagnostic queries might also provide some inspiration.

  DBA Dash - Free, open source monitoring for SQL Server

• KonRob

  Grasshopper

  Points: 16

  More actions

  March 6, 2025 at 11:09 am

  #4552502

  Extended Events can definitely help with baselining, but as mentioned, they are more suited for diagnostics than continuous performance tracking. If you're looking for resource utilization metrics, DMVs like sys.dm_os_wait_stats, sys.dm_exec_requests, and sys.dm_io_virtual_file_stats can give valuable insights.

  Perfmon is another great option for tracking CPU, memory, and disk I/O over time. If you want something more structured, DBA Dash, which was already mentioned, is a solid choice. Additionally, you could explore SQL Server Query Store for query performance baselining and historical analysis, especially useful if you're consolidating multiple instances. If you need a lightweight approach without third-party tools, consider a combination of Extended Events for capturing slow queries and system views for performance metrics.

• March 11, 2025 at 7:32 am

  This was removed by the editor as SPAM