SQL 2014 SSMS is Killing my C:

I could hear SQL saying to my C: Drive "Why You Little...."

 Hello Dear Reader! 

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

The 200 Billion Dollar Tweet

Hello Dear Reader!  Ever try to convince someone that they should be paying attention to Twitter?  A co-worker, Vice President of something or other, CMO, CEO, or CIO?  

I’ve had conversations over the years with many people as to why Twitter is important to their business.  If they don’t have a dedicated “Social Media” guru or their Corporate and Legal Policies do not yet “allow” an official Twitter presence, they can still effectively market, and monitor marketing, using Hash Tags.  It’s a way of dipping your toes in the water without really diving in.

Alas most of the conversations revolve around what “real” results they can gain from Twitter.  Most lack the vision or understanding to see how it could benefit them.  Tying a dollar amount to Twitter can be difficult.  Most companies don’t track or publish earnings related around a Twitter campaign.  They do with traditional advertising, but with social media it’s hard to tie a dollar amount to it.

I was a little busy yesterday with work.  Like most days I poked my head out on Twitter and checked several new sites.  I even received some push updates to my phone via USA Today, Fox News, and the New York Times.  So Imagine my surprise when this morning while standing in line at the Starbucks in the Charlotte airport that I see this.  Twitter Terror Hoax Rocks Wall Street, AARRRUUUU?

Learning about social media occurrences the next day via the new paper.  How old fashioned.  Loosing 200 Billion dollars over 80 typed characters, not so old fashioned.

Twitter first came out I laughed at it.  Why would anyone want to have a website version of Instant Messenger, remember AOL, limited to 140 characters?  Turn around several years and I have firmly flip flopped, Oxymoron, and have drunk the Twitter Kool-Aid.

What changed my mind?  Becoming a presenter in the SQL Server Community.  I went to my first SQL Saturday and caught the bug, read TSQL Tuesday #41 Becoming a SQL Server “Presenter” GETINVOLVED!  One of the things I did was examine the really good speakers and see what they were doing.  They blogged, they were on Linked-In, they were on Twitter.  So I did all of that. 

Only after the fact did I find #SQLHelp, the hashtag that allows people to ask whatever SQL question they have and get free advice from some of the Top SQL Talent in the world.  There were situations where I had production outages and we, my fellow DBA’s and managers, used Twitter to post questions and get deep technical answers quickly.

“So Balls”, you say, “YAY you’re on Twitter.  Now what about this 200 Billion dollar Tweet?!”

At 1:07 pm, East Coast Time, the Associated Press Twitter account, currently suspended, was hacked.  The 80 characters contained within the Tweet typed by the hackers seemed particularly potent given the turmoil of the last week.  “Breaking: Two Explosions in the White House and Barak Obama is injured”.  I’m not alone in my conversion, apparently the people trading on the New York Stock Market follow the Twitter as well.

By 1:09 pm the Dow had fallen over 137 points.  While it rebounded quickly, 1:12 pm when Sam Hananel (@SamHananelAP) Tweeted, “Please ignore AP Tweet on explosions we’ve been hacked”.  The market corrected.  But did it?

My Dad does a lot of day trading.  He’s studied up on it, and I’ve learned a tiny (very tiny) little bit about it vicariously through him.  You can take options out on Stocks termed calls, where you bet they will over or under perform.

Based on their performance you get your money back and a little extra.  The most notorious case of this came during 9-11 where some nefarious people had bet on the airlines to lose money.  In regular terms though this is used daily, sometimes you win, sometimes the company you bet on wins.  It's part of our system.

*It’s a lot more complicated than that, but we’ll stick with the very basics.*  Think betting, but using the stock market.  There are also automatic options that you can set through e-investing web sites.

So it could have affected Calls.  People could have lost money.

Another way that people could have lost money on this?  Using automated software to monitor stocks.  

Quick example, I like Disney.  So I go buy some Disney stock.  Disney is a non-essential good (basically).  They make entertainment, theme parks, dresses that my daughter likes to wear, movies my kids like to watch (DAD include on this).  I don’t NEED Disney stuff.  We get it with disposable income.  We NEED food.  The mortgage HAS to be paid.  Electricity, need that too!  So when there are hard times disposable good take a hit.  Look at the stock market when 9-11 happened, the Mortgage Crisis, and other such events.

So I buy 100 Disney stock at $35 a share for $3,500.  If something happens and Disney stock drops $10 per share, I could set up a pre-arranged sell order.  At $25 sell it all.  Let’s say the stock market tanks Disney stock because a Hacker gets into their official account and announces “Disney to go bankrupt, 10,000 employees to be laid off immediately”.

My Stock dips down to $23, while I’m at work.  My e-banking software kicks in and sells all my Disney shares.  My money is down to $2500 and I’ve just lost $1000.  When it is correctly reported that Disney is not going bankrupt and is having a record Quarter beating all earnings estimates, and the stock now corrects to $40 a share, I’m no longer a stock holder.  Not only did I take a loss, but I was out on the Win too.

I just lost $1000.  I just missed out on gaining $500.  There is no customer service department.  No receipt to be returned.  I’m out.  The market was correctly reported.  Based on my input a sell order happened.  Potentially A LOT of sell orders from all sorts of people happened. 

WHAT DOES THIS MEAN

Security and Reality.  First Security.  It means some real people lost real money yesterday.  We’ll have to see what the fallout of this is.  It also means that if you do have a Corporate Twitter account you probably need to look at changing the password every 90 days, and using something like KeePass to manage your password.

Warnings aside it also means we can now put a dollar amount on Twitter.  USA Today estimated that $200 billion dollars in broader market capital was lost when the Tweet occurred.  The market rebounded and that capitol was regained, with some people suffering losses.

Before I could even finish writting this blog, there was a new story.  SEC, FBI probe fake Tweet that rocked stocks.  And another Twitter working on two-step authentication. 

Now Reality.  The financial world is following Twitter and most other forms of social media.  The importance of effectively using this channel to communicate with customers and the world at large is only going to gain importance as time goes on.  The most expensive Tweet in the world so far is now worth $200 Billion dollars.  How much will the next one be worth?

As always Thanks for stopping by.

Thanks,

Brad

Top 5 Reasons You Should Be on Twitter

Yesterday for Monday Morning Humor I talked a little about how Twitter was a great resource for learning.  But before we do that I wanted to out myself.  The lovely Mrs. Balls still teases me about this, but I used to make fun of Twitter. I thought it was ridiculous when I first heard of it.  And I swore up and down that I would never use it. 

“So Balls”, you say, “What changed your mind?”

Excellent question Dear Reader, SQL Saturday 49 was the start of changing my mind.  I really enjoyed it and was inspired to become more active in the SQL Community.  I looked at what all the top people presenting were doing and wanted to push myself in that direction.  I created a blog, I joined Linkedin, and I joined Twitter.  But my mind wasn’t yet changed.

I fumbled around, added a couple of people that I looked up to or respected.  And by virtue of watching their conversations found more people in our industry, who I respected and were very active in the SQL Community.  Listening SQL MVP’s talk SQL on Twitter is like hearing the CIO tell you what their plans are for the future.  It’s an inside track into a conversation that you wouldn’t be privy to unless you were playing in the Big Leagues.  

A book that Brent Ozar (@BrentO | Blog) had written called The Simple Twitter Book really helped as well.  When my Dad signed up on Twitter I sent him a copy of this and said “You should read this ASAP”.   My Dad isn’t in the tech field but he has always been tech savvy, and Twitter can do a lot of things for a business owner.  There are a lot of good tips on etiquette as well as how to make Twitter interesting to you.  And Dear Reader if you are on Twitter and you haven’t read it, go grab a FREE PDF copy and get to reading!

TOP 5 REASONS YOU SHOULD BE ON TWITTER

1.  Paul Randal’s Twitter Feed

Paul Randal(@PaulRandal | Blog) is one of the Owner & Founders of a company named SQLSkills (@sqlskills), he’s a Regional Mentor for the SQL MVP Program, he lead the team that wrote the Storage Engine for SQL 2005 & the Access Methods for SQL 2008.  In other words he’s wicked smart in ways that most of us would like to be.  He and his company share a lot of information freely on Twitter.  And for a while they were doing a weekly Quiz, chocked full of good info on SQL.  As my cartoon yesterday indicated he will say something smart that you will want to remember.  And you can get a lot of learning from this.

2.  #SQLHelp

If you are having a work Problem and you and your co-workers are stumped, #SQLHelp is a great resource.  In Twitter the Hashtag, aka pound sign #, is used in front of a phrase to make it searchable.  SQL MVP Aaron Nelson (@SQLVariant | Blog) had a brilliant idea that if you need help with SQL throwing this phrase out there would be like our own mini Bat Signal, saying SQL GURU’s HELP OVER HERE, MAN THE LIFE BOATS, or CALL A LOCKSMITH.  However you phrase it, I need some help.  Some of the top minds in our field watch this, and respond during the day with suggestions and answers.  I don’t mind saying I’ve used this more than once and it has been awesome every time.

3.  Blogs

There are so many people whose Blogs I follow, or have learned of because of Twitter.  We as the SQL Community Love to write, we LOVE it like Romeo has this little crush on Juliet.  I’ve got over 240 people that I follow on Twitter, and I would say that around 200 of them Blog.  Whenever I write a new one it goes out on the RSS feed and I announce it on Twitter.  When SQL comes out with a new Service Pack someone blogs about it, when a new CTP comes out for the latest SQL Version 100 people blog on it.  If someone hit’s a bug, they blog on it.  A new technology comes out that your work is looking at, someone probably Blogged on it.  You will find the people you know you can listen to, and you will start watching for their blogs.

4. Events

There is a lot going on out there.  I love Dave Matthews, he’s not touring this year but he is doing a concert for Charity August the 20^th, up in Virginia…..where I moved from….., but it’s not Just Dave Matthews.  SQL Solstice taking place in North Carolina in August.  SQL Saturday's, like SQL Saturday 85 (in Orlando September 24th!),  SQL Rally, and SQL Server User Groups.

5.    #MCMPREP

      This is another brain child of a great SQL Blogger, Thomas Larock (SQLRockstar | Blog).  Back in November last year Microsoft announced changes to the Certification Program for a Microsoft Certified Master.  Tom had the brilliant idea of holding study sessions on Twitter, now in G+.  One day when I'm not at work and can actually hop onto one.  I'm planning on it.  I'm still ways away from this goal.  But I can't help but want to learn from the people that are already there, or are about to be there.  And I hope you would want to learn from them to! 


     So there you have it Dear Reader, my Top 5 Reasons for being on Twitter.  There are a lot more, but I'll let you go and find those reasons out for yourself.  Thanks for stopping by again.

      Thanks,

      Brad

 

Monday Morning Humor: Don't Forget Twitter!

Next Humor

Hello Dear Reader, after writting last week, How Do You Learn?, one thing became apparent to me very quickly.  I forgot Twitter!  More importiantly I forgot Paul Randal's (@PaulRandal | Blog) Twitter Feed!  And no sooner did I realize that when over the weekend there was discussion on Trace Flag 3422.   Twiter deserves it's only blog entry, because while Paul Randal's Twitter feed is awesome, there are a lot of other great thing's like #SQLHelp, and litterly hundreds of amazing bloggers!

I drew this up while doing some late night work and thinking on the subject.  I hope you enjoys this and I'll post more tonight!

Thanks,

Brad