We would like to collect SQL Server performance metrics (memory, Cpu, page writes/sec, batches/sec,active sessions), etc. I heard its a rule not to collect Sql server metrics on the live Financial Server itself. Place it somewhere else. Should the monitor metrics be on a Different server instance or be on a totally different server? Should I consider Sql server standard, or anything lower?
Sql-server – Performance Metrics be on Own Server or Own Server Instance
performanceperformance-tuningsql serversql-server-2012
Related Question
- SQL Server 2005 – Maximum RAM Allocation for Performance Tuning
- Sql-server – How to increase performance in Cross Database operations
- Sql-server – high writelog wait on OLTP system and fast SSD storage, log flush is slow
- SQL Server Performance – High Checkpoint Pages/sec and Memory Pressure
- SQL Server Performance – Resolving High CPU Usage and RESOURCE_SEMAPHORE Waits
Best Answer
The issue is mostly because of resource competition. Any tool or method you use to monitor your server will introduce observer overhead. This goes for traces, Extended Events, home grown scripts, or third party monitoring tools.
The goal of putting them on a separate server is to reduce further overhead.
Monitoring queries write a lot to disk at regular intervals, and often get hit with rollup/aggregation queries to report on metrics, smooth data out, etc.
Those queries are usually big and ugly because those tables don't have a lot of indexes. Lots of indexes to help reporting queries would just slow those big/regular writes down.
Production resources should be solely for production data. It's not just a rule for monitoring tools, but for dev, QA, UAT, or demo databases sitting in prod. They're a potential performance liability that's easy to avoid.