SQL Server COMPILE locks?

Question

SQL Server 2000 here.

I'm trying to be an interim DBA, but don't know much about the mechanics of a database server, so I'm getting a little stuck. There's a client process that hits three views simultaneously. These three views query a remote server to pull back data.

What it looks like is that one of these queries will work, but the other two fail (client process says it times out, so I'm guessing a lock can do that). The querying process has a lock that sticks around until the SQL process is restarted (I got gutsy and tried to kill the spid once, but it wouldn't let go). Any queries to this database after the lock hang, and blame the first process for blocking it.

The process reports these locks... (apologies for the formatting, the preview functionality shows it as fully lined up).

spid    dbid  ObjId       IndId   Type    Resource        Mode    Status
53    17 0         0     DB                  S     GRANT
53    17 1445580188 0     TAB                  Sch-S GRANT
53    17 1445580188 0     TAB     [COMPILE]       X     GRANT

I can't analyze that too well. Object 1445580188 is sp_bindefault, a system stored procedure in master. What's it hanging on to an exclusive lock for?

View code, to protect the proprietary...I only changed the names (they stayed consistent with aliases and whatnot) and tried to keep everything else exactly the same.

    SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER view [dbo].[theView] 
as
select 
    a.[column1] column_1 
    ,b.[column2] column_2
    ,[column3]
    ,[column4]
    ,[column5]
    ,[column6]
    ,[column7]
    ,[column8]
    ,[column9]
    ,[column10]
    ,p.[column11]
    ,p.[column12]
FROM 
    [remoteServer].db1.dbo.[tableP] p
    join [remoteServer].db2.dbo.tableA a on p.id2 = a.id
    join [remoteServer].db2.dbo.tableB b on p.id = b.p_id
WHERE 
    isnumeric(b.code) = 1

GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

Answer 1

Take a look at this link. Are you sure it's views that are blocking and not stored procedures? To find out, run this query below with the ObjId from your table above. There are things that you can do to mitigate stored procedure re-compiles. The biggest thing is to avoid naming your stored procedures with an "sp_" prefix, see this article on page 10. Also avoid using if/else branches in the code, use where clauses with case statements instead. I hope this helps.

[Edit]:

I believe sp_binddefault/rule is used in conjunction with user defined types (UDT). Does your view make reference to any UDT's?

SELECT * FROM sys.Objects where object_id = 1445580188

Answer 2

Object 1445580188 is sp_bindefault in the master database, no? Also, it shows resource = "TAB" = table.

USE master
SELECT OBJECT_NAME(1445580188), OBJECT_ID('sp_bindefault')

USE mydb
SELECT OBJECT_NAME(1445580188)

If the 2nd query returns NULL, then the object is a work table.

I'm guessing it's a work table being generated to deal with the results locally. The JOIN will happen locally and all data must be pulled across.

Now, I can't shed light on the compile lock: the view should be compiled already. This is complicated by the remote server access and my experience of compile locks is all related to stored procs.