Feb 12 2016

Speaker of the Month: February 2016

I’ve been a little remiss on this. I just haven’t been getting out to see people speak for a while (no travel is nice). However, I’m back in the airplane seats again, so these blog posts are off and running again.

Speaker of the Month for February 2016 is Jason Hall (b|t) for his talks on SQL Cruise. Jason covered two topics, TempDB and SQL Sentry Plan Explorer. I missed the talk on TempDB, but I heard it was great. I was there for his presentation on Plan Explorer. Excellent stuff. I had never seen Jason present before. In fact, I didn’t know he did presentations. He does. He spoke really well to the crowd. He knew the material down (not surprising since he’s been responsible for developing the tool) and handled questions extremely well. He kept great eye contact with the audience and his slides were clear and easy to follow. The demos were interesting and illustrated each point well. It was a great and informative session.

Feedback… Jason knows this topic. He demonstrated that knowledge extremely well. However, he came off as less than confident in that knowledge. I think by presenting just tons and tons of knowledge he blew away the room, but if he’d had less time to overwhelm us, he might not have succeeded. If you know the topic, stand behind it. If you’re unsure if you know the topic, pretend you know the topic. Fake it til you make it actually does work. Now, you have to be able to back that up, as Jason did, ably, but still, come at the session with confidence. Your confidence will communicate to the audience.

I’m sorry I missed the TempDB session. I can’t wait to see what else Jason presents on in the future.

 

Feb 04 2016

Positivity

I’m sitting in the classroom of SQL Cruise listening to Tim Ford (b|t) explain mechanisms for monitoring indexes. It’s a great class. Earlier in the week I got to hear Jes Borland (b|t) talk about extended events and do a session on wait statistics. I was also lucky enough to listen to David Klee (b|t) talking about systems monitoring, especially around VMs. Argenis Fernandez (b|t) and Jason Hall (b|t) are coming up today. In short, I’ve received some excellent learning while on a boat in the Caribbean. Now, one could argue (and you’d be right) that I’m thinking about positivity because of the nature of the position in which I find myself. Hang on though, I have some additional points.

One of the biggest strengths of the SQL Cruise is the intimacy of the event. You’re not just sitting through a one hour session with David or Jes, Tim or Jason or Argenis (the sessions are two hours anyway) like at a regular event. You’re sitting across from them at breakfast, lunch and dinner. You’re going out for drinks. You’re hanging out on the beach. You’re zip lining through the canopy. You’re exploring 17th century fortifications. In short, you get to have nice, long, thoughtful conversations with these people and your fellow cruisers. Positive now? Please bear with me. I don’t mean for this to be an advertisement for SQL Cruise (although…)

For all of the above, I  love SQL Cruise. However, there’s more to it than that.

No, the principal thing I love about SQL Cruise is the extremely high degree of positivity of the people that seem to be drawn to it. Maybe it’s Tim. Maybe he’s just good at pulling together a bunch of people who are incredibly positive. They care about what they do. They care about how they do it. They are unremittingly positive. I think of them as a bunch of happy warriors. They’re fighting with some of the hardest data problems yet don’t come down against their jobs, their lives, or the technology they use. Instead, they tighten their belts, crack their knuckles, get a smile on their face, and tear into the problems with glee. How do I know this? Because I watch them interacting with all the other people on the cruise. They listen with focus and once the problem is defined, you see that smile appear.

It’s so easy to be negative. The world is full of reasons to be unhappy, dissatisfied and disgruntled. Tearing things (and people) down is quite simple and maybe even satisfying in a way. However, negativity is draining. Negativity breeds additional discontent and more negativity. Sooner or later, enough negativity leads to a simple statement, “Eh, what does it matter.” Down that path lies the end of your career.

No.

Be positive. Be energized. Get that smile on your face and rip into that problem. Further, get people around you as much as you can who also will put that smile on their face, will also rip into that problem. Positivity breeds positivity and you’re so much more likely to have fun while getting your work done. Seek out the kind of people who want to help. Talk to people who are going to nod their heads and say “Yes, you can do that” and then will help you figure out how. I mean this not just for technology, but for your career and personal development. Find positive people and positive experiences (like SQL Cruise), that are going to lift you up and in turn enable you to lift your career.

Feb 01 2016

Learning R: Happy With My Book Choice

This is just a quick update. I wrote about the two books I’m using to learn R. Well, I’m extremely happy with my choices, especially with the Using R for Introductory Statistics book. It has problems at the end of each chapter. No, unlike our elementary school math books the answers are not in the back of the book (you knew that, right?). The answers are however very clearly within the text of each chapter. I’ve really been enjoying these little problems. They’re helping me cement both my understanding of R and my understanding of the mathematics. I strongly recommend this book.

Jan 29 2016

PASS Board 2016: Update #1

Hello everyone. Just because I’ve moved on to the executive committee doesn’t mean I’m walking away from these reports. I will continue to communicate all that I can about my role as EVP throughout the year. One of our commitments this year is providing greater insight into each of the portfolio roles, including the Executive. You’ll start to see more communications in the coming weeks of each of our roles.

The last month has largely been about learning my new role.

As EVP, my primary responsibilities include working closely with PASS HQ on finances and governance. Some of these responsibilities may seem tedious and mundane, but they are an essential part of ensuring that PASS delivers on its mission to provide our global community with the best professional development and networking opportunities.

My first tasks included reviewing the budget and becoming familiar with the by-laws. I’ve sat in on my first ExecCo meetings and approved my first round of bills. I have also held individual meetings with the various department heads at PASS HQ so that I can better understand their roles and day-to-day issues to help address them and better serve you all.

One of our in-person board meetings was this month. Unfortunately, I had to travel to the UK for work. But I still attended the meeting. That’s right; I’m not going to stop working for you just because I’m one-third of the way around the planet. I spent the evenings in my hotel after work, staying up late, to be online and at the meeting. I took part in the discussions and the votes. I thought it was a very productive meeting and we made good progress on some very important topics, further details of which will be revealed to you very soon. I’m impressed by the seriousness and capability of my fellow board members. You guys are being well served by this board.

Nothing else to report at this point. I’m still largely getting my feet underneath me in my new role.

Jan 27 2016

The Importance of a Full Backup in SQL Server

This is the first of 12 posts this year in support of Tim Ford’s #iwanttohelp initiative. These will be completely 100 level, introductory blog posts meant to help people that are just getting started as data professionals. I’ll try to cover several different topics over the year, but felt I should start with what I think is the most important, backups.

It is impossible to overstate the importance of getting a good backup of your SQL Server databases. A backup is the most fundamental of protections for the information on which your business is dependent. Since SQL Server is a service, it manages it’s own files. Because of this, you can’t just copy the *.mdb file where your data is stored. Instead, you must run a process, usually through the BACKUP command within SQL Server. That link goes to the MSDN documentation at Microsoft (which is always your first source for information about SQL Server). The first command outlined there is BACKUP DATABASE… This will create a complete, page-by-page (everything is stored within the database file in a construct called a page), copy of your database, including every single object within the database and all the data.

Well, you think, I’ve got that bit. I’ll take one of those full backup thingies and I’m all set. Ooh, look there are all kinds of other backups too. Log, Differential, File, Filegroup and options, COPY_ONLY, MIRROR… Not only does this get complicated, quickly, but it’s very easy to lose sight of the most fundamental aspect of all this. That is, that backups are not the main thing you’re going for here. The main thing you’re going for here is the ability to RESTORE your database. That link goes to the MSDN entry on RESTORE.

As you explore all the aspects of backups, you must always remember that the most important thing is that you can restore those backups. The most important aspect of the RESTORE operation, whether you’re restoring a full backup, a differential backup, a file backup a filegroup backup or a log backup is that every single one of them starts with a restore of the full backup. If you lose your full backup, that complete, BACKUP DATABASE command, you can’t run the first restore needed. Yes, taking log backups means that you can restore to a point in time. That starts with a restore of the database from a full backup. Yes, taking differential backups can be faster because they’re copying fewer pages to create a backup. Restoring a differential is only possible when you have the full backup that was taken before that differential was taken. The same goes for the rest of the backups (with some exceptions that are WAY beyond entry level). You must have that full backup before you can do the rest. This simple fact is why the full backup is so important.

So please, if you’re just getting started working with SQL Server, then get your backups in order, but make darned sure that you know exactly where your backups are stored and that you can get to them when the time comes to restore a database. Without that full backup, you’re in serious trouble.

I have a video of exactly how to take a full backup of your database using T-SQL to help you get started. Just remember, don’t lose that backup.

Jan 25 2016

Finding Your Query in Query Store

Query Store is pretty amazing. I’m loving working with it. I think it’s likely to change how query tuning will be done in the future. Lots of people are probably going to just use the reports and tools in SQL Server Management Studio. However, a pretty healthy chunk of us will start using the system views in order to programmatically access the information stored in Query Store. One of the first things you’re going to want to do is track down your query.

The primary views you’ll want are sys.query_store_query and sys.query_store_query_text. They join together based on the query_text_id. Let’s take four scenarios and see if we can retrieve the correct query. First up, an ad hoc query:

SELECT  e.NationalIDNumber,
        p.LastName,
        p.FirstName,
        a.City,
        bea.AddressTypeID
FROM    HumanResources.Employee AS e
JOIN    Person.BusinessEntityAddress AS bea
        ON bea.BusinessEntityID = e.BusinessEntityID
JOIN    Person.Address AS a
        ON a.AddressID = bea.AddressID
JOIN    Person.Person AS p
        ON p.BusinessEntityID = e.BusinessEntityID
WHERE   p.LastName = 'Hamilton';

If we wanted to retrieve this from the Query Store AdventureWorks2014, we’d run a query like this:

SELECT * FROM sys.query_store_query_text AS qsqt
WHERE qsqt.query_sql_text = 'SELECT  e.NationalIDNumber,
        p.LastName,
        p.FirstName,
        a.City,
        bea.AddressTypeID
FROM    HumanResources.Employee AS e
JOIN    Person.BusinessEntityAddress AS bea
        ON bea.BusinessEntityID = e.BusinessEntityID
JOIN    Person.Address AS a
        ON a.AddressID = bea.AddressID
JOIN    Person.Person AS p
        ON p.BusinessEntityID = e.BusinessEntityID
WHERE   p.LastName = ''Hamilton''';

Of note, I had to drop the statement terminator from the text of the query, the semi-colon, in order to retrieve the correct query. That’ll be good to know in a moment. This retrieves the query information I requested, just fine.

For our next example, let’s simplify things a whole bunch:

SELECT  *
FROM    Production.BillOfMaterials AS bom
WHERE   bom.BillOfMaterialsID = 2363;

If I then attempt to retrieve the information from Query Store like this:

SELECT  *
FROM    sys.query_store_query_text AS qsqt
WHERE   qsqt.query_sql_text = 'SELECT  *
FROM    Production.BillOfMaterials AS bom
WHERE   bom.BillOfMaterialsID = 2363';

It actually doesn’t work. Note, I took off the statement terminator, just like before. In fact, the problem here can be identified if we look at the T-SQL from the execution plan from the second example:

SELECT * FROM [Production].[BillOfMaterials] [bom] WHERE [bom].[BillOfMaterialsID]=@1

This query has gone through simple parameterization. So, in order to retrieve the information from Query Store, we have a function, sys.fn_stmt_sql_handle_from_sql_stmt that we have to incorporate like this:

SELECT  qsqt.*
FROM    sys.query_store_query_text AS qsqt
JOIN    sys.query_store_query AS qsq
        ON qsq.query_text_id = qsqt.query_text_id
CROSS APPLY sys.fn_stmt_sql_handle_from_sql_stmt('SELECT  *
FROM    Production.BillOfMaterials AS bom
WHERE   bom.BillOfMaterialsID = 2363;', qsq.query_parameterization_type) AS fsshfss

And note, I have the statement terminator back in place, but this function takes that into account. I joined to the query_store_query table in order to get the parameterization type value. With the function figuring out the statement handle based on the text I originally ran the query through, everything is hunky dory.

Let’s look at one more example:

EXEC dbo.spAddressByCity
    @City = N'London';

This is a stored procedure, so I can just do this:

SELECT  qsqt.*
FROM    sys.query_store_query_text AS qsqt
JOIN    sys.query_store_query AS qsq
        ON qsq.query_text_id = qsqt.query_text_id
WHERE   qsq.object_id = OBJECT_ID('dbo.spAddressByCity');

However, you may have multi-statement stored procs, so you might want to query based on the text within the procedure like this:

SELECT  *
FROM    sys.query_store_query_text AS qsqt
WHERE   qsqt.query_sql_text = 'SELECT  a.AddressID,
        a.AddressLine1,
        a.AddressLine2,
        a.City,
        sp.Name AS StateProvinceName,
        a.PostalCode
FROM    Person.Address AS a
JOIN    Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
WHERE   a.City = @City'

Bad news. That doesn’t work. If you looked at the qsqt.query_sql_text value in the previous Query Store query using the OBJECT_ID function, you’ll see that the query looks like this within the Query Store:

(@City nvarchar(30))SELECT  a.AddressID,          a.AddressLine1,          a.AddressLine2,          a.City,          sp.Name AS StateProvinceName,          a.PostalCode  FROM    Person.Address AS a  JOIN    Person.StateProvince AS sp          ON a.StateProvinceID = sp.StateProvinceID  WHERE   a.City = @City

But, if you look at the documentation for sys.fn_stmt_sql_handle_from_sql_stmt that I linked above, it only works with simple or forced parameterization (this, despite having options for none and user, 0 & 1 respectively in the documents). That means you may be resorted to the use of LIKE to retrieve particular statements:

SELECT  qsqt.*
FROM    sys.query_store_query_text AS qsqt
WHERE qsqt.query_sql_text LIKE '%SELECT  a.AddressID,
        a.AddressLine1,
        a.AddressLine2,
        a.City,
        sp.Name AS StateProvinceName,
        a.PostalCode
FROM    Person.Address AS a
JOIN    Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
WHERE   a.City = @City%';

At least, that’s where we’re at with the current, public, CTP.

Tracking down your query can be a little bit of work and hopefully these tips will make it a little easier.

Jan 18 2016

Removing All SQL Server Query Store Data

While setting up example code for my presentation at SQL Cruise (which is going to be a fantastic event), I realized I wanted to purge all the data from my Query Store, just for testing. I did a series of searches to try to track down the information and it just wasn’t there. So, I did what anyone who can phrase a question in less than 140 characters should do, I posted a question to Twitter using the #sqlhelp hash tag.

Jamey Johnston (t|b) came through… and it was right there in the documentation that I had been reading, over and over. In fact, it was in the documentation in two different places. Reading is clearly a problem for me today.

Just so that you know, it’s actually really easy:

ALTER DATABASE AdventureWorks2014 SET QUERY_STORE CLEAR;

By the way, Query Store is absolutely amazing. Did you know that you can set up an extended event to fire when Query Store detects a query regression? Yep. The extended event query_store_aprc_regression will do just that. Query Store is coming with SQL Server 2016, but it’s already in production in Azure SQL Database.

Oh yes, we’re moving into some very interesting times for query tuning. Now if I can just get my reading comprehension to keep up…

Jan 13 2016

Learning R: Hitting the Books

IMG_20160111_075959238I’ve been using a series of web sites while I was starting the process of learning R. I’ve highlighted several of these in previous posts. While people will claim to learn better based on a single source-type, studies have found that you actually learn best by a multitude of methods. So, in addition to video instruction, web sites, guides, etc., I’m also going to read a few books to help learn R.

After a lot of research, I’ve arrived at two that I’m starting with. The first is R In Action. I’m already on the 2nd chapter and I’m enjoying the style and approach. The other book is Using R for Introductory Statistics. I’m using this book because as I have already picked up, the real trick to learning R is not figuring out the syntax and methodology, but rather understanding the math and the data in support of that math. This book seems to cover those aspects better than the R In Action book.

I’ll post actual reviews of these books when I’m done with them. In the mean time I hope my explorations are proving a little bit useful to those who are starting down the path.

Jan 12 2016

“Applies To…” in the MSDN Documentation

Quick little post. I just wanted to share how happy I am with the new “THIS TOPIC APPLIES TO” infographic. An example here:appliesto

I think it makes things much more clear when you’re attempting to figure out what’s up with some T-SQL syntax.

Well done Microsoft and thank you.

Side note, this only exists in documentation that has been updated recently. I first saw it in some documentation that was updated January 11, 2016. It’s not there in another piece of documentation I saw that was updated October 15, 2015. Here’s hoping it gets put everywhere. It works.

Jan 11 2016

Restoring a Database in Azure

One of the many small things you don’t have to worry about any more when working with Azure SQL Database are those pesky backups… Ha-ha-ha! Yeah, I can’t keep a straight face when I type it. Microsoft takes on the role of DBA and creates backups and log backups for you when you’re working in the Platform as a Service offering of Azure SQL Database. If that doesn’t scare you, I’m not sure what will. However, there’s good news. There are still ways for you to create your own backup (through the bacpac, I outlined it here, years ago). More good news is, Microsoft’s backups actually work. Testing it out is easy. Let’s walk through it once.

I’m going to assume you have an Azure account on which you already have Azure SQL Databases created. If you navigate to one of your databases, the screen, in the new portal, looks something like this:

azuredb

If you look at the tool bar at the top, you can see the command for “Restore.” Clicking on that opens another window:

AzuredbRestore

The first, and most important thing to notice here is that it’s supplying me with a new name. I can change that to anything I want as long as it’s not the name of a database already in existence on my Azure SQL Database Server. You read that correctly, you can restore a database to Azure SQL Database, but there is no WITH REPLACE option. The restore creates a new database. This is important. In a recovery scenario, you need to be sure that you’re prepared to deal with this fact. How could you replace the existing database? Immediately run a pair of ALTER DATABASE commands to change the name of the existing database to something else and then change the name of your newly created database to the old name. That’s your choice.

The other options and information are pretty clear. You can decide on the server you’d like to restore to, allowing you to move or copy a database to another server. You get the current date and time. More importantly, you get the oldest restore point. This is determined by the service tier that this database is currently defined on. Different service tiers have different SLAs from Microsoft on how much backups are kept around. At the bottom you can pick the date, within the range of backups you have available, as well as the hour and minute. At the bottom of the screen, not shown, you can then click Create. That’s right, Create, not Restore. Why? Because, as I already said, the restore operation is creating a new database.

I’ve tested this a number of times with databases from various tiers. It works and works well. I will caution you on another point. Let’s say you want to restore just, say, a couple of rows of data or a table, not the entire database. You’re going to have to get imaginative with how you deal with that because, let’s say I run this query:

SELECT  *
FROM    [CruiserDirectory_2016-01-07T09-04Z].dbo.table3;

The results I get are an error:

Msg 40515, Level 15, State 1, Line 16
Reference to database and/or server name in ‘CruiserDirectory_2016-01-07T09-04Z.dbo.table3’ is not supported in this version of SQL Server.

Because, you can’t do cross-database queries in Azure SQL Database.

2f31427My best suggestion here, take a look at a third party solution that allows you to compare the data between two databases, even in Azure, and then provides you with the ability to move those rows or tables that you need. Hint, hint, nudge, nudge, wink, wink, SAY NO MORE! You can then capture the data from one database and move it into the other pretty easily.

That’s it. Azure SQL Database does provide a very simple mechanism for getting at your backups that are automatically created for you. That’s pretty cool.