Procedure cache - recompile problem

Question

Post reply

Procedure cache - recompile problem

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

lehar SSC-Addicted Points: 411 More actions · Answer 1

The problem is just that Microsoft is reluctant to publish this.

We have to wait until they understands the need for official documentation.

Or that we get an official explanation of this behaviour.

Following is regarding NO_BROWSETABLE ON:

SET NO_BROWSETABLE ON is an undocumented option performed for Remote Data Service (RDS) ActiveX Data Connector (ADC) connections to SQL Server. Enabling this option makes every SELECT statement act as though FOR BROWSE had been appended to the statement, but bypasses the temporary table that FOR BROWSE normally pipes the results through. The net effect is to add keys and timestamps to the query as hidden output columns so the client can update specific rows (updateable cursors) without separate trips to the server to pick up the meta-data and munging the query to get the appropriate columns.

SET FORCEPLAN ON:

This is tested on the example from BOL, and then querying the syscacheobjects and the parse trees have different setopts.

No 2:

This is yet unknown. But we do not have any object without it and I can not get an object without it, so it must be central.

In SQL 7: The value without most settings was 0.

No 1024:

This also by a lot testing. As you have understood we use triggers far more than may be optimal, so this is not that difficult to identify. The procedures called from a single row trigger object also gets the 1024.

I will later let you all know what MS has for explanation.

Thank you for now.

Leif Haraldsson

lehar SSC-Addicted Points: 411 More actions · Answer 2

Just a follow-up to setopts & 2 = 2

USE master

EXEC sp_configure 'show advanced option', '1'

EXEC sp_configure 'max degree of parallelism','1'

reconfigure

This will lead to a setopts column of 0. IE setopts & 2 = 2 should mean MDOP != 1

Leif Haraldsson

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 3

Here is an interesting new technical article about recompilations:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

This article compares the SQL Server 2005 behaviour with the SQL Server 2000 behaviour. Here is an interesting quote about this subject:

"Special case 2: Trigger recompilations

All of the plan optimality-related reasons for recompilations are applicable to triggers. In addition, plan optimality-related recompilations for triggers can also happen because of the number of rows in the inserted or deleted tables changing significantly from one trigger execution to the next.

[...]

In SQL Server 2000, "sufficiently different" is defined by:

| log10(n+5) – log10(m+5) | >= 1

where n and m are defined as before. Notice that as per this formula, in SQL Server 2000, a change in cardinality of either inserted or deleted table from 5 to 95 will cause a recompilation, whereas a change from 5 to 94 will not."

In conclusion, Leif, it seems that there are more than two plans which share the same setopts. It's not only 2-4 and >4; it's 2-4, 5-94, 95-994, etc.

Razvan

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 4

Good news: Microsoft has issued a fix for this in SQL Server 2000 SP4 (actually, in 8.00.0954):

http://support.microsoft.com/kb/870972

You have to enable trace flag 9055 to activate the "threshold improvement".

Razvan