Could anybody please suggest me a way to monitor the load on my database server?
What perfmon counters should I monitor on daily basis and confirm that load on the server is increasing day by day [or] by week by week [or] every month. Also, suggest me if we can achieve this using DMV's so that we can store this info in a table using a SQL agent job. Because as a sql guy I would be more happy working with TSQL queries.
Do all this helps me, to put for forward my argument to the management that this server can handle this much of load based on data and in case if the load is increased on the server , we might need more resources ( like cpu, better storage , more RAM etc …) or move to a new server which has more power.
Also, please suggest any load testing tools so that I can test this on testing environment.
Best Answer
There are a couple of things here. First performance monitoring is a subject that is very broad. When I started down the rabbit hole of performance monitoring I found Brent Ozar and Paul Randal's blogs very helpful.
Specifically Brent's post on perfmon counters. Its a little old but still a very good resource. To go along with that is Paul's post on I/O latencies and Brent's How to Prove Your SQL Server Needs More Memory
You will notice that most of the information I have posted here is from only two sources but they are the first place I tend to look. The second place I look to is Google. On top of those there are third party monitoring solutions such as Red Gate SQL Monitor, Confio Ignite (now Solar Winds) and many others.
The way I learned how to do performance monitoring was by being in a situation where I thought something was going on and so I started to try and prove it. Google was my best friend to find blogs and other information as well as reading this site and others as people asked questions. Once I got enough information from perfmon and other sources to point out that there was something wrong but we needed better visibility into the server I was able to get a third party monitoring tool which really helped. Turns out we did not really need more of something just tune several queries.