Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

  1. 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.
  2. 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.
  3. 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?

share|improve this question
add comment

2 Answers 2

When you run DBCC DROPCLEANBUFFERS, DBCC FLUSHPROCINDB you require that the data is loaded into RAM and all plans are recompiled.

In a live system, you'd only this double whammy on startup or after heavy maintenance that evicted all data from cache and all plans were invalidated by a statistics update.

Your typical response time would be for the 2nd and subsequent calls.

On a really "cold" system your app in IIS would be unloaded too and would incur a CLR compile overhead

share|improve this answer
    
Right. So if the login is slow for Jim, I login as Jim and it creates a plan in the cache so any immediate future logins are speedy. However, Jon logs in and it's slow on the first login. So that plan is only specific to each user of each db? So since our instances have 100's of databases with up to 1000's of users per database, that seems poor odds that you will have a plan for you to speed up your login. Does this make sense? –  David George Nov 14 '11 at 17:00
    
@David George: yes. If you have 100's of databases then it is likely that your data won't live long in memory. You could maybe find this out from Page Life Expectancy –  gbn Nov 14 '11 at 17:01
    
Yeah the strange part is I generally see high PLE on this instance: SQL1\a Page life expectancy 6438 SQL1\b Page life expectancy 168797 SQL2\a Page life expectancy 13228 SQL2\b Page life expectancy 89557 –  David George Nov 14 '11 at 19:03
add comment

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:

  1. Duration is recorded in microseconds but displayed in milliseconds in the GUI.
  2. 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.
share|improve this answer
    
I really felt like it could be a code issue as well, but I didn't have an answer for why it takes 10+ seconds to load after I run my SQL commands ("DBCC FLUSHPROCINDB (id)", etc.) but subsequent loads for that user take 1-2 seconds. Nothing changes on the "code" side is the argument I get. Like "you're clearing the SQL cache and it takes longer, so obviously the problem lies in SQL". –  David George Nov 14 '11 at 15:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.