2

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?

3

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.

1

If you want to get a lightweight performance tool that will provide plenty of information on the health of your SQL Server, I would use the tools provided by Brent Ozar.

http://www.brentozar.com.

If you are planning to use a perfmon to capture CPU, Memory, page writes/reads sec etc.. I would collect them from another server for a short period of time. I would then analyze them with PAL

https://github.com/clinthuffman/PAL

  • I would prefer not to consume server resources running perfmon locally. The one exception would be if I wanted to measure network performance. – Goforebroke Jul 18 '18 at 22:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy