Use Query Store to help with SQL Server upgrades

The Query Store is a feature that helps you evaluating query performance. It captures execution information asl well as up to 200 plans for a single query. For a thorough overview on the Query Store you can read Enrico van de Laar’s article here. Query Store is also available for Azure since V12. To turn on Query Store you would run the following command:

ALTER DATABASE <database_name> SET QUERY_STORE = ON;

You can also use the GUI in SQL Server to turn on Query Store:

 

Query Store GUI 1
Turn on and tune Query Store conveniently

 

I learned the following neat trick last week when I attended SQL Saturday. We know upgrading between SQL Server versions may lead to slow queries, as our queries are optimized for, well, the SQL Server we use. That means if your production server is a 2008 R2, and you upgrade to a 2016, you might end up with a few queries that in fact execute slower than what you are used to. If you are a smart DBA (and you are a smart DBA), you put a few test databases on the target server and let them run for a while. You evaluate performance, growth, make some changes, and when everything looks peachy you move on to upgrade your servers.

This is where the Query Store can help you in that process. Say, I want to upgrade from my current 2008R2 server. I would grab a database from my current 2008R2 server, restore it on the 2016 server, but leave the compatibility level at 2008R2. I turn on Query Store here, and allow it to capture all execution plans for all queries. These data will stay in the memory for 900 seconds by definition, then will be flushed to disk. You hear that right — Query Store saves all this information to disk. When I restart the server, the execution plans are still going to be there for me. I will let that database happily spin around for, say, a month. I can always check on its progress by accessing Query Store in SSMS.

 

Query Store in SSMS
Query Store in SSMS

 

At that time I change the compatibility mode to SQL Server 2016. What do you think will happen? You guessed it right, nothing will change for the most part, except for a few queries that would suddenly run slower. Way slower. I have heard stories of queries that used to run in mere seconds on 2008R2, but would take up to 15 minutes on 2016. That is because the Cardinality Estimator has undergone major changes over the past few years, especially with the introduction of SQL Server 2014.

At this point I turn to my new best friend, Query Store, and look at the executions plans it saved for that particular query. I would see something like this:

Query Store GUI
Execution plans in the Query Store

 

I can see that plan 146 was the best performing execution plan. I can now select that plan and click the ‘Force Plan’ button right under the scatter plot.

 

Force and Unforce Plan buttons in Query Store
Force and Unforce Plan buttons

 

I would do this to all my slow-performing queries. Tah-dah! I now crossed off one item on my ‘upgrade to SQL Server 2016’ list, and I don’t have to worry about execution plans anymore. Isn’t that neat?!

 

For more information on the Query Store, I recommend Microsoft’s surprisingly reader-friendly page here, as well as the aforementioned Enrico van de Laar’s extensive article over on Simple Talk. Credit goes to these two articles for their images as well.

When the client asks for a unique ID based on name and address but gives you dirty data

case when len(ROW_NUMBER() OVER( PARTITION BY left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3) ORDER BY left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3))) < 2
then (UPPER(left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3) + '0' + convert(varchar(3),ROW_NUMBER() OVER( PARTITION BY left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3) ORDER BY left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3)))))
else (UPPER(left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3) + convert(varchar(3),ROW_NUMBER() OVER( PARTITION BY left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3) ORDER BY left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3)))))
end as ID

 

exhausted
my work is done

 

Tesla Model 3 pre orders have begun in Australia

Tesla’s Model 3 is here!

According to several articles (here is one)  I see on Reddit, Tesla is now taking pre-orders for its new car, Model 3. This made me think.

Around 40 years ago people in the soviet bloc were given the chance to own this really cool new car, the Trabant. The trabant was nothing particularly mind-blowing as far as engineering or design goes, but in a world where owning a brand new car was a big deal people still lined up to reserve a new Trabant. All you had to do was to wait before the store, put down some money, and wait for your new car. Sometimes over a year.

Around 40 years after the first Trabant came out Tesla announced a new line to its prestigious cars, the Model 3. The Model 3 is geared towards those who can’t really afford to spend $100K on the original Tesla cars. No one really knows the specs of the Model 3, and it won’t be available until end of 2017. And yet, people lining up starting today. All they have to do is to wait before the store, put down some money, and wait for their new car. Likely over a year.

My point being…

Curious how history repeats itself, is it? Sometimes my Social Science background kicks in and I can’t help but draw analogies between two occurrences, like Trabant and Tesla. The Western worlds have always spoken with such low opinion of the Soviet-era, they completely miss to draw a parallel between our current world and what it was just 40 years ago. 40 years! Don’t get me wrong, I don’t advocate for communism. Those who consider themselves experts of Social Science and History agree that so far none of the socio-economic models were deemed successful over the course of humanity’s history. Communism, socialism, capitalism – these are mere attempts at organizing the world’s economy. And as you can see, at the end of the day it doesn’t even matter what we call them – they are quite the same after all.

If I had a restaurant, it would fail (Part 2)

This is a follow up to my post in May 2014 titled “If I had a restaurant…I would build the 20% service fee into the prices.” (Clicky clicky. It opens in a new window.)

Obviously I wasn’t the only one to think tipping is the work of the devil. Over the past two years several restaurants in several cities have experimented with tips built into the prices. Turns out: it doesn’t work. An SF restaurant owner, Thad Vogler introduced no-tipping at his two restaurants and now he’s reverting back to tipping.

It appears after he raised his prices with 20%, his servers’ true wages dropped from $35-45 to $20-35 an hour. It also appears that due to also increased taxes he would have to hike the menu’s prices around 40% to maintain the same wages for his staff. On the note of true wages: needless to say servers’ declared and true wages are entirely different. Now that servers had to tax on their income their true wages dropped, and according to Thad 70% of them have left over the 10 months of no-tipping.

I’m guessing a sudden price increase of 20% (let alone 40%) in San Fran wasn’t a viable option, given the variety and availability of restaurants in the city. Too bad. Another large city’s restaurant owner, Danny Meyer, announced getting rid of tips in 13 of his restaurants in NYC. Let us see if Danny can make it happen.

My takeaway is that no-tipping takes two to tango. As a DBA I pay taxes, healthcare and all that after my income – roughly 30% of my income goes to deductions after everything considered. Such is life. Most servers however are accustomed to declaring only a portion of their tips, keeping an un-taxed chunk. Restaurant owners also prefer that solution since they are not obligated to pay several deductions associated with their employees’ true income. So, from their standpoint: you’re taking home $100, and suddenly someone says: “You can take home $100 on a good night and maybe $30 on a bad night, but none of that is guaranteed. Or you can take home guaranteed $50 on both nights. Which one would you prefer?”

Apparently more servers would prefer the higher, but not guaranteed $$$.

Conclusion: so far no-tipping business model in restaurants is not viable. 

How I passed 70-461

Conclusion: the exam is easier than you would think. Take advantage of the second shot offer, and test your knowledge as soon as possible. 

I put the conclusion on top of this article, because this was my biggest surprise after completing the exam.
I used Transcender’s exam simulator pack that comes with flashcards, too, if you’d like to practice every question. The 70-461 exam prep does not have the same questions that I saw in the exam, but the verbiage, format, and difficulty are quite similar, which is why I recommend this product.

For this particular exam I don’t recommend the official prep book. It does cover the necessary topics, just not in depth to call it an adequate preparation tool for the exam.

Here is what I did:
I bought the exam pack and practiced every day when I had some free time at work, I would say one hour every day.
After a month of that I signed up for the exam next week. Microsoft periodically offers second shot promos, that is, you can re-take the exam for free if you fail the first time.
(By the way, Transcender also offers money-back guarantee if you can’t complete the exam within a certain time.)
I attended the first exam with the only intention to see how I measure up. I expected to fail.

I did not fail. My score wasn’t stellar, but I passed.

Therefore, please allow me to repeat the conclusion:

Conclusion: the exam is easier than you would think. Take advantage of the second shot offer, and test your knowledge as soon as possible. 

Syrian refugees in Hungary

I’m reposting an  article from the New York Times, because I am truly ashamed with my country of birth’s behavior.

The American mass media and social media seems to largely ignore the issue, aside from a few paragraphs here and there on Reddit. This is just not a top concern in the States right now, but in European media is is THE TOPIC. I’ve been seeing discussions on Facebook between my Hungarian friends. I’ve been reading the news on Hungarian portals for quite a few weeks now, too, sometimes with some addition from the Guardian or the German media. The stories from refugees, the pictures are overwhelming, I would silently cry reading them, tears running down my cheeks. My mom was crying too, when I spoke with her over the phone. ‘I went for coffee in the morning, when I saw them, marching down the highway,’ she says, her voice would then break and she would stay silent, trying to regain control over her feelings.

I feel pride and shame. These people have been walking through a half a continent for a better life. Nothing could stop them, their behavior sets an example for all of us. We pity them, but in fact they are better than us. I feel immense pride just thinking about their bravery.

And I feel shame for Hungary for what I have been reading in the media.

The below are excerpts from NY Times’ memoirs from a reporter who has been following the Syrians. The quotes are found under the September 5th entry, titled Why Migrants don’t want to stay in Hungary?

When asked, the migrants now stuck at a train station in Budapest say that they put Hungary in much the same category as Macedonia and Serbia, the Balkan countries they passed through on their journey. They see Hungary as having a thin veneer of prosperity, but being fundamentally relatively poor and still developing. And Greece, though developed, is in economic crisis.

Like many Syrians, they maintained a sense of decorum by removing their shoes before sitting on their blanket. […] They said they resented being called eyesores and being blamed for trash. This was not their true nature, they said.

“We’ve been through all these countries, this one is definitely the worst,” Mr. Darwish said, sounding like a lawyer. “It is supposed to be an E.U. country, but it has broken every single tenet they had. Greece is such a poor country, and it treated us better.”

And meanwhile, on the border between Hungary and Serbia, from the entry titled Hungarian Police Spray Family Trying to Cross Border:

Once again, the group walked into the ditch before the barbed wire. A woman in a straw-colored bonnet and carrying an 18-month-old infant led the way.
She was only a few inches from one of the police officers when he sprayed something at the group. A fine mist glittered in the light above the woman’s head. For a moment only the child’s cry could be heard before the scene devolved into chaos. Those in the ditch scrabbled back up to the Serbian side, screaming in apparent pain as they coughed and gagged.
The Hungarian officers remained quiet. A few minutes later, they climbed into the car and drove off.

 

Setting database to trustworthy will let you deploy assemblies in unsafe mode

The title says it all, but let me expand on this a little bit.

I have an assembly that I wanted to emulate in SQL server. The first, obvious choice is to use safe mode, which results in the following message:

Msg 6212, Level 16, State 1, Line 2
CREATE ASSEMBLY failed because method ‘x’ on type ‘x.x’ in safe assembly ‘x’ is storing to a static field. Storing to a static field is not allowed in safe assemblies.

Be quiet my racing heart. Sassy SQL Server be sassy. So evidently I’d go ahead and deploy it in unsafe mode, just so that we can run into another error message:

Msg 10327, Level 14, State 1, Line 2
CREATE ASSEMBLY for assembly ‘CTXCRMEncryption’ failed because assembly ‘CTXCRMEncryption’ is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.

SQL Server is actually trying to help in her quite, sassy way here, so let’s give her what she wants.

ALTER DATABASE databasename SET TRUSTWORTHY ON;

And now we can emulate the assembly in unsafe mode. I wish I had known that sooner.
I wish I had known that

SSIS – SQL Server data type

This blog post here on BI Developer Network contains a nice list on data conversion between SSIS and SQL server, something that gets my blood pressure going due to SSIS’s ‘check your metadata’ error message. Good cheat sheet that list the data types.

 

SSIS Data Type

SSIS Expression

SQL Server

single-byte signed integer

(DT_I1)

 

two-byte signed integer

(DT_I2)

smallint

four-byte signed integer

(DT_I4)

int

eight-byte signed integer

(DT_I8)

bigint

single-byte unsigned integer

(DT_UI1)

tinyint

two-byte unsigned integer

(DT_UI2)

 

four-byte unsigned integer

(DT_UI4)

 

eight-byte unsigned integer

(DT_UI8)

 

float

(DT_R4)

real

double-precision float

(DT_R8)

float

string

(DT_STR, «length», «code_page»)

char, varchar

Unicode text stream

(DT_WSTR, «length»)

nchar, nvarchar, sql_variant, xml

date

(DT_DATE)

date

Boolean

(DT_BOOL)

bit

numeric

(DT_NUMERIC, «precision», «scale»)

decimal, numeric

decimal

(DT_DECIMAL, «scale»)

decimal

currency

(DT_CY)

smallmoney, money

unique identifier

(DT_GUID)

uniqueidentifier

byte stream

(DT_BYTES, «length»)

binary, varbinary, timestamp

database date

(DT_DBDATE)

date

database time

(DT_DBTIME)

 

database time with precision

(DT_DBTIME2, «scale»)

time(p)

database timestamp

(DT_DBTIMESTAMP)

datetime, smalldatetime

database timestamp with precision

(DT_DBTIMESTAMP2, «scale»)

datetime2

database timestamp with timezone

(DT_DBTIMESTAMPOFFSET, «scale»)

datetimeoffset(p)

file timestamp

(DT_FILETIME)

 

image

(DT_IMAGE)

image

text stream

(DT_TEXT, «code_page»)

text

Unicode string

(DT_NTEXT)

ntext

 

Credit goes to their user, DevinKnight.

 

Fast-Food chart from the Economist

On the topic of McDonald’s slowly going downhill, the Economist came up with a decent little chart on fast-food around the world.

See it yourself on the Economist’s site; below are some of my thoughts on the matter.

1. There are a lot of restaurants in the States compared to the rest of the world.

Fast-food outlets per person in each country

You can check the interactive map over at the Economists – it’s 224 restaurants per million people in the US. Think of demand-supply. There are a lot of restaurants in the States because there is a high demand for fast-food.

See that, young padawan? Only in the Us and Canada are there more than 200 restaurants for each 1 million citizens. Australia and Iceland also fall towards the end of the spectrum, however, let us not forget that the population of Iceland errs around 400 thousand people, Australia’s at 23 million. Both Iceland and Australia have a population density of 3 people per every square mile. (One day I will write up an article just on population density and urban development.) (US pop.density  34; population is at 320 million.) Less customers to serve by the same ratio of restaurants.

2. Canada has just as much.

The total number of Mcdonalds restaurants

Checking the number of restaurants between Canada and the US, we can see that Canadians have 1/10th of the number of McDonalds. Does Canada have less citizens, too? Yes. 35 million vs. US’s 320 million. Tenth of the restaurants for tenth of the citizens, numbers decrease proportionally.

However, an important point, that probably relates to #1 and #2, is that this map is very America-centric. I’d presume there is a good chance of the States and Canada having the most amount of Mcdonald’s / KFC / Starbucks / Subway / Pizza Hut for these chains are American. Whereas in other countries the local chains are not being considered in this map.

3. Subway takes it all.

Leading_chains_per_country

Look who has the most restaurants in the most countries! Yes! Subway takes it all. What I love about this last chart is Greece and South Korea: they both have more Starbucks than other restaurants. In fact, Greece has 31 Starbucks and 43 everything-else; and South Korea has 700 Starbucks and 960 everything-else. (Everything-else being KFC, Pizza Hut, Subway, and McDonald’s.) Them Greek and South Koreans really love overpriced, mediocre coffee.

Conclusion: this is a good map, but it’s very America-centered. There is a good chance that local chains, that aren’t represented on the map, have a strong presence in countries, which would definitely give a biased representation of # of fast-food restaurants per population on this map, as we could see in the points above.

Now run along, and click around in this sweet little chart on the Economist’s site. By the way – it was made with Raphael.js. A UX job well done, Economist’s data team!