Usually querying the table is in the millisecond range at most. But sometimes it takes 30 seconds and then times out.

What could cause SELECT smallColumn FROM SomeTable WHERE pkID=@pkID to be slow?

If I had to guess, I'd say the query is being prevented from being run because another query is altering the table in some way, but my knowledge of locking is severely limited. Or it could be possible, maybe?, that another transaction is holding the table even though the table isn't really being used anymore for that transaction.

I can't use a SQL Profiler. Is there some list of things I can systematically go through to check?

Thanks in advance.

  • 3
    Blocking .. download sp_whoisactive and ask your dba to give you execute permission. – Kin Shah Jul 7 '16 at 22:15
  • @Kin: thanks but I think that the slowness happens infrequently enough that it would be difficult to execute that query in time. Maybe there's a way to set it up to automatically run anytime the query takes more than a few seconds, but I guess my goal was to understand what could cause blocking. (That's the term I was looking for :-)) – user420667 Jul 8 '16 at 0:22
  • 2
    @user420667 you can log it to a table in a loop so you don't have to worry about catching it when it's slow. brentozar.com/responder/log-sp_whoisactive-to-a-table – Erik Darling Jul 8 '16 at 0:39
  • 2
    You could also configure the blocked process threshold and run a server side trace that logs blocking longer than the threshold. – Martin Smith Jul 8 '16 at 8:57
  • 1
    Primary key is not necessarily the clustered index, but it's probably a blocking issue. – Jonathan Fite Feb 13 '17 at 20:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.