Sample Header Ad - 728x90

Is it parameter sniffing?

1 vote
1 answer
105 views
Application uses Entity Framework to create/run query. SELECT [t].[sbt_assetid], [t].[sbt_messagetimestampmst], [t].[sbt_transid], [t].[CurrentRow], [t].[etl_timestampUTC] , [t].[sbt_battery], [t].[sbt_cargostate], [t].[sbt_city], [t].[sbt_country], [t].[sbt_extpwr], [t].[sbt_geoname] , [t].[sbt_geotypename], [t].[sbt_heading], [t].[sbt_idleduration], [t].[sbt_idlegap], [t].[sbt_idlestatus], [t].[sbt_latitude] , [t].[sbt_longitude], [t].[sbt_messagetype], [t].[sbt_movementstate], [t].[sbt_mtsn], [t].[sbt_quality], [t].[sbt_skyfencestatus] , [t].[sbt_speed], [t].[sbt_state], [t].[sbt_tetherstate], [t].[sbt_tirestate] FROM [sb].[TrailerLocations] AS [t] WHERE ((((((((((((([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_0) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_1) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_2) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_3) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_4) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_5) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_6) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_7) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_8) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_9) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_10) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_11) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_12) AND ([t].[CurrentRow] = 1)) Typically runs < 3 seconds but several times a day exceeds 30 secs whereupon app times out (dev has said need sub 30 sec response times). Table has ~60 million rows. There are 7126 different "assets" that can be passed in as one of 12 parameters . PK Index on [sbt_assetid] ASC, [sbt_messagetimestampmst] ASC, [sbt_transid], ASC (maybe change sbt_messagetimestampmst to DESC?) NC Index on [sbt_assetid] ASC, [CurrentRow] DESC Stats updated automatically (last update yesterday). https://www.brentozar.com/pastetheplan/?id=SyjnVJcHA
Asked by Sergio Pacheco (13 rep)
Jun 14, 2024, 05:07 PM
Last activity: Jun 20, 2024, 01:46 PM