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

Recompilations after index creation

Yesterday I got an interesting question: Does adding an index to a table force the execution plans to be recreated?
Books online says this:

 

Please note the first, second and fourth bulleted points.

It says either a structure should be changed or index used by the query should be changed. In other words not any change in the index will not force recompile.

Today I will be speaking at the user group meeting

As some of you know, SS SLUG (SQL Server Sri Lanka User Group) meets today. Generally we have two topics to discuss. 
According to the plan we had earlier, I will be in the panel of the second topic (database design)
But due to certain unforeseen issues, at the last minute, I accepted the challenge to speak at today's user group meeting. That means I will speak on execution plans first and then will be in the panel for the second topic too.

If everything goes well with the plan, In today's presentation, I will talk about reading execution plan, various table and index operations, and the logic behind it. At the end of the session, I expect the users to guess the execution plan for simple queries


See you there!

Trigger Happy at SSSLUG

Hope you were there at the last user group meeting!

I was talking about triggers.  This was a beginner level session.
The code samples and the slide deck are uploaded at SSLUG.  you ncan download them from here:
http://sqlserveruniverse.com/v2/SSSLUG/Downloads.aspx