Covering index causes blocking on exclusive latch acquisition in tempdb?

From: Kevin Stark (ktstark.REMOVE_at_sigma-micro.THESE.com)
Date: 08/13/04 

Date: Fri, 13 Aug 2004 11:37:19 -0500

Yes, this problem is as bizarre as the subject line. Let's see if I can
explain it.

System: SQL 7.0 Enterprise Edition SP4. Windows 2000 Advanced Server.
Running in shared disk cluster.

We have around two dozen databases with the same schema. There are two
tables in question: multi_val_attr and variant. These two table definitions
(with their index definitions) are included below. Each of these tables
contains between 10K and 50K records each. These two tables are used in a
stored procedure that makes a simple join between them and returns a result
set. The stored procedure definition is below as well.

Now, as the usage activity is nearly 100% read activity, the thought was
that introducing non-clustered covering indexes on each of these tables
would eliminate some of the bookmark lookups happening on them that were
dragging down performance a bit. When these two indexes were introduced on
these tables, we started noticing blocking taking place in the database. In
analyzing the output of the sysprocesses table, all of the queries in
question (up to ten at a time) were all waiting to issue an exclusive page
latch (waittype 0x411) on the exact same page in tempdb (page id 2:1:24262).
The queries were even accessing different databases in the blocking chain,
but still attempting to issue exclusive page latches on the same page in
tempdb. Seeing as how none of these queries are creating anything in tempdb
nor are they using intermediate tables, we found this extraordinarily odd.
Dropping these new indexes from all of the databases eliminated the problem.

Running a dbcc page (2,1,24262, 1) didn't turn up anything in the data
portion, though I'm not sure exactly what to look for in the statistics of
that command. Is there something specific that should be obvious here? Are
there any trace flags that may show us more information?

The tables:

CREATE TABLE [dbo].[multi_val_attr] (
 [pf_id] [varchar] (100) NOT NULL ,
 [mva_name] [varchar] (100) NOT NULL ,
 [mva_value] [varchar] (100) NOT NULL ,
 [mva_index] [int] NULL ,
 [mva_equiv] [char] (5) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[variant] (
 [sku] [varchar] (100) NOT NULL ,
 [pf_id] [varchar] (100) NOT NULL ,
 [attr_value1] [varchar] (100) NULL ,
 [attr_value2] [varchar] (100) NULL ,
 [attr_value3] [varchar] (100) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[multi_val_attr] WITH NOCHECK ADD
 CONSTRAINT [PK_multi_val_attr] PRIMARY KEY NONCLUSTERED
 (
  [pf_id],
  [mva_name],
  [mva_value]
 ) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[variant] WITH NOCHECK ADD
 CONSTRAINT [PK_variant] PRIMARY KEY NONCLUSTERED
 (
  [sku]
 ) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE INDEX [variant0] ON [dbo].[variant]([sku], [pf_id]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

The stored proc:

CREATE Procedure GetStyleMaster
 @product_number varchar(20) = NULL,
 @attr varchar(100) = NULL
As
/*
 Name: GetStyleMaster
 Purpose: Retrieves the attribute list for the passed product and
attribute.
 Returns: (= -101) if the selected attribute does not exist.

*/

 SELECT DISTINCT mva.mva_value, mva.mva_index, mva.mva_equiv
  FROM multi_val_attr mva, variant v
  WHERE
 mva.mva_name = @attr
 AND
 mva.pf_id = v.pf_id
 AND
 (
  (
   v.sku = @product_number
  )
  OR
  (
   mva.pf_id = @product_number
  )
 )
  ORDER BY mva.mva_index
if (@@rowcount = 0)
 return(-101)
 /* Success if we get here */
 return (0)

The covering indexes:

CREATE
  INDEX [IX_variant_covered] ON [dbo].[variant] ([sku], [pf_id],
[attr_value1], [attr_value2], [attr_value3])

CREATE
  INDEX [IX_multi_val_attr_covered] ON [dbo].[multi_val_attr] ([pf_id],
[mva_name], [mva_value], [mva_index], [mva_equiv])

Relevant Pages

  • Re: The best elegant solution to override 65k rows limit in a sheet
    ... All math may reduce to arithmetic when calculating cardinal and ordinal ... The calculations take place in the same CPU for Excel and Access, ... The problem with databases is the relative inflexibility of tables as ... data structures and the awkwardness of queries as the only referencing ...
    (microsoft.public.excel)
  • Re: Running queries in different DBs
    ... the other databases. ... couple of existing Access DB's with their own queries and tables. ... is execute these queries & copy some reports in the ... Now I'm trying to launch this code from my new Access ...
    (microsoft.public.access.modulesdaovba)
  • Re: Stored procedure
    ... the stored procedure. ... to use in ISAM databases (yes... ... fields are not used in SQL Server except in rare situations. ... > @Param3 money, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Vlookup nightmare
    ... most order-dependent calculations, I'd already have gotten the anser. ... writing the nontrivial queries needed to produce the same result. ... Pivot tables are no use whatsoever for amortization tables, ... As long as training budgets are minimal, databases are going ...
    (microsoft.public.excel)
  • Re: The best elegant solution to override 65k rows limit in a sheet
    ... MDB is friggin dead.. ... SQL Server has taken over the world. ... than read-only access to central company databases. ... I learned to write queries in Access after an hours' ...
    (microsoft.public.excel)