Sql-server – Excessive compile blocking on sp_procedure_params_90_rowset

blockingsql-server-2008-r2

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>

Best Answer

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

Related Question