Tempdb Contention

Question

Post reply

Tempdb Contention

san43

Hall of Fame

Points: 3196
More actions
September 27, 2007 at 2:44 pm

#73553

Right now we are having performance issues with one application. But how can we findout that if there a tempdb contention in realtime?

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

This may sound like a stupid question, but please humour me.

How do you know you're having performance issues?

Are queries slow? If so, all queries or some? All the time or occasionally?

Are you seeing lots of blocking? If so on what resources?

Are you seeing high wait times? If so, what types?

Are the users complaining? All of them? Some of them? Some of the time?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

san43 Hall of Fame Points: 3196 More actions · Answer 2

Users are sometimes complaining that application is running slowly. We did 2 tests but we didn't find any long running sql statements except for one which is running for 5 secs in one of the transaction. Each transaction is hitting database server with lot of sql statements.

But not sure of how to measure tempdb contention if there is a possibility.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

You could start by running sp_Lock during the periods of slowness. Running sp_HelpDB will allow you to figure out the DBID for TempDB... check for blocking on that DBID using sp_Lock.

You might also want to take the time to setup a trace... I'm not a DBA and it would take me longer to tell you than if you look it up in Books Online.

And, a transaction that runs for 5 seconds is a terrible thing... you might want to start by looking at that code. If it's not full of RBAR (pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row"), you might want to check out what kind of indexing you have.

If it appears you have a contention on TempDB (or any other DB), it may be due to growth... when a DB grows in size (reallocation), nothing in the database works. If you never took the time to preconfigure the sizes of your DB's or TempDB, that may be your problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

noeld SSC Guru Points: 96590 More actions · Answer 4

... Running sp_HelpDB will allow you to figure out the DBID ...

select db_name(dbid)

Cheers,

* Noel

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Jeff Moden (9/27/2007)
You could start by running sp_Lock during the periods of slowness. Running sp_HelpDB will allow you to figure out the DBID for TempDB... check for blocking on that DBID using sp_Lock.

TempDB has a DBID of 2, always.

San:

What's the spec of your server, memory, cpu? What's the size of the db? How many users? How many queries/sec?

Look at your CPU usage (task manager's ok for this) What's the average?

Run profiler over over the slow periods, trace for RPC completed and SQL Batch completed. Look for queries with high duration, reads, cpu or all.

Look at your wait times (DBCC SQLPERF(WaitStats)) Which ones are high? Is the signal_wait_time for any of them high?

Watch the active processes (sysprocesses) Do you see any blocking or waits. If so, check the resources that the blocks are on.

The most common type of TempDB contention is latch contention on the system pages when creating temp tables. It requires a large amount of temp tables created per sec. I know, I had this problem some time back. You'll see that as page latch on page 2:1:3 (file 1 of tempdb, the first SGAM page) in sysproceses.

t.b.c later with some perfmon counters you can use to check. Please note that debugging performance problems from across the internet is not an easy thing

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

p.s. If you can, go and sit with one of the users who is complaining so that you can see what's happening.

I heard a good story at PASS. One guy had a user who kept reporting performance problems with the app, but he could not find anything wrong. He checked everything. Finally, he checked on the user. Long story but he discovered that she reported the server slow any time she was behind on her work, so that she had an excuse for not getting things done on time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

:w00t:, what class was that in Gail? I got to hear that story when the recordings come out.

One more thing to consider now protecting ourselves from... user laziness. I'll ask Steve to make an article about this one :).

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

iirc, it wasn't a class. Was during supper on friday night. Was a group of 12 or so of us and most of the people at the table were MVPs or speakers or both. Was good fun. I have some photos...

I could be mistaken, so I'll check some of the sessions on the DVD, when I get it.

I'm probably going to blog on the subject, when I get started with the planned series on finding performance problems

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Damn, there were still Dbas left Friday night. I guess I missed them all. It was like a ghost town right after the last session has started.

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 10

Gail and Jeff have great suggestions. But make sure you get some good comparisons of what you expect from tempdb during slow periods or "normal periods." Having that comparison is critical to be sure that you can tell what's the bottleneck when things are slow.

I'd be sure you compare the transaction levels between things being slow and not slow. It could be simple load.

I'm not surprised users would complain more when they're behind. That's classic human reaction. I've seen people delete files from their machines, hoping for a crash, loosen network cables, etc. Drives IT people crazy, but not much you can do. Laughing is the best idea.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Hey Steve, that would definitly make a good article. Maybe you could even make a contest out of it (like the nightmare contest). I'm sure we'd get a lot of good stories.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 12

noeld (9/28/2007)
... Running sp_HelpDB will allow you to figure out the DBID ...
select db_name(dbid)
Cheers,

Heh... yeah... that'll work if you know the DBID... won't help you find the DBID for TempDB unless you get lucky guessing the DBID 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 13

GilaMonster (9/29/2007)
TempDB has a DBID of 2, always.

Teaching how to fish... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

I have the 4 system DB's IDs memorised. It's quicker when reading through syslockinfo. Come to think of it, I have half of my user DB's ids memorised too... :hehe:

I believe the command you were looking for was select db_id([db name])

Edit: Hmmm. I can't use triangular brackets any more....

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass