Help with the locks on the TempDB

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

General SQL Server Forums

New to SQL Server Programming

Help with the locks on the TempDB

Author

Topic

avmreddy17
Posting Yak Master

180 Posts

Posted - 2009-10-20 : 14:01:54

Help with the locks on the TempDB.

Below is the SP in which SM table has just 10 rows and the SP return in no time and there
are no locks when i run it from the backend (Query AMalyser )

But when the same SP is run from a C++ app I see aroubd 50+ locks on the TempDB system Objects.

CREATE PROC dbo.usp_GetFxSymbols
AS
BEGIN

SET NOCOUNT ON

SELECT Symbol = LTRIM(RTRIM(BASECURRENCY)) + '/' + LTRIM(RTRIM(QUOTEDCURRENY)),
LastBid,LastAsk,ClosingPrice
INTO #T1
FROM SM
UNION
SELECT Symbol = LTRIM(RTRIM(QUOTEDCURRENY)) + '/' + LTRIM(RTRIM(BASECURRENCY)),
LastBid,LastAsk,ClosingPrice
FROM SM

SELECT Symbol, LastBid,LastAsk,ClosingPrice
FROM #T1
ORDER BY Symbol

DROP TABLE #T1

END

Locks:-

spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
6084 2 0 0 DB [BULKOP_BACKUP_LOG] NULL GRANT
6084 2 5 0 TAB IX GRANT
6084 2 4 0 TAB IX GRANT
6084 2 7 0 TAB IX GRANT
6084 2 0 0 DB [BULKOP_BACKUP_DB] NULL GRANT
6084 2 13 0 TAB IX GRANT
6084 2 15 0 TAB IX GRANT
6084 2 26 0 TAB IX GRANT
6084 2 34 0 TAB IX GRANT
6084 2 41 0 TAB IX GRANT
6084 2 54 0 TAB IX GRANT
6084 2 0 0 MD 8(1:0:0) Sch-S GRANT
6084 2 34 4 KEY (2c00649d5031) X GRANT
6084 2 4 1 KEY (0400395cb212) X GRANT
6084 2 13 1 KEY (0400395cb212) X GRANT
6084 2 54 2 KEY (2c00d77e7b11) X GRANT
6084 2 26 1 KEY (010068aecba5) X GRANT
6084 2 34 2 KEY (551614f37c21) X GRANT
6084 2 7 1 KEY (0000222c61d4) X GRANT
6084 2 34 1 KEY (2b00667f7dcb) X GRANT
6084 2 13 1 KEY (01000bac6c25) X GRANT
6084 2 4 1 KEY (01000bac6c25) X GRANT
6084 2 15 1 KEY (00000691978b) X GRANT
6084 2 5 1 KEY (00000691978b) X GRANT
6084 2 13 1 KEY (03008064658f) X GRANT
6084 2 4 1 KEY (03008064658f) X GRANT
6084 2 41 1 KEY (2f00ee0de22e) X GRANT
6084 2 41 1 KEY (2d003252890b) X GRANT
6084 2 41 1 KEY (2c00dcfd3c19) X GRANT
6084 2 99 0 RID 1:20252:1 X GRANT
6084 2 13 1 KEY (0200e503d937) X GRANT
6084 2 4 1 KEY (0200e503d937) X GRANT
6084 2 0 0 PAG 1:20252 X GRANT
6084 2 0 0 PAG 1:20257 X GRANT
6084 2 41 1 KEY (2e00573535b3) X GRANT
6084 2 41 2 KEY (9601cff0099d) X GRANT
6084 2 54 1 KEY (2b00319bc7d7) X GRANT
6084 2 41 2 KEY (9001b2213b03) X GRANT
6084 2 41 2 KEY (5a012eae9701) X GRANT
6084 2 1908462123 0 TAB Sch-M GRANT
6084 2 0 0 HBT Sch-M GRANT
6084 2 41 2 KEY (82022bfe427f) X GRANT
6084 2 0 0 AU [BULK_OPERATION_PAGE] S GRANT
6084 2 34 3 KEY (55169151e3b0) X GRANT

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-20 : 14:53:50

Not sure why that would be...but why not just eliminate the temp table with:


SELECT Symbol = LTRIM(RTRIM(BASECURRENCY)) + '/' + LTRIM(RTRIM(QUOTEDCURRENY))
       ,LastBid
       ,LastAsk
       ,ClosingPrice
FROM SM
UNION ALL
SELECT Symbol = LTRIM(RTRIM(QUOTEDCURRENY)) + '/' + LTRIM(RTRIM(BASECURRENCY))
       ,LastBid
       ,LastAsk
       ,ClosingPrice
FROM SM
ORDER BY 1

EDIT:
if you were intentionally distincting the two results then you can take the ALL back out. But if not then you should use UNION ALL

Be One with the Optimizer
TG

avmreddy17
Posting Yak Master

180 Posts

Posted - 2009-10-20 : 15:13:57

That was intentional using a UNION.
I am wondering why its placing so many locks on the TempDB

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-20 : 15:20:41

It's not...It's the temp table. Let us know if my solution (minus the ALL) works for you.

Be One with the Optimizer
TG

avmreddy17
Posting Yak Master

180 Posts

Posted - 2009-10-20 : 16:46:19

TG, its still the same.

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-20 : 16:55:25

doesn't have value that I see, unless it doesn't matter that you don't know the difference between the 2 prices...how can you tell which is which

In any case, what are you actually trying to do


SELECT Symbol = LTRIM(RTRIM(BASECURRENCY)) + '/' + LTRIM(RTRIM(QUOTEDCURRENY)),
LastBid,LastAsk,ClosingPrice
INTO #T1
FROM SM
UNION
SELECT Symbol = LTRIM(RTRIM(QUOTEDCURRENY)) + '/' + LTRIM(RTRIM(BASECURRENCY)),
LastBid,LastAsk,ClosingPrice
FROM SM

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-20 : 17:27:17

So you're still getting tempdb locks (from this process) even though you are no longer SELECTing INTO a temp table ? Your output shows a lot of different object ids - how do you know they are coming from this SP?

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources