24Feb 2016 by Grant Fritchey 6 Comments

Query Store and Recompile

Azure, SQL Server 2016, T-SQL
One of the many advantages of SQL Cruise is the ability to have enough time during a presentation to be able to answer questions from the people there in great detail. One question came up while I was showing the new functionality of Query Store (available soon in SQL Server 2016, available right now in Azure SQL Database). What happens to plan forcing when you have OPTION RECOMPILE on a query? Great question. I have a favorite procedure I use to illustrate the functionality of parameter sniffing: ALTER PROC dbo.AddressByCity @City NVARCHAR(30) AS 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; If this procedure is called with the value of 'Mentor' you get…
Read More
22Feb 2016 by Grant Fritchey 3 Comments

Why Is The Server Slow?

SQL Server, SQL Server 2016, T-SQL
This is blog post #2 in support of Tim Ford's (b|t) #iwanttohelp, #entrylevel. If you haven't been working in SQL Server for very long, you may not have got this phone call yet, but you will: Hi, yeah, the server is slow. Thanks. Bye. Let's pretend for a moment that you know which server they're referring to (because just finding out that piece of information can be a challenge). Now what? The list of tools and mechanisms within SQL Server for gathering metrics is extremely long: Performance Monitor Dynamic Management Views & Functions System Views Extended Events Trace Events Activity Monitor Data Collector Execution Plans 3rd Party Tools I'm leaving out lots of stuff in that list. So where do you start when you get this phone call? Where is the server slow?…
Read More
18Feb 2016 by Grant Fritchey 4 Comments

Azure SQL Database Error

Azure
I was on SQL Cruise where I was scheduled to present a session on Azure SQL Database. I recorded all my demonstrations before we went to sea, but, I planned to attempt to try live demo's. Yeah, yeah. Well, anyway, I got a unique error when I attempted to connect: Forced connection closes from remote host That's a partial message from the whole error. I don't have a good screen capture. I wasn't able to find anything on it through Ging searches, but this week I was at Microsoft for a training course on Azure. I asked the room. The rough answer is (paraphrasing): The IP address I was attempting to connect from is not is not on the approved list Interesting. I didn't realize there were blackout zones. The really…
Read More
15Feb 2016 by Grant Fritchey 11 Comments

Loading Data into Azure SQL Data Warehouse

Azure, Data Science
Ouch. Let's start with the level set. I'm not an ETL expert. In fact, I haven't done any professional ETL work for several years. My skills are, at best, rusty. With this in mind, I knew I'd have a hard time extracting data from a local database in order to move it up to Azure SQL Data Warehouse. I expected to be fumbling and slow and to constantly feel more than a little stupid as I made one mistake after another. All of this came to pass. Yet... OMG! THAT WAS DIFFICULT! Here's how I started. I defined a bcp command for the tables I was interested in. I ensured it was working correctly, then wrote a little PowerShell script so I could supply a list of tables and get…
Read More
12Feb 2016 by Grant Fritchey No Comments

Speaker of the Month: February 2016

Professional Development
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)…
Read More
04Feb 2016 by Grant Fritchey 4 Comments

Positivity

Professional Development
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…
Read More
01Feb 2016 by Grant Fritchey No Comments

Learning R: Happy With My Book Choice

Data Science
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.
Read More
29Jan 2016 by Grant Fritchey No Comments

PASS Board 2016: Update #1

PASS
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…
Read More
27Jan 2016 by Grant Fritchey 4 Comments

The Importance of a Full Backup in SQL Server

Database Lifecycle Management, DevOps, Professional Development, SQL Server, SQL Server 2016
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…
Read More
25Jan 2016 by Grant Fritchey 7 Comments

Finding Your Query in Query Store

Azure, SQL Server 2016, T-SQL
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…
Read More