We have sporadic poor query performance of the same query with just a different parameter and we do not know why this is happening.
Maybe someone has an idea how this can happen.
We are running the same query thousands of times a day and somehow a few times the query runs 400ms instead of 1ms.
I created a trace-file to get some information which exact query is causing the problems and found this in the trace log:
exec sp_executesql
N'SELECT A.*
FROM Teile A
WHERE (A.Barcode = @P1)
ORDER BY A.Barcode ASC, A.ID ASC
OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY',
N'@P1 char(26)','1022669459 '
This query starts at 15:24:03.250 and stops at 15:24:03.650 => duration 400ms.
During the execution the query
exec sp_executesql
N'SELECT A.*
FROM Teile A
WHERE (A.Barcode = @P1)
ORDER BY A.Barcode ASC, A.ID ASC
OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY',
N'@P1 char(26)','1015092284 '
starts at 15:24:03.450 and stops at 15:24:03.451 => duration 1ms.
Has anybody an idea why the same query with just a different parameter is executing very slow and another as the average in 1ms?
I tried the first query in the smss but there I always get a execution time of 1ms, too. So it seems to me the server sometimes needs more time to execute the query, but I do not know why and have no idea to found out why.
Every help is greatly appreciated.
Thanks, Markus
Aucun commentaire:
Enregistrer un commentaire