14

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

 <blocked-process-report>
    <blocked-process>
        <process
            id="process5bc1288"
            taskpriority="0"
            logused="0"
            waitresource="OBJECT: 32767:124607697:0 [COMPILE]"
            waittime="28887"
            ownerId="11638114050"
            transactionname="sqlsource_transform">
            <executionStack>
                <frame
                    line="1"
                    sqlhandle="0x000000000000000000000000000000000000000000000000">
                    <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>
                </frame>
            </executionStack>
            <inputbuf>
SET NO_BROWSETABLE OFF   </inputbuf>
        </process>
    </blocked-process>
    <blocking-process>
        <process
            status="suspended"
            waitresource="OBJECT: 32767:124607697:0 [COMPILE]"
            waittime="35693"
            spid="1121"
            sbid="0"
            ecid="0"
            priority="0"
            trancount="0"
            lastbatchstarted="2013-12-16T14:45:48.960">
            <executionStack>
                <frame
                    line="1"
                    sqlhandle="0x000000000000000000000000000000000000000000000000" />
            </executionStack>
            <inputbuf>
SET NO_BROWSETABLE OFF   </inputbuf>
        </process>
    </blocking-process>
</blocked-process-report>
  • Do you have the most recent service pack and cumulative updates installed for SQL Server 2008 R2? – Max Vernon Dec 16 '13 at 18:15
  • SP2 CU4 Microsoft SQL Server 2008 R2 (SP2) - 10.50.4270.0 (X64) – dan holmes Dec 16 '13 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 Dec 17 '13 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 Dec 17 '13 at 12:05
  • 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 Dec 17 '13 at 20:50
2

There is an excellent blog post http://blogs.msdn.com/b/support_sql_france/archive/2012/02/07/sql-server-compilation-gateways-and-resource-semaphore-query-compile.aspx 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, privacy policy and cookie policy

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