Duration in SQL Profiler

Question

Duration in SQL Profiler

Rajesh Kumaran

SSC Eights!

Points: 971
More actions
July 4, 2005 at 3:38 am

#110568

I am running an application developed in .NET from a client, (3 tier environment) . I have set up an SQL profiler trace on the database Server to capture SQL statements and to get the duration of each SQL statements. I wanted to collect the SQL statements only for a particular application event that is called 'Generate Quote’. I have collected all the SQL statements( 197 statements) I wanted and checked the total duration , it has come to 1197 ms.

I copied all 197 SQL statements and pasted in SQL Query Analyser, which is on the same database server, and activated the SQL profiler with the same fashion as before. I took the total duration this time and now it is 2781 ms.

To my understanding , when running on a 3 tier architecture the network traffic would be more when compared to running the SQL statements on the same database server and hence duration should be longer. Why SQL profiler shows longer time even when the SQL statements got fired from the same DB server?

If anybody could throw some light on this would be very much apreciated.

Regards
Rajesh

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Maybe the statement didn't return the same amount of data or the data was cached for the 2nd execution. Maybe there was less network traffic. It could also be that the server was less busy or that their was no compilation to do. It's very hard to tell without having access to the server.

Rajesh Kumaran SSC Eights! Points: 971 More actions · Answer 2

Hi Remi,

I really appreciate the courtousy you have shown me to reply.

Well, to my knowwledge the dataset returns is exactly the same. If we are running the SQl statements on the database server using query analyser, there is no network traffic is invoved and hence the execution should be faster. But unfortunatly is just the opposite.

In order to make use of the cached plan, I created a stored procedure that contains all these 197 sql statments , and executed the SP . Still there isn't much difference in time. (it is 2591 ms)

I would like to get the botton of it with out opening up a case with microsoft. ( I have only 1 free call left).

Cheers

Rajesh

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

You didn't rule out the cached data and the compiled plans. When doing performance testing you must always ignore the first 1-3 calls to make sure that the data is cached into memory between each test, or you flush the data from cache and then each test is run reading from disk.

Anyways it's gonna be really hard to compare performance between the real life cases and the current cases you get because the environement will never be totally the same.

Rajesh Kumaran SSC Eights! Points: 971 More actions · Answer 4

Well, the question I have posted was based on a test that I had done not less than 10 times. In the sense considering the data cache and execution plans of all the quires.

As a different stategy I tried out the test by clearing the buffer cache before each run from application and QA.

Please find the test result.

If running from application it is 1908 ms

if running from QA it is 3269 ms .

Here the difference is not as wide as before however , regardless of the stategy I opt, according to SQL profiler duration ,running the quires from QA is taking longer.

I am bound believe the duration that takes while executing from QA would be more genuine since STATISTICS TIME also shows more or less the same time. I would have been quite happy if duration is longer if runnning from the applcation ( network latency ).

To be honest , it confuses me !!!!!!!!!!!!!!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

The real question here is... is it fast enough rom the application side to satisfy the needs of the users. If not then you have a problem, otherwise I would just drop this .

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 6

Are you returning the results to a grid or text in Query Analyzer?

The grid takes a lot longer to poulate, and uses a lot more cpu on the client than returning the results in text.

Rajesh Kumaran SSC Eights! Points: 971 More actions · Answer 7

Thank you very much for the responses. Well, I have realised that when running from QA execution plans are not getting cached effectivly as it does when runnning from application.

I have checked the syscacheobject table to verify this and I am happy with this conclusion.

Thanks Remi and Ian.

Cheers

Rajesh