April 7, 2008 at 1:09 pm
Hi all,
I had a particularly interesting conference call today with a customer regarding the use of temp tables and tempdb.
This customer is having performance problems with a client server based app that uses C++ ODBC and MSSQL 2000 (we also support 2005 but this dude is on 2000). The SQL Server is located in Denver and the Client is located in Tacoma. I think his performance issue is related to the WAN squashing the throughput of the ODBC client.
He thinks among the following:
1. our application can't handle the bandwidth it is receving. (I've proven that wrong here with some tests)
2. our dependence on temp tables is causing performance problems.
3. our dependence on tempdb is causing problems.
I know that 6.5 and 7.0 there were issues with using tempdb especially when you had to grow the database as a matter of writing to. I believe this is resolved with 2000 and higher. He also said we are probably losing data when we use temp tables. We only use temp tables within stored procedures and never with dynamic sql. I can't imagine how we could lose data doing that or people wouldn't use mssql.
So any drawbacks to using temp tables?
any drawbacks to using tempdb?
do you think running microsofts ODBC SQL client over a wan is a performance killer?
always get a backup before you try that.
April 7, 2008 at 1:29 pm
- it is prooven oledb outperforms odbc.
- use profiler to detect if you have issues with tempdb.
then you can optimise it.
You need to measure it to be able to know if it hurts in your case.
- Is tempdb residing on its own disk(s) (raid01) ?
- are temp tables created outside the transaction scope of a proc ? (tempdb catalog contention possible)
- Generaly I would state that the lack of regular db-maintenance (reindex), missing clustering indexes and propper fk-indexes are the most common
reasons for databases and procedures to perform bad.
- cursors also put heavy pressure to tempdb. That's one of the reasons to avoid them (and what does odbc ?? ... right)
- how do they connect (isolationlevel) and what isolationlevel are they using to execute the commands ?
- art: " Optimize tempdb in SQL Server by Striping and Splitting"
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1276989,00.html
- art: "How to Performance Tune the Microsoft SQL Server tempdb Database" http://www.sql-server-performance.com/tips/tempdb_p1.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 7, 2008 at 2:21 pm
Depending on how you use them, temp tables can cause performance problems.
For example, if you create a temp table in a proc after you have already run DML commands in the proc, then every time the proc is run, it has to be compiled. That means every time it's called, it creates a compile lock, which means only one connection can really run it at a time. So, if you have mixed DML and DDL, like this, then you can end up with serious performance problems since the proc can't run simultaneously for multiple users/connections.
(The solution is simply to move the temp table DDL commands to the beginning of the procs and don't have other DDL commands later, after DML commands. Pretty easy to do, if you know to do it.)
Another situation is any place where you have a cursor running on a temp table, that again means the proc has to be compiled each time it's run. The simple solution for that is don't run cursors on temp tables. If you absolutely have to have a cursor on temporary data, use a table variable. Better yet, don't use cursors.
You can also run into performance problems if you have tempdb too small, or on a heavy-use disk, or not on its own disk, or various other situations with tempdb not being set up well. That will slow down temp tables, of course. (If you use a lot of temp tables, placing tempdb on its own raid 1/10/01 array, with one file per CPU on the server, can make a big difference in speed.)
The only way I can think of to lose data when using temp tables would be to have the code populating them or pulling data from them be badly written. It's possible, but it's not the fault of the temp tables, it's the fault of code that would lose data no matter how it was used. So, I'd discount that problem.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 8, 2008 at 8:21 am
Thanks for the input! I have my temp tables built at the very beginning of all sp code and not later on in the code for that reason. Many times I use permanent temp tables unless I have a lot of different users hitting the permanent temp table. I have had some issues with deadlocks on deletes from the permanent temp tables. By Permanent temp table I mean a permanent table I populate while the stored procedure is executing and then when the sp completes it deletes the data execution of the sp inserted into the table.
I know if I understood the locking options better I could probably resolve this. I have to insert\select and delete from these temp tables with multiple concurrent process and the delete always seems to deadlock and confound me.
what happens in my sp is that I decalre a variable @timeid and then I use getdate() to populate it. When I insert into my permanent temp table I have a column for @timeid named timeid. When all my work is done I delete from the temp table by saying where the timeid=@timeid. It works great with no concurrency but if I have two users I start getting deadlocks.
I know this is a side issue but you made me think of it.
As a side note don't you hate when you work with someone who has some knowledge of SQL and they continually talk in technobabble that isn't immediately obvious they are ignorant? That is what I am dealing with here. But I realize I need to do some work on my part.
always get a backup before you try that.
April 9, 2008 at 8:24 am
There are two better options than your @timeid, though that one is a good step in the right direction.
One option is to use the SPID as part of the primary key and clustered index on the "permanent temp table". That works quite well if the application doesn't pool/re-use SPIDs.
The other, that I like better, is to use NewSequentialID() instead of a timestamp. Again, make sure it's part of the primary key and clustered index. If you reference that field in your selects/updates/deletes, it should help eliminate your locking issues.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2008 at 8:34 am
ROCK ON!
Now that is what I'm talking about. I'll give that a whirl!
always get a backup before you try that.
April 11, 2008 at 9:38 am
That worked like a charm! I am using SQL 2000 so I used the NEWID() function but basically the same concept and I eliminated my locking problems.
thanks!
always get a backup before you try that.
April 11, 2008 at 1:54 pm
If you're using NewID as part of the clustered PK, you should also add an incrementing field to it. A transaction time would work. Otherwise, since NewID isn't sequential, you can end up with problems with page splits in the table when you insert non-sequential values in the clustered index.
Simply put, a sequential field before the NewID field in the clustered index will help make inserts into the table faster.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply