June 16, 2011 at 4:11 am
Can we create a temp table that does not use tempdb?
June 16, 2011 at 4:32 am
If you mean a table that behaves like a # table (scoped to your connection, random name to avoid collisions, dropped when your connection closes) then no.
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
June 16, 2011 at 5:24 am
Ditto what Gail said.
However, a lot of people I know use staging tables that they create and drop at need in a user db. These are permanent tables, even though they are only temporarily kept in the db. I don't know if that helps you or not.
June 16, 2011 at 8:02 pm
Thanks both.
We have heavy IO on tempdb right now and the size of tempdb grow rapidly. We needed to restart service last to solve locking issue.
Our programmers use temp table a lot. So I guess I need to find another ways to improve that.
June 16, 2011 at 8:24 pm
If you could expand on the actual issue there may be a solution out there. I don't see that using temp tables should cause locking, and TempDB growing isn't in itself an issue.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
June 16, 2011 at 9:53 pm
sqlnes (6/16/2011)
Thanks both.We have heavy IO on tempdb right now and the size of tempdb grow rapidly. We needed to restart service last to solve locking issue.
Our programmers use temp table a lot. So I guess I need to find another ways to improve that.
How many data files you have created for your tempdb? If your tempdb has only one file (default) you need to add files to improve the performance.
Locking is not an issue. Is it causing blocking or deadlocks? Which isolation level are you using? Is it Serializable?
June 16, 2011 at 11:10 pm
we have suspended sessions with command "INSERT" and wait_type "PAGEIOLATCH_IO" and the "blocked by" is 0. And those stroed procedures use temp tables a lot and only insert commend in them are insert into temp tables. And it can be resolved by restarted service. That's why I thought it's related to tempdb.
And yes we have only one datafile for tempdb. Does it require restarting service to add datafiles to tempdb?
appreciate for suggestions.
June 16, 2011 at 11:51 pm
There is no blocking issue. You have data file contention issue.
Go ahead and data files of to tempdb. It does not require restarting the service.
Size of the data files should be equal.
Decide the number of files, based on the number of processors in the server.
If you have multiple physicsl disk, place the files on separate physical disks.
June 17, 2011 at 12:05 am
sounds good. thanks Suresh!
I'll add more files. we have 16 core on the server, so I guess I 'll put 4 files for both data files & Tlog files.
June 17, 2011 at 12:18 am
Put 4 DATA files only. Not Tlog files.
TLog is written sequentially. So multiple files will not improve the performance.
Data files are written randomly.
(Don't add more than 8 data files to tempdb. More than 8 files is counter productive)
-- Added
PAGE IO LATCH happens only on data files. There are no pages in the TLog file.
June 17, 2011 at 1:08 am
Hi Suresh, thanks for the good information. I'll add files to datafile only. 🙂
I wish it will resolve our issue here.
June 17, 2011 at 1:13 am
Since this is an IO contention issue (PageIOLatch) you will need to put each of those files on a separate physical drive, or you will have little to no effect on your problem.
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
June 17, 2011 at 1:25 am
Suresh B. (6/17/2011)
P(Don't add more than 8 data files to tempdb. More than 8 files is counter productive)
Not true. It can in cases be necessary to add way, way 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
June 17, 2011 at 3:21 am
Thanks for reminding.
I actually will add one datafile first and add two more later. Since another concern about tempdb on our server is that we've failed to apply SP2 CU6 to it in order to resolve the tempdb issue, http://support.microsoft.com/kb/947204/en-us?fr=1,
I would like to see if add more files can also postpone the contention issue to appear. (i think if the file was too big will affect preformance).
June 17, 2011 at 3:22 am
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply