Performance Testing Using ACT

I'm using MS ACT to test RS URL access performance but the results
were very confusing...
The parameters I used in ACT:
- 500 simultaneous browser connections
- .dat file containing 500 reference IDs to be append to the URL
- 1 iteration to run the test
My script contains a procedure that will execute 500 times the same as
the available IDs in the .dat file:
============================================================================ Sub SendRequest(strID)
Dim oConnection, oRequest, oResponse, oHeaders, strStatusCode,
fEnableDelays
If fEnableDelays = True then Test.Sleep (0)
Set oConnection = Test.CreateConnection("abc.xyz.com", 80, false)
If (oConnection is Nothing) Then
Test.Trace "Error: Unable to create connection to abc.xyz.com"
Else
Set oRequest = Test.CreateRequest
oRequest.Path = "/ReportServer"+"?/ReportName&rs:Command=Render&rs:format=html4.0&rc:Toolbar=false&ID="+strID
oRequest.Verb = "GET"
oRequest.HTTPVersion = "HTTP/1.1"
set oHeaders = oRequest.Headers
oHeaders.RemoveAll
oHeaders.Add "Accept", "image/gif, image/x-xbitmap,
image/jpeg, image/pjpeg, application/vnd.ms-powerpoint,
application/vnd.ms-excel, application/msword,
application/x-shockwave-flash, */*"
oHeaders.Add "Accept-Language", "en-us"
ASP.NET_SessionId=jdhndgqnhrfgkuzpx53lcbaf;
%2fReportName=5edhrh55zoydow45aeasfj55"
oHeaders.Add "Cookie", "(automatic)"
oHeaders.Add "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0;
Windows NT 5.0; .NET CLR 1.1.4322)"
oHeaders.Add "Host", "abc.xyz.com"
oHeaders.Add "Authorization", "Basic
dG9tbXl0XGFkbWluaXN0cmF0b3I6"
oHeaders.Add "Cookie", "(automatic)"
Set oResponse = oConnection.Send(oRequest)
oConnection.Close
End If
End Sub
============================================================================ The result of the testing shows:
============================================================================ Test Name: ACTSamples: RS Performance Test 3
Test Run Name: report-RS Performance Test 3-Aug 24, 2004 03-48-35
Test Started: 8/24/2004 3:38:57 AM
Test Duration: 00:00:09:37
Test Iterations: 1
Test Notes: -
Properties
Test type: Dynamic
Simultaneous browser connections: 500
Warm up time (secs): 0
Test duration: 00:00:09:37
Test iterations: 1
Detailed test results generated: Yes
Summary
Total number of requests: 500
Total number of connections: 500
Average requests per second: 0.87
Average time to first byte (msecs): 492.48
Average time to last byte (msecs): 1,111.46
Average time to last byte per iteration (msecs): 555,728.00
Number of unique requests made in test: 1
Number of unique response codes: 1
Errors Counts
HTTP: 0
DNS: 0
Socket: 0
Additional Network Statistics
Average bandwidth (bytes/sec): 107,079.37
Number of bytes sent (bytes): 272,940
Number of bytes received (bytes): 61,511,854
Average rate of sent bytes (bytes/sec): 473.03
Average rate of received bytes (bytes/sec): 106,606.33
Number of connection errors: 0
Number of send errors: 0
Number of receive errors: 0
Number of timeout errors: 0
Response Codes
Response Code: 200 - The request completed successfully.
Count: 500
Percent (%): 100.00
============================================================================ Issues:
============================================================================ 1. When I run the script, monitoring the RPS of ACT, it never goes
more than 2 (not multi-thread? flow control?)
2. 500 users took about 10 mins. to complete (same issues as item 1)
3. Checking the RS Web Service Performance monitor, 3 suspicious
counter values were:
a. Total Processing Failure = 5120 (why it is so high?)
b. Report Executed / Sec = 1 (never goes more than 1 during the
entire testing, threading problem?)
c. Request / Sec = = 1 (never goes more than 1 during the entire
testing)
4. All 500 URL requests were logged into ExecutionLog of RS Server and
tagged as "rsSuccess" (ACT was able to send all requests to RS and
have them processed. but it was logged into the table 1 by 1 until all
requests were processed. Meaning you will receive your 500th report
after "n sec/report x 500 reports"?)
5. Why Number of unique requests made in test = 1 while URLs were
distinct because of the ID parameter?
I really wonder why RS behaves this way, it doesn't seems to be
multi-thread. In RSReportServer.config, MaxActiveReqForOneUser was set
to 0 to ensure maximum number of users can be handled but still it
doesn't help.
I don't think DoS attack protection features/setting of RS is a valid
reason for the system to behave like that. If so, then the problem
should be with ACT who issues 500 concurrent connections from the same
workstation? Something must be wrong.
My question:
RS URL access cannot support more than 500 users? If not, why? If yes,
can anyone tell me how to perform a correct performance test? Thanks!
CCCsome answers inline
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"CCC" <tommytan25@.yahoo.com> wrote in message
news:ab9130f9.0408240040.3c25f0d4@.posting.google.com...
> I'm using MS ACT to test RS URL access performance but the results
> were very confusing...
> The parameters I used in ACT:
> - 500 simultaneous browser connections
> - .dat file containing 500 reference IDs to be append to the URL
> - 1 iteration to run the test
> My script contains a procedure that will execute 500 times the same as
> the available IDs in the .dat file:
>
============================================================================> Sub SendRequest(strID)
> Dim oConnection, oRequest, oResponse, oHeaders, strStatusCode,
> fEnableDelays
> If fEnableDelays = True then Test.Sleep (0)
> Set oConnection = Test.CreateConnection("abc.xyz.com", 80, false)
> If (oConnection is Nothing) Then
> Test.Trace "Error: Unable to create connection to abc.xyz.com"
> Else
> Set oRequest = Test.CreateRequest
> oRequest.Path =>
"/ReportServer"+"?/ReportName&rs:Command=Render&rs:format=html4.0&rc:Toolbar
=false&ID="+strID
> oRequest.Verb = "GET"
> oRequest.HTTPVersion = "HTTP/1.1"
> set oHeaders = oRequest.Headers
> oHeaders.RemoveAll
> oHeaders.Add "Accept", "image/gif, image/x-xbitmap,
> image/jpeg, image/pjpeg, application/vnd.ms-powerpoint,
> application/vnd.ms-excel, application/msword,
> application/x-shockwave-flash, */*"
> oHeaders.Add "Accept-Language", "en-us"
> ASP.NET_SessionId=jdhndgqnhrfgkuzpx53lcbaf;
> %2fReportName=5edhrh55zoydow45aeasfj55"
> oHeaders.Add "Cookie", "(automatic)"
> oHeaders.Add "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0;
> Windows NT 5.0; .NET CLR 1.1.4322)"
> oHeaders.Add "Host", "abc.xyz.com"
> oHeaders.Add "Authorization", "Basic
> dG9tbXl0XGFkbWluaXN0cmF0b3I6"
> oHeaders.Add "Cookie", "(automatic)"
> Set oResponse = oConnection.Send(oRequest)
> oConnection.Close
> End If
> End Sub
>
>
============================================================================> The result of the testing shows:
>
============================================================================> Test Name: ACTSamples: RS Performance Test 3
> Test Run Name: report-RS Performance Test 3-Aug 24, 2004 03-48-35
> Test Started: 8/24/2004 3:38:57 AM
> Test Duration: 00:00:09:37
> Test Iterations: 1
> Test Notes: -
>
> Properties
> Test type: Dynamic
> Simultaneous browser connections: 500
> Warm up time (secs): 0
> Test duration: 00:00:09:37
> Test iterations: 1
> Detailed test results generated: Yes
> Summary
> Total number of requests: 500
> Total number of connections: 500
> Average requests per second: 0.87
> Average time to first byte (msecs): 492.48
> Average time to last byte (msecs): 1,111.46
> Average time to last byte per iteration (msecs): 555,728.00
> Number of unique requests made in test: 1
> Number of unique response codes: 1
> Errors Counts
> HTTP: 0
> DNS: 0
> Socket: 0
> Additional Network Statistics
> Average bandwidth (bytes/sec): 107,079.37
> Number of bytes sent (bytes): 272,940
> Number of bytes received (bytes): 61,511,854
> Average rate of sent bytes (bytes/sec): 473.03
> Average rate of received bytes (bytes/sec): 106,606.33
> Number of connection errors: 0
> Number of send errors: 0
> Number of receive errors: 0
> Number of timeout errors: 0
> Response Codes
> Response Code: 200 - The request completed successfully.
> Count: 500
> Percent (%): 100.00
>
============================================================================> Issues:
>
============================================================================
*** Tudor: this shows a report is returned in ~ 1 sec on average, so the
number of request per second match what you see in ACT, assuming your server
has 1 or 2 processors. This is not about flow control, it simply takes that
much to generate the reports in this suite. The number of concurrent users
the system supports depends on how long a report generation takes.
If you would running all 500 requests at the same time, it would probably
trash the system from all the context switches.
***
> 1. When I run the script, monitoring the RPS of ACT, it never goes
> more than 2 (not multi-thread? flow control?)
> 2. 500 users took about 10 mins. to complete (same issues as item 1)
> 3. Checking the RS Web Service Performance monitor, 3 suspicious
> counter values were:
> a. Total Processing Failure = 5120 (why it is so high?)
***
Tudor: I believe this is an additive counter, so its value is the total
since the report server started. There may also be cases in which we wrongly
increment this for warnings. The execution log and ACT should give you the
correct data here.
***
> b. Report Executed / Sec = 1 (never goes more than 1 during the
> entire testing, threading problem?)
> c. Request / Sec = = 1 (never goes more than 1 during the entire
> testing)
> 4. All 500 URL requests were logged into ExecutionLog of RS Server and
> tagged as "rsSuccess" (ACT was able to send all requests to RS and
> have them processed. but it was logged into the table 1 by 1 until all
> requests were processed. Meaning you will receive your 500th report
> after "n sec/report x 500 reports"?)
> 5. Why Number of unique requests made in test = 1 while URLs were
> distinct because of the ID parameter?
> I really wonder why RS behaves this way, it doesn't seems to be
> multi-thread. In RSReportServer.config, MaxActiveReqForOneUser was set
> to 0 to ensure maximum number of users can be handled but still it
> doesn't help.
***
Tudor: You should change MaxActiveReqForOneUser to 500 or a large number I
don't think 0 means unbounded.
***
> I don't think DoS attack protection features/setting of RS is a valid
> reason for the system to behave like that. If so, then the problem
> should be with ACT who issues 500 concurrent connections from the same
> workstation? Something must be wrong.
> My question:
> RS URL access cannot support more than 500 users? If not, why? If yes,
> can anyone tell me how to perform a correct performance test? Thanks!
> CCC|||Don't trust ACT's RPS - instead use ASP.NET's RPS and Requests Executing value. What do they show?
Also, I don't believe ACT factors in the QueryString when determining # of unique requests.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"CCC" <tommytan25@.yahoo.com> wrote in message
news:ab9130f9.0408240040.3c25f0d4@.posting.google.com...
> I'm using MS ACT to test RS URL access performance but the results
> were very confusing...
> The parameters I used in ACT:
> - 500 simultaneous browser connections
> - .dat file containing 500 reference IDs to be append to the URL
> - 1 iteration to run the test
> My script contains a procedure that will execute 500 times the same as
> the available IDs in the .dat file:
> ============================================================================> Sub SendRequest(strID)
> Dim oConnection, oRequest, oResponse, oHeaders, strStatusCode,
> fEnableDelays
> If fEnableDelays = True then Test.Sleep (0)
> Set oConnection = Test.CreateConnection("abc.xyz.com", 80, false)
> If (oConnection is Nothing) Then
> Test.Trace "Error: Unable to create connection to abc.xyz.com"
> Else
> Set oRequest = Test.CreateRequest
> oRequest.Path => "/ReportServer"+"?/ReportName&rs:Command=Render&rs:format=html4.0&rc:Toolbar=false&ID="+strID
> oRequest.Verb = "GET"
> oRequest.HTTPVersion = "HTTP/1.1"
> set oHeaders = oRequest.Headers
> oHeaders.RemoveAll
> oHeaders.Add "Accept", "image/gif, image/x-xbitmap,
> image/jpeg, image/pjpeg, application/vnd.ms-powerpoint,
> application/vnd.ms-excel, application/msword,
> application/x-shockwave-flash, */*"
> oHeaders.Add "Accept-Language", "en-us"
> ASP.NET_SessionId=jdhndgqnhrfgkuzpx53lcbaf;
> %2fReportName=5edhrh55zoydow45aeasfj55"
> oHeaders.Add "Cookie", "(automatic)"
> oHeaders.Add "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0;
> Windows NT 5.0; .NET CLR 1.1.4322)"
> oHeaders.Add "Host", "abc.xyz.com"
> oHeaders.Add "Authorization", "Basic
> dG9tbXl0XGFkbWluaXN0cmF0b3I6"
> oHeaders.Add "Cookie", "(automatic)"
> Set oResponse = oConnection.Send(oRequest)
> oConnection.Close
> End If
> End Sub
>
> ============================================================================> The result of the testing shows:
> ============================================================================> Test Name: ACTSamples: RS Performance Test 3
> Test Run Name: report-RS Performance Test 3-Aug 24, 2004 03-48-35
> Test Started: 8/24/2004 3:38:57 AM
> Test Duration: 00:00:09:37
> Test Iterations: 1
> Test Notes: -
>
> Properties
> Test type: Dynamic
> Simultaneous browser connections: 500
> Warm up time (secs): 0
> Test duration: 00:00:09:37
> Test iterations: 1
> Detailed test results generated: Yes
> Summary
> Total number of requests: 500
> Total number of connections: 500
> Average requests per second: 0.87
> Average time to first byte (msecs): 492.48
> Average time to last byte (msecs): 1,111.46
> Average time to last byte per iteration (msecs): 555,728.00
> Number of unique requests made in test: 1
> Number of unique response codes: 1
> Errors Counts
> HTTP: 0
> DNS: 0
> Socket: 0
> Additional Network Statistics
> Average bandwidth (bytes/sec): 107,079.37
> Number of bytes sent (bytes): 272,940
> Number of bytes received (bytes): 61,511,854
> Average rate of sent bytes (bytes/sec): 473.03
> Average rate of received bytes (bytes/sec): 106,606.33
> Number of connection errors: 0
> Number of send errors: 0
> Number of receive errors: 0
> Number of timeout errors: 0
> Response Codes
> Response Code: 200 - The request completed successfully.
> Count: 500
> Percent (%): 100.00
> ============================================================================> Issues:
> ============================================================================> 1. When I run the script, monitoring the RPS of ACT, it never goes
> more than 2 (not multi-thread? flow control?)
> 2. 500 users took about 10 mins. to complete (same issues as item 1)
> 3. Checking the RS Web Service Performance monitor, 3 suspicious
> counter values were:
> a. Total Processing Failure = 5120 (why it is so high?)
> b. Report Executed / Sec = 1 (never goes more than 1 during the
> entire testing, threading problem?)
> c. Request / Sec = = 1 (never goes more than 1 during the entire
> testing)
> 4. All 500 URL requests were logged into ExecutionLog of RS Server and
> tagged as "rsSuccess" (ACT was able to send all requests to RS and
> have them processed. but it was logged into the table 1 by 1 until all
> requests were processed. Meaning you will receive your 500th report
> after "n sec/report x 500 reports"?)
> 5. Why Number of unique requests made in test = 1 while URLs were
> distinct because of the ID parameter?
> I really wonder why RS behaves this way, it doesn't seems to be
> multi-thread. In RSReportServer.config, MaxActiveReqForOneUser was set
> to 0 to ensure maximum number of users can be handled but still it
> doesn't help.
> I don't think DoS attack protection features/setting of RS is a valid
> reason for the system to behave like that. If so, then the problem
> should be with ACT who issues 500 concurrent connections from the same
> workstation? Something must be wrong.
> My question:
> RS URL access cannot support more than 500 users? If not, why? If yes,
> can anyone tell me how to perform a correct performance test? Thanks!
> CCC|||Hi Tudor,
Thank you very much for your comments...
Are you saying the RS's URL access cannot handle 500 users accessing
their own report at the same time? I'm not quite clear with the
dependencies of concurrent users and report generation time, mind to
clarify further? Are you saying it is normal for 500th user to get
their report after first 499 reports were generated?
On Total Processing Failure, I restarted all counters before running a
test therefore I'm certain that this counter was generated in 1 test
and not additive of multiple tests. RS Web Service counters is what MS
included in the suite and the counter is unique from the data
presented in ExecutionLog and ACT.
I'm also wondering why give there are a lot of processing failures, RS
can still generate the report correctly (except too slow) and logs
them into ExecutionLog and ACT. What are the other possible cause of
high processing failure aside from "originate from the report
processor or any extension" as stated in RS documentations? Also, what
are the cause of processing failure in report processor and other
extensions?
On MaxActiveReqForOneUser, please see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_configfiles_v1_9y5i.asp
something must be wrong here, either msdn documentation or... :)
CCC
"Tudor Trufinescu \(MSFT\)" <tudortr@.ms.com> wrote in message news:<Opy2QDiiEHA.1104@.TK2MSFTNGP10.phx.gbl>...
> some answers inline
> --
> Tudor Trufinescu
> Dev Lead
> Sql Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "CCC" <tommytan25@.yahoo.com> wrote in message
> news:ab9130f9.0408240040.3c25f0d4@.posting.google.com...
> > I'm using MS ACT to test RS URL access performance but the results
> > were very confusing...
> >
> > The parameters I used in ACT:
> > - 500 simultaneous browser connections
> > - .dat file containing 500 reference IDs to be append to the URL
> > - 1 iteration to run the test
> >
> > My script contains a procedure that will execute 500 times the same as
> > the available IDs in the .dat file:
> >
> ============================================================================> > Sub SendRequest(strID)
> >
> > Dim oConnection, oRequest, oResponse, oHeaders, strStatusCode,
> > fEnableDelays
> >
> > If fEnableDelays = True then Test.Sleep (0)
> > Set oConnection = Test.CreateConnection("abc.xyz.com", 80, false)
> > If (oConnection is Nothing) Then
> > Test.Trace "Error: Unable to create connection to abc.xyz.com"
> > Else
> > Set oRequest = Test.CreateRequest
> >
> > oRequest.Path => >
> "/ReportServer"+"?/ReportName&rs:Command=Render&rs:format=html4.0&rc:Toolbar
> =false&ID="+strID
> > oRequest.Verb = "GET"
> > oRequest.HTTPVersion = "HTTP/1.1"
> > set oHeaders = oRequest.Headers
> > oHeaders.RemoveAll
> > oHeaders.Add "Accept", "image/gif, image/x-xbitmap,
> > image/jpeg, image/pjpeg, application/vnd.ms-powerpoint,
> > application/vnd.ms-excel, application/msword,
> > application/x-shockwave-flash, */*"
> > oHeaders.Add "Accept-Language", "en-us"
> > ASP.NET_SessionId=jdhndgqnhrfgkuzpx53lcbaf;
> > %2fReportName=5edhrh55zoydow45aeasfj55"
> > oHeaders.Add "Cookie", "(automatic)"
> > oHeaders.Add "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0;
> > Windows NT 5.0; .NET CLR 1.1.4322)"
> > oHeaders.Add "Host", "abc.xyz.com"
> > oHeaders.Add "Authorization", "Basic
> > dG9tbXl0XGFkbWluaXN0cmF0b3I6"
> > oHeaders.Add "Cookie", "(automatic)"
> > Set oResponse = oConnection.Send(oRequest)
> > oConnection.Close
> > End If
> >
> > End Sub
> >
> >
> >
> ============================================================================> > The result of the testing shows:
> >
> ============================================================================> > Test Name: ACTSamples: RS Performance Test 3
> > Test Run Name: report-RS Performance Test 3-Aug 24, 2004 03-48-35
> > Test Started: 8/24/2004 3:38:57 AM
> > Test Duration: 00:00:09:37
> > Test Iterations: 1
> > Test Notes: -
> >
> >
> > Properties
> >
> > Test type: Dynamic
> > Simultaneous browser connections: 500
> > Warm up time (secs): 0
> > Test duration: 00:00:09:37
> > Test iterations: 1
> > Detailed test results generated: Yes
> >
> > Summary
> >
> > Total number of requests: 500
> > Total number of connections: 500
> >
> > Average requests per second: 0.87
> > Average time to first byte (msecs): 492.48
> > Average time to last byte (msecs): 1,111.46
> > Average time to last byte per iteration (msecs): 555,728.00
> >
> > Number of unique requests made in test: 1
> > Number of unique response codes: 1
> >
> > Errors Counts
> >
> > HTTP: 0
> > DNS: 0
> > Socket: 0
> >
> > Additional Network Statistics
> >
> > Average bandwidth (bytes/sec): 107,079.37
> >
> > Number of bytes sent (bytes): 272,940
> > Number of bytes received (bytes): 61,511,854
> >
> > Average rate of sent bytes (bytes/sec): 473.03
> > Average rate of received bytes (bytes/sec): 106,606.33
> >
> > Number of connection errors: 0
> > Number of send errors: 0
> > Number of receive errors: 0
> > Number of timeout errors: 0
> >
> > Response Codes
> >
> > Response Code: 200 - The request completed successfully.
> > Count: 500
> > Percent (%): 100.00
> >
> >
> ============================================================================> > Issues:
> >
> ============================================================================> *** Tudor: this shows a report is returned in ~ 1 sec on average, so the
> number of request per second match what you see in ACT, assuming your server
> has 1 or 2 processors. This is not about flow control, it simply takes that
> much to generate the reports in this suite. The number of concurrent users
> the system supports depends on how long a report generation takes.
> If you would running all 500 requests at the same time, it would probably
> trash the system from all the context switches.
> ***
> > 1. When I run the script, monitoring the RPS of ACT, it never goes
> > more than 2 (not multi-thread? flow control?)
> > 2. 500 users took about 10 mins. to complete (same issues as item 1)
> > 3. Checking the RS Web Service Performance monitor, 3 suspicious
> > counter values were:
> > a. Total Processing Failure = 5120 (why it is so high?)
> ***
> Tudor: I believe this is an additive counter, so its value is the total
> since the report server started. There may also be cases in which we wrongly
> increment this for warnings. The execution log and ACT should give you the
> correct data here.
> ***
> > b. Report Executed / Sec = 1 (never goes more than 1 during the
> > entire testing, threading problem?)
> > c. Request / Sec = = 1 (never goes more than 1 during the entire
> > testing)
> > 4. All 500 URL requests were logged into ExecutionLog of RS Server and
> > tagged as "rsSuccess" (ACT was able to send all requests to RS and
> > have them processed. but it was logged into the table 1 by 1 until all
> > requests were processed. Meaning you will receive your 500th report
> > after "n sec/report x 500 reports"?)
> > 5. Why Number of unique requests made in test = 1 while URLs were
> > distinct because of the ID parameter?
> >
> > I really wonder why RS behaves this way, it doesn't seems to be
> > multi-thread. In RSReportServer.config, MaxActiveReqForOneUser was set
> > to 0 to ensure maximum number of users can be handled but still it
> > doesn't help.
> ***
> Tudor: You should change MaxActiveReqForOneUser to 500 or a large number I
> don't think 0 means unbounded.
> ***
> >
> > I don't think DoS attack protection features/setting of RS is a valid
> > reason for the system to behave like that. If so, then the problem
> > should be with ACT who issues 500 concurrent connections from the same
> > workstation? Something must be wrong.
> >
> > My question:
> > RS URL access cannot support more than 500 users? If not, why? If yes,
> > can anyone tell me how to perform a correct performance test? Thanks!
> >
> > CCC|||> Are you saying the RS's URL access cannot handle 500 users accessing
> their own report at the same time?
No, I am just saying that the number of requests/sec the server supports
depends on the resources available on the server (processors, memory, disk)
and the report complexity. From your results it looks like the server can
sustain 1 reports/sec in this configuration and this type of load. Also from
ACT results, your 500 users will get their report back in ~ 1 sec on average
which is a relatively good response time. If you want to support a higher
load, try simpler reports or use RS caching your number of reports per
second will increase accordingly.
I would not worry about the report errors counter for now, the execution log
has the right data in it.
The documentation for MaxRequestsForOneUser seems wrong, thanks for pointing
that out.
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"CCC" <tommytan25@.yahoo.com> wrote in message
news:ab9130f9.0408241848.3bf408e2@.posting.google.com...
> Hi Tudor,
> Thank you very much for your comments...
> Are you saying the RS's URL access cannot handle 500 users accessing
> their own report at the same time? I'm not quite clear with the
> dependencies of concurrent users and report generation time, mind to
> clarify further? Are you saying it is normal for 500th user to get
> their report after first 499 reports were generated?
> On Total Processing Failure, I restarted all counters before running a
> test therefore I'm certain that this counter was generated in 1 test
> and not additive of multiple tests. RS Web Service counters is what MS
> included in the suite and the counter is unique from the data
> presented in ExecutionLog and ACT.
> I'm also wondering why give there are a lot of processing failures, RS
> can still generate the report correctly (except too slow) and logs
> them into ExecutionLog and ACT. What are the other possible cause of
> high processing failure aside from "originate from the report
> processor or any extension" as stated in RS documentations? Also, what
> are the cause of processing failure in report processor and other
> extensions?
> On MaxActiveReqForOneUser, please see
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_configfiles_v1_9y5i.asp
> something must be wrong here, either msdn documentation or... :)
> CCC
> "Tudor Trufinescu \(MSFT\)" <tudortr@.ms.com> wrote in message
news:<Opy2QDiiEHA.1104@.TK2MSFTNGP10.phx.gbl>...
> > some answers inline
> >
> > --
> > Tudor Trufinescu
> > Dev Lead
> > Sql Server Reporting Services
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "CCC" <tommytan25@.yahoo.com> wrote in message
> > news:ab9130f9.0408240040.3c25f0d4@.posting.google.com...
> > > I'm using MS ACT to test RS URL access performance but the results
> > > were very confusing...
> > >
> > > The parameters I used in ACT:
> > > - 500 simultaneous browser connections
> > > - .dat file containing 500 reference IDs to be append to the URL
> > > - 1 iteration to run the test
> > >
> > > My script contains a procedure that will execute 500 times the same as
> > > the available IDs in the .dat file:
> > >
> >
============================================================================> > > Sub SendRequest(strID)
> > >
> > > Dim oConnection, oRequest, oResponse, oHeaders, strStatusCode,
> > > fEnableDelays
> > >
> > > If fEnableDelays = True then Test.Sleep (0)
> > > Set oConnection = Test.CreateConnection("abc.xyz.com", 80, false)
> > > If (oConnection is Nothing) Then
> > > Test.Trace "Error: Unable to create connection to abc.xyz.com"
> > > Else
> > > Set oRequest = Test.CreateRequest
> > >
> > > oRequest.Path => > >
> >
"/ReportServer"+"?/ReportName&rs:Command=Render&rs:format=html4.0&rc:Toolbar
> > =false&ID="+strID
> > > oRequest.Verb = "GET"
> > > oRequest.HTTPVersion = "HTTP/1.1"
> > > set oHeaders = oRequest.Headers
> > > oHeaders.RemoveAll
> > > oHeaders.Add "Accept", "image/gif, image/x-xbitmap,
> > > image/jpeg, image/pjpeg, application/vnd.ms-powerpoint,
> > > application/vnd.ms-excel, application/msword,
> > > application/x-shockwave-flash, */*"
> > > oHeaders.Add "Accept-Language", "en-us"
> > > ASP.NET_SessionId=jdhndgqnhrfgkuzpx53lcbaf;
> > > %2fReportName=5edhrh55zoydow45aeasfj55"
> > > oHeaders.Add "Cookie", "(automatic)"
> > > oHeaders.Add "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0;
> > > Windows NT 5.0; .NET CLR 1.1.4322)"
> > > oHeaders.Add "Host", "abc.xyz.com"
> > > oHeaders.Add "Authorization", "Basic
> > > dG9tbXl0XGFkbWluaXN0cmF0b3I6"
> > > oHeaders.Add "Cookie", "(automatic)"
> > > Set oResponse = oConnection.Send(oRequest)
> > > oConnection.Close
> > > End If
> > >
> > > End Sub
> > >
> > >
> > >
> >
============================================================================> > > The result of the testing shows:
> > >
> >
============================================================================> > > Test Name: ACTSamples: RS Performance Test 3
> > > Test Run Name: report-RS Performance Test 3-Aug 24, 2004 03-48-35
> > > Test Started: 8/24/2004 3:38:57 AM
> > > Test Duration: 00:00:09:37
> > > Test Iterations: 1
> > > Test Notes: -
> > >
> > >
> > > Properties
> > >
> > > Test type: Dynamic
> > > Simultaneous browser connections: 500
> > > Warm up time (secs): 0
> > > Test duration: 00:00:09:37
> > > Test iterations: 1
> > > Detailed test results generated: Yes
> > >
> > > Summary
> > >
> > > Total number of requests: 500
> > > Total number of connections: 500
> > >
> > > Average requests per second: 0.87
> > > Average time to first byte (msecs): 492.48
> > > Average time to last byte (msecs): 1,111.46
> > > Average time to last byte per iteration (msecs): 555,728.00
> > >
> > > Number of unique requests made in test: 1
> > > Number of unique response codes: 1
> > >
> > > Errors Counts
> > >
> > > HTTP: 0
> > > DNS: 0
> > > Socket: 0
> > >
> > > Additional Network Statistics
> > >
> > > Average bandwidth (bytes/sec): 107,079.37
> > >
> > > Number of bytes sent (bytes): 272,940
> > > Number of bytes received (bytes): 61,511,854
> > >
> > > Average rate of sent bytes (bytes/sec): 473.03
> > > Average rate of received bytes (bytes/sec): 106,606.33
> > >
> > > Number of connection errors: 0
> > > Number of send errors: 0
> > > Number of receive errors: 0
> > > Number of timeout errors: 0
> > >
> > > Response Codes
> > >
> > > Response Code: 200 - The request completed successfully.
> > > Count: 500
> > > Percent (%): 100.00
> > >
> > >
> >
============================================================================> > > Issues:
> > >
> >
============================================================================> >
> > *** Tudor: this shows a report is returned in ~ 1 sec on average, so the
> > number of request per second match what you see in ACT, assuming your
server
> > has 1 or 2 processors. This is not about flow control, it simply takes
that
> > much to generate the reports in this suite. The number of concurrent
users
> > the system supports depends on how long a report generation takes.
> > If you would running all 500 requests at the same time, it would
probably
> > trash the system from all the context switches.
> > ***
> >
> > > 1. When I run the script, monitoring the RPS of ACT, it never goes
> > > more than 2 (not multi-thread? flow control?)
> > > 2. 500 users took about 10 mins. to complete (same issues as item 1)
> > > 3. Checking the RS Web Service Performance monitor, 3 suspicious
> > > counter values were:
> > > a. Total Processing Failure = 5120 (why it is so high?)
> >
> > ***
> > Tudor: I believe this is an additive counter, so its value is the total
> > since the report server started. There may also be cases in which we
wrongly
> > increment this for warnings. The execution log and ACT should give you
the
> > correct data here.
> > ***
> >
> > > b. Report Executed / Sec = 1 (never goes more than 1 during the
> > > entire testing, threading problem?)
> > > c. Request / Sec = = 1 (never goes more than 1 during the entire
> > > testing)
> > > 4. All 500 URL requests were logged into ExecutionLog of RS Server and
> > > tagged as "rsSuccess" (ACT was able to send all requests to RS and
> > > have them processed. but it was logged into the table 1 by 1 until all
> > > requests were processed. Meaning you will receive your 500th report
> > > after "n sec/report x 500 reports"?)
> > > 5. Why Number of unique requests made in test = 1 while URLs were
> > > distinct because of the ID parameter?
> > >
> > > I really wonder why RS behaves this way, it doesn't seems to be
> > > multi-thread. In RSReportServer.config, MaxActiveReqForOneUser was set
> > > to 0 to ensure maximum number of users can be handled but still it
> > > doesn't help.
> >
> > ***
> > Tudor: You should change MaxActiveReqForOneUser to 500 or a large number
I
> > don't think 0 means unbounded.
> > ***
> > >
> > > I don't think DoS attack protection features/setting of RS is a valid
> > > reason for the system to behave like that. If so, then the problem
> > > should be with ACT who issues 500 concurrent connections from the same
> > > workstation? Something must be wrong.
> > >
> > > My question:
> > > RS URL access cannot support more than 500 users? If not, why? If yes,
> > > can anyone tell me how to perform a correct performance test? Thanks!
> > >
> > > CCCsql

performance parameters.,,,

Dear all

Can any one provide me details of the parameters to be monitored in sql server 2005

Currently i am monitoring

SQLServer : Buffer Manager : Buffer Cache Hit Ratio

SQLServer : Databases : Log Flushes / Sec

SQLServer : Databases: Transactions / Sec

SQLServer : Access Methods : Page Splits / Sec.....

1) Please provide if any other relevant parameters to be monitored by perfmon.

2) Which are the most essential parameters to be monitored.

3) Is there any query or trace that will provide me performance details in SQL.

4) Can i have a query that get me the most resource consuming process or query in sql server.

5) wht parameters do i need to monitor on weekly and monthly basis for sql server 2005.

It would be very helpful if you guys could provide me help on these.

Thanks in advance

That always depends upon what you are attempting to monitor. However, that said, there are a few 'base' counters to monitor. I've included a group of articles that will provide you excellent information about performance monitoring and performance tuning.

Performance Audit
http://www.sql-server-performance.com/articles_audit.asp
http://www.sql-server-performance.com/sql_server_performance_audit10.asp

Performance -Link Server Performance Tips
http://www.sql-server-performance.com/linked_server.asp

Performance Monitoring
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx Performance WP's
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx Troubleshooting Performance 2005
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
http://support.microsoft.com/?id=271509 Script to Monitor Blocking

Performance Tuning -Articles
http://www.sql-server-performance.com/articles_performance.asp

Performance Tuning –Hardware
http://www.sql-server-performance.com/sg_sql_server_performance_article.asp

|||

As Arnie said, it really depends on your workload, and what your normal baseline values are. Having said that, here are some basic "rules of thumb" for those counters:

SQLServer : Buffer Manager : Buffer Cache Hit Ratio - Higher is better, below 90% is considered very bad. Its one sign of memory pressure.

SQLServer : Databases : Log Flushes / Sec - Lower is generally better

SQLServer : Databases: Transactions / Sec - This is one measure of how "busy" your system is. Batch Requests/Second is another measure that can be more helpful.

SQLServer : Access Methods : Page Splits / Sec - Lower is better. Having indexes with very high fill factors on volatile tables will cause more page splits, which leads to fragmented indexes, which causes more I/O.

Performance Opening Report in Preview

Opening the report in preview is very slow, as if the main query is running
without the parameters. Is there a setting to make sure a dataset is not
queried until after the report view button is clicked?Do you have defaults for all the parameters? If so then it runs as soon as
you preview the report. To have it not run until you click on the view
button at least one parameter needs to be without a default.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"WillC" <WillC@.discussions.microsoft.com> wrote in message
news:3578FA47-2646-419F-B683-32E8C70B4622@.microsoft.com...
> Opening the report in preview is very slow, as if the main query is
> running
> without the parameters. Is there a setting to make sure a dataset is not
> queried until after the report view button is clicked?

Performance of stored procedure varies

I have run into an issue that if I use the parameters passed into strored
procedure directly the performance is slower than when i declare local
variable and assign the values passed in the procedure to those local
variable and then use the local variables.
Why is this the case?
RahulRead this:
http://www.microsoft.com/technet/pr...comp.mspx#E6TAE
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message
news:%23RXsRNbuHHA.1168@.TK2MSFTNGP02.phx.gbl...
>I have run into an issue that if I use the parameters passed into strored
>procedure directly the performance is slower than when i declare local
>variable and assign the values passed in the procedure to those local
>variable and then use the local variables.
> Why is this the case?
> Rahul
>

Performance of stored procedure varies

I have run into an issue that if I use the parameters passed into strored
procedure directly the performance is slower than when i declare local
variable and assign the values passed in the procedure to those local
variable and then use the local variables.
Why is this the case?
Rahul
Read this:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx#E6TAE

Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message
news:%23RXsRNbuHHA.1168@.TK2MSFTNGP02.phx.gbl...
>I have run into an issue that if I use the parameters passed into strored
>procedure directly the performance is slower than when i declare local
>variable and assign the values passed in the procedure to those local
>variable and then use the local variables.
> Why is this the case?
> Rahul
>

Performance of stored procedure varies

I have run into an issue that if I use the parameters passed into strored
procedure directly the performance is slower than when i declare local
variable and assign the values passed in the procedure to those local
variable and then use the local variables.
Why is this the case?
RahulRead this:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx#E6TAE
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message
news:%23RXsRNbuHHA.1168@.TK2MSFTNGP02.phx.gbl...
>I have run into an issue that if I use the parameters passed into strored
>procedure directly the performance is slower than when i declare local
>variable and assign the values passed in the procedure to those local
>variable and then use the local variables.
> Why is this the case?
> Rahul
>

Performance of parameterized queries

I've come up with a query which allows me to perform fairly dynamic
queries depending on which parameters are set when a stored procedure
is called, but I'm not sure if what I'm doing is a performance no-no.
Here is an example:
CREATE PROCEDURE p_TradesGetForProcessing
@.tradeID int = null,
@.underlyingSymbol varchar(20) = null,
@.startTradeDate datetime = null,
@.endTradeDate datetime = null,
@.expirationDate datetime = null
AS
SELECT TradePL.TradeID, TradeID.OptionID,
FROM OptionTradeViewForSelectingTradesToProce
ss
WHERE (@.tradeID IS NULL or TradeID = @.tradeID)
and (@.underlyingSymbol IS NULL or UnderlyingSymbol = @.underlyingSymbol)
and (@.startTradeDate is NULL or TradeDay >= @.startTradeDate)
and (@.endTradeDate is NULL or TradeDay <= @.endTradeDate)
and (@.expirationDate is NULL or ExpirationDate >= @.expirationDate)
In essence, this allows me to pass in some or all of the filter
parameters. I was hoping that this would be fairly optimal in that the
optimizer has a change to compile the query. What I don't know is if
there is going to be a huge amount of overhead and I'm better off doing
it some other way.Whether some other method will be performant can be determined only by
testing the various methods. However, for a variety options, see:
http://www.sommarskog.se/dyn-search.html
Anith|||bpeikes (ben@.peikes.com) writes:
> I've come up with a query which allows me to perform fairly dynamic
> queries depending on which parameters are set when a stored procedure
> is called, but I'm not sure if what I'm doing is a performance no-no.
> Here is an example:
> CREATE PROCEDURE p_TradesGetForProcessing
> @.tradeID int = null,
> @.underlyingSymbol varchar(20) = null,
> @.startTradeDate datetime = null,
> @.endTradeDate datetime = null,
> @.expirationDate datetime = null
> AS
> SELECT TradePL.TradeID, TradeID.OptionID,
> FROM OptionTradeViewForSelectingTradesToProce
ss
> WHERE (@.tradeID IS NULL or TradeID = @.tradeID)
> and (@.underlyingSymbol IS NULL or UnderlyingSymbol = @.underlyingSymbol)
> and (@.startTradeDate is NULL or TradeDay >= @.startTradeDate)
> and (@.endTradeDate is NULL or TradeDay <= @.endTradeDate)
> and (@.expirationDate is NULL or ExpirationDate >= @.expirationDate)
> In essence, this allows me to pass in some or all of the filter
> parameters. I was hoping that this would be fairly optimal in that the
> optimizer has a change to compile the query. What I don't know is if
> there is going to be a huge amount of overhead and I'm better off doing
> it some other way.
In SQL 2000, the optimizer have no idea of the values, and will arrange
for a one-size-fits all plan. Which in this case is mostly like to
be a table scan, because of the >= conditions. Had you only had =
conditions and all columns had been indexed, it's possible that SQL
Server would pick index concatenation.
For SQL 2005 you can add the hint OPTION (RECOMPILE) to force statement
recompilation - SQL Server will then look at the actual values and use
them for the plan.
I have a longer article on the topic of dynamic searches on my web
site: http://www.sommarskog.se/dyn-search.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx|||bpeikes wrote:
> SELECT TradePL.TradeID, TradeID.OptionID,
> FROM OptionTradeViewForSelectingTradesToProce
ss
> WHERE (@.tradeID IS NULL or TradeID = @.tradeID)
> and (@.underlyingSymbol IS NULL or UnderlyingSymbol =
> @.underlyingSymbol) and (@.startTradeDate is NULL or TradeDay >=
> @.startTradeDate)
> and (@.endTradeDate is NULL or TradeDay <= @.endTradeDate)
> and (@.expirationDate is NULL or ExpirationDate >= @.expirationDate)
You're not likely to get index optimization using this method. The only
way to tell for sure is to check the execution plans for each version of
the query, but I'm fairly sure you'll see a lot of index scans (assuming
indexes are in place). What you might want to do is have this procedure
call other procedures depending on the parameters passed. I don't know
if you want to allow all NULL values passed in. If so, the query could
do away with the entire WHERE clause. Once you determine the valid
versions of the query, you can validate the parameters in the main proc
and then design the necessary indexes to support those queries.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi,
That is a performance no-no, you'll not get good if any index usage on that
query because of the IS NULL and OR stuff.
For search stuff, like below, you should look at using either a ton of IF
ELSE statements or preferably dynamic parameterised SQL...
Build the SQL only for the parameters that are passed, if tradeID is the
only parameter passed then do this...
SET @.nsql = '
select...
from ..
where TradeID = @.tradeID'
exec sp_executesql @.nsql,
N'@.tradeID int',
@.tradeID
The execution plan will be cached and is reusable so it isn't compiled each
time.
Make sure you use parameters as i've done rather than hard code values and
you won't have a problem with injection.
The one draw back is that you will need to permission access to the base
tables/views used in the dynamic SQL rather than execute permission on the
proc; you can get round this problem of security using application roles in
your application.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"bpeikes" <ben@.peikes.com> wrote in message
news:1131574058.424530.205360@.g43g2000cwa.googlegroups.com...
> I've come up with a query which allows me to perform fairly dynamic
> queries depending on which parameters are set when a stored procedure
> is called, but I'm not sure if what I'm doing is a performance no-no.
> Here is an example:
> CREATE PROCEDURE p_TradesGetForProcessing
> @.tradeID int = null,
> @.underlyingSymbol varchar(20) = null,
> @.startTradeDate datetime = null,
> @.endTradeDate datetime = null,
> @.expirationDate datetime = null
> AS
> SELECT TradePL.TradeID, TradeID.OptionID,
> FROM OptionTradeViewForSelectingTradesToProce
ss
> WHERE (@.tradeID IS NULL or TradeID = @.tradeID)
> and (@.underlyingSymbol IS NULL or UnderlyingSymbol = @.underlyingSymbol)
> and (@.startTradeDate is NULL or TradeDay >= @.startTradeDate)
> and (@.endTradeDate is NULL or TradeDay <= @.endTradeDate)
> and (@.expirationDate is NULL or ExpirationDate >= @.expirationDate)
> In essence, this allows me to pass in some or all of the filter
> parameters. I was hoping that this would be fairly optimal in that the
> optimizer has a change to compile the query. What I don't know is if
> there is going to be a huge amount of overhead and I'm better off doing
> it some other way.
>|||Tony Rogerson wrote:
> Hi,
> That is a performance no-no, you'll not get good if any index usage
> on that query because of the IS NULL and OR stuff.
> For search stuff, like below, you should look at using either a ton
> of IF ELSE statements or preferably dynamic parameterised SQL...
> Build the SQL only for the parameters that are passed, if tradeID is
> the only parameter passed then do this...
> SET @.nsql = '
> select...
> from ..
> where TradeID = @.tradeID'
> exec sp_executesql @.nsql,
> N'@.tradeID int',
> @.tradeID
>
To add to what Tony has described, you can use dynamic SQL and send in
all parameters, regardless of whether those parameters exist in the SQL
statement. Assuming your database can support dynamic SQL, you could use
something like the following even if the SQL statement only contains
@.Param1:
Exec sp_executesql @.nvcSql, N'@.Param1 INT, @.Param2 INT, @.Param3 INT',
@.Param1, @.Param2, @.Param3
David Gugick
Quest Software
www.imceda.com
www.quest.com