April 23, 2008 at 11:01 am
This is probably a very obvious question.
Background: SQL Server 2005 SP2 (running on a VMware virtual server), table in question has 10M rows and no indexes (yet). All data files and tempdb are on the E: drive.
After loading the table via bulk insert, if I do a Select * on it, I noticed that the C: drive is filling up. I haven't placed any indexes on the table yet, I wanted to get the data in there first.
Further investigation shows that a temp file in C:\Documents and Settings\ \Local Settings\Temp is the one increasing in size as the select statement runs. How is SQL Server using this particular temp file. I realize without any indexes, this is extremely inefficient.
Thanks in advance.
Kevin
April 23, 2008 at 12:17 pm
What's the name of that file?
It's possible that it's the windows swap file, which means SQL's using more memory than the machine has available and windows is swapping it to disk.
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
April 23, 2008 at 12:37 pm
C:\Documents and Settings\ \Local Settings\Temp\tmp25.tmp. I'm assuming with each new query it creates a new tmp file, since it was tmpB.tmp on the prior select.
I'm sure it's something as simple as the fact that I'm asking SQL to return all the rows to the screen and it has to have some place to build this; maxing out on memory. I'm looking to verify this and learn a little bit about what SQL Server is doing behind the scenes. I've got the Inside SQL Server 2000 and Guru's Guide to Arch & Int, so if this is covered in them please kindly state the obvious and tell me to look there.
Kevin
April 23, 2008 at 12:49 pm
The SQL database engine doesn't use temp files and certainly not in those directories. It uses TempDB for its temporary work space. Otherwise it uses its allocated memory.
It could be something to do with the client tool you're using, or windows itself. Not sure.
Try querying from another machine, see what changes.
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
April 23, 2008 at 1:14 pm
If I run the query from SSMS on my laptop, it builds the tmp file locally. So SSMS is using this tmp file to build the result set. So we've discovered the who and why.
Kevin
April 23, 2008 at 1:22 pm
So it's management studio's local work space. Good to know.
Try opening the file in notepad. I'd be interested to know if it's the query in there, or the results. I know SSMS writes the queries away somewhere because it can sometimes recover them after a PC crash
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
April 23, 2008 at 1:29 pm
Yes, it has to buffer the data it is going to show in the output window.
April 23, 2008 at 1:50 pm
It appears to be the results only.
Thanks everyone for the replies and suggestions. It helps to be able to bounce ideas to arrive at a solution.
Kevin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply