Solving high page load time using SQL Profiler

I'm looking into a performance of a login page taking 10-13 seconds to load. I'm using a DEMO database to test the performance and tweak to see any improvement. I'm using SQL Profiler to capture what is going on during the login and see what I can change. However, I'm noticing a few oddities.

• If I store to table the "duration" column in profiler is [n]*1000

compared to what is on the profiler GUI. (ie. GUI Windows shows
"exec proc1" and duration 50, but in the SQL trace table "exec
proc1" shows 50000.

• If I add up all the duration values they are

not even 1/10th of the time the page takes to load. (I know profiler
will slow down the page load, but even without profiler a "fresh"
page load is 10 seconds+ on average and profiler duration (ms) add
up to roughly 1.1 second.

• Also in between each run I am running CHECKPOINT, DBCC

DROPCLEANBUFFERS, DBCC FLUSHPROCINDB () to ensure each test is
"fresh".

Is there anything I'm doing wrong that would make these discrepancies occur? Is there anything you would recommend to change in my approach to solving this problem?

Edit: Following comments.

Obviously (as @gbn pointed out) the 1st call, following buffer and query plan flush, is going to be slower than subsequent calls. That said, there must be something wrong with either the queries or the IO capabilities of the server for the difference to be a factor of 10.

Can you add to your question the execution plans (XML), output from SET STATISTICS IO ON; SET STATISTICS TIME ON;, along with the database IO stats.

Original answer:

• Duration is recorded in microseconds but displayed in milliseconds in the GUI.

• Endless possibilities here. Most likely issues in the application code, rather than the database. The golden oldies, like string concatentation in ASP code, still popup with surprising regularity.

StackExchange Database Administrators Q#7859, answer score: 4