Clint Davis

I was fortunate to catch the Developer Chalk Talk about Hekaton at PASS Summit 2012. For those not familiar, Hekaton is a new in-memory technology being integrated with SQL Server that is able to radically improve performance. Here are some of the things I caught from the talk with the developers of Hekaton.

• The teams goal was a 100x performance increase over normal tables, hence the name Hekaton.
• Hekaton is not just putting tables into memory. It is a completely redesigned way of thinking about tables.
• While you could put an entire database in Hekaton, that is not exactly the way Microsoft engineers thought it would be used. Probable use case’s from Microsoft are that specific tables are only brought in to Hekaton, specifically those with problems with locking and latching.
• There are no locking and latching in Hekaton tables.
• Data is persisted in Hekaton tables.
• You do not want to under allocate space in Hekaton. Microsoft didn’t exactly say what would happen if you wrote more data than space in Hekaton from the tone it didn’t seem pretty. I got the vibe that it may crash.
• You can also recompile some stored procedures to further increase performance. Apparently, they are compiling the stored procedures to machine code to get them to run faster. This may not work on all stored procedures. This is also optional, you do not have to do this.
• Only hash indexes are supported in Hekaton at this time.
• At this time, only whole tables are supported in Hekaton. No partions.
• Hekaton will be in SQL Server Enterprise edition when it comes out.

The guys were very good at answering questions. They made it clear that not all features of traditional tables will be available in the first release. They are working with customers to see what exactly should be available in the first release. They couldn’t answer all the questions as it was supposed to only be a 30 minute chat (I had to leave after 45) but they did say they would put out a paper about some of the more detailed technical items.

To clarify, Hekaton is not out yet. It will be released with the next version of SQL Server. The guys claimed to see 2x – 50x improvement with Hekaton tables in their labs but it’s still very early.

Here I chronicle my first time at PASS Summit 2012.My goals for the conference and my experiences.

I’m very excited to be able to make it to the PASS Summit this year. I’m hoping to learn as much as I can about everything but especially about some of the peculiarities of administering SQL Server and learning about some of the business intelligence tools coming out from Microsoft.

Day 0 is the first day for me. I arrived in Seattle on November 4 and the pre-cons start the next day. I signed up for two pre-cons and the full conference.

So first, the Seattle Airport is great. I did not have any problems finding my way around. I walked (and walked, and walked) to the LINK light rail to take me from the Airport to downtown Seattle. The train trip took about 45 minutes and went through many stops, including by the football field where a game was being held. The train was nice and clean.

The train ends in downtown underground near Pine St. I am staying at the La Quinta since everything else was booked up. I found my way around and took the Seattle Streetcar to the stop near 9th and Denny. I then walked to my hotel off 8th.

I’ve had good experiences with La Quinta in the past so I chose them again. This hotel is about 0.6 miles from the conference center or about a 15 minute walk. I arrived early and they would have checked me in but my room wasn’t ready. They did offer to store my stuff in a locked room they had and offered me coffee in their breakfast area. I took them up on storing my bag but I decided to go walk around the city.

Seattle is a neat city. It is very walkable and you just never know what you’re going to see. I was walking towards a water feature when I noticed all the trees around me were purple! Some kind of art project. I found the conference center and then grabbed something to eat and went back to the hotel. I was still early but they had gotten my room ready while I was gone and was able to check in.

I took a small nap to recover from the three hour time difference and then went to the early check-in. I’m glad I did because there were a couple hundred people there when the doors were opened! Check-in was very organized and they had everything ready for me. I didn’t realize a bookbag came with the check-in but I’m glad it did. I was about to go get another one because before I had noticed my bag was ripping.

I tried to stick around to meet people but I didn’t know anyone and people either knew each other already or were recovering from their flights as well. So I went back to the hotel to get ready for the next day. My first pre-con: Relational Database Design Workshop.

Found these scripts on the MSDN site. These scripts will drop extended properties from SQL Server.

--tables
 
select 'EXEC sp_dropextendedproperty
 
@name = ''MS_Description''
 
,@level0type = ''schema''
 
,@level0name = ' + object_schema_name(extended_properties.major_id) + '
 
,@level1type = ''table''
 
,@level1name = ' + object_name(extended_properties.major_id)
 
from sys.extended_properties
 
where extended_properties.class_desc = 'OBJECT_OR_COLUMN'
 
and extended_properties.minor_id = 0
 
and extended_properties.name = 'MS_Description'

--columns
 
select 'EXEC sp_dropextendedproperty
 
@name = ''MS_Description''
 
,@level0type = ''schema''
 
,@level0name = ' + object_schema_name(extended_properties.major_id) + '
 
,@level1type = ''table''
 
,@level1name = ' + object_name(extended_properties.major_id) + '
 
,@level2type = ''column''
 
,@level2name = ' + columns.name
 
from sys.extended_properties
 
join sys.columns
 
on columns.object_id = extended_properties.major_id
 
and columns.column_id = extended_properties.minor_id
 
where extended_properties.class_desc = 'OBJECT_OR_COLUMN'
 
and extended_properties.minor_id > 0
 
and extended_properties.name = 'MS_Description'

Thanks to Louis Davidson!

PowerPivot is really neat, so I was excited to try the new version. Unfortunatley, when I installed the new version it would not load. It gave a generic error message saying it could not load. My problem is that I installed the .NET 4 Client version instead of the full version. This is the correct way to install PowerPivot 2012 RTM on a Windows 7 64 machine.

1.  Make sure the machine is fully patched
2. Install .NET Framework 4.0 (the full version)
3. Install Visual Studio 2010 Tools for Office Runtime (x64)
4. Install PowerPivot 2012 RTM (x64)

These must be installed in this order or it will not work.

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

From Pinal Dave