Should Denali work on Windows XP

Microsoft release a list of Operating Systems (OS) and SQL Version Upgrade Paths that will be compatible with SQL Denali, SQL 11, aka SQL Next or whatever it will be called,  the next version of SQL.

1) The current support matrix for OSes is as follows:

·    Windows Vista SP2 or later

·    Windows Server 2008 SP2 or later

·    Windows Server 2008 R2 SP1 or later

·    Windows 7 SP1 or later

2) Denali will support upgrading from these SQL Server versions:

·    SQL Server 2005 SP4 or later

·    SQL Server 2008 SP2 or later

·    SQL Server 2008 R2 SP1 or later

3) The installer is going to block installation on unsupported OSes and it will block unsupported upgrade paths.

Go and leave your comments, get your thoughts in because this is your chance.

“So Balls”, You say, “What’s your opinion?”

Well Dear Reader I’ve got a couple thoughts I’ll give those and then I’ll give you the post I put in place.

IT’S ALL ABOUT THE CUSTOMERS

No matter what industry you are in you serve a customer.  Every year Microsoft has a lot of people whose sole job is to find out how many Microsoft Licenses you company has and how many it needs to have.  They work out Licensing deals and companies pay money.  When this occurs Microsoft get’s an inventory of sorts that let’s them know what people are using. 

I don’t know how granular that gets, I have no access to that information, I can only speak for myself.  Every job I’ve had since graduating college or while I was still in college has been using computers that ran Windows XP.  It has been around a LONG time.

So I would hope that the numbers Microsoft get’s would tell them what Percentage of customers still running XP.  You would then assume a natural upgrade trend, whatever the average is they see in their analysis.

I would then hope that they look at the number of hours it would take to make Denali work on XP or any other OS, based off the amount of work, the talent and cost of the team (and there is some serious Talent working for them) vs. the Revenues they project to get from licensing. 

Then I would look at the number of shops that run a lot of SQL Server and what their XP breakdown is.  If it doesn’t make Dollars and Sense they shouldn’t do it.

MAYBE THEY SHOULDN’T

“But Balls,” You say, “Earlier you told us you would like to see it support XP?   WTH?”

Ahh Dear Reader, I would like it.  It would make it easier for me to demo at work, and sell to management.  Which would work out to some of those Licensees that I talked about up top.  I’ve Windows 7 at home, and I’ll install it and learn the heck out of it there. 

But what is right for me might not be right for Microsoft.  Or it could be, it would all depend on where their customers are.

Even here at work, there is an upgrade strategy that will eventually be put into place, but that may be months or a year or more from now, those decisions are currently above my pay grade.

So finally here is what I wrote.  In the end it is up to Microsoft.  And as they are with the Clouds so am I with them, when it comes to Microsoft “I’m All IN!”.

Hi Dan,

   I'm going to disagree with the crowd, I think support for XP should be put in place.  I'm on Windows 7 at home, but work is still on XP slowly moving to 7. 

For some of us IT is a fight, it is a fight to get our companies to adopt the latest and greatest, and sometimes the big ships turn slowly.  This would certainly hurt my ability to sell the technology internally and I would assume I'm not alone in this.

As a SQL DBA I feel by default I'm a Microsoft Advocate.  In IT there are 50 ways you can do anything, I choose to do those 50 things using Microsoft and predominately SQL Server.  I Advocate moving to new technology and using the latest and greatest.  But it was a fight to use Powershell 2.0, Mirroring, 2008 R2, and Policy Based Management. 

I won't stop Advocating but it will make my fight more of an uphill battle if I cannot install on XP.

Thanks,

Brad

SQL Rally Q&A: Where is the SQL Engine does Compression Occur

This is another follow up on the Q & A’s that came about from SQL Rally, this question was asked by Jim Murphy (@SQLMurph | Blog).   Jim asked “Where in the SQL Engine does Compression Occur.”  Well Dear Reader the simple answer is in the Access Methods, but let’s dive a little deeper.

THIS IS YOUR BRAIN ON SQL

If you think of the SQL Server Engine as a brain, which has 2 hemispheres right and left, it has 2 parts the Relational Engine (right) and the Storage Engine (left). 

The Relation Engine receives a Query from the end user and it Parses it for syntax, Checks for a Query Plan, Retrieves it or Creates and Caches an execution plan, and finally it begins Executing the instructions to retrieve the Data.

This is where the Storage Engine comes into play.  The Relational Engine says here is what I want, and makes a Grocery List.  The Storage Engine takes that list and goes to the store to get all of the groceries. 

The Storage Engine receives the instructions of the Data that is required by the Relation Engine via the Access Methods.  The Access Methods will ask the Buffer Manager if this information is already in the Buffer Pool as part of the Data Cache.  If the Buffer Pool does not have the data in the Buffer Pool (ACTIVE MEMORY) then it is read off of Disk into the Buffer Pool.

As I mentioned earlier the Part of the Storage Engine that handles Compression is the Access Methods.

SO AMERICA WHY DOES THIS MATTER?

“So Balls,” You Say, “Why is this so important?”

Well Dear Reader it is because when the data is in the Buffer Pool (Active Memory) it is still in a compressed state.  So you are using the memory on your server more efficiently.  Not only is compression saving you space on disk, I/Os being read off of Disk, but the space occupied by the cached data pages is used more efficiently. 

A good example of why this is important is Transparent Data Encryption, TDE.  TDE is manage by the Buffer Pool.  That means that the data is unencrypted when it is read off of the disk and into the Buffer Pool Data Cache.  Because Page & Row Compression are managed by the Access Methods the Data is still compressed in Memory.

Another good example is when using backwards compatibility.  I've blogged about being able to use TDE on a SQL 2008 R2 Database using compatibility level 80 as well as a SQL 2008 R2 Database using Page & Row Compression using compatibility level 80.  The reason is that backwards compatibility changes the rules used by the Optimizer in the Relational Engine.

 The Storage Engine is not affected by compatibility level 80.  So knowing how SQL operates under the covers will show you how you should expect it to act, and help you know how to use the product to it's fullest extent.

Want to learn a little more about SQL Server Internals?

A great book on that is Professional SQL 2008 Internals and Troubleshooting, it is a wonderful read and there are more SQL top names on this book than you can shake a stick at.  I recommend having this book in your library.  It will help you on your way to discover more about how SQL works Internally, I know it helped me!

Thanks,

Brad

SQL Rally Q & A: Page Compression, Heaps, and Forwarding Pointers

One of my favorite parts of presenting is the questions you get.  It is always nice to hear the questions people have, because it helps you to expand your knowledge.  I got three questions after my presentation that lead to really nice follow up conversations, and I felt each answer deserved a blog.  One was from SQL MVP Louis Davidson (@DrSQL|Blog)

The question was, and I’m paraphrasing “Does the behavior Forwarding Pointers change in Heaps?”  The short answer is no, and Dr. SQL knew this but was still kind (and smart) enough to ask, but Dear Reader let’s prove it out just for fun.

THERE’S NO DATA HERE JUST POINTERS

Yesterday I wrote about Heaps & Compression, this was to give a bit of an introduction to this topic.  So as we covered yesterday when a Page on a Heap has Page Compression applied to it, subsequent inserts are not in a Page Compressed format, there are three exceptions to this for Bulk Insert, Insert Into with (TABLOCK) specified, or when Rebuilding a table specifying Page Compression.   *Actually it is 4, after you Rebuild your Heap once specifying Page Compression, you can rebuild it after and it will be Page Compressed, unless you specify a different type of Compression.

Let’s start off by creating our Demo Database.

USE master

GO

IF EXISTS(SELECT * FROM sys.databases WHERE name='demoCompressionINTERNALS')

BEGIN

    DROP DATABASE demoCompressionINTERNALS

END

CREATE DATABASE demoCompressionINTERNALS

GO

USE demoCompressionINTERNALS

GO

Now let’s add 460 rows of Data maxed out so that we fill our uncompressed pages.  I’m adding so many rows because I’ve optimized these records for Compression and I want to pack my Page tight so I can guarantee that I will get a Forwarded Record.

DECLARE @i INT, @myID INT

SET @i=0

WHILE (@i<460)

BEGIN

         SET @myID=(SELECT (MAX(myid)+1) FROM dbo.heap1)

         INSERT INTO dbo.heap1(myChar1, myChar2)

         VALUES(

                  (REPLICATE('a', (100- LEN(@myID))) + CAST(@myID AS VARCHAR(3)))

                  ,(REPLICATE('b', (500- LEN(@myID))) + CAST(@myID AS VARCHAR(3)))

             )

    SET @i=@i+1

END

Set Trace Flag 3604 on, so we can use DBCC Page with output to SSMS later.  And here’s a DBCC IND to view our Pages.

DBCC TRACEON(3604)

GO

DBCC IND(demoCompressionINTERNALS, 'heap1',1)

Now let’s apply Page Compression.

ALTER TABLE dbo.heap1

REBUILD WITH (DATA_COMPRESSION=Page)

GO
Here’s another DBCC IND to view our new Pages.

DBCC IND(demoCompressionINTERNALS, 'heap1',1)

If we do a DBCC Page we see Page 170 is empty and Page 224 has our records.  Let’s do a quick view to see the our Page Headers to validate the space.

Page  170

m_pageId = (1:170)                   m_headerVersion = 1                  m_type = 1

m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x0

m_objId (AllocUnitId.idObj) = 28     m_indexId (AllocUnitId.idInd) = 256 

Metadata: AllocUnitId = 72057594039762944                                

Metadata: PartitionId = 72057594038845440                                 Metadata: IndexId = 0

Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (0:0)

pminlen = 4                          m_slotCnt = 0                        m_freeCnt = 8096

m_freeData = 96                      m_reservedCnt = 0                    m_lsn = (50:93:13)

m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0

m_tornBits = 0                      

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED              

PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED

Page  224
 

m_pageId = (1:224)                   m_headerVersion = 1                  m_type = 1

m_typeFlagBits = 0x80                m_level = 0                          m_flagBits = 0x0

m_objId (AllocUnitId.idObj) = 28     m_indexId (AllocUnitId.idInd) = 256 

Metadata: AllocUnitId = 72057594039762944                                

Metadata: PartitionId = 72057594038845440                                 Metadata: IndexId = 0

Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (0:0)

pminlen = 4                          m_slotCnt = 460                      m_freeCnt = 5

m_freeData = 7267                    m_reservedCnt = 0                    m_lsn = (50:93:65)

m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0

m_tornBits = 0                      

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED           PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL

DIFF (1:6) = CHANGED                 ML (1:7) = NOT MIN_LOGGED           

So now let’s update Row 4, a rewrite will place this data in a Row Compressed Format.

UPDATE dbo.heap1

SET myChar1 = (REPLICATE('c', 99) + CAST(4 AS VARCHAR(1))),

     myChar2 = (REPLICATE('d', 499) + CAST(4 AS VARCHAR(1)))

WHERE myID=4

Let’s take a look at Page 224 again, and we’ll see that a forwarding record is in place of our Data.

DBCC PAGE('demoCOMPRESSIONInternals', 1, 224, 3)

GO

Slot 3 Offset 0x1c59 Length 9

Record Type = (COMPRESSED) FORWARDING_STUB                                Record size = 9

CD Array

Record Memory Dump

63FBDC59:   09aa0000 00010000 00†††††††††††††††††    ª.......               

Forwarding to  =  file 1 page 170 slot 0

And it tells us right there, if we look on Page 170 then we see there is our Data Record, sitting in Slot 0.  So let’s look at page 170.

m_pageId = (1:170)                   m_headerVersion = 1                  m_type = 1

m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8

m_objId (AllocUnitId.idObj) = 28     m_indexId (AllocUnitId.idInd) = 256 

Metadata: AllocUnitId = 72057594039762944                                

Metadata: PartitionId = 72057594038845440                                 Metadata: IndexId = 0

Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (0:0)

pminlen = 4                          m_slotCnt = 1                        m_freeCnt = 7474

m_freeData = 1336                    m_reservedCnt = 0                    m_lsn = (50:171:6)

m_xactReserved = 0                   m_xdesId = (0:1514)                  m_ghostRecCnt = 0

m_tornBits = 0                      

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED              

PFS (1:1) = 0x61 MIXED_EXT ALLOCATED  50_PCT_FULL                         DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 1

myID = 4                            

Slot 0 Column 2 Offset 0xc Length 100 Length (physical) 100

myChar1 = ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc4

Slot 0 Column 3 Offset 0x70 Length 500 Length (physical) 500

myChar2 = ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd4

So the short answer is that it doesn’t, change the way that Forwarded Records behave, but it sure is a lot of fun to play around with.

Thanks,

Brad