Locking tempdb - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Locking tempdb

Author

Topic

PreethiSRaj
Starting Member

4 Posts

Posted - 2009-02-12 : 10:44:46

Our production server is 8 CPU, 16GB RAM server
We are experiencing locking on tempdb.
The data files associated with the tempdb are of equal size.
Below is the locking information.

The lastwaittype PAGELATCH_UP which is something to do with the tempdb. The storedproc PLSiteLanguage is stored procs with Select statement using order by clause.

The other process is a maintainenece task running on tempdb.

------------------------------------------------------------------------
BLOCKING SITUATION [SQLSTATE 01000]
FE CurrDate BLcount waittime_avg waittime_min waittime_max db_name loginame cmd hostname spid
---- --------------------------- ----------- ------------ ------------ ------------ ------------------------------ ------------------------------ ---------------- ------------------------------ ------
BL_S 2009-02-12 09:47:00.810 1 7250 7250 7250 SELLEULOLA1 SELLEULOLA1handler AWAITING COMMAND MUCWWP97.muc.msp.amadeus.net 312
-- [SQLSTATE 01000]
BLOCKING PROCESS [SQLSTATE 01000]
FE CurrDate spid kpid blocked waittype waittime lastwaittype waitresource dbid uid cpu physical_io memusage login_time last_batch ecid open_tran status sid hostname program_name hostprocess cmd nt_domain nt_username net_address net_library loginame context_info sql_handle stmt_start stmt_end db_name
----- ------------------------------------------------------ ------ ------ ------- -------- ----------- -------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ ------ ----------- -------------------- ----------- ------------------------------------------------------ ------------------------------------------------------ ------ --------- ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ---------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------ ------------ -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ----------- ----------- --------------------------------------------------------------------------------------------------------------------------------
BL_P1 2009-02-12 09:47:00.810 312 0 0 0000 0 PAGELATCH_UP 2:1:50 5 15 34283 28 112 2009-02-11 08:30:07.920 2009-02-12 09:46:35.013 0 1 sleeping B1138574DA2D9943A46E1BC0B7EC56D300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 MUCWWP97.muc.msp.amadeus.net 0 AWAITING COMMAND 000000000000 TCP/IP SELLEULOLA1handler 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000 0 0 SELLEULOLA1
-- [SQLSTATE 01000]
BLOCKED PROCESSESS [SQLSTATE 01000]
FE CurrDate spid kpid blocked waittype waittime lastwaittype waitresource dbid uid cpu physical_io memusage login_time last_batch ecid open_tran status sid hostname program_name hostprocess cmd nt_domain nt_username net_address net_library loginame context_info sql_handle stmt_start stmt_end db_name
----- ------------------------------------------------------ ------ ------ ------- -------- ----------- -------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ ------ ----------- -------------------- ----------- ------------------------------------------------------ ------------------------------------------------------ ------ --------- ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ---------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------ ------------ -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ----------- ----------- --------------------------------------------------------------------------------------------------------------------------------
BL_P2 2009-02-12 09:47:00.810 85 6592 312 0003 7250 LCK_M_S KEY: 2:2:1 (e70043fdba94) 2 1 11668 29 30 2009-02-11 10:16:56.700 2009-02-12 09:46:53.547 0 0 sleeping 01050000000000051500000026409373AD54C304030DE7A0F103000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 MUCWWP2B OSQL-32 2492 SELECT MUCWWP2B 1apatrol 0016357D46D3 TCP/IP MUCWWP2B\1apatrol 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 010002005E43D334C020D62C0000000000000000 2478 2874 tempdb
-- [SQLSTATE 01000]
-- BLOCKING PROCESS COMMAND ------------------------------------------- [SQLSTATE 01000]
dbcc inputbuffer (312) [SQLSTATE 01000]
EventType Parameters EventInfo
-------------- ---------- -----------------------------------
Language Event 0 EXEC dbo.PLSiteLanguage 'BAULBAUL'

(1 rows(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
-- [SQLSTATE 01000]
-- BLOCKED PROCESSES COMMANDS ----------------------------------------- [SQLSTATE 01000]
dbcc inputbuffer (85) [SQLSTATE 01000]
EventType Parameters EventInfo
-------------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Language Event 0
if (@@ERROR <> 0)
begin
print 'MSSQLKMTAG_NO_DB_ACCESS tempdb Skipping Database Collection'
end
else
begin
declare @select_ok int
declare @msg_text varchar(255)

select @select_ok = 0
select @msg_text = ''

if ((permissions(object_i
----------------------------------------------------------------------------------------

We tried this on a different server and facing the same issue. Please help how to start debugging the issue.

Thanks,
Preethi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 11:00:16

Post code of procedure PLSiteLanguage

E 12°55'05.63"
N 56°04'39.26"

PreethiSRaj
Starting Member

4 Posts

Posted - 2009-02-13 : 04:00:54

SELECT Code
FROM TableA WHERE CoreDate = @ABC AND SiteData= @DEF
ORDER BY Code

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-13 : 04:05:56

And how many records do that SELECT statement normally return?

E 12°55'05.63"
N 56°04'39.26"

PreethiSRaj
Starting Member

4 Posts

Posted - 2009-02-13 : 05:41:06

Very few records, about 10

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-13 : 05:49:54

I find it hard to believe this procedure that returns only 10 records to deadlock other statements.
What about transactions and isolation levels?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-13 : 06:03:34

Why don't you tell us the whole picture?
By the log, I can see that procedure PLSiteLanguage is called with one and only one parameter like this

EXEC dbo.PLSiteLanguage 'BAULBAUL'

But when I asked you for the code of same procedure you posted

SELECT Code
FROM TableA WHERE CoreDate = @ABC AND SiteData= @DEF
ORDER BY Code

Which has two parameter. Or one or both is variables, but then there is some missing code.

However procedure PSGetSiteParameter do have two parameters according to the log.
EXEC PSGetSiteParameter 'WJC', 'FILTER_KEY'

If you can't provide what I ask for, I am sorry I can't help you.
I hope you solve your problem soon and that some other here at SQLTeam are willing to assist you.

E 12°55'05.63"
N 56°04'39.26"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-13 : 16:56:57

quote:
Originally posted by PreethiSRaj

Our production server is 8 CPU, 16GB RAM server
We are experiencing locking on tempdb.
The data files associated with the tempdb are of equal size.
Below is the locking information.

The lastwaittype PAGELATCH_UP which is something to do with the tempdb. The storedproc PLSiteLanguage is stored procs with Select statement using order by clause.

The other process is a maintainenece task running on tempdb.

------------------------------------------------------------------------
BLOCKING SITUATION [SQLSTATE 01000]
FE CurrDate BLcount waittime_avg waittime_min waittime_max db_name loginame cmd hostname spid
---- --------------------------- ----------- ------------ ------------ ------------ ------------------------------ ------------------------------ ---------------- ------------------------------ ------
BL_S 2009-02-12 09:47:00.810 1 7250 7250 7250 SELLEULOLA1 SELLEULOLA1handler AWAITING COMMAND MUCWWP97.muc.msp.amadeus.net 312
-- [SQLSTATE 01000]
BLOCKING PROCESS [SQLSTATE 01000]
FE CurrDate spid kpid blocked waittype waittime lastwaittype waitresource dbid uid cpu physical_io memusage login_time last_batch ecid open_tran status sid hostname program_name hostprocess cmd nt_domain nt_username net_address net_library loginame context_info sql_handle stmt_start stmt_end db_name
----- ------------------------------------------------------ ------ ------ ------- -------- ----------- -------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ ------ ----------- -------------------- ----------- ------------------------------------------------------ ------------------------------------------------------ ------ --------- ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ---------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------ ------------ -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ----------- ----------- --------------------------------------------------------------------------------------------------------------------------------
BL_P1 2009-02-12 09:47:00.810 312 0 0 0000 0 PAGELATCH_UP 2:1:50 5 15 34283 28 112 2009-02-11 08:30:07.920 2009-02-12 09:46:35.013 0 1 sleeping B1138574DA2D9943A46E1BC0B7EC56D300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 MUCWWP97.muc.msp.amadeus.net 0 AWAITING COMMAND 000000000000 TCP/IP SELLEULOLA1handler 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000 0 0 SELLEULOLA1
-- [SQLSTATE 01000]
BLOCKED PROCESSESS [SQLSTATE 01000]
FE CurrDate spid kpid blocked waittype waittime lastwaittype waitresource dbid uid cpu physical_io memusage login_time last_batch ecid open_tran status sid hostname program_name hostprocess cmd nt_domain nt_username net_address net_library loginame context_info sql_handle stmt_start stmt_end db_name
----- ------------------------------------------------------ ------ ------ ------- -------- ----------- -------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ ------ ----------- -------------------- ----------- ------------------------------------------------------ ------------------------------------------------------ ------ --------- ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ---------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------ ------------ -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ----------- ----------- --------------------------------------------------------------------------------------------------------------------------------
BL_P2 2009-02-12 09:47:00.810 85 6592 312 0003 7250 LCK_M_S KEY: 2:2:1 (e70043fdba94) 2 1 11668 29 30 2009-02-11 10:16:56.700 2009-02-12 09:46:53.547 0 0 sleeping 01050000000000051500000026409373AD54C304030DE7A0F103000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 MUCWWP2B OSQL-32 2492 SELECT MUCWWP2B 1apatrol 0016357D46D3 TCP/IP MUCWWP2B\1apatrol 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 010002005E43D334C020D62C0000000000000000 2478 2874 tempdb
-- [SQLSTATE 01000]
-- BLOCKING PROCESS COMMAND ------------------------------------------- [SQLSTATE 01000]
dbcc inputbuffer (312) [SQLSTATE 01000]
EventType Parameters EventInfo
-------------- ---------- -----------------------------------
Language Event 0 EXEC dbo.PLSiteLanguage 'BAULBAUL'

(1 rows(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
-- [SQLSTATE 01000]
-- BLOCKED PROCESSES COMMANDS ----------------------------------------- [SQLSTATE 01000]
dbcc inputbuffer (85) [SQLSTATE 01000]
EventType Parameters EventInfo
-------------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Language Event 0
if (@@ERROR <> 0)
begin
print 'MSSQLKMTAG_NO_DB_ACCESS tempdb Skipping Database Collection'
end
else
begin
declare @select_ok int
declare @msg_text varchar(255)

select @select_ok = 0
select @msg_text = ''

if ((permissions(object_i
----------------------------------------------------------------------------------------

We tried this on a different server and facing the same issue. Please help how to start debugging the issue.

Thanks,
Preethi

Can you explain what this other process is? Are you using Select * INTO to create Temp table? Do they have indexes to speed up? Are they local or Global Temp table? How many rows are you dealing with it in your Store proc?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources