Post reply

Monitor load on database server

  • March 15, 2014 at 2:34 am

    #305297

    Hi Experts,

    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.

    Thanks in Advance.

  • March 25, 2014 at 6:39 am

    #1699835

    SQL Nexus is a good tool to evaluate the performance of a server whilst perfoming a load test.

    http://sqlnexus.codeplex.com/

    To evaluate the IO subsystem there is a utility called SQLIO:

    http://www.microsoft.com/en-us/download/details.aspx?id=20163

    I assume by load test you want to evaluate the most concurrent transactions that can occur on the server? and the effects this has on performance i.e. CPU utilisation, Batch requests per second, memory utilization, reads and writes per second, waits, io stalls etc etc?

    Most of these metrics are available in perfmon and can be written to file then imported into a SQL database or written directly to a SQL database using an ODBC connection, once in a DB performing analysis of the statistics captured is very easy.

    MCITP SQL 2005, MCSA SQL 2012

  • March 25, 2014 at 7:03 am

    #1699849

    There are several third party packages that can help with this.

  • March 25, 2014 at 8:00 am

    #1699885

    Thanks very much Taylor and Dj.

  • March 25, 2014 at 12:21 pm

    #1700019

    Oracle_91 (3/15/2014)

    Hi Experts,

    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.

    Thanks in Advance.

    You can use perfmon\DMvs to load the counters into SQL Server DB. And then run a report to find the changes over time.

    Check these ..

    https://www.simple-talk.com/sql/performance/collecting-performance-data-into-a-sql-server-table/

    http://www.mssqltips.com/sqlservertip/2188/collect-and-store-historical-sql-server-performance-counter-data-with-dmvs/

    http://flippingbits.typepad.com/blog/2011/11/please-stop-using-sqliosim-to-model-sql-workloads.html

    --

    SQLBuddy

  • March 25, 2014 at 11:31 pm

    #1700135

    Thank you sqlbuddy123. Nice posts. Thanks for sharing.:-)

  • March 26, 2014 at 2:20 pm

    #1700459

    You are welcome, Oracle_91 🙂

    --

    SQLBuddy

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply