SQL 2000 SP4 behavior change when using API cursors to be aware of.

A few customers and ISVs who’ve applied SQL Server 2000 Service Pack 4 (SP4) have observed degradation in performance. While there could be many causes that may result in performance degradation, there is one change introduced in SP4 which has been particularly common in resulting in such issues. Below is an explanation of this problem and the recommended fix.

When using SQL Server API cursors (client-side cursors) all queries are required to qualify the data-types and precision of parameter values passed in to the query. These data types need to match the data-types of the table columns to which the parameter will be compared. In cases where the passed in data-type does not match the underlying data-type of the table column SQL Server tries to implicitly convert data types for comparison and assignment operations based on the implicit conversion rules specified in the matrix: (https://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp).

Implicit conversions, while a powerful feature can sometimes result in performance problems because they may exclude the use of an existing index and require the database engine to do a full-scan or range-scan of the table in order to read each column value, convert it and then be able to accurately compare it to the parameter value.

For example the following query passes in a parameter (@P3) as a decimal(38,2) and compares it to an underlying table column (col1) which is defined as SMALLINT in the table schema.

declare @P1 int

set @P1=-1

exec sp_prepexec @P1 output, N'@P1 decimal(38,2),@P2 varchar(508), @P3 decimal(38,2) ,@P4 varchar(508),@P5 decimal(38,0),@P6 varchar(508),@P7 varchar(508),@P8 varchar(508),@P9 decimal(38,0),@P10 decimal(38,0),@P11 varchar(508),@P12 varchar(508),@P13 varchar(508),@P14 varchar(508),@P15 varchar(508),@P16 varchar(508),@P17 varchar(508),@P18 varchar(508),@P19 varchar(508)', N'

UPDATE TabA SET col1 = ‘X’

WHERE

col1 = ‘Y’

AND col2 = @P2

AND col3 = @P3

AND col4 = @P4

AND col5 = @P5

AND col6 = @P6

AND col7 = @P7

AND col8 = @P8

AND col9 = @P9

AND col10 = @P10

AND col11 = @P11

AND col12 = @P12

AND col13 = @P13

AND col14 = @P14

AND col15 = @P15

AND col16 = @P16

AND col17 = @P17

AND col18 = @P18

AND col19 = @P19’,

0.00, 'MDX2312', 0, '000025729', 0, '10 MDX', 'SCANT 2006MDX', 'MDX2312', 1, 101043, 'VICE', '10034', 'SHARP', '10034', '10', '10', 'MDX2312', ' ', '480647C'

In this case TabA has an index (idx1) on columns (col1, col4, col3, col6) which would normally result in a index seek operation for the above query. However because of the mismatched data-type for the @P3 parameter, each value of col2 has to be read from the table column before it can be compared to the passed in parameter value. This limits the use of the idx1 index to seek only on the first two columns of the composite index and does a range col3 on col6 which results in degraded performance. The problem is particularly acute if TabA is large, or when the first two columns (col1 and col4) are not very selective.

If col3 is correctly passed in as a smallint, all 4 columns of the idx1 index would be used for the seek operation thereby completely eliminating need to do a full or range scan through the table.

Recommended Workaround:

This is an application coding error and not a SQL Server bug. There are three possible ways to resolve this problem:

(1) The best solution is of course to ensure that all parameter values always match the corresponding column data types identically. In this case the above example query would have col2 correctly passed in as a smallint and not a decimal.

(2) If this is not possible, an alternative solution is to change the order of the index columns such that the column which has the incorrect data type passed in would be the last, or close-to-last, column in the composite index. While still sub-optimal this will help limit the range scan operator. Using this fix in the above example, the index idx1 would be altered to consist of columns (col1, col4, col6, col3).

(3) If started experiencing this performance problem in a more acute way after installing SP4 you can use trace flag –T9059 to revert to the SP3 behavior. Please note that the behavior in SP3 and earlier can result in incorrect results as explained in the KBA on “FIX: A query that involves data that is the numeric data type may return incorrect results in SQL Server 2000 SP3 and in earlier SQL Server 2000 service packs” (https://support.microsoft.com/kb/899976/en-us).