Wednesday, October 26, 2016

SQL Pass Summit 2016... day 3


Just like the other two days, day three in Seattle started early....very early. This is day three for me, but this is the first day of the general sessions. This year over 6000 people came to attend the Pass Summit.

My day started with a 6:50 AM session

6:50 AM -  8 AM

Why Use AWS for Running Your Databases
A session explaining how you can run databases on Amazon Web Services, they explained the difference between running RDS and EC2. Overall the presenter gave a nice overview of what is all available within the Amazon cloud

8:15 AM - 10 AM

Keynote

Keynote was well attended, Joseph Sirosh Corporate Vice President for the Data Group, Microsoft started the keynote (he is not pictured here)

Below are some pictures I took of the keynote, I have mostly one liners below the images. The keynote was not bad, it wasn't the best ever but I think it was better than the last couple of years.


ACID doesn't mean what you think it means

You can run queries against a variety of data source straight from SSMS

Intelligent Database Service...this means taking the intelligence out of the apps and moving it into the database, think of running R service inside the database.

This Data Warehouse on Azure will be general available in a couple of weeks

A lot of people are already looking at SQL Server running on Linux

Installing SQL Server on Linux. And just like that, by being available on Linux, Microsoft just doubled the number of platforms that SQL Server runs on :-)


The Planet of the Apps. Apps completely dominate how much data they use and also how much data they generate

Some stuff about DocumentDB

DocumentDB is Rick Grimes favorite weapon to kill walkers, 43 million walker eliminated each day....

Here is what the explosion of data looks like when you look at it as rice

A look from the back of the room...


10:15 AM - 11:30 AM

Powershell Jumpstart for SQL Server DBAs [DBA-102] presented by Michael Fal



Powershell can be intimidating. There are many challenges to using it, especially for those not familiar with its nuances. Once understood, the language can be used for effective and robust automation that brings together SQL Server with other Windows components, such as the file system or Active Directory. 

In this session, we will cover the Powershell fundamentals, how you can use Powershell itself to learn about concepts and syntax, and techniques for using Powershell with SQL Server. We will focus on core language patterns that you can use immediately, take the mystery out of the code, and help you get started writing your own PowerShell scripts. Attendees will not only acquire an understanding of Powershell, but see practical examples of how it can be used with SQL Server, along with a set of resources they can use to learn more about the language.
I attended this session with 3 of my co-workers, I saw some interesting stuff, I only played around a little with powershell and I will spend more time with it for sure.


01:30 PM - 02:45 PM
03:15 PM - 04:30 PM
Inside SQL Server In-Memory OLTP [DBA-500-HD] presented by Bob Ward

Hekaton is the original project name for In-Memory OLTP and just sounds cooler for a title name. Keeping up the tradition of deep technical “Inside” sessions at PASS, this half-day talk will take you behind the scenes and under the covers on how the In-Memory OLTP functionality works with SQL Server. 

We will cover “everything Hekaton”, including how it is integrated with the SQL Server Engine Architecture. We will explore how data is stored in memory and on disk, how I/O works, how native complied procedures are built and executed. We will also look at how Hekaton integrates with the rest of the engine, including Backup, Restore, Recovery, High-Availability, Transaction Logging, and Troubleshooting. 

Demos are a must for a half-day session like this and what would an inside session be if we didn’t bring out the Windows Debugger. As with previous “Inside…” talks I’ve presented at PASS, this session is level 500 and not for the faint of heart. So read through the docs on In-Memory OLTP and bring some extra pain reliever as we move fast and go deep. 

This session will appear as two sessions in the program guide but is not a Part I and II. It is one complete session with a small break so you should plan to attend it all to get the maximum benefit.
This was a 3 hour session and Bob also cut the break down to 15 minutes...30 minute breaks are for wimps he said  :-)


This was a very good and very fast sessions, the way you attend these sessions is that you attend it in person and then you watch the recorded session at least two more times...and only then will you be able to understand everything.


Of course Bob was very cruel, he had the Cowboys use In Memory OLTP tables and the Eagles were stuck with regular disk based tables....  Kidding aside, if you ever have a chance to attend one of Bob's session, make sure you do... who else would run the debugger against SQL Server and step through a Hekaton Stored Procedure



04:45 PM - 06:00 PM

This Batch-Mode Window Aggregate Operator Will Change Your Life! [AD-400] presented by Itzik Ben-Gan

Window functions introduced a paradigm shift in solving T-SQL querying tasks. They allow more elegant, and often more efficient, solutions compared to traditional predicate-based solutions. Still, prior to SQL Server 2016, there were a number of inefficiencies in their optimization. SQL Server 2016 introduces the new batch-mode Window Aggregate operator, which changes everything as far as performance is concerned. This session describes and demonstrates this new operator, and also provides tips and tricks to enable its use in cases you might not have expected.
Itzik is a very good presenter and he always shows cool stuff


In this session he showed how you can make SQL Server run in batch mode instead of row mode by having a dummy columnstore index. You create a dummy table by adding a where clause and you do for example  WHERE Col1 =2 AND Col1 = 1, this is a filtered index with a filter that can never be true. This session was very good


06:00 PM - 08:00 PM
Exhibitor Reception

This was the time to go see the vendors, learn about the products, meet and greet with your peers, there was also food.

 A view of the hall

 Another view of the hall

 We are sooo tired from sitting whole day long, we need a massage


Some swag I managed to grab, these shirts will be mostly to work out in.

That's all for today, tomorrow is another jam-packed day....

Tuesday, October 25, 2016

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

 

SQL Pass Summit... SQL Teaser Transactions

I am sitting in Kalen Delaney;s session on Locking, Blocking, Versions: Concurrency for Maximum Performance and had a great idea for a SQL Server Teaser. <<<<<

Without running it, what will printed when the following two blocks of code are run?


BEGIN TRAN
BEGIN TRAN
BEGIN TRAN
BEGIN TRAN
BEGIN TRAN
BEGIN TRAN

WHILE @@TRANCOUNT > 0
BEGIN
 PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)
 COMMIT
END

PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)


GO

-----------------------

BEGIN TRAN
BEGIN TRAN
BEGIN TRAN
BEGIN TRAN
BEGIN TRAN
BEGIN TRAN

WHILE @@TRANCOUNT > 0
BEGIN
 PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)
 ROLLBACK
END

PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)


Now run it.... Were you right?

What about the code below, what will happen if you run these two code blocks? Does it matter that the transactions are created in separate batches?


BEGIN TRAN
GO 7

WHILE @@TRANCOUNT > 0
BEGIN
 PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)
 COMMIT
END

PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)


GO

-----------------------
BEGIN TRAN GO 7 WHILE @@TRANCOUNT > 0 BEGIN PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT) ROLLBACK END PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)

Saturday, October 22, 2016

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

Wednesday, September 28, 2016

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


Thursday, May 19, 2016

Living in the NYC tri-state area? Attend the SQL 2016 Launch Event in NYC on June 2nd


Got this info from my co-worker Daryl earlier today.

On June 2nd Microsoft will hold a SQL 2016 Launch Event in New York City. The Microsoft Offices at 11 Times Square (at 8th and 42nd) in New York is where this event will be held.


WHEN
Thursday, June 2, 2016 from 8:00 AM to 2:00 PM (EDT)

WHERE
Microsoft Offices - 11 Times Square, New York, NY 10036

I might be there but I don't want to promise anything. You can sign up here for this event: https://www.eventbrite.com/e/sql-2016-launch-event-nyc-ny-tickets-25477835893

Wednesday, May 11, 2016

How to disable tests in tSQLt



The tSQLt unit test framework that we use to do our DB tests does not allow for disabled tests, this is because each test is just a stored proc. Right now the only way is to either rename the proc so it doesn’t start with test or to remove the test

I was thinking about this in the shower this morning….here is what I came up with

Here is a quick way how to prevent a test from running when the tSQLt tests run.
Put the code below into each unit test that you want disabled, you should put this code right after the CREATE PROCEDURE  ProcName AS part

This code will pick the correct name and schema, so you can use the same exact code in each test


DECLARE @IsTestDisabled bit = 1 -- set this to 1 if you don't want the test to run

IF @IsTestDisabled =1
BEGIN
 DECLARE @SchemaName varchar(1000)
 SELECT @SchemaName = QUOTENAME(SCHEMA_NAME(schema_id)) +'.'
 FROM sys.procedures WHERE object_id = @@procid

 PRINT 'Disabled Test: ' + @SchemaName +  QUOTENAME(OBJECT_NAME(@@procid))
 RETURN
END

So if we grab the example test from the tSQLt website

We would change it from this


CREATE PROCEDURE testFinancialApp.[test that ConvertCurrency converts using given conversion rate]
AS


BEGIN
    DECLARE @actual MONEY;
    DECLARE @rate DECIMAL(10,4); SET @rate = 1.2;
    DECLARE @amount MONEY; SET @amount = 2.00;

    SELECT @actual = FinancialApp.ConvertCurrency(@rate, @amount);

    DECLARE @expected MONEY; SET @expected = 2.4;   --(rate * amount)
    EXEC tSQLt.AssertEquals @expected, @actual;

END;
GO


To  this


CREATE PROCEDURE testFinancialApp.[test that ConvertCurrency converts using given conversion rate]
AS

DECLARE @IsTestDisabled bit = 1 -- set this to 1 if you don't want the test to run

IF @IsTestDisabled =1
BEGIN
 DECLARE @SchemaName varchar(1000)
 SELECT @SchemaName = QUOTENAME(SCHEMA_NAME(schema_id)) +'.'
 FROM sys.procedures WHERE object_id = @@procid

 PRINT 'Disabled Test: ' + @SchemaName +  QUOTENAME(OBJECT_NAME(@@procid))
 RETURN
END


BEGIN
    DECLARE @actual MONEY;
    DECLARE @rate DECIMAL(10,4); SET @rate = 1.2;
    DECLARE @amount MONEY; SET @amount = 2.00;

    SELECT @actual = FinancialApp.ConvertCurrency(@rate, @amount);

    DECLARE @expected MONEY; SET @expected = 2.4;   --(rate * amount)
    EXEC tSQLt.AssertEquals @expected, @actual;

END;
GO


Now you will get the following in your tSQLt test run output

Disabled Test: [testFinancialApp].[test that ConvertCurrency converts using given conversion rate]

This will be right above this text

+----------------------+
|Test Execution Summary|
+----------------------+



I want this printed so we know we have disabled tests when we get a email whenever Jenkins kicks off these tests…..


To enable the test again, all you have to do is change the value from 1 to 0 here. So instead of

DECLARE @IsTestDisabled bit = 1

You would make it

DECLARE @IsTestDisabled bit = 0


To see all my tSQLt posts, visit this link: http://sqlservercode.blogspot.com/search/label/tSQLt

Monday, May 02, 2016

SQL Server 2016 will be generally available on June 1, 2016


Mark your calendars, SQL Server will be generally available less than a month from now, June 1st 2016.

You don't have a reason not to play around with SQL Server 2016, in March it was announced that the SQL Server 2016 Developer edition will also be available as a free download.

Also today, Lenovo published a new #1 TPC-H 30 TB world record using SQL Server 2016 and Windows Server 2016 on Lenovo System x2950 X6

To find out about all the difference between the various editions, download this pdf: SQL Server 2016 editions –what’s new 

For the official announcement, see here: Get ready, SQL Server 2016 coming on June 1st

Thursday, March 31, 2016

Microsoft releases SQL Server Horizontica, a Vertica killer



Amsterdam, Noord Holland, Nederlands, European Union. — April 1, 2016 — Microsoft Corp. on Friday morning announced that it has released the next version of its flagship Relational (and beyond) Database Management System SQL Server Horizontica.

Here is a quick rundown of all the new, exciting and shiny things......

Goes to eleven.
What we have done here is, if you select this option, we will offload very CPU intensive operations to the GPU. Think about it, GPUs are many times faster than CPUs… hookup a bunch of graphic card to you server and your CPU utilization goes down by 99%. Here is what an external GPU rig look like from one of our biggest beta testers



Mr Stein who is a member of that team will explain what he likes the most about Goes To Eleven.

Frank E. Stein

The best part about Goes To Eleven is that if the GPU used capacity is below 50%, SQL Server will automatically use the excess capacity to start bitcoin mining. This in turn will lower the cost of running SQL Server, it really is a no brainer. When you need capacity it is there, when you don't need it, you earn money. Being a former Oracle customer where the licensing model is "How much money do you have?" this is really welcome.

Some more new goodies

BSON
Every vendor has JSON by this point, we took it s step further and added Binary JSON also known as BSON support


MINDOP
Minimum degree of parallelism. Brand new in this version, you can now specify the minimum number of CPUs/GPUs that a query should use. If you know your query is going to return a lot of data...instead of adding indexes...think big and use a minimum of 128 CPUs/GPUs for your query

Rowstore
When we looked at Vertica and Sybase IQ customers we noticed a lot of queries executed were using SELECT *, this is very slow with a columnar data storage. With our patent pending rowstore, it is as fast as the traditional page store. The ROI is immense, no more do programmers and brogrammers have to find all the code where a table is used to add the additional column, SELECT * is always correct. Rowstore is also more natural, when getting the data back people read from left to right or right to left, people don't read from top to bottom, since the data is already stored in rows...no transform needs to happen, this also makes data display faster.

Variable page sizes
Instead of just 8K page sizes, SQL Server now also offers the following page sizes: 2K, 4K, 8K, 16K, 32K, 64K and 640K. The reason we have 640K is because that is the max page size anyone will ever need, you probably will not use it now, but you might in 10 years, we are already prepared for that

Smallishint
Smallishint is a 3 byte integer data type, bigger than tinyint and smaller than smallint. You can store values up to 4096

Myriaton
We took the concepts of Hekaton and got another 100x improvement

One of our customers will explaining how they are using it and how it has improved their applications

Dr A. Cula


I am Dr A. Cula and I work for Ichor, a company specializing in eternal life. We are running a big blood bank, we need to keep this stocked for our mmm inner circle members. We use tagging of subjects so that we can go and mmm get more blood when needed. We were always running into problems when summer came along..our mmm dispatchers and collectors don't like to work when it is sunny. So we needed really good performance during summer hours. After switching SQL Server Horizontica and using Myriaton we now are so efficient that we opened up 4 more blood banks. We could never have envisioned something like this was possible, because of this we also expanded our inner circle by 200%


Universal Stretch and Universal Availability Groups
This is really exciting, instead of a regular stretch database on Azure, Universal Stretch will copy your data on one of our data centers located in the universe. This year it is just on the Moon where we have a data center already, in 2 years you can also use the one we are currently sending to Mars. Then when we start sending people to Mars, you are already open for business...the data is already there and it will be synchronized between Earth, Moon and Mars. Universal Availability Groups work the same way, you plug in the planet or moon and we take care of the rest.


ChandarUma ChandranJaya (also known as CU) program manager on the SQL Server programmability team will fill us in on some additional new features not mentioned yet.

New JOINS

ANTI JOIN
This is a brand new JOIN and it is ANSI SQL 2022 compliant. It is basically a JOIN between 2 tables on a column where the values are not the same (Think like NOT EXISTS)


SELECT *
FROM Table1 t1
ANTI JOIN Table2 t2
ON t1.ID = T2


CUBE JOIN
The CUBE JOIN takes the CROSS JOIN to whole other level...instead of returning all rows in one table for each row in the other tables.... the CUBE JOIN returns all values in all columns in all rows in one table for each value in all columns in all rows in another table... it is like a hypercube...


SELECT * 
FROM Table1 t1
CUBE JOIN Table2 t2

NATURAL JOIN
The  NATURAL JOIN joins two tables on columns with the same name


SELECT * 
FROM Table1 t1
NATURAL JOIN Table2 t2

KEY JOIN
The KEY JOIN is really cool, what it does it joins tables based on primary and foreign keys, no need to specify these in the join condition anymore


SELECT * 
FROM Table1 t1
KEY JOIN Table2 t2


New Traceflags
Traceflag 666
This is a brand new traceflag, it will give your SQL Server instance hellish performance. It acts like a Double Distributed Denial Of Service (DDDOS) attack against you SQL Server instance...... IO, Memory.... everything will be completely saturated... however you can still use the DAC (Dedicated Administrative Connection) to get in and run queries against DMVs to check the Subsystem poerformance

New functions
We have implemented the following new functions
IsDaylightSavingsTime
IsNotWeekend
Trim
sp_MsForEachServer
DBCC CheckCrappyCode
DBCC
Quartile
DCOUNT
DAYS360
IsOdd
IsNontext
Gigo
Lifo
Fifo
Fido

Specialized indexes
We now have the following new indexes

DateIndex  designed to work with all the date data types

FunctionIndex, finally you can index for example the first 5 characters of a column only, you can use all the functions available in SQL Server

Swift
Swift is a brand new storage engine, you can use this when you don't need constraints, keys..after all real developers manage those in the app tier. You enable this per database and you can make the same face as Taylor when thinking how you don't have to deal with all those nasty constrains that just get in the way. The beauty of Swift is that if you do have constraints in your DDL scripts, they will be just silently ignored. No more scripts that have to run in a specific order...it always runs fine..., your manager will love, no adore you..



There are many more things that are new, you can visit the SQL Server homepage to find out even more exciting stuff