SQL Server slow query

Question

我有一个查询是比较慢的，我不明白为什么。我想，表上的索引是正确的。

SELECT
e.id, e.coordinate, e.lat AS latitudine, e.lon AS longitudine, e.dataora, e.indirizzo, e.dato, e.precisione_metri, e.precisione_secondi, e.precisione_invalid, e.distanza, e.velocita, es.descrizione AS evento, es.operazione, es.colore_shape, e.dato barcode, es.gestione_euristica, e.id_dispositivo  
FROM eventi_kml_polygon AS ekp
INNER JOIN eventi AS e
ON e.id=ekp.id_evento
INNER JOIN sistema_eventi AS es
ON es.evento=e.id_evento
INNER JOIN kml_polygon AS kp
ON kp.id=ekp.id_kml_polygon
INNER JOIN kml AS k
ON k.id=kp.id_kml
INNER JOIN waypoint AS w
ON w.id_kml=k.id
INNER JOIN waypoint_periodi AS wp
ON (wp.id_waypoint=w.id AND e.dataora BETWEEN wp.dataora_inizio AND wp.dataora_fine)
INNER JOIN modelli AS m
ON m.id=wp.id_modello
WHERE m.id=224882
AND es.operazione IN (8,15)

执行计划没有提出任何索引建议。受影响的巨表上的记录为:

• eventi: 12250946
• waypoint_periodi: 650703
• eventi_kml_polygon: 1500040
• kml_polygon: 21870
• kml: 9246

这是执行计划:执行计划brentozar

请问谁能帮我?我试着分析索引，表等。但是我没有找到解决办法。我想象有一个索引可以帮助我的查询

Answer 1

我很好奇…试着将它引入查询设计器(CTRL-SHIFT-Q)，然后单击要返回的列，看看它是否运行。

SELECT        *
FROM            (SELECT        TOP (2147483647) *
FROM            eventi_kml_polygon AS ekp INNER JOIN
eventi AS e ON e.id = ekp.id_evento INNER JOIN
sistema_eventi AS es ON es.evento = e.id_evento INNER JOIN
waypoint_periodi AS wp ON e.dataora BETWEEN wp.dataora_inizio AND wp.dataora_fine INNER JOIN
modelli AS m ON m.id = wp.id_modello
WHERE        (m.id = 224882) AND (es.operazione IN (8, 15))) AS derivedtbl_1 INNER JOIN
kml AS k INNER JOIN
kml_polygon AS kp ON k.id = kp.id_kml INNER JOIN
waypoint AS w ON w.id_kml = k.id ON derivedtbl_1.id_kml_polygon = kp.id AND derivedtbl_1.id_waypoint = w.id

Answer 2

我认为最大的问题是由于ix_eventi没有覆盖而导致的键查找。尝试修改该索引，使其包含被引用的列，如lat、lon、dateora等。当然，您需要确定这是否会妨碍服务器上的其他查询。