Sql-server – When exactly are multiple users unable to simultaneously run a stored procedure with a temp table

sql serverstored-procedurestemporary-tables

I have a question regarding a piece of documentation on Temp Tables that I recently read on TechNet. The fourth paragraph of the Temporary Tables section on that page reads as follows:

If a temporary table is created with a named constraint and the temporary table is created within the scope of a user-defined transaction, only one user at a time can execute the statement that creates the temp table. For example, if a stored procedure creates a temporary table with a named primary key constraint, the stored procedure cannot be executed simultaneously by multiple users.

I work in an environment where we make significant use of a handful of stored procedures that use indexed temp tables, and we've never encountered an issue where users are having to wait for one execution to complete before the next begins. I hope that will continue to be the case, but I'm concerned that it could become an issue if this caveat is not properly understood.

Specifically, I am unclear on the following points:

Does this apply only to global temp tables, or to local ones as
well? It seems strange that a table that isn't visible outside of
the session (as in the latter case) would prevent another session
from executing simultaneously.
What qualifies as a "named constraint"? Don't all constraints have names (even if they are system-generated)? Is this referring to constraints with a user-defined alias? This seems like poor phrasing to me.
Does "multiple users" actually mean multiple sessions? These procedures are called through our application using a single service account, so 99.9% of calls to our scripts are made to the DB by that single account (and I'm unconcerned about the occasional call an admin may make on the backend). If the service account can run the sproc in multiple sessions simultaneously, then this issue is moot for my purposes.

Best Answer

I think of it as you can't have any duplicate names in tempdb.sys.key_constraints. Here's what's in that metadata view on one of my servers:

All of the odd names that end with _6E... were names generated automatically by SQL Server. They are not named constraints because I did not explicitly give them a name when creating them. SQL Server generates a constraint name behind the scenes that in theory avoids name collisions.

If I try to create the following table in two different sessions:

create table #x1 (
ID INT NOT NULL,
CONSTRAINT NAMED_CONSTRAINT_1 PRIMARY KEY (ID)
);

The one that runs second throws an error:

Msg 2714, Level 16, State 5, Line 1

There is already an object named 'NAMED_CONSTRAINT_1' in the database.

Msg 1750, Level 16, State 1, Line 1

Could not create constraint or index. See previous errors.

Checking out the view again:

If I try to create the following table in two sessions there's no issue:

create table #y1 (
ID INT NOT NULL,
PRIMARY KEY (ID)
);

Here's the metadata view:

Just to answer your questions directly: the part that you quoted applies to both local and global temporary tables, a named constraint is one in which you deliberately give it a name, and multiple users means multiple sessions.

Related Solutions

Sql-server – sp_prepexec (sp_execute) vs. sp_executeSQL

Are actual stored procedures the only mechanism that implements temp table caching or do system stored procedures such as sp_executeSQL / sp_execute also take advantage of them?

You need a real stored procedure (CREATE PROCEDURE) to benefit from temporary table caching. This includes temporary stored procedures (#procname).

Point #3 on this blog post suggests that EXEC cannot use temp table caching, but leaves out whether sp_executeSQL can.

Notice that EXECUTE is used to run sp_executesql.

Testing: There are many ways to check whether caching is occurring. Some of them are listed in my original article referenced in the question, some more methods are shown in my follow-up post, Temporary Table Caching Explained, for example:

SELECT 
    DOMCC.name,
    DOMCC.pages_kb,
    DOMCC.pages_in_use_kb,
    DOMCC.entries_count,
    DOMCC.entries_in_use_count
FROM sys.dm_os_memory_cache_counters AS DOMCC
WHERE DOMCC.[type] = N'CACHESTORE_TEMPTABLES';

Stored procedure input TVPs are also cached, and starting with SQL Server 2012, these can also be cached when used with sp_executesql. See the linked CSS blog post for details.

Sql-server – Is it advisable to use a temp table when the stored procedure could be run simultaneously by different users

Yes, each user will get their own copy of the #temp table, even if they run at the exact same time.

(However, don't use global ##temp tables, signified with two leading pound/hash signs.)

But why do you need a #temp table here at all? Something like this should work (untested, as I don't have LDAP anywhere near me):

CREATE PROCEDURE dbo.stp_adlookup -- ALWAYS use schema prefix
  @user varchar(100),
  @objectGUID varbinary(256) OUTPUT
AS
BEGIN -- use body wrappers
  SET NOCOUNT ON;

  DECLARE @qry nvarchar(max); -- don't use CHAR for dynamic SQL

  SET @qry = N'SELECT @o = objectGUID
    FROM openquery(ADSI, ''SELECT  objectGUID              
      FROM    ''''LDAP://mydomaincontroller.com''''
      WHERE sAMAccountName = ''''' + @user + ''''''')';

  -- can probably parameterize the above, but those single
  -- quotes are a nightmare. Not sure if they're necessary
  -- but I do not feel like trying to untangle them.

  EXEC sys.sp_executesql @qry, N'@o UNIQUEIDENTIFIER', @o = @objectGUID OUTPUT;

  -- SET NOCOUNT OFF; -- don't do this.
END
GO

Best Answer

Related Solutions

Sql-server – sp_prepexec (sp_execute) vs. sp_executeSQL

Sql-server – Is it advisable to use a temp table when the stored procedure could be run simultaneously by different users

Related Question