0

The Data Collector is the single most obscure SQL Server concept that I know of. I have never even seen it mentioned in a professional or educational context, never mind used. I don't even think that it has a tag on this website, although I have made my best guesses. Yet, its support in dbatools is a strong sign that it must have some value.

I have read the documentation for the Data Collector with an open mind, but I simply cannot fathom a use case for it. What are some typical use cases in supported (i.e. 2012+) versions of SQL Server?

J. Mini
  • 1,161
  • 8
  • 32

1 Answers1

2

It is very handy if there are no third party monitoring tools available/installed to monitor SQL instance performance. It collects and generates reports of data collected. It has some pre-defined data collection sets and can have customized sets to collect the needed. Below are some usecases,

Performance Monitoring and Tuning: Collect performance data such as CPU, Disk I/O, Memory Usage, etc. Use this data to identify and troubleshoot performance issues, optimize query performance, balance SQL Server loads, and for capacity planning.

Disk Space Usage Monitoring: Monitor database and log file size growth, manage space effectively and plan for future disk space requirements.

Monitoring Server Activity: Collect data about user activity, SQL Server instance resources usage, and server operating conditions.

Collecting Query Statistics: Identify costly queries, long-running queries, and tune them for better performance.

Baseline Collection: Collect data over time to establish a baseline for the SQL Server instance for comparison with future performance.

Security Auditing: Keep track of changes in server activity and query statistics for auditing and compliance reasons.

This has good read of it, but based on 2008 version though.

S.D.
  • 754
  • 5
  • 19