So you might already know, but I didn’t know, until I learned it, of course.
My first Windows 8 machine was my Surface Pro 3 and I LOVED the way it started up so FAST. Fast is good, right? I didn’t even bother to wonder WHY or HOW it was so fast. I just thought Moore’s Law was hard at work.
But then I noticed something very strange after I started doing most of my SQL Server testing on my Surface. Sometimes cache didn’t seem to be cleared. Sometimes temp tables would mysteriously be there right after starting up. Memory consumption was way too high. What was going on?
Then I found it. The reason Windows 8 (and now 10) can start up so fast is that they’re really not shutting down completely. There’s an explanation here:
One of the things it says is:
Instead of saving everything, Windows 8/10 saves just the OS kernel in a file on the hard drive and then uses it to while booting up, speeding the whole processing considerably.
And then there is an illustration that indicates that one of the things that gets started in a cold boot that doesn’t get started in this fast boot is services. And SQL Server is a service. So when I think I’m shutting down Windows, which includes shutting down SQL Server, I’m really not. The SQL Server service, with all the temp tables, plan cache, data cache and memory, is saved and then restored.
Yeah, fast is good, but it’s not always what I want. If I’ve already started up and I really need to restart SQL Server, I can just restart the service from Management Studio. I even created a stored procedure to quickly tell me my approximate start time, so I can know if it’s been just a few minutes, or actually days since my SQL Server was last started:
CREATE PROC dbo.sp_starttime as
SELECT create_date FROM sys.databases
WHERE name = 'tempdb';
Then I can just execute sp_starttime to see when my last restart was.
As an alternative to stopping and restarting the SQL Server Service, I could do a Windows Restart. That option, as opposed to Windows Shut down, will actually stop everything, including all services.
At first, I was so shocked that Windows really wasn’t shutting down when I asked it to, I wanted a way to turn it OFF. So I found this article:
It says to go to Power Options and Choose What the Power Button Does. You’ll get a screen like this one:
The instructions just say to uncheck the box next to Turn on fast startup, but I found I wasn’t able to do that; the checkbox was inaccessible (greyed out). I finally found a really tiny message up near the top, that I have pointed to with a red arrow, that I needed to click on to enable the checkboxes. Then there is a Save changes button at the bottom, which I didn’t capture in the screen above.
I did that. And lived with no fast startup for a while. But then my Surface broke, and I had to get a new one (yes, it was covered under my service agreement.) But after setting up the new machine, which came with the fast startup as default, I realized I had missed it. So for now I’m leaving it on. I just remember to restart the SQL Server service before I start any testing.