Speaking at SQLSaturday #110 in Tampa,FL

I am happy to announce that my session on “Optimizing SQL Server I/O with Solid State Drives ” was selected for SQL Saturday #110 in Tampa, FL  on Saturday March 10, 2012!

Location is:
1001 East Palm Avenue,
Tampa, FL 33605

SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. Admittance to this event is free, all costs are covered by donations and sponsorships. Please register soon as seating is limited, and let friends and colleagues know about the event.
If you are attending please stop by and introduce yourself. Also if you are on Twitter don’t forget to use hashtag #sqlsat110 and see what others are doing.


Due to a family emergency, I had to cancel this speaking engagement and luckily my slot was filled by another speaker.I hope to make it next year!

Weekly links for 11-21-2011

SQL Server 2012 Release Candidate is now Available from SQL Server Team blog

The Data Scientist from BuckWoody

7 Scaling Strategies Facebook Used to Grow to 500 Million Users  HighScalabilty

10 Sloppy Social Media Mistakes to Fix NOW from Hubspot Blog

What is Hadoop? And Why is it Good for Big Data?  from The Data Roundtable

Hacker Says Texas Town Used Three Character Password To Secure Internet Facing SCADA System from ThreatPost

The AdventureWorks2008R2 Books Online Random Workload Generator from Jonathan Kehayias



SQLskills Community Mentoring and me

As some of you might be aware Paul Randal (blog | twitter) has just announced the launch of a new SQL Skills Community Mentoring program for a few individuals that took part in the SQL Skills Immersion Events. I’m deeply honored to be one of the select few that get access to invaluable advice from what I consider the best team in the SQL Server world.LukeAndPaul

Allow me to go back to a year ago (and boy what a great year 2011 was), when I could only dream of getting trained by Paul Randal and Kimberly Tripp, but my perseverance paid off and after multiple requests,  me and my buddy Vince managed to convince the company to send us to the first week of Immersion training in May this year especially since it was in the western suburbs of Chicago. Very soon I realized that this is by far the best training I have ever received, period. The amount of content was overwhelming (as Brad put it “it’s like drinking from a proverbial fire hose”) but the presentation style was extremely engaging and well prepared. Paul and Kimberly don’t just tell you “this is what you should/shouldn’t do” they go beyond that and explain why and how  and then they demo the whole concept. Just like a good teacher they can take a difficult topic  and present it in a way that makes it easy for you to follow and absorb  a lot of  information.
On the fourth day of the IE 1 class I volunteered to present a slide deck. I was extremely embarrassed as I felt like I was not in the same league as the rest of the folks present, but everything went so well that it made me reconsider my thoughts. As a matter of fact it made me submit  a couple sessions for SQL Saturday #82 in Indianapolis and this was the beginning of my public speaking adventure. A few days ago I actually presented the same slide deck from IE1 at SQL Saturday #96 in Washington, DC and the feedback was excellent.

Last month I continued my learning odyssey with the second week of training (IE2). Right away  the fact that we attended IE1 made a huge difference and it helped us understand a lot better the subjects presented to us. This time it wasn’t just Paul and Kimberly but Jonathan and Joe  have joined them adding their technical skills and expertise to the table.   I learned so many new things that I have already used in my environment.

To conclude, these classes have helped me to understand significantly better how SQL Server works. Things like PLE, forwarding records, heap fragmentation, index tuning, page structure, SAN performance, waits and stats, extended events, NUMA architectures, MCM program are only a few of the subjects that were explained in great detail.

Now on the last binary day of  the century all this becomes even better by having the unique chance to have someone like  Joe Sack as Mentor and working with him to advance my career. It’s been an incredible year for me and I look forward to work with my Mentor Joe Sack and  see what the next 6 months will bring !

Once more, thank you SQLskills. It is an honor to be in such good company.



We are family!

This is the second time I’m writing a post in the  “Meme Monday” series and this time it originates from Tom LaRock (blog | twitter).Tom makes coffee The topic is : What #SQLfamily means to me.

This weekend I participated in a #SQLfamily reunion in Washington, DC. Some might argue that this is called SQL Saturday #96 but I prefer to see it as a family reunion because this is how it felt. It started with a #SQLfamily dinner where we had a good time told stories (some would even make a good Agatha Christie book)… but I digress. We all sat around the table and felt like we are part of a family. I met Tom LaRock, Joey Dantoni, Karen Lopez, Andy Leonard, Brian Moran, Aaron Nelson, Matt Velic, Chris Skorlinski, Wayne Sheffield, Sebastian Meine and many others. I truly felt like a family reunion and it was.
On Saturday we continued, had fun presenting our sessions, learning new things, hanging out in the speaker room and talking about all kinds of things. Everybody contributed to making this  #SQLfamily reunion an event to remember. Tom even offered to make coffee and but had no idea what to do with the extension cord. (…after the last power outage they had in New England you would think he should know better 🙂 )

Looking back I realize that every #SQLfamily event I have participated to has made me feel like this whether it was a SQL Saturday in Indianapolis or in Chicago or a user group meeting I never felt left out. I had my brothers and sisters and uncles and aunts and they made me feel like I am part of the #SQLfamily.

And the best part is that our #SQLfamily is growing every day. Every reunion we discover more people we can relate to even though we are not related (but in a way we are).

God bless our #SQLfamily!

How to: Poor man’s cursor template for batch processing

John Sterrett blog|twitter asked a question about “How do I loop by rowcount in SSIS? I have a table with 5 million IDs and would like to loop to process 10K at a time. #sqlhelp”

This cannot be done natively in SSIS but you could create a stored procedure that uses the concept known as “poor man’s cursor” to split it in batches.
I included a template of code that I use  to create smaller transactions that do not block normal users accessing the same tables

Let me know what you think.

 * Name:            Poor mans cursor example for batch processing
 * Author:            Luke Jian - luke(a.t)sqlpositive.com
 * Creation Date:    11/02/11
 * Purpose:            Template for batch processing using Poor man's cursor
    DECLARE @RowStart [INT]
    DECLARE @RowEnd [INT]
    DECLARE @RowToDo [INT]
    DECLARE @RowDone [INT]
    DECLARE @BatchSize [INT]
    --    Load Users To Enable
    IF ( OBJECT_ID('[TempDB]..[#PoorManCursor]') IS NOT NULL )
    DROP TABLE [#PoorManCursor]
    [ID] = IDENTITY ( [INT], 1, 1)
    --.. SOME other rows
    INTO    [#PoorManCursor]
    FROM    dbo.BaseTables
    --ORDER BY [Whatever column for batch order]
    --    Add index to temp table
    --    because table may easily exceed 10,000,000 rows
    CREATE UNIQUE CLUSTERED INDEX [UCPoorManCursorID] ON    [#PoorManCursor] ( [ID] )
    --    Set up batching
    SELECT  @RowStart = 1,
    @RowEnd = @BatchSize, --can use 10K
    @RowToDo = MAX([ID]),
    @RowDone = 0
    FROM    [#PoorManCursor]
    PRINT CAST(@RowToDo AS [VARCHAR]) + ' records to be processed.'
    --    Process batch
    WHILE ( @RowStart <= @RowToDo )
    --        Load PoorManCursor_Tracking
    INSERT  INTO [dbo].[SomeTargetTable]
    SELECT  [col1],
    FROM    #PoorManCursor
    WHERE   [ID] >= @RowStart
    AND [ID] <= @RowEnd
    + ' records completed in this batch'
    SET @RowDone = @RowDone + @RowCount
    SET @RowStart = @RowStart + @BatchSize
    SET @RowEnd = @RowEnd + @BatchSize
    PRINT CAST(@RowDone AS [VARCHAR]) + ' total records processed.'
    IF ( OBJECT_ID('[TempDB]..[#PoorManCursor]') IS NOT NULL )
    DROP TABLE [#PoorManCursor]

Presenting 2 sessions at SQL Saturday #96 in Washington, DC

I am deeply honored that I was chosen to present 2 sessions at SQL Saturday #96 in Washington, DC this Saturday,   November 5, 2011!

Location is:
Microsoft MTC
5404 Wisconsin Ave, Suite 700
Chevy Chase, MD 20815

View Larger Map

Subway: Friendship Heights Metro Station

SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. Admittance to this event is free, all costs are covered by donations and sponsorships. Please register soon as seating is limited, and let friends and colleagues know about the event.
If you are attending please stop by and introduce yourself. Also if you are on Twitter don’t forget to use hashtag #sqlsat96 and see what others are doing.

The 2 sessions I will be presenting are :

Optimizing SQL Server I/O with Solid State Drives and Join operations – Explaining Merge,Hash and Loop

Hope to see you there!


Update: 11-09-2011

I have uploaded the slide decks to the session pages:

Thanks to all that attended and don’t forget to leave feedback at:

Speaking on SQL Server Algebrizer at SQL Saturday #82 Indianapolis

I was selected to present a session Back to basics – How does the Algebrizer work  has been selected for  SQL Saturday #82 in Indianapolis, IN on Saturday,   June 25, 2011!

Location is:
University of Indianapolis-1400 E Hanna Ave
Indianapolis, IN 46227

View Larger Map

SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. Admittance to this event is free, all costs are covered by donations and sponsorships. Please register soon as seating is limited, and let friends and colleagues know about the event.
If you are attending please stop by and introduce yourself. Also if you are on Twitter don’t forget to use hashtag #sqlsat82 and see what others are doing.

Hope to see you there!

SQL Azure – Alternative to what

After attending a presentation on SQL Azure by Microsoft’s George Huey an interesting topic came to my mind in regards to the actual use for a cloud based database that has over 1400 incompatibilities with SQL Server: What’s the use for this since it is not a viable alternative to a production database.
I would say that it is an alternative to MS Access!!!
Let me explain that. I definitely see the use for it when you need to deploy a solution quickly for an event or conference and you need a reliable solution for the backend of your application that fits a tight budget and an even tighter schedule or even as George suggested for a startup that has no money for a datacenter hosted db environment.
in these situations deploying a 1GB database to host non-confidential information for under $10/mth seems a no-brainer. But I would not recommend this when HIPAA compliance is required for instance.

My thanks to George for a great presentation.

Query optimizer pains

As requested by Yavor Angelov from MSFT via Twitter im writing down what seem to to be my biggest pains with QO in SqlServer.

Whenever you have a a join between large tables QO will look at the indexes that exists on the join keys. If some indexes are missing it seems that the default join type chosen is Nested Loops which is OK for fast results but is really slow if the memory is scarce on the server or there is a high load.
Also this is hard to execute in parallel.
Usually in this situation I have to look at the execution plan and suggest the proper plan to QO by creating missing indexes.
I think creating a temporary index if the cardinality of the join tables is high would help a lot to change QP from nested loops to hash joins which can take advantage of multiple processors. Also the time to create the NC index that is missing is also very low.

A lot of times it seems that the QO is not looking at the statistics to apply Selection first on small and limit the resultset that has to be joined with the other tables to a small number of rows.
The QO should calculate QP cost using different methods and then pick the QP with the smallest cost. This does not seem to happen very well especially when there is a mix of small and large tables to be joined.

To test these scenarios you can use a Virtual Server with minimum memory resources and you’ll see that the differences between QPs become very evident.

How (not) to design a SQL Server table

I recently saw the worst example of  how not to design a table and indexes in SQL Server.

So here it is:


Role : Log any activity that a user is invited for.

PrimaryKey: … Wait for it … tada : UserID,ActivityID,ActivityType,AddDate … Wait you say …. YES … all table columns are used for the primary key and of course by default this will be a clustered index. Which means that if you want to insert 100K records in this table … it will split a lot of pages to reorganize the index beside actually adding the table records.

Wait … it gets better…

Someone created a non clustered index on ActivityID,UserID,,ActivityType,AddDate … yes in that order … and NO it does not help but I guess not everybody knows that a non clustered index will contain the clustered index besides the actual index.

So let’s recap:

Table : 4 Columns

PK Clustered Index: Same 4 Columns

Non clustered index: Same 4 columns x2

Insert efficiency -> extremely poor.