I have a query with corresponding indexes and is shown in the query plan with an estimated subtree cost of about 1.5. The plan shows an index search followed by Key Lookup - this is normal for a query that should return 1 row from a set of 5-20 rows (i.e., an index search should find 5 to 20 rows, and after 5-20 Key Lookups, we must return 1 line).
With an interactive start, the request returns almost immediately. However, DB keeps track of today's live-run displays (web applications), which vary widely; usually a query accepts <100 DB Reads and effectively 0 runtime ... but we get several runs that consume> 170,000 DB Reads and a runtime of up to 60 seconds (more than our timeout value).
What can explain this change on disk? I tried to compare queries online and use Actual Execution plans from two parallel runs with filter values taken from fast and slow runs, but in interactive mode they show no difference in the plan used.
I also tried to identify other queries that might block this, but I'm not sure it would affect reading the database so much ... and in any case, this query was the worst for runtime in my trace logs.
Update: Here is an example of a plan created by interactively querying a request:
Please ignore the text "missing index". It is true that changes in current indexes may allow a faster query with fewer searches, but this is not a problem (there are already corresponding indexes). This is the actual execution plan, where we see numbers such as "Actual Number of Rows." For example, in Index Search, the actual number of rows is 16, and the cost of I / O is 0.003. The cost of I / O is the same when searching for keys.
Update 2:. Trace results for this query:
exec sp_executesql N'select [...column list removed...] from ApplicationStatus where ApplicationGUID = @ApplicationGUID and ApplicationStatusCode = @ApplicationStatusCode;',N'@ApplicationGUID uniqueidentifier,@ApplicationStatusCode bigint',@ApplicationGUID='ECEC33BC-3984-4DA4-A445-C43639BF7853',@ApplicationStatusCode=10
The query is built using the Gentle.Framework SqlBuilder class, which constructs parameterized queries as follows:
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(ApplicationStatus)); sb.AddConstraint(Operator.Equals, "ApplicationGUID", guid); sb.AddConstraint(Operator.Equals, "ApplicationStatusCode", 10); SqlStatement stmt = sb.GetStatement(true); IList apps = ObjectFactory.GetCollection(typeof(ApplicationStatus), stmt.Execute());