0

I have a weired problem.

I have a SQL Server database that i query from a ASP.NET website using Linq. I have a few queries that from time to time takes so long that they time out.

The weired part is that the exact same query can 10 minutes later execute in less than 100 ms. I have traced the problem with SQL Server Profiler and found that some times the query takes very long time (> 30 sec.) and minutes later takes no time.

Any one know what to look for?

This is one of the queries with the problem, it has been created by Linq:

    exec sp_executesql N'SELECT COUNT(*) AS [value]
FROM (
    SELECT DISTINCT [t0].[Id], [t0].[Title]
    FROM [dbo].[ListProduct] AS [t0]
    LEFT OUTER JOIN [dbo].[CategoryExtension] AS [t1] ON [t0].[Id] = [t1].[ProductId]
    WHERE ([t0].[CategoryId] = @p0) OR ([t1].[CategoryId] = @p1)
    ) AS [t2]',N'@p0 int,@p1 int',@p0=1,@p1=1
Axel Andersen
  • 330
  • 4
  • 14
  • Please add high level information about what the queries are doing, what other operations maybe interacting with the tables being queried. First suggestion would be to identify any locks, see here: https://stackoverflow.com/questions/694581/how-to-check-which-locks-are-held-on-a-table – Tanner Dec 12 '17 at 10:53
  • One of the things said in that post is to figure out if there are any locks. If I run this query: EXEC sp_lock; it will not execute until my query has thrown a timeout, and then all locks are "Granted". It seems that the SQL Server it self is hanging when running these queries – Axel Andersen Dec 12 '17 at 11:43
  • Just updated my original question with a query – Axel Andersen Dec 12 '17 at 11:46

0 Answers0