A resurgence of this questionon MSDN: Blocked-process-report: what is this waitresource "OBJECT: 32767:124607697:0 [COMPILE]"

I have caught these statements in Profiler. They all have durations over 3 seconds. Some over 10+. The blocking activity is the same as the link from MSDN.

The calls all use 3 part naming. All specify a different proc in form they look like the following:

exec [db1].[sys].sp_procedure_params_90_rowset N'proc1', 1, NULL, NULL
exec [db2].[sys].sp_procedure_params_90_rowset N'proc2', 1, NULL, NULL
exec [db3].[sys].sp_procedure_params_90_rowset N'proc3', 1, NULL, NULL
exec [db4].[sys].sp_procedure_params_90_rowset N'proc4', 1, NULL, NULL

What can I do to reduce this level of blocking?

(edit) I am now seeing the same thing for:

exec [db1].[sys].sp_primary_keys_rowset N'view1', N'dbo'
exec [db2].[sys].sp_primary_keys_rowset N'view1', N'dbo'
exec [db3].[sys].sp_primary_keys_rowset N'view1', N'dbo'
exec [db4].[sys].sp_primary_keys_rowset N'view1', N'dbo'

There is a systemic something going on but I don't know what else to do. the caller is VB6 via ADO. It is ADO making these calls.

An example blocked process report is below

            waitresource="OBJECT: 32767:124607697:0 [COMPILE]"
                    <sqltext>EXEC [dbo].[spAlertDetectByPoll] ':V:^RMAlert^:Z:^&amp;N&amp;#RMAlert#&amp;S&amp;#L#&amp;UID&amp;#19#&amp;AGN&amp;#1#&amp;DFC&amp;#103#^', 1</sqltext>
            waitresource="OBJECT: 32767:124607697:0 [COMPILE]"
                    sqlhandle="0x000000000000000000000000000000000000000000000000" />
  • SP2 CU4 Microsoft SQL Server 2008 R2 (SP2) - 10.50.4270.0 (X64)
    – dan holmes
    Commented Dec 16, 2013 at 19:02
  • When did this start happening? Did you recently apply the Service Pack or Cumulative Update? I'm also supporting VB6/ADO and I recall seeing these system procs come up once or twice, but I don't think there was a blocking issue. I think they came up because they are called so frequently. I pray this isn't SP/CU-related because we're still on 10.50.2500, and it would be death if these things started taking 3-10 seconds each.
    – Jon Seigel
    Commented Dec 17, 2013 at 2:27
  • it put one of the many into pastbin pastebin.com/4wUgzby9. this has been going on for about 2 or 3 weeks. We have not applied a CU in a long while. It happened in early 2012 the first time as dated by the MSDN post.
    – dan holmes
    Commented Dec 17, 2013 at 12:05
  • So the blocking-process is, itself, suspended waiting on the same thing. Is there a report where the blocking-process is not suspended? What is the head blocker doing? Also after reading cause of compile locks here can you trace whether the stored procedure is in fact continuously being (re)compiled and what the reason for that is? Commented Dec 17, 2013 at 15:08
  • 1
    this might be just the symptom. I have regular wait actvity on RESOURCE_SEMAPHORE_QUERY_COMPILE. Here is the best treatment of this waittype i have found: blogs.msdn.com/b/support_sql_france/archive/2012/02/07/…
    – dan holmes
    Commented Dec 17, 2013 at 20:50

1 Answer 1


There is an excellent blog post that explains what's happening.

SQL Server allows for a set number of compilations based on their complexity. It groups them into small, medium, and large. For large compilations, there can be onlyi one compiled at a time, so let's say all of your procs are considered large, then each one has to be compiled serially. That could account for the blocking.
I think there may be several approaches to the problem - consider more resources (more CPUs will allow more small and medium queries to be concurrent or may up the threshold for what is considered medium). Also, more memory may solve the problem.

If you're like most of us, that might not be possible. Another option might be to review the ADO calls and see if the number of calls can be reduced or spread out so that not all calls happen at the same time. Reducing the number at any given time should reduce your wait time.

If that doesn't work, consider fixing the 'compilability' of the stored procs. Maybe break them down into smaller chunks which might reduce them to the small or medium buckets and allow more parallel compilations. Or determine why the procs need to be recompiled each time. See if they can be rewritten such that they don't need to be recompiled. Finally, I'd consider using Plan Guides. These will allow the procs to be precompiled and may save some time.

Hope that helps

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.