 |
| I could hear SQL saying to my C: Drive "Why You Little...." |
The Blog about SQL Saturday 232 coming shortly, but first I needed to blog about an error that I’m getting. I presented this weekend on SQL 2014 a First Look at What’s New. One of the demo’s I did was using Buffer Pool Extensions.
To confirm with what I’m seeing I tested this on 2 VM’s and one physical Instance.
VM1
I created VM1 on my SSD.
I allocated 4 processors and 8 GB of RAM for my VM. I created 3 drives for data files M: , log
files L: , backup files W:, Buffer Pool Extensions S: . I then Installed CTP1 using Oracle’s Virtual
Box. Max Memory set to 4096 MB.
VM2
I also confirmed these results by creating a Hyper-V VM on
our Host server in the Pragmatic Works Lab.
The guest has 4 processors and 8 GB of RAM. Max Memory set to 4096 MB. No BPE was used on this VM.
Physical Instance
I also tested this on my laptop on a SQL 2012 SP1 CU2
instance. I have 4 cores and 16 GB of
RAM. Max Memory set to 6144 MB.
I then used WinDirStat
to confirm the results. During my SQL Saturday
presentation my VM unexpectedly ran out of space on the C drive. This was a little confusing as I had
specifically set up my SQL instance to not be installed on the C drive.
“So Balls”, you
say, “What was taking up all that space
on the C drive?”
Great Question Dear Reader!
That’s want I wanted to know as well.
KILLING ME SOFTLY…
THEN NOT SO SOFTLY
This is the VM. As
you can see I didn’t go with a huge C drive, only 25 GB. The only drive I didn’t mention above was my
T drive that I used to store a database that I put a Clustered Columnstore
Index on.
I’ve got a script that I need to blog on called
MakeAdventureWorksDW_big, but I don’t have that typed up just yet. I use that script to make my
FactInternetSales_big table. I used my
friend and cohort Robert Cain’s (@ArcaneCode
| Blog) script Make
Adventure Works Modern for Today to make my AdventureWorks2013 Database.
My table has 42.9 Million rows in it and is a little over 8
GB. My test for PBE was pretty
simple. I created a 15 GB BPE. The instance has 8 GB. My max memory for the instance was set to 4
GB. Then I raised the max memory to 19 GB. I ran sys.dm_os_buffer_descriptors using the
is_in_bpool_extension to find the data pages as they were allocated.
SELECT DB_NAME(database_id) AS [Database Name]
, case is_in_bpool_extension
when 1 then 'Stored in BPE'
when 0 then 'Stored in non-BPE cache'
end as BufferLocation
,COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4
AND database_id <> 32767
GROUP BY DB_NAME(database_id), is_in_bpool_extension
ORDER BY [Cached Size (MB)] DESC
OPTION (RECOMPILE);
I then ran the following query to load up as much data as
possible.
Use AdventureWorksDW2013
go
set transaction isolation level read uncommitted
go
set nocount on
select * from [dbo].[FactInternetSales_big]
It is just a simple select statement. Rerunning my buffers query I could see things were loading up nicely.
My plan was to leave this query running so I could load all the data I needed to into my BPE. That is when the C: ran out of space and the VM froze on me.
In order to catch this bug I then ran
WinDirStat. Looking under my
C:\Users\SQLBALLS.PW\AppData\Local\Temp\1 folder I found this.
Hmmm…. right now not that bad still plenty of free space, but here’s
this temp file. Let’s fast forward to 3
minutes in. We’re now at 2 GB.
At 5 minutes we are at 3 GB.
Looking at my C Drive, I’ve now dropped from 10 GB of free
space to a little over 7 GB.
The result set is large enough that this will continue until
my C drive runs out of space. If I try
to delete the file I get a nice error letting me know that the file is locked
by SQL Server Management Studio.
15 minutes in my C drive is looking pretty scary. We are now in the red. I kill the query so I can keep looking at my
VM. The C drive doesn’t free up. I know from experience I could now delete the
temp file because it is no longer in use.
I asked the question on twitter and Jason Kyle(@JasonNKyle) replied with a
suggestion that maybe I was looking at a Swap file.
As I understand them a swap file allows an operating system
to use hard disk space to simulate extra memory when the system runs low on
memory. The way this behaves is that the
OS swaps a section of RAM that an idle program is using onto the hard disk to
free up memory for the program you are using.
Then when you switch back to the other program the OS trades out the
memory for bytes on the HD.
I don’t think this is a swap file though. I could be wrong.
When I open one of the smaller temp files in notepad, the
query results from my my query re-run so it is a much smaller result set. This is what I see.
Not really useful, except that I realized the first data
that I highlighted happened to be my sales order number from my query.
As I continue to mine through this I see that this is truly my
query result set.
If I run this same query on my laptop. 16 GB of RAM, 6 GB Max Memory, after a
restart with only around 1 GB in the buffers, I get the exact same
results. At first I thought this was an
issue with Buffer Pool Extensions. Now
it looks like this is just the way queries return on SSMS.
Granted you normally don’t run million row transactions in
SSMS. Your application runs them. So more digging to go, but I wanted to get
the blog out to attract comments and help show what I was seeing.
As always Dear Reader, Thanks for stopping by.
Thanks,
Brad
