Nagaraj K

Posted this a couple of days ago on SQL Server Database Engine but no response. Hence posting in this forum again.

I've a web service which is being load tested using VSTS Load Test.
I want a suggestion on what counters are to be tracked to see that the SQL Server is performing as expected. It would be great if someone help me in gathering the right set of counters related to the database.

Also, I've researched over the net and found out the following counters which could help in tracking the behavior of SQL Server:

Avg. Transactions/sec
Deadlocks
Page Splits/sec
Batch Req/sec
SQL Compilations/Sec
User Connections
Average Wait Time (ms)
Full Scans/sec
Buffer Cache Hit Ratio
Page Life Expectancy
Lazy Writes/Sec
Pages/sec
Page Reads/sec
Page Writes/sec
% Processor Time - _Total
Processor Queue Length
Errors/sec

Please let me know

1. If they are the intended counters to track
2. Any other counters should also be tracked
3. What should be the Ideal value for each of the counter




Re: Visual Studio Team System - Testing Ideal SQL Counters to track while load tesing a webservice

slumley MSFT

Load tests have a sql counter set that has a number of good counters to track for sql performance.  Check out this help link for adding this counter set: http://msdn2.microsoft.com/en-us/library/ms404695(VS.80).aspx

Also here is another post which talks about collecting counters: http://msdn.microsoft.com/library/default.asp url=/library/en-us/dnpag/html/scalenet.asp






Re: Visual Studio Team System - Testing Ideal SQL Counters to track while load tesing a webservice

khawaja

Hi Nagaraj,

Do you have a same list for ASP .NET and IIS counters that are of significant importance.






Re: Visual Studio Team System - Testing Ideal SQL Counters to track while load tesing a webservice

slumley MSFT

Loadtests also have a predefined asp.net and iis counter set. Check out this link for how to add counter sets and map them to particular machines: http://msdn2.microsoft.com/en-us/library/ms404695(VS.80).aspx