Did You Know: Windows Fast Startup is not really a StartUp!

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:

http://blog.gsmarena.com/windows-8-to-have-a-hybrid-shutdown-boot-times-as-fast-as-8-seconds/ 

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:

USE master
GO
CREATE PROC dbo.sp_starttime as
SELECT create_date FROM sys.databases
WHERE name = 'tempdb'; 
GO 

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:

http://mywindowshub.com/fast-start-up-in-windows-8/

It says to go to Power Options and Choose What the Power Button Does.  You’ll get a screen like this one:

fast startup.png

 

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.

Enjoy!

~Kalen

Geek City: Intent to Update

In the last couple of posts, I've told you about Intent Locks and UPDATE locks. I want to just provide a wrap up to those posts that talks about both of these aspects of locking.

You can think of both of these locking aspects as although SQL Server to indicate an intention. However, Intent Locks have to do with the unit of locking and UPDATE locks have to do with the type of lock. 

Here's how you might think about the 'intention':

If you get individual rows locks, you might have the intention of getting more row locks on the same page or table, so you might eventually want to lock the entire page or table. To make sure the page or table is available when you're ready to lock it, SQL Server acquires an Intent lock on the larger units. If you have a row lock, you'll get Intent locks on the page and the table containing that row. Intent locks can go with any type of lock: we can have Intent-Shared (IS), Intent-Exclusive (IX) and even Intent-Update (IU).

UPDATE locks are a way of SQL Server stating your intention to change the type of lock. If you are searching for data to modify, you might intend to eventually get an X lock. So you get U locks while you're searching (instead of S locks)  and then X locks when you find the data to modify.

You might also want to note that although you can get an IU lock on a page, at the table level an IU lock becomes IX. You can see that in the output I showed you in my UPDATE lock posting, which I repeat here, with a couple of extra callouts for emphasis:

figure 1.png

 

Session 64 is requesting a U lock on a key, and it already holds an IU lock on the page that contains the key, but an IX lock on the object.

I'm thinking that soon I'll write a post about interpreting some of the other information that shows up in the sys.dm_tran_locks view, for example the entity and the resource_description.  But not today…

Have fun!

~Kalen

P.S. And for LOTS more details on how to deal with and troubleshoot Locking and Blocking, I'm offering a half day class on June 14! 

https://www.sqlserverinternals.com/events/2018/3/28/online-class-sql-server-concurrency-control

Geek City: What gets logged for index rebuild operations?

This blog post was inspired by a question from a future student. Someone who was already booked for my SQL Server Internals class asked for some information on a current problem he was having with transaction log writes causing excessive wait times during index rebuild operations when run in ONLINE mode. He wanted to know if switching to BULK_LOGGED recovery could help.

Read More

Geek City: What do you intend with that lock?

Way back in the olden days, prior to SQL Server 7, I already knew that the lock manager was one of the most complex and resource intensive parts of SQL Server. Keeping track of every lock held, who was waiting for that lock, who could be granted the lock, and who was next in line for the data if the lock was released was a lot for SQL Server to keep track of. I admit, I was worried about the promised feature of row-level locking, thinking that it was going to be orders of magnitude more expensive. Without going into all the details of the complete rewrite of the locking implementation for SQL 7, let’s just say that my fears were quite exaggerated.

Read More

Geek City: sp_cacheobjects for SQL Server 2017

Several times over the last decade, I've posted a version of a view that lists all the compiled plans in SQL Server's plan cache. It's based on the old pseudo-table syscacheobjects, returning basically the same columns with extra filters added. Over the versions, Microsoft keeps showing me more and more internal operations in the plan cache, that usually just clutter up the output, so I keep having to add filters to get rid of the unwanted rows.

Read More

Geek City: Changing How To Change Your Database Properties — ALTER DATABASE

Long ago (in SQL Server years) many metadata changes were implemented with special one-off stored procedures. For example, we had sp_addindex and sp_dropindex, as well as sp_addtypesp_droptypesp_addlogin and sp_droplogin. For changing certain database properties, we had sp_changedbowner and sp_dbcmptlevel, to name a few.

Gradually, Microsoft has started replacing these procedures with the more generic DDL commands ADD, DROP and ALTER. This is both good news and bad news.

Read More