3

I'm running these two queries in SQL Server 2012:

SELECT tpua.Field1,
    CAST(round(twg.Field2 * 2, 0) / 2 As Decimal(8,1)) As Field2, 
    CAST((round((twg.Field3/10.0) * 2, 0) / 2) * 10 As int) As Field3,
    Count(*) As Count 
FROM LargeTable1 twg, SmallTable tpua 
WHERE tpua.JoinField1 = twg.JoinField1 AND tpua.JoinField2 = twg.JoinField2 
    AND tpua.Field1 = 'whatever' and twg.TimeStamp >= '2015-01-01T00:00:00' AND twg.TimeStamp < '2015-04-01T00:00:00'
GROUP BY tpua.Field1,
    round(twg.Field2 * 2, 0) / 2,
    (round((twg.Field3/10.0) * 2, 0) / 2) * 10
SELECT tpua.Field1,
    CAST(round(twg.Field2 * 2, 0) / 2 As Decimal(8,1)) As Field2, 
    CAST((round((twg.Field3/10.0) * 2, 0) / 2) * 10 As int) As Field3,
    Count(*) As Count 
FROM LargeTable2 twg, SmallTable tpua 
WHERE tpua.JoinField1 = twg.JoinField1 AND tpua.JoinField2 = twg.JoinField2 
    AND tpua.Field1 = 'whatever' and twg.TimeStamp >= '2015-01-01T00:00:00' AND twg.TimeStamp < '2015-04-01T00:00:00'
GROUP BY tpua.Field1,
    round(twg.Field2 * 2, 0) / 2,
    (round((twg.Field3/10.0) * 2, 0) / 2) * 10
--OPTION (MAXDOP 1)

As you can see they are identical except from their table names. Both tables are also identical (same fields, same keys, same indexes, etc...), except for the amount of data they contain: LargeTable1 contains 7,4 million rows while LargeTable2 contains 5,7. The joined table, the small one, contains around a thousand rows.

The query statistics and actual query plans are the following:

 Tiempos de ejecución de SQL Server:
   Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.
Tiempo de análisis y compilación de SQL Server: 
   Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.

(1456 filas afectadas)
Tabla 'Worktable'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.
Tabla 'LargeTable1'. Recuento de exámenes 1, lecturas lógicas 1329, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.
Tabla 'SmallTable'. Recuento de exámenes 1, lecturas lógicas 7, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

(1 filas afectadas)

 Tiempos de ejecución de SQL Server:
   Tiempo de CPU = 32 ms, tiempo transcurrido = 20 ms.

(585 filas afectadas)
Tabla 'Smalltable'. Recuento de exámenes 5, lecturas lógicas 16, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.
Tabla 'Worktable'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.
Tabla 'Worktable'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.
Tabla 'LargeTable2'. Recuento de exámenes 1, lecturas lógicas 12616, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

(1 filas afectadas)

 Tiempos de ejecución de SQL Server:
   Tiempo de CPU = 31 ms, tiempo transcurrido = 42 ms.
Tiempo de análisis y compilación de SQL Server: 
   Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.

Query 1 - Better performance

Query 1 - Better performance

Query 2 - Poor performance

Query 2 - Poor performance

At the beginning I thought the problem could be something related to parallelism, since that's the operation that differs from one query plan to the other. Due to that I tried with the hint (commented) OPTION (MAXDOP 1), but the statistics result was exactly the same.

Although I know that none of these specific queries are really hurting the server's performance I'm more worried on other queries that could come on LargeTable2, as I've already seen from time to time, but I never stopped myself to analyze what was happening. Now I need to fix this cause I intent to widen the date periods of my queries.

Does anybody have an idea what's happening here?

  • First query pulls out around 1500 rows, while second one around 600.
  • The daily frequency of these queries is quite low, around 10 times or so per day. The point is that this same bad performance from the second query is appearing as well in other queries that gather data from LargeTable2. And some of these are hurting performance a lot more (like taking around 1 minute to run....totally unacceptable).
  • Fragmentation % is 27,86 for LargeTable1 cluster index and 29,33 for LargeTable2. I've rebuilt both indexes causing fragmentation to drop to 1 %, but result in the queries are exactly the same in terms of logical reads.
  • Did a statistics update with fullscan but retrieved stats keep the same.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.