Wednesday, 21 March 2012

24 Hours of PASS 2012

Last 24 hours where the 24 hours of PASS tightly packed with very interesting sessions about many aspects of SQL Server. A lot of information, a lot of stories and unfortunately some technical problems too. Still a time well spent. 

My favourite presentations of the day in the order they happened were
In 'Where are my (primary) keys' Ami Levin discussed indexing reminding everybody that natural keys may have some advantages over so popular surrogate keys. I think, in his presentation he was trying to show that despite what one would tend to think performance can be comparable and, maybe surprisingly, tables with surrogate keys could take actually more storage space. Unfortunately he hit some problems during the demo so I cannot wait to get my hands on his code to do some testing for myself. Especially that this comparative performance was on a slightly biased example and I would like to run it on... something biased differently. 

He didn't mention one, important for me, factor in choosing the primary key. Each non-clustered index has to store the primary key value. In case of big data sets with many non-clustered indexes the difference between 4 byte integer and 128 of varchar may contribute significantly directly to storage size and indirectly to performance issues. He explicitly said there is no difference for a query optimiser between a join on integer and a join on varchar(128). For optimiser it is just a value but already the CPU has to compare up to 4 or up to 128 bytes of data for each row and more importantly this extra (up to) 124 bytes per index per row will take  precious memory. 

But he didn't say go and use natural keys, no. In fact the he did say was: evaluate and make your decision [...] hopefully the demo will make you think twice. 

Then Neil Hambly (@Neil_Hambly) has explained 'Memory Management' changes in SQL Server 2012. Interesting subject but sadly disturbed with technical problems. I am pretty sure Neil said he is going to re-record it and make it available on-line. I am looking forward to it. 

Kendra Little (@Kendra_Little) in her talk 'SQL Server First Responder Kit' had one very important message. When you face a problem don't think it is what it was the last time. Don't assume anything. Take time and think. Not what it usually is or what it gotta be. 

In fact most of the speakers I listened to today said something along those lines. Would that be a sign of growing irritation with bespracticionism? Internet is full of best practitioners but I won't let myself started on that one, not this time.

And the show by Grant Fritchey (@GFritchey) presenting Execution Plans a session with a nice flow, wit and with some good takeaways too. What I will try to remember is, firstly, to check reasons for early termination, if it is a time out the execution plan is most likely not worth reading. He stressed importance of reading the properties of pretty much anything in the execution plan but especially ignored (apparently by most of the people definitely by me) stats of initial operations so I will try to improve there too. 

Another discovery for me was the fact that the same query run with different ANSI settings may result in multiple query plans per query. I will have to dive into the subject and understand how it actually happens.

All in all a great event. Many thanks to the speakers and the organisers. 

No comments:

Post a Comment