SQL Server Performance

How should I interpret these IO numbers?

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by jamiet, Nov 18, 2009.

  1. jamiet New Member

    Hello folks,
    Perf tuning isn't my strong point so I'm looking for any pointers going. I have a query that is taking a couple of seconds to run on serverA yet over a minute on serverB. The underlying data is the same on both schemas, as are the tables & indexes.
    Here's the output from SET STATISTICS IO ON. First, serverA:Table 'Journal'. Scan count 9, logical reads 388397, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    And now serverB:Table 'Journal'. Scan count 9, logical reads 381755, physical reads 5639, read-ahead reads 372576, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    As you can see physical reads and read-aheads are different!
    Note also, if I execute:DBCC
    DROPCLEANBUFFERS
    on serverA prior to running the query then the query takes abotu a minute yet on subsequent executions its back down to a couple of seconds.
    I am assuming that the data cache is being populated on serverA but not on serverB, and this is where I draw a blank. Why might this be? Like I say, any pointers greatly appreciated.
    Now going off to search for info on the data cache, but thought I'd ask here first in case anyone can help....
    -Jamie
    More info:
    I've queried sys.dm_os_sys_info and discovered that serverA has bpool_commit_target=1036288, on serverB bpool_commit_target=524288. Not sure if this is significant or not. physical_memory_in_bytes and virtual_memory_in_bytes are the same on both servers.
  2. michaellutz New Member

    yeah I agree that's wierd. If my calculations are correct, the # of logical reads equates to about 3 MB of data. I was going to suggest that you check the memory settings on each instance of SQL Server to see if they are different, but since this is only 3 MB of data that should fit regardless of the settings. You might want to take a look though.
    Couple questions for you.
    On server A you get noticeable performance difference after running DBCC dropcleanbuffers. Are you saying that ServerB is slow (1 minute execution) regardless of whether or not your first run DBCC dropcleanbuffers?
    What is the activity on ServerA compared to ServerB? In other words, are a lot of users using ServerB but is ServerA mostly sitting idle?
    How long is the intervel between each invocation on ServerB?
  3. jamiet New Member

    Hi Michael,
    Thanks for the reply.
    How did you come by the figure of 3MB? I'm sure you're right but I calculate it as more than that. i.e.:
    number-of-logical-reads * number-of-bytes-per-page
    388397 * (8*1024) = 3181748224 bytes = 3107176KB = 3034MB
    Yes/No? Like I say, I'm sure I'm wrong but can you tell me why?
    Answers to your questions:
    1. Yes, serverB is slow regardless of whether I empty the data cache
    2. Very low activity on both. These are not production boxes, they are dev boxes
    3. Instantaneous (just me hitting F5 continually in SSMS)
    Last bit of info (which I suspect is very important here):
    • serverA has 4096MB <= server-memory <= 8096MB
    • serverB has 0MB <= server-memory <= 4096MB

    cheers
    Jamie
  4. michaellutz New Member

    yes sorry, on calculation my math was off. I agree, 3 GB.
    Based on the information you sent, I think the available memory is the differentiator here. You can use Perfmon (SQL Server Memory Manager : Target Server Memory and Actual Server Memory) to validate our theory on both machines. I suspect that's the issue here. Don't just look at min and max memory, depending on a variety of other settings and OS version SQL Server may not use up to the max. Setting memory is a whole topic in itself.

Share This Page