How to create a baseline using counters to analyze server performance?

Question

I am currently working on creating a baseline for our server, using various counters to analyze performance. The server exclusively hosts SQL Server.

Could anyone here share their experiences or methodologies on how to create such a baseline and use it for performance analysis?

Answer 1

Hi,test code

The performance of SQL Server is determined by the server hardware, network, application software, and the usage of the database itself. Monitoring SQL Server performance and establishing a performance baseline is a complex and systematic project.

It mainly involves the following points:

defining objectives (ensuring system stability, preparing for troubleshooting, etc.),

collecting data (identifying data sources, KPIs),

analyzing and establishing baselines (identifying performance bottlenecks, determining the normal range of indicators),

and setting up monitoring and alerts.

ystem performance is mainly affected by the following resources:

1. Memory
2. CPU
3. I/O
4. Blocking and Deadlocks

Commonly involved collection tools include:

• PerfMon (SQL Server itself provides many counters for data collection and can also be used to collect information on the system's own resource usage. It is very powerful and complex, and requires consulting related materials for use.)
• Dynamic Management Views and Dynamic Management Functions System. Refer toCollect and store historical SQL Server performance counter data with DMVs
• Stored Procedures (sp_who, sp_lock)

After collecting the relevant data, you need to determine the thresholds based on the daily operation of the server. You can manually review the indicators and analyze and monitor them, or you can import the log files into a specialized database and use scheduled jobs for automated monitoring. Below are a few related links:

https://www.cnblogs.com/Amaranthus/archive/2011/04/09/2010390.html

https://solutioncenter.apexsql.com/how-to-detect-sql-server-performance-issues-using-baselines-part-1-introduction/

https://solutioncenter.apexsql.com/how-to-detect-sql-server-performance-issues-using-baselines-part-2-collecting-metrics-and-reporting/

Best regards,

Mikey Qiao

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it.