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/