SQL Server Performance

What is the best?

Discussion in 'T-SQL Performance Tuning for Developers' started by brafols, Feb 18, 2009.

  1. brafols New Member

    Hi!!
    I have tried to improve a query and restults in the old and new are clearly different but can not decide which one offers better performance.
    SQL Server Execution time is better in the new query but elapsed time is worse.
    Logical reads are similar in both queries.
    ¿Can you help me?
    This is the result of the old query:

    SQL Server parse and compile time:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    (1 row(s) affected)
    Table 'dtUsuariosPorEventoGrupo'. Scan count 2, logical reads 8889, physical reads 0, read-ahead reads 0.
    Table 'dtUsuariosPorGrupoUsuarios'. Scan count 160, logical reads 480, physical reads 0, read-ahead reads 0.
    Table 'dtGruposUsuarios'. Scan count 320, logical reads 966, physical reads 0, read-ahead reads 0.
    Table 'dtServiciosGrupoPorGrupoUsuarios'. Scan count 160, logical reads 480, physical reads 0, read-ahead reads 0.
    Table 'dtUsuariosPorEventoPersonal'. Scan count 1, logical reads 103, physical reads 0, read-ahead reads 0.
    Table 'dtRolesPorEvento'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
    Table 'dtEventos'. Scan count 1, logical reads 2633, physical reads 0, read-ahead reads 0.
    Table 'msTiposEventos'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
    Table 'dtEventosPeriodicos'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
    SQL Server Execution Times:
    CPU time = 1734 ms, elapsed time = 2856 ms.
    And this is of the new:
    SQL Server parse and compile time:
    CPU time = 71 ms, elapsed time = 71 ms.
    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    (1 row(s) affected)
    Table 'dtEventos'. Scan count 12, logical reads 7893, physical reads 0, read-ahead reads 16.
    Table 'dtEventosPeriodicos'. Scan count 4, logical reads 141, physical reads 0, read-ahead reads 0.
    Table 'dtUsuariosPorEventoPersonal'. Scan count 4, logical reads 103, physical reads 0, read-ahead reads 0.
    Table 'dtGruposUsuarios'. Scan count 4, logical reads 799, physical reads 0, read-ahead reads 0.
    Table 'dtUsuariosPorEventoGrupo'. Scan count 4, logical reads 4471, physical reads 0, read-ahead reads 0.
    SQL Server Execution Times:
    CPU time = 296 ms, elapsed time = 4138 ms.
    Thank you!!
  2. Adriaan New Member

    The data is cached after the first query you execute, and the execution plan may be reusable. So whichever of the two queries is executed second will be faster.
    In a test environment (never in production) you can put two commands before each query to get a clean slate:
    DBCC DROPCLEANBUFFERS
    DBCC FREEPROCCACHE
    You'll need sysadmin privileges to issue those DBCC commands.
  3. satya Moderator

    There is not hardcoded rule that logical or physical read is better for performance, in general SQL optimizer itself will decide and go as per the statistics that are updated for the tables that are involved in the query.
    Execution plan is generated by optimizer, one of components in SQL Server database engine; it contains how to access data to satisfy the query request. You probably meet few queries having slow response time by looking into physical read and logical read.
    During a meeting with Kalen Delaney referred that:
    Logical read indicates total number of data pages needed to be accessed from data cache to process query.
    Physical read indicates total number of data pages that are read from disk. In case no data in data cache, the physical read will be equal to number of logical read.
    So in this regard you have to look for the number of records that are returned based on the query used will have to optimized with better indexes.
  4. brafols New Member

    Thanks to both!
    But my question refers mainly to the difference between cpu time and elapsed time of both queries.
    In the first query, the CPU time is greater than the second, but the elapsed time is lower, always. And all executions of the queries is the same.
    I do not understand why this happens.
    Anyway, thanks to both!

Share This Page