Denali CTP3, Juneau CTP3 & SQL Server 2008 R2 sp1 Available

 

Let me give a different order:

SQL Server 2008 R2 sp1

Yes, it is finally available.  Check out the links below.

• SQL Server 2008 R2 SP1
• SQL Server 2008 R2 SP1 Express
• SQL Server 2008 R2 SP1 Feature Packs

You can download the files based on the processor you have.  In addition to bug fixes some interesting enhancements too are added to this service pack.

Dynamic Management Views for increased supportability:

sys.dm_exec_query_stats DMV is extended with additional columns to improve supportabilities over troubleshooting long-running queries. New DMVs and XEvents on select performance counters are introduced to monitor OS configurations and resource conditions related to the SQL Server instance.

FORCESEEK for improved querying performance :

Syntax for FORCESEEK index hint has been modified to take optional parameters allowing it to control the access method on the index even further. Using old style syntax for FORCESEEK remains unmodified and works as before. In addition to that, a new query hint, FORCESCAN has been added. It complements the FORCESEEK hint allowing specifying ‘scan’ as the access method to the index. No changes to applications are necessary if you do not plan to use this new functionality.

Data-tier Application Component Framework (DAC Fx) for improved database upgrades:

The new Data-tier Application (DAC) Framework v1.1 and DAC upgrade wizard enable the new in-place upgrade service for database schema management. The new in-place upgrade service will upgrade the schema for an existing database in SQL Azure and the versions of SQL Server supported by DAC. A DAC is an entity that contains all of the database objects and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects. For more information, see Designing and Implementing Data-tier Applications.

Disk space control for PowerPivot:

This update introduces two new configuration settings that let you determine how long cached data stays in the system. In the new Disk Cache section on the PowerPivot configuration page, you can specify how long an inactive database remains in memory before it is unloaded. You can also limit how long a cached file is kept on disk before it is deleted.

As you may aware, SQL Server 2008 R2 is not supported on Windows XP.

On feature packs, some of the cools stuffs are added.  The list below is my favourites

• Report Builder 3.0 enhancements:

Additional visualizations including maps, spark lines and data bars are introduced.  It also comes with report part gallery. It means you can develop report parts and add them to the reports you want.

• Microsoft® System CLR Types for SQL Server® 2008 R2

This allows the CLR based data types of SQL Server, geography, geometry and hierarchyid to be installed outside the server. By doing so, a client application can use the same data types. 

• Microsoft® SQL Server® 2008 R2 Remote Blob Store

I first heard about it as part of Denali.  However, I am surprised to see it as part of feature pack.  This component allows the data to be stored in an external data store. Microsoft is providing a client side DLL and SQL Server stored procedures

• Microsoft® Windows PowerShell Extensions for SQL Server® 2008 R2

How can I hide my joy to see something connected to PowerShell. In fact one of my favourite tools is PowerShell. It allows server administration, irrespective of whether it is Windows,  SQL Server or Exchange, through a set of cmdlets.  It eases the tasks of administrators.  I hear that a couple of new cmdlets and a new provider have beed added.  Need to have a look.

Denali CTP 3

Despite some earlier announcements, CTP3 was made available to public on 12th.  Denali is the next version of SQL Server.  Earlier references said it would be released in the second half of 2011, yes we are already there.  However, Now the messages are suggesting that it will be named as SQL Server 2012 indicating the release date to end of this year, or somewhere in next year.

Let me warn you that you way start downloading files of 2.4 GB of size. Do you have the facility to do so? then go for.

Want to download? Try this link:

https://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/

I’ll write about the features of Denali on a different post.

Juneau CTP3

It gives a different user interface, to database developers. It allows Visual Studio to be used to create all database objects from table to stored procedures. People who are familiar with “Data Dude” or “Visual Studio for Database Professionals” can consider Juneau as the next generation of that.

There is little more on it:  You can do all design in your local machine, and deploy to SQL Server or SQL Azure. you don’t need different tools to find the changes happened, it is included in Juneau. Additionally, it all in one source control..

Try this link:

http://msdn.microsoft.com/en-us/data/hh297027

There are many more internal projects, Apollo, Velocity, Crescent and many more are on the pipeline. The information we receive are exciting. Lets wait to see them too.

Understanding Page Splits

Note: I thought of writing about page splits. Initially I started writing a blog and it slowly became a huge article. I was having two minds on whether I should write a series, of blogs or go for a big article. Finally, I have made up my mind for the first option and here is the first part of the article. My plan is to write different aspects of page splits in coming days…


In SQL Server data is stored into a page. A data page is 8 kb of size. It means when the rows are inserted they need to be inserted into a page. There is an exception when large blob data is inserted sql server can insert the data separately into another page and keep only the pointer (For more details refer: row overflow). Apart from that, a row should fit into the page and cannot split into multiple pages.

The decision on where the data should be inserted is decided by the clustered key. The data is logically stored in clustered key order. (I’ll discuss later on why the word “logically” is used here. But it is safe to assume the order of the pages is clustered key order) When a row need to be inserted or updated but it cannot fit into the empty space, the content of the row splits into two pages (without breaking the row) and it is called page split.
There are primarily two reasons for it to occur:
· Data inserts not in clustered key order
· Update of variable length columns
Let’s analyse each scenario:
Now let us assume we have a table which has a row size of 1300 bytes. It means a fully occupied page will have 6 rows. Let us assume that we have a table where a particular page has 5 rows having the clustered key values 2,4,6,8, 10 and 12. Now a new row is inserted with the clustered key of 5. It should be inserted into the same page as the value 5 falls between 4 and 6. But the page does not have space and it needs a new page. However, if the only the new row is copied into the new page, the clustered key order cannot be maintained.
Now SQL server will perform the operation of page split. It will create a new page and copy half of the rows into the new page. Now both pages will be half empty, so that the new page will be inserted into the page it needs to be inserted. In this case the first page will have rows 2,4,5 and 6 and the next page will have rows 8, 10 and 12.
The following code illustrates the point:
 

CREATE TABLE dbo.Note

(

    NoteID int NOT NULL CONSTRAINT PK_Note PRIMARY KEY CLUSTERED (NoteID),

    NoteText varchar(2000) NOT NULL

)

GO

INSERT INTO dbo.Note VALUES( 2,'Some notes here.')

INSERT INTO dbo.Note VALUES( 4,'Some notes here.')

INSERT INTO dbo.Note VALUES( 6,'Some notes here.')

INSERT INTO dbo.Note VALUES( 8,'Some notes here.')

INSERT INTO dbo.Note VALUES(10,'Some notes here.')

INSERT INTO dbo.Note VALUES(12,'Some notes here.')

As the data is ordered on clustered key (i.e. NoteID) this will not create page split. However, when the data for the next day is inserted it will cause page split.

INSERT INTO dbo.Note VALUES( 5,'New notes here.')

The reason for page split is it cannot hold the all the rows in the page. When a page split happens the row will be divided into two and the second half of the data will be moved into the new page.
There is another reason for page splits. Assume you have a variable length column in the table. Assume it is defined as varchar(2000). Since it is a variable length column, it will not occupy all 2000 bytes. Lets take this example:
 

CREATE TABLE dbo.Note

(

     NoteID int NOT NULL CONSTRAINT PK_Note PRIMARY KEY CLUSTERED (NoteID),

     NoteText varchar(2000) NOT NULL

)

GO

INSERT INTO dbo.Note VALUES ( 1,REPLICATE('a', 900))

INSERT INTO dbo.Note VALUES ( 2,REPLICATE('a', 900))

INSERT INTO dbo.Note VALUES ( 3,REPLICATE('a', 900))

INSERT INTO dbo.Note VALUES ( 4,REPLICATE('a', 900))

INSERT INTO dbo.Note VALUES ( 5,REPLICATE('a', 900))

INSERT INTO dbo.Note VALUES ( 6,REPLICATE('a', 900))

INSERT INTO dbo.Note VALUES ( 7,REPLICATE('a', 900))

INSERT INTO dbo.Note VALUES ( 8,REPLICATE('a', 900))

Even though the NoteText is specified to have 2000 bytes, they hold only 900 bytes. Now when a row is updated with a higher length string, it may need space more than what is available.


UPDATE dbo.Note SET NoteText = REPLICATE('c', 2000) WHERE NoteID = 3
Since there is no space to accommodate the additional 1100 bytes, it needs a page split. Again the page is divided where the original page will have only four rows ( NoteID <=4) and rest will be in new page.
There are couple of differences between a new page getting added and page split.

1. Data growth in the order of clustered key leads to new pages getting added. But it ALWAYS happen at the end of the page. Page split refers to a page getting inserted in the middle of the list
2. Adding new page starts with the blank page and only updates a pointer in the previous page.  When page split occurs half of the rows are getting copied into the new page.

There are there any more reasons for page splits? Lets look it in another blog…   

Speaking Again: Wait Statistics

Yes I am back from Hibernate mode.   Due to various reasons I couldn't update my blog for four long months. A couple of my friends were repeatedly reminding me.  Now, I am starting again.
This month,  I am scheduled to speak about Wait Statistics at SQL Server Sri Lanka User Group (SSSLUG).  I gave this topic somewhere in the beginning of this year, By I need to wait till this month to speak about wait statistics.

Wait statistics is a key part in tracking down application and server performance issues. I believe it is very important to Server Administrators, DBAs and Developers

Due to Vesak holidays, the meeting is postponed to next week and I am expecting an official notice soon.
See you all there...

MVP Award

As some of you may know,  Microsoft has sent me a new year gift.  I have been awarded with MVP status.
I thank all those recommended and proposed my name for this award.  this is really an encouragement to the work and service I do.
I was never satisfied with all the work I have done in the past. I know as a fact that there is more I can do.

SQL Server 2008 Microsoft Certified Master (MCM) Readiness Videos

Thanks to Brad McGehee. (Linkedin)
He is the one who first mentioned about this link. (or that is where I saw it)
When I went to the site I was able to discover some more content deep and simple.
Do you want to know about the internals, and like to analyze various ways of handling things, this is something great to visit.

http://www.microsoft.com/feeds/TechNet/en-us/How-to-videos/SQL_Server_2008_Microsoft_Certified_Master_%28MCM%29_Readiness_Videos.xml

I am speaking today at SSSLUG on Excution Plans

Today the sessions include an introduction to Denali and the second part of Execution Plans Explained. Today's session focuses on specific items in the execution plan:  Seeks, Scans and Physical Join operators.

Bug? - Multiple drop and add columns can cause error

This issue was first reported by my friend and colleague at eCollege Tim Sesst. We did a lot of research on this. Dinesh Asanka, Susantha and I really got into this.  Initially we thought this as a feature, but it took some time for us to understand the issue. Now we feel that it is a bug.

Before going any further, check this code:

IF OBJECT_ID('TestAlterTable') IS NOT NULL

    DROP TABLE TestAlterTable

create table dbo.TestAlterTable

(

     Id int identity not null CONSTRAINT PK_TestAlterTable PRIMARY KEY CLUSTERED

     ,col1 char(8000) not null

     ,col2 char(49) not null

)

go

Declare @i int =1

While @i <100

begin

print @i

IF OBJECT_ID('DF_Col2') IS NOT NULL

    ALTER TABLE dbo.TestAlterTable DROP CONSTRAINT DF_COl2

Alter table dbo.TestAlterTable Drop Column Col2

Alter table dbo.TestAlterTable add Col2 char(49) NOT NULL CONSTRAINT DF_Col2 DEFAULT('a')

set @i+=1

end

Based on the number of columns and the data types you have, at one point,  you may receive an error similar too this:

.Net SqlClient Data Provider: Msg 1701, Level 16, State 1, Line 9
Creating or altering table 'TestAlterTable' failed because the minimum row size would be 8061, including 8 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.
 
 The number of times you need to drop and add columns depend totally on number of columns and data types used. 
Initially we thought that this was due to cleaning of data of the dropped columns from data pages.  Like the ghost entries, (where the row is deleted logically, but the data is still there in the data page) I was even prepared to name this action as ghost columns but, sorry, I was wrong. This happens at the meta data level.

Still we haven;t figured out the reason behind this.  But based on our research, it has all the behaviors of a bug. It does not happen at the first time. 

Interestingly, if you rebuild the primary key, you can continue on the same path for another fixed number of times.

If anyone knows a good reason to say, why this is not a bug, please let me know.
Thanks to all the guys at eCollege who worked on this. 

Index Remained Fragmented EVEN AFTER Index Rebuild Job

I had an interesting thing to find out this week. An Organization had an issue: Their queries are running extremely slow. They did some research on this. They found that even simple queries which uses index seek too are extremely slow. They copied the data into another database and created the indexes and executed again. They were quite fast. There were no other processes running when this test was carried out.

Through another friend of mine they got my contacts and they called me. I got the execution plans from both databases and they were identical. Then with the help from them, I ran a query using sys.dm_db_index_physical_stats against this index and found that it is fragmented. They rebuilt the index and the query started working well again.

Using the same DMV against other tables, I found out that the indexes are fragmented. They sent me the figures they received using dm_db_index_physical_stats Fragmented means some are 80% - 95% fragmented. To be honest with you, I even saw a few indexes showing 100% fragmentation which I have never seen before. I asked them to rebuild the indexes. Later I explained them how these house keeping jobs could be automated.

After a couple of weeks, they called me again. The indexes are fragmented again. I asked them whether they have added the index maintenance job. They answered back saying they have an index maintenance task as part of the database maintenance job and even made it as the first task of the job, [which runs on Sunday nights] but that has not resolved the issue.

Red gate labs

Red gate has opened its lab for public viewing!

It is web site, where the tools developed are available for download. They include
Log Shipping Monitor, SQL Backup to MTF Converter (which converts a red-gate backup to native SQL Server backup and Red Gate Snapper (that allows you to create SQL Compare snapshot files for SQL Server databases).

The lab does include tools for .Net developers. Red Gate Memory Tracker (analyzing application memory usage, including loaded libraries, system heap memory and .NET memory) and Diagnostic Tool (used to collect useful system information) will help serious developers.

These are tiny tools, which i believe Red-gate may include or have included with their products. Now these tools come as free downloads.
I haven't tested all the applications yet, (in fact I haven't tested any of them) but I feel that most of them are really useful.

Try this link
http://labs.red-gate.com/

NO Recompilations when (parameter) values are different

Please read another post on this subject by Dinesh Priyankara.
http://dinesql.blogspot.com/2010/10/do-we-need-to-recompile-stored.html

This is another topic came in the last user group meeting: 
Does SQL Server generate a new plan (in other word recompile) the query if the parameter value is changed?
The actual answer is it depends:
First of all, SQL Server does not go through the recompilation process simply because the parameter is changed.
As I mentioned in the previous blog, there are only certain conditions where an existing execution plan is thrown away and a new plan is used.

Even though in my example, a new plan is created between these two statements, the value is not the cause.

DBCC FREEPROCCACHE

GO

SELECT * FROM Sales.SalesOrderDetail

WHERE SalesOrderDetailID <= 1

GO

SELECT * FROM Sales.SalesOrderDetail

WHERE SalesOrderDetailID <= 1000

GO

When I ran the queries and captures the SQL:Recompile event, nothing was captured. Interestingly, ShowPlan XML for Query Compilation too, didn't give any number as the reason for recompilation. For me it looked like SQL Server didn't consider them as same query.
I removed the GO statement in between and tried too. The result was same. (except the column line number showed 1 and 2 instead of 1 always.
The image is attached.

Fortunately, SQL Server provides a few DMVs (dynamic management views) which allows us to check the execution plans