08Nov 2016 by Grant Fritchey 2 Comments

PASS Board Update: Post-Summit 2016

PASS
Monday I got in on Sunday and chose to have a small dinner with a couple of friends, quiet, preparing. Monday was a less hectic day than the others . The Board had the morning off, although Redgate had me go and give a session at an event. Monday afternoon was one of our three in-person board meetings. The minutes will be published soon. I was responsible for running the meeting. I also presented two topics, first, and most importantly, our current financial status. Then I presented the initial set of thoughts towards some SMART goals for Global Growth, which I will share once they are further developed . Monday evening I had two events I had to attend. First, as part of the Executive Committee, I attended the kick off dinner…
Read More
07Nov 2016 by Grant Fritchey 2 Comments

sp_executesql Is Not Faster Than an Ad Hoc Query

SQL Server, SQL Server 2016, T-SQL
This requires an immediate caveat. You should absolutely be using sp_executesql over any type of non-parameterized execution of T-SQL. You must parameterize your T-SQL because the lack of parameters in building up and executing strings is a classic SQL Injection attack vector. Using straight ad hoc T-SQL is an extremely poor coding choice because of SQL Injection, not because there is something that makes one method faster than the other. Yet, I see in performance checklists that you should be using sp_executesql over straight ad hoc T-SQL because it will perform faster. That statement is incorrect. Some Discussion Let me reiterate the caveat before we continue. I 100% advocate for the use of sp_executesql. This function is preferred over ad hoc SQL because, used properly (and isn't that usually one of the main problems,…
Read More
03Nov 2016 by Grant Fritchey 1 Comment

Reinforcing the Importance of Statistics on Row Estimate

SQL Server, SQL Server 2016, T-SQL
I recently wrote an introductory post about the importance of statistics. I just received a reinforcement on how important they are during my own work. Bad Estimate I hit a weird problem while I was setting up a query to illustrate a point (blog to be published next week). Let's take the basis of the problem and explain it. I wanted data with distribution skew, so I ran this query to find out if there was a wide disparity between the top and bottom of the range: SELECT i.BillToCustomerID, COUNT(i.BillToCustomerID) AS TestCount FROM Sales.Invoices AS i GROUP BY i.BillToCustomerID ORDER BY TestCount ASC; Sure enough, the bottom of the range returned three (3) rows and the top returned 21,551. If I then run a query to retrieve just a few rows…
Read More
01Nov 2016 by Grant Fritchey 5 Comments

Stored Procedures Are Not Faster Than Views

SQL Server, SQL Server 2016, T-SQL
A performance tuning tip I saw recently said, "Views don't perform as well as stored procedures." <sigh> Let's break this down, just a little. Definitions A view is nothing but a query. The definition given by Microsoft is that it's a virtual table that's defined by a query. It's a query that is used to mask data or perform a complex join or similar behaviors. Views are queries that get stored in the database. Views can be easily referred to as if they were a tables. That's it. I've written in the past about views, including how they can possibly perform poorly. A stored procedure is also a query, or a series of queries, or, a whole lot more. Microsoft's definition of a stored procedure basically defines it as programming object that can accept input through…
Read More
24Oct 2016 by Grant Fritchey 10 Comments

A Sub-Query Does Not Hurt Performance

SQL Server, SQL Server 2016, T-SQL
The things you read on the internet, for example, "don't use a sub-query because that hurts performance." Truly? Where do people get these things? Let's Test It I've written before about the concept of cargo cult data professionals. They see one issue, one time, and consequently extrapolate that to all issues, all the time. It's the best explanation I have for why someone would suggest that a sub-query is flat out wrong and will hurt performance. Let me put a caveat up front (which I will reiterate in the conclusion, just so we're clear), there's nothing magically good about sub-queries just like there is nothing magically evil about sub-queries. You can absolutely write a sub-query that performs horribly, does horrible things, runs badly, and therefore absolutely screws up your system. Just as…
Read More
18Oct 2016 by Grant Fritchey 3 Comments

SQL Cruise as a Speaker and Sponsor

Professional Development
SQL Cruise offers a number of unique opportunities for everyone involved, including speakers and sponsors. I've written before, several times, about the benefits of SQL Cruise. As an attendee of the cruise, you will get to sit in classes by amazing people (and me) talking about all aspects of the Data Platform. Further, you get the opportunity to sit down, for long hours, with these people and get, for want of a better description, free consulting time. What about as a speaker and a sponsor though, do we get benefits? As A Speaker Tim Ford is quite a bright fellow. He has tweaked and tuned SQL Cruise. I have been on SQL Cruise a number of times over the last five years, and it has changed radically since my first cruise…
Read More
17Oct 2016 by Grant Fritchey 32 Comments

SELECT * Does Not Hurt Performance

SQL Server, SQL Server 2016, T-SQL
I read all the time how SELECT * hurts performance. I even see where people have said that you just have to supply a column list instead of SELECT * to get a performance improvement. Let's test it, because I think this is bunkum. The Test I have here two queries: SELECT * FROM Warehouse.StockItemTransactions AS sit; --and SELECT sit.StockItemTransactionID, sit.StockItemID, sit.TransactionTypeID, sit.CustomerID, sit.InvoiceID, sit.SupplierID, sit.PurchaseOrderID, sit.TransactionOccurredWhen, sit.Quantity, sit.LastEditedBy, sit.LastEditedWhen FROM Warehouse.StockItemTransactions AS sit; I'm basically going to run this a few hundred times each from PowerShell. I'll capture the executions using Extended Events and we'll aggregate the results. The Results I ran the test multiple times because, funny enough, I kept seeing some disparity in the results. One test would show a clear bias for one method, another test would…
Read More
11Oct 2016 by Grant Fritchey No Comments

Pre-Summit PASS Board Update

PASS
Busy, busy, busy. A lot of the work around PASS currently is getting ready for Summit. I'm prepping my part in the keynotes. I'm involved in lots of community discussions including SQLSaturday, Chapters and Regional Mentors. We're setting up the Community Zone for all sorts of activities throughout the week. We're also going to have one of our few in-person board meetings at Summit. Putting on the single largest gathering of data professionals around the Microsoft Data Platform is actually labor-intensive. The majority of the work is done by the amazing individuals at Christianson & Company, but the board is involved in the necessary decisions and, being the guy who handles finance, I'm in on a lot of those decisions. Personally, I think we're putting together, say it with me, THE BEST SUMMIT EVER.…
Read More
10Oct 2016 by Grant Fritchey 2 Comments

Statistics Are Vital For Query Performance

SQL Server, SQL Server 2016
This is post #10 supporting  Tim Ford’s (b|t) initiative on #iwanttohelp, #entrylevel. Read about it here. When you send a query to your SQL Server database (and this applies to Azure SQL Database, APS, and Azure SQL Data Warehouse), that query is going to go through a process known as query optimization. The query optimization process figures out if you can use indexes to assist the query, whether or not it can seek against those indexes or has to use a scan, and a whole bunch of other stuff. The primary driving force in making these decisions are the statistics available on the indexes and on your tables. What Are Statistics Statistics are a mathematical construct to represent the data in your tables. Instead of scanning through the data each and every…
Read More
05Oct 2016 by Grant Fritchey 20 Comments

Where Do We Go To Share?

Misc
No one reads blogs any more. Twitter is dying. Facebook is broken. LinkedIn? Please. G+. Is that even on any more? Where do we go to share? I'm seeing it here on this blog. Traffic is down. Not just day-to-day traffic, but the search hits. That could just be that I'm producing crap content or stuff that no one is interested in. However, Twitter isn't growing like it once was and there are many reports that it's shrinking. Facebook is running into problems. So... Where the heck are people going? How do we continue to share without a relatively common communication tool? I know there's some push for Yammer. However, lots of people hate it. Slack and slack channels get a little traction, but to a degree this is just another type of…
Read More