PASS tv has added a boatload of videos

While I was away on vacation in San Francisco,  I noticed that PASS tv has added dozens of videos to their YouTube channel. These videos were added 2 days ago. There should be something there for everyone interested in working with SQL Server as well as interested in presenting

There are videos by Kalen Delaney, Bob Ward, Buck Woody, Kendra Little and many more


You can find all the videos here: https://www.youtube.com/user/SQLPASSTV/videos

The best thing about the PASS summit for me is...

This past week I attended the PASS Summit 2016. I went there with 6 co-workers and I had a terrific time. This time the weather wasn't that bad, when I left Princeton, NJ, it looked like it was going to rain for 5 days straight. It was actually sunny on a couple of occasions  :-)

One thing I really liked were the chalk talk sessions they had with the program managers of SQL Server and Azure SQL DB. People attending these sessions could ask questions and the team then answered these questions. There were also some cool announcements, I documented those here: Some cool SQL Server announcements SQL Graph, Adaptive Query Plan, CTP1 of SQL vNext, SQL Injection detection

Another thing I really liked, and this is not specific to this PASS summit, is that I got to see some of the people I befriended over the years. I chatted and hung out with some of them.

I liked all the sessions I attended, the one negative feedback I have is that there were a couple of sessions at the same time, so I had to pick one of them. This is not such a big deal because once the sessions are made available online, I will just watch the ones I really wanted to attend first.


So now back to the title of this post..... what was the best thing about this PASS summit for me? It is the fact that going to this summit reignited my love for SQL Server, and the passion for learning about SQL Server. I have laid low for the last couple of years but now I feel like a spark has been lit again,  I have so much to learn.

One of the best ways for me to learn is to blog about it. So here is what I am thinking I will do:

For December I will do a SQL advent series, this series will be about windowing functions. Everyone knows about the basic ones that were introduced in 2005, but very few people are using the stuff introduced later.

After that I will do a  series about r and SQL Server, it will take me around 3 months,  and I will probably do two posts per week.

After the r series, I want to do a series about the QueryStore.

I will also do a series about new stuff in SQL Server 2016, but that will be in between all the other posts. Whenever someone asked during a session how many people were already on SQL Server 2016, very few hands went up, this is the reason, I want to blog about this as well.


For all these series of posts, I plan to have a repo on github with a yet to be determined name. Then I will have all the SQL code for all the series organized there


So that is my plan


P.S.

If you want to read about my time at SQL Pass 2016, read these posts


SQL Pass Summit 2016... day 2
SQL Pass Summit 2016... day 3
SQL Pass Summit 2016... day 4
SQL Pass Summit 2016... day 5


You might be thinking, where is day 1?  The day 1 posts is mostly about Seattle, if you are interested in that one, here it is:  SQL Pass Summit 2016... day 1.... just looking around

SQL Pass Summit 2016... day 5

Sadly this was the last day of the Pass Summit, but I think I was my most favorite day. First of all it didn't rain, it turned out to be a nice day.

08:00 AM - 09:15 AM
Data Partitioning for Database Architects and Mere Mortals [AD-303]
Speaker(s):  Dmitri Korotkevitch

Data partitioning is an essential technique that simplifies database administration tasks, improves system availability and reduces the cost of the solution. Contrary to popular belief, data partitioning is not limited to partitioned tables and the Enterprise Edition of SQL Server.

This session demonstrates the data partitioning techniques available in the various editions of SQL Server. It discusses benefits, downsides and the best use-cases for partition tables, local and distributed partitioned views and stretch databases. The session will also demonstrate how data partitioning helps boost performance of systems handling a mixed workload, improves cardinality estimations with large tables, and reduces the system’s storage cost. 

Finally, the session provides a set of guidelines on how to split the data between relational and non-relational storages in OLTP and IoT systems hosted in the Cloud.

The room was packed and I had to stand in the back...it also didn't help that this ession was on level 2, it took me a while to find this room

This session was okay, I knew most of the stuff covered, below are two slides that give a summary between native partitioning and partitionmed view

09:30 AM - 10:45 AM
Chalk Talk with Azure SQL Database PM Team [AD-501-M]
Speaker(s):  Tomas Talius  Lindsey Allen

Join us in an open dialog with Microsoft Partner Architect Tomas Talius and members of the Azure SQL Database team. Don't miss this unique opportunity to ask the hard questions. Seating is limited to 50 attendees so arrive early. Chalk Talk sessions are not recorded.

This and the session after this one were my two favorite sessions. A couple of big announcement.. First CTP of the next version of SQL Server will drop next month, this includes both the Linux as well as the Windows version
SQL Injection detection
SQL Server optimizer has machine learning built in
SQL Graph (Think Neo4J) SQL Graph adds graph processing capabilities to SQL Server
Adaptive Query Plan
There will be a big announcement about SQL Server Standard Edition on November 15th....  mmmm maybe the ability to use more memory? We will have to wait and see what will be announced

I will go into some of these announcements into more details in a separate blog post. That post can be found here: Some cool SQL Server announcements SQL Graph, Adaptive Query Plan, CTP1 of SQL vNext, SQL Injection detection

Here is a picture of the team... after I posted this I got a funny comment from Adam Machanic (see below picture)

This image would make a great cover for a Christian rock album. https://t.co/W8MkhCBJeY

— Adam Machanic (@AdamMachanic) October 28, 2016

11:00 AM - 12:15 PM
Chalk Talk with the SQL Server Team [DBA-501-M]
Speaker(s):  Lindsey Allen  Jakub Szymaszek  Jovan Popovic  Kevin Farlee  Tomas Talius  Sunil Agarwal  Joseph Sack

More or less the same concept as the session before it.

02:00 PM - 03:15 PM
Go, Go, QueryStore! [DBA-210]
Speaker(s):  Gail Shaw

One of the hardest things to do in SQL is to identify the cause of a sudden degradation in performance. The DMVs don’t persist information over a restart of the instance and, unless there was already some query benchmarking (and there almost never is), answering the question of how the queries behaved last week needs a time machine. Up until now, that is. The addition of the QueryStore to SQL Server 2016 makes identifying and resolving performance regressions a breeze.

In this session we’ll take a look at what the QueryStore is and how it works, before diving into a scenario where overall performance suddenly degraded, and we’ll see why QueryStore is the best new feature in SQL Server 2016, bar none.

Gail Shaw did a good job giving an overview of the Querystore and how one might use it

03:30 PM - 04:45 PM
Microsoft R for the Architect [BIA-323-M]
Speaker(s):  Buck Woody

This was the last session, if you have never been to a Buck Woody presentation..you are missing something

In this hands-on workshop you’ll cover a series of modules that guide you from a review of the R programming environment, the Cortana Intelligence Suite Process, the Cortana Intelligence Suite Platform, to the Microsoft R platforms including: Microsoft Open R, the Microsoft R Client, Microsoft R Server, SQL Server with R Services, R in Azure ML, and HDInsight with R. Final lab is an SQL Server R Services solution.

Buck gave a nice overview of R Services and data science on the Microsoft stack

And this was taken on my way to the hotel after all the sessions were done  :-(

SQL Pass Summit 2016... day 4

I got to the conference center, nice an early, as you can see if was still not completely light after I finished my breakfast.

This is day four for me, but it's day two of the regular conference. Today started with the the second day keynote.




8:15 AM - 10 AM

Keynote

The first thing we learned about was some stuff about financials as well as the member growth. A BA day in Chicago was announced as well. 

The main keynote presenter was David DeWitt. David talked about data warehouse technologies, The three products discussed were Amazon Redshift, Snowflake, and SQL DW.

I took some pictures, I will only post 6 of those so that you get an overall idea of what was discussed

Hash Key Partitioning

Round Robin Partitioning

Table Replication

Here are the summary slides for each product

Microsoft Azure SQL Data Warehouse

Redshift

Snowflake

10:15 AM - 11:30 AM
SQL Server 2016 R Services - How Can You Make Your Apps Intelligent Today? [AD-310-M]
Speaker(s):  Umachandar Jayachandran  Nellie Gustafsson  Jarupat Jisarojito

Attend this session to get an overview of SQL Server 2016 R Services and how you can use R in T-SQL today to make your applications more intelligent and build predictive logic.

This was an introductory session to R services and how you can call R from within SQL Server. I took a picture of Nellie and UC

01:30 PM - 02:45 PM
Lightning Talks 101
Speaker(s):  Hugo Kornelis  Wayne Sheffield  Russ Thomas  Rob Volk  Jeff Renz

This session is one of four Lightning Talks sessions, each featuring five 10-minute talks on a variety of topics.

Lightning Talks 101 focuses on the AppDev track, featuring:

1. Hugo Kornelis - "Managing Execution Plans"

2. Wayne Sheffield - "How Referential Integrity Helps SQL Server Optimize Queries"

3. Russ Thomas - "GIT Your Scripts"

4. Rob Volk - "Variations on SQL_VARIANT"

5. Jeff Renz - "Do This... NOT This!"

So this was kind of an interesting idea, 5 really short sessions. The sessions were interesting, however some of them were running out of time



Somewhere around lunch time I snapped this pic

My ex boss is Scottish, so of course I had to send this to him.....



03:15 PM - 04:30 PM
Focus Group

I was invited with 9 other people to be part of a PASS focus group, this was an interesting session, we basically were giving feedback about PASS, the PASS sessions, as well about the changing roles in the IT industry

04:45 PM - 06:00 PM

UNPLUGGED: SQL Server 2016 [AD-404-M]

Speaker(s):  Scott Klein  Rohan Kumar  Lindsey Allen 

SQL Server Unplugged is your opportunity to meet Rohan Kumar and the SQL Engineering Team – the individuals who own the relational database at Microsoft. The session is hosted by Scott Klein and the show is your avenue to ask questions and talk directly to Rohan and Engineering PMs about anything and everything related to SQL Server and Azure SQL Database, and get “unplugged” scoop and insights into the inner workings of the SQL Server engineering team. No NDA material will be discussed during this session.

A picture of Mark Souza, Rohan Kumar and Lindsey Allen

This sessions was very interesting, I even asked a question. The question I asked was if in the future they can make an option so that a temporary table could be hekatonized (created as a In Memory OLTP table), The answer was that they are looking into it.

There was also something funny that happened

Person: "Will you add sharding to SQL Server?"
Rohan: "No"
Person: " Oracle just announced this yesterday"
Rohan "Ok, it's more real now"

The room erupts in laughter  :-)

They also announced that Python will come to SQL Server, right now we have R, but it looks like Python will be coming down the road as well

One more interesting  thing announced was resumable index rebuild. I am not even sure how this would work... maybe like how you can stop and resume index defragmentation??

There was also the raffle, but in my case it might as well not have been.

SQL Pass Summit 2016... day 2

Day two in Seattle started early....very early....after turning and tossing since 2 AM, I decided to get out of bed at 3 AM. I read a little, hit the gym, showered and wrote the following blogpost: SQL Pass Summit 2016... day 1.... just looking around
At 7 AM it was still pitch dark, Here is a pic of what it looked like at that time

I went down to the lobby, met a co-worked and we walked to the Washington Convention Center. We had breakfast, we were joined by another co-worker, we also talked with some people at the table.

After breakfast, it was time to go to our sessions. I went to the Locking, Blocking, Versions: Concurrency for Maximum Performance [DBA-331-PC] session by Kalen Delaney

This is the description of this session

Failing to design an application with concurrency in mind, and failure to test an application with the maximum number of expected simultaneous users is one of the main causes of poor application performance.

SQL Server offers two methods for an application to provide data consistency: Pessimistic and Optimistic Concurrency Control. In this seminar we’ll discuss what consistency might mean to your applications, and describe the details of how each of the concurrency models works internally. We’ll examine the costs and trade-offs between the two concurrency models that SQL Server 2014 supports through different transaction isolation levels. Finally, we’ll examine the tools available for analyzing and troubleshooting blocking problems, including metadata views and Extended Events. You will learn:

• why concurrency management is a crucial part of database and application tuning
• how each of the transaction isolation levels impacts concurrency
• the details of how SQL Server implements pessimistic concurrency through locking, and when locking causes blocking
• how the snapshot-based isolation levels implement optimistic concurrency, and the problems to be aware of
• how In-Memory OLTP allows maximum concurrency with no locking
• how to use the metadata views and Extended Events to troubleshoot concurrency problems and monitor the resources used to manage concurrency.


Here is a picture of Kalen presenting


The session was pretty good, there was some stuff I knew, some stuff I didn't know but should have known, some stuff I knew at one point but forgot. We had an one hour break for lunch and I must tell you the lunch was excellent, here is also a pic of what I ate. You can see, there is some salad, carrots, potatoes, stuffed tomatoes, salmon, pork and also some rice




After lunch we went back to the session. I was there a little early and decided to play around with some code. I came up witht the following teaser, I then posted it on this blog SQL Pass Summit... SQL Teaser Transactions

The session ended at 4:30 PM, we then walked back to our hotel to take a one hour break. We met up at 6:15 PM in the lobby and went back to the Washington Convention Center for the PASS Summit 2016 Welcome Reception At the welcome reception I saw many old friends like Brent Ozar, Mladen Prajdic, Jason Strate, Thomas LaRock, Rob Farley, Tim Ford, Kevin Kline and many more. Sometimes I miss being a SQL Server MVP, because these are the folks I hung out with back in the day.  There were a ton of people at the reception, here is a small selection of them.

While being at the PASS Summit 2016 Welcome Reception I left my iPad in my hotel room to record a time lapse, this ran between 6:15 and 8:30, here is what that looks like

 

PASS Summit 2016 sessions I am attending

I will be at the PASS Summit this year. After a long and hard look at the available sessions, I decided to attend the following sessions. It was hard at times to decide what sessions to go to because sometimes there were two sessions that were at the same time that I really wanted to see. I guess, I will watch those later since most of the sessions are recorded anyway

Tuesday, October 25, 2016

8:30 AM - 4:30 PM

6B 

 [DBA-331-PC] Locking, Blocking, Versions: Concurrency for Maximum Performance

This is a pre-conference full day session, myself and a couple of co-workers will attend this sessions

Wednesday, October 26, 2016

10:15 AM - 11:30 AM

TCC Tahoma 3-4 

 [DBA-102] Powershell Jumpstart for SQL Server DBAs

1:30 PM - 4:30 PM

606-609 

 [DBA-500-HD] Inside SQL Server In-Memory OLTP

4:45 PM - 6:00 PM

6E 

 [AD-400] This Batch-Mode Window Aggregate Operator Will Change Your Life!

Here is what day one of the regular PASS Summit 2016 sessions looks like for me, the keynote is not displayed here.

Thursday, October 27, 2016

10:15 AM - 11:30 AM

606-609 

 [AD-310-M] SQL Server 2016 R Services - How Can You Make Your Apps Intelligent Today?

1:30 PM - 2:45 PM

618-620 

 [LT-101] Lightning Talks 101

3:15 PM - 4:30 PM

611-614 

 [DBA-322-M] SQLCAT : Early Customer Experiences with SQL Server R Services

4:45 PM - 6:00 PM

6E 

 [AD-404-M] UNPLUGGED: SQL Server 2016

Here is what day two of the regular PASS Summit 2016 sessions looks like for me, the second day keynote is not displayed here.

Friday, October 28, 2016

8:00 AM - 9:15 AM

2AB 

 [AD-303] Data Partitioning for Database Architects and Mere Mortals

9:30 AM - 10:45 AM

602-604 

 [AD-501-M] Chalk Talk with Azure SQL Database PM Team

11:15 AM - 12:30 PM

6B 

 [AD-403-M] Inside SQL Server 2016 R Services - Architecture, Security, Performance, and Troubleshooting

2:00 PM - 3:15 PM

6B 

 [DBA-210] Go, Go, QueryStore!

3:30 PM - 4:45 PM

611-614 

 [BIA-323-M] Microsoft R for the Architect

The final day looks like this, I am sure my brain will be mush by the time I get this far.



And here is what the weather looks like for that week...

I guess that is typical Seattle weather.....

[Edit]
Added after I attended the PAss Summit

If you want to read about my time at SQL Pass 2016, read these posts


SQL Pass Summit 2016... day 2
SQL Pass Summit 2016... day 3
SQL Pass Summit 2016... day 4
SQL Pass Summit 2016... day 5


You might be thinking, where is day 1?  The day 1 posts is mostly about Seattle, if you are interested in that one, here it is:  SQL Pass Summit 2016... day 1.... just looking around

If you are going to PASS Summit 2016, build your schedule today

If you are going to the SQL Server Pass summit this year, you can start building your schedule already. Don't wait too long because some events seat only a limited number of people. Take the Chalk Talk with the SQL Server Team [DBA-501-M] session for example
Here is what is says on the page for that session

Speaker(s):  Lindsey Allen 

Duration: 75 minutes

Track: Enterprise Database Administration & Deployment

Join us in an open dialog with members of the SQL Server 2016 Program Management team. Don't miss this unique opportunity to ask the hard questions. Seating is limited to 50 so arrive early. Chalk Talk sessions are not recorded. 

As you can see this session sits only 50 people, so make sure to make a note of that, this way you know to get there early

Some other sessions are also going to be either very popular or they might be in a smaller room, so add those sessions to your schedule first.

You can see all the sessions here: http://www.sqlpass.org/summit/2016/Sessions/Schedule.aspx


That's all

See you at the PASS summit in October

Pass Summit 2014 pre-conference day 2

I went to the SQL Server PASS Summit 2014 in Seattle, this is just my recap of my first day. My first day was day 2 of the pre-conference. My day started early, really early, the jet lag is killing me and I am up by 2 AM tossing and turning. By 5 I was already done with my workout, read 30 pages of the book Neuromancer and had showered.



I met a co-worker and we walked to the Seattle conference center so that he could register and we could get some breakfast before the pre-conference sessions kicked off.. You can see a picture I took of all the people eating breakfast above

I attended the Troubleshoot Customer Performance Problems Like a Microsoft Engineer, this session was presented by Tim Chapman and Denzil Ribeiro, both of them work for Microsoft CSS

The session started at 8:30 AM and ended at 4:30, I took lots of notes and I have pasted them below as is, there are typos and some stuff might not make sense to you, but these are for me, and I will use these note to investigate and research the stuff I learned at this session. The reason I took all these notes is because the pre-conference sessions are not recorded and won't be on the PASS 2014 DVDs.
Scroll down to after the notes for more stuff about this day if you don't care about these notes
Diag manager 
Choose 64 bit (AMD button)
Perfmon information..overhead is very low
Trace is collected by default... uncheck that... If you don't fin the info you want, you can go back and then run a trace as well

My_Colleectors..click on details an then enhance by picking what you need......
pssdiag.XML..change version to 12 in 3 different places  change 10.5 (2008 R2) to 11 (SQL Server 2012)  or 12 (SQL Server 2014)
Overhead is 1 to 3 percent if you are not running a trace. DOn't put the data collection files on the same drives as you SQL Server data files
SQL Nexus
-------
Modify rowset file if you want to add your own or if you want to supply columns names...SQL Nexus will figure out names by default... name  =  table name..identifies is th print statement in the collection files

WHERE %%LOCKRES%%  = '(HashNumberHere)'
Windows OS = preemptive, SQL Server OS is non preemtive

----  After break
High signal wait time =  CPU pressure
Buffer cache hit ratio is since restarted...if you have a problem in the last week but your server has been up for 6 months you might still be at 95% or higher thus masking the problem
sys.dm_io_virtual_file_stats.....stalls is what SQL Server sees, not what WIndows sees
Cost threshold of parallelism.....  default = 5 ... For OLTP you might want to set it to maybe 30... better to use MAXDOP instead of Cost threshold of parallelism.. Version of SQL Server and NUMA plays a role..hardware changes significantly since they tested this last.

COmmon High stats
WRITELOG
When database changed are flushed to the transaction log file
CXPACKET
parralel queries are happening, not a good or bad thing, sometimes less desirable for OLTP
SOS_SCHEDULER_YIELD
When a thread voluntarily releases its hold on the scheduler to allow another thread to do its work. Not necessarily a problem unless it consumes a very high % of wait time on the system
PAGEIOLATCH_*
Latching a buffer structure to move a page to disk from memory, long waits may indicate a disk or memory issue
PAGELATCH_*
A task is waiting for a page latch not associated with an IO request, can be caused by inserts into the same page or contention on allocation pages
ASYNC_NETWORK_IO
Typically occurs because the client requestion data from SQL Server is not processing the request fast enough
OLEDB
Occurs when SQL Server calls the OLE DB provider. Often associated with 3rd paty tools that heavily call DMVs. Also can be caused by RPC, linked server calls, OpenQuery, OpenRowset or profiler
LCK_*
Waiting to acquire a lock
These are accumulated after the lock has been released

RESOURCE_SEMAPHOR
Waiting for memory grant due to a high number of concurrent queries or excessive memory grant requests. Not uncomment for data warehouse workloads
LATCH_*
When a latch is acquired on some non-buffer construct. Mostly internal uses - usually not a lot you can do about it
CMEMTHREAD
When a task is waiting for a thread-safe memory object, increases when multiple tasks try to allocate from the same memory object
tempdb
Can become a bottleneck if not properly sized/allocated. Faster drives are better
Tempdb is used a lot, for example
Temporary tables and table variables
Internal work tables (spools)
Spills (hash/sort/exchange)
Version Store
Make sure all files are equally sized upon creation
For # of files we recommend
<8 Cores = 8 tempdb files
>= cores =  use 8 unless you still have latch contention, then add 4 at a time afterwards

Perfmon
Save the counters in a cfg file and then next time you open this file you will have all the counters available

Useful Memory Counters
SQL Server: Buffer Manager
Page Life Expectancy
Checkpoint Pages/Sec
Free Pages
Lazy Writes/Secgg
Memory Manager: Memory Grants Pending
Process: Working Set
Memory: Available MBytes
Useful Network Counters
Network Adapter: Current bandwith
Network Adapter: Bytes Total/sec
Network Adapter: Output Queue Length
Useful CPU Counters
Processor % Privileged Time
Processor % Processor Time
Process:*
SQL Statistics: Batch Requests/Sec
Database: Transaction/Sec
SQL Statistics: Compiles/Sec

Useful Process Counters
IO Data Bytes/sec
% Processor Time
Working Set
Useful IOCounters
Logical Disk  (what SQL Server sees)
Physical Disk    (What the OS sees)
Avg Disk Sec/Read
Avg Disk Write
% Idle Time
Disk Transfer/sec

Power Settings
Switch from balanced to high performance

Performance Dashboard Reports
Set of SSRS performance eports that integrate into SSMS, needs to be installed, download from CodePlex

------------------------------------------------------------------------------------------------------------
Denzil
------------------------------------------------------------------------------------------------------------

Why Xevents?
SQL trace not as performant, reduce need for a debugger, learn internals and troubleshoot, common instrumentation
Xevents: events, Predicates, Actions, Targets
Targets
Event file:    async, disk    Writes events from buffer to disk
ETW file:    sync, disk    Used to correlate with Windows or app data
Event counter:    sync, memory    Counts all specified events
Ring Buffer:    async, memory    event data held in FIFO
Histogram:    async, memory    Sums event occurances based on column or action
Pair matching:    async, memory  Determince when a specified paired event does not occur in a matched set
Demo of how to setup extended events with the wizard as well as T-SQL
Debug channel exposes some advanced things to help you better troubleshoot
Demo of using extended events to track on a per sessionid basis, this is not possible with DMVs

System Health Session
1)Errors
Any error with severity > 20
Memory related errors
CLR allocation and virtual allocation failures
2)Waits
lathes > 15 seconds
Locks > 30 seconds
preemtive waits > 15 seconds
3)Monitors
Deadlocks
sp_Server_diagnostics
4)Ring Buffer Data
COnnectivity Errors
Security Errors
Memory broker
Memory Node OOM
Scheduler Monitor
sp_server_diagnostic_component
If System Health Session is somehow disabled, it will not affect a cluster failover
Spinlocks
If the first acquire is unsuccessful a collision happens causing a spin. then we try again, during this phase we are spinning, we backoff and try again. You cn use spinlock_backoff and spinlock_backoff_warning, spinlock_backoff_warning happens when we have been spinning for a while and the thread got kicked off the CPU
traceflag 3656 + symbols makes callstacks readable instead of just Hex. Symbol files resolve memory into actual SQL Server calls, you can download the symbol list from Microsoft
Showed how Microsoft redesigned locks by using partition_id so locks were not in just one bucket and spinning decreased a lot, you can enable this by running SQL Server with trace flag 1236

Next up was a slide about problems with plans in the cache.
Cache limit is 160K..kb 2964518, I looked this up and here is what is in that kb article about plans
SQL service (Memory Manager)    TF 8032    Entries in the plan cache are evicted because of growth in other caches or memory clerks. You might also encounter plan cache eviction when the cache has reached its maximum number of entries. In addition to this trace flag 8032, consider the optimize for ad hoc workloads server option and also the FORCED PARAMETERIZATION database option.
CMEMTHREAD
Creating a spatial index on a 3 billion row took 4 days, demo showed a smaller table and how it affected performance. Spinlocks wait was high, a lot of CMEMTHREAD wait types, spiking from1 CPU to another. After doing a DBCC STACKDUMP and then loading it in WinDbg, we could see that it was partitioned by node instead of CPU. When starting with trace flag T8048 performance was much better, all the waits vanished. The index creation went from 4 days to 5 hours after using trace flag T8048
The last part of the session was about Hekaton or better known by it's marketing term In-Memory OLTP. At least Denzil didn't say that to use Hekaton there are no application changes needed  :-)
In-Memory OLTP is a good fit for
Performance-critical OLTP (think order processing or trading)
High data-input rate (nicknamed “Shock Absorber”)
In-Memory OLTP as components of ETL
Session state management
Read scale
In-Memory OLTP is not a good fit for
No permission for code changes
App depends on locking behavior
Full data warehousing
Long-running reporting workload (use Columnstore instead)
Use a lot of XML manipulation/Full-Text searches
Heavily parallelized query
Constrained on memory
******** END OF NOTES  ****************************

All in all an excellent session,  but man am I wiped out  :-)


I went back to the hotel to drop off my laptop, it was actually dry for a change, not a raindrop in sight and about 55 degrees, a very pleasant evening. I had an hour to kill so I took some pics of Seattle, in the picture below you can see Pike Place Market, you can see all of my Seattle 2014 pictures here on flickr

When I got back to the Seattle convention center, the welcome reception started

There were a ton of people at this reception, I saw a bunch of old friends and acquaintances.
I didn't stay that long because I had dinner plans with some MVPs (Adam Machanic, Davide Mauri, Marco Russo, Peter Myers and Alberto Ferrari). We went to a restaurant named Rione XIII, this restaurant is the only one that had burrata on the menu, the food was excellent, you can find the menu here: Rione XIII menu
After being up for over 21 hours I finally went to bed. Guess what, four hours later, I am awake again....writing this post...by the time I am used to this jet lag, it will be time to fly back to Princeton again