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.
| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2001-12-23 : 13:33:09
|
| We're having a few performance problems here and there, and I'm on a mission to convert older queries that use temp tables into new high-tech queries that use the table variable type. So far I've seen some impressive gains.However, I've killed every temp table I can find, and I'm still getting quite a lot of disk access to tempdb, and perfmon shows that tempdb has almost as many transactions/second as my production database. That can't be good.Does anyone have any ideas for tracking down the queries that are hitting tempdb? What else can cause a tempdb hit other than an explicit temp table creation?Thanks in advance-b |
|
|
sica
Posting Yak Master
143 Posts |
Posted - 2001-12-23 : 15:08:14
|
| I would recommend Profiler.Check for tempdb id in the sysdatabases.Sica |
 |
|
|
sica
Posting Yak Master
143 Posts |
Posted - 2001-12-23 : 15:08:17
|
| I would recommend Profiler.Check for tempdb id in the sysdatabases.Sica |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-24 : 11:50:27
|
I would encourage you to search BOL for references to tempdb. Here are a couple quotes that may apply.Regarding SQL Cursors, BOL says this:quote:
INSENSITIVE Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications. When SQL-92 syntax is used, if INSENSITIVE is omitted, committed deletes and updates made to the underlying tables (by any user) are reflected in subsequent fetches.
And regarding ADO connections and cursors:quote:
Static and keyset-driven cursors increase the usage of tempdb. Static server cursors build the entire cursor in tempdb; keyset-driven cursors build the keyset in tempdb.
|
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2001-12-24 : 12:12:01
|
| Also, I don't think you can eliminate tempdb usage completely. SQL Server uses temp tables internally while performing sorts, hash joins, grouping and aggregates... |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2001-12-26 : 02:12:11
|
| Thanks, everyone. I'm away on vacation right now, and I'll take a look at profiler when I get home, but if my understanding of how it works is right, I'm not expecting it to help much. Will it really show the queries that cause tempdb to be hit? I'm not expecting to eliminate tempdb usage altogether; I just have a production DB that's averaging about 75 transactions/second, and a tempdb that's averaging 55/second. That can't be right (before eliminating the use of temp tables, tempdb was averaging 90/second).Cheers-b |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-26 : 12:39:47
|
Sure, it might be right. Based on Ilya's response...quote:
SQL Server uses temp tables internally while performing sorts, hash joins, grouping and aggregates...
... depending on what your transactions are doing, such as selecting from multiple tables, I don't see any reason to believe that 55 trans/sec on tempdb is unusual given 75 trans/sec on main db.Enjoy your vacation! |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2001-12-26 : 21:08:40
|
| I would love to hear from others about their ratios of temdb transactions/second to their production transactions/second. My feeling is that my tempdb usage is high, but I'll freely admit that's a gut instinct and that I'm one of those dangerous self-taught DBA's who periodically learns something staggering yet basic (I just learned not to use sp_ two months ago, for instance, after 4 years of writing stored procedures).Since I'm being ignorant, can someone explain why SQL server uses tempdb for sorts or hash joins? I could see how it might for UNIONs, or even aggregates, but sorts? That can't be right, can it? And, if it is somehow right, do *those* transactions get an exclusive lock on tempdb (as temp tables do), so only one query using hash joins or sorts runs at once? Say it ain't so!Likewise with cursors: I definitely have some static cursors in my application. Are they acquiring exclusive locks on tempdb? Would I be wise changing some of my cursors to a different type, or even client-side on the web servers?Please forgive my ignorance here -- my background is in networking and network/OS benchmark development (I'll happily trade advice, up to and including OSPF, BGP, GSLB, statistics and benchmark design, you name it), but my job is becoming more and more that of a DBA as our network infrastructure more or less done and very stable, while our database is growing in both size and complexity and increasingly criticial to our success.Thanks, everyone!-b |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2001-12-27 : 11:27:51
|
quote:
Since I'm being ignorant, can someone explain why SQL server uses tempdb for sorts or hash joins? I could see how it might for UNIONs, or even aggregates, but sorts? That can't be right, can it?
Would you believe it if Microsoft said so? Try this link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_5ayb.aspUnfortunately I have not seen a single good article explaining how these operations use worktables. quote:
And, if it is somehow right, do *those* transactions get an exclusive lock on tempdb (as temp tables do), so only one query using hash joins or sorts runs at once? Say it ain't so!
Ok, it ain't!  As a matter of fact, even regular temp tables (the ones you declare explicitly with '#') do not lock other transactions out of tempdb (well, when you create a temp table you need to obtain a lock on system tables, but if you know what's good for you, you don't hold those locks for long). quote:
Likewise with cursors: I definitely have some static cursors in my application. Are they acquiring exclusive locks on tempdb? Would I be wise changing some of my cursors to a different type, or even client-side on the web servers?
Yes! In fact you might be able to do away with some of your cursors completely. And where you can't do away with them, client-side cursors might be a good option. I doubt you'd want to change your static cursors to something more dynamic -- doing so might decrease the use of tempdb but it will put way more overhead on the server and slow things down. And one last thing: why are you so determined to decrease the use of tempdb? I would guess that there might be places in your app you can tweak to get more significant performance gains... Stored procedures that use cursors is probably one of those places.Edited by - izaltsman on 12/27/2001 11:31:09 |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-27 : 14:19:22
|
quote:
Would I be wise changing some of my cursors to a different type, or even client-side on the web servers?
Well... you know, the word cursor is such a tricky thing because it can have some many different connotations. Expanding on some of Ilya's comments... If you have code such as a stored procedure and it is doing a DECLARE CURSOR FOR... or for that matter any other type of looping logic, I'd look long and hard at it to see if it could be replaced by set-based operations. I've heard it said (by nr) that EVERYTHING can be done without having to use cursors (provided that your database structure is properly designed).Likewise, from web pages, when you get a recorset, you declare a cursor type, and can't get away from it. But what type it is and what you do with it are in your control. For example, many people do a DO While Not rs.EOF... Loop process which is really slow compared to rs.GetString or rs.GetArray.So I guess my final conclusion is the same as Ilya's... If you're looking for performance gains, there are a lot of other places to look besides tempdb. Two additional sources to get you started are:[url]http://www.sqlteam.com/FilterTopics.asp?TopicID=103[/url][url]http://www.sql-server-performance.com[/url]--------------------------------------------------------------1000 Posts, Here I come! I wonder what my new title will be... |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2001-12-28 : 16:16:28
|
| Thanks for the feedback and advice.Part of the reason I'm starting to look at tempdb is because my SQL server's disk subsystem is not ideal, and tempdb is on the same spindles as my production database. I've put a lot of work into tuning the database and application, and almost none into tempdb usage. Seemed like the most logical next step, and so far at least eliminating temp tables seems to really have helped.Fortunately, even in my days as a truly incompetent dba, I knew enough to stay away from declared cursors. Not a single one anywhere in the app, except the obligatory ones on the ASP pages dealing with recordsets.I will admit, though, that I've got some DO WHILE NOT RS.EOF code that could be converted to RS.GETARRAYs. Definitely a project for the near future.Thanks, everyone!-b |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2001-12-28 : 17:11:52
|
| I promise you, you will be so mad at yourself after you see how much better things run without rs.eofGetString and GetRows are invaluable and extremely efficient. I recently converted some "long winded" pages and it's been an incredible difference.Mike "A program is a device used to convert data into error messages." |
|
|
|
|
|
|
|
|
Tracking down queries that use TempDB?