mercredi 25 mars 2015

Performance differs on same query against two (almost) identical tables?


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 parallellism, 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?


EDIT 1:



  • 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.





Aucun commentaire:

Enregistrer un commentaire