Grant Fritchey

29May 2018 by Grant Fritchey 2 Comments

Query To Retrieve Statistics Data: dm_db_stats_histogram

SQL Server 2017, T-SQL

Starting with SQL Server 2016 Sp1 CU2, a new way of directly querying statistics, specifically the histogram, has been introduced: dm_db_stats_histogram. We've always been able to query the statistics using DBCC SHOW_STATISTICS. However, the output, three different result sets with three different structures, made automating access to statistics information a pain. Now, we have more capability through dm_db_stats_histogram. dm_db_stats_histogram To access the information in dm_db_stats_histogram, you just have to pass in the object_id and the statistics_id values for the statistics you're interested in like this: SELECT * FROM sys.dm_db_stats_histogram(OBJECT_ID('HumanResources.Employee'), 1) AS ddsh; It's very straight forward to use. The results look like this: Handy right? Now you can query the histogram directly. Yeah, I hear a few of you saying... and this helps me... how? Here's an example. This query…

21May 2018 by Grant Fritchey No Comments

Why PASS?

PASS

In case this is your first time hearing of it, PASS is the single largest community of Microsoft Data Platform professionals on the planet. It consists of local groups, virtual groups, multiple online events, SQLSaturday, and, to pay for it all, PASS Summit. However, that's not true. It consists of a whole bunch of our peers, people, data pros and developers, trying to do better and be better. PASS I'm currently serving, as an unpaid volunteer, on the Board of Directors of the PASS organization. Actually, truth be told, I'm sitting as president of the board. That means that I'm responsible for the whole shooting match. Our goals are really simple. We want to create as many possible ways for you, me, and all our peers to connect, share and…

14May 2018 by Grant Fritchey 11 Comments

A View Will Not Make Your Query Faster

SQL Server 2017

Twice recently, one on a blog post, and one in a forum post, I've seen people state, unequivocally, without reservation or hint of a caveat, that, "Oh, just put that query into a view. It will run faster." To quote the kids these days... Time for a rant. But First... Frequently when I post something that says, "Query Y runs faster than Query Red", I get responses from people saying, "Yeah, but if you run Query Red more than once..." or "Query Red was experiencing blocking..." or "You can't say Query Y is ALWAYS faster..." So, before we go down that road, a quick note on methodology. First, I'll be using Adventureworks because, reasons. Second, I won't run any of the following queries once. When doing something like this, I'll…

11May 2018 by Grant Fritchey 2 Comments

YouTube Channel Update: 11 May 2018

Misc

I've been busy getting the videos up on YouTube. If they're helpful to you, please subscribe. Some point soon I'll start doing some livestreams, maybe from different events, or during a presentation. If you're subscribed, you'll know about it when it happens. Videos We're getting closer and closer to when implementation of enforcement of the GDPR starts on the 25th of this month. Here's something else to think about in regards to data mapping: [embedyt] https://www.youtube.com/watch?v=GWIhg7uV1IQ[/embedyt] I find over and over that people really don't understand what a deadlock is. I make an attempt at explaining it so that everyone can understand: [embedyt] https://www.youtube.com/watch?v=pmxQGfasm54[/embedyt] Have I mentioned the GDPR yet? Yeah, well, you're also going to have to deal with data portability when dealing with the GDPR. Get your JSON…

07May 2018 by Grant Fritchey 5 Comments

When Simple Parameterization…Isn’t

SQL Server 2017

I'm desperately working to finish up a new version of my book on Execution Plans. We're close, so close. However, you do hit snags. Here's one. My editor decided to change one of my queries. I used a local variable so that I got one set of behaviors. He used a hard-coded value to get a different set. However, the really interesting thing was that his query, at least according to the execution plan, went to simple parameterization. Or did it? Simple Parameterization The core concept of simple parameterization is easy enough to understand. You have a trivial query using a hard-coded value like this: SELECT * FROM Person.Person AS p WHERE p.BusinessEntityID = 42; The resulting execution plan looks like this: The initial, graphical, pointer that we're seeing parameterization…

30Apr 2018 by Grant Fritchey 5 Comments

Automating Automatic Indexing in Azure SQL Database

Azure

I've been in love with the concept of a database as a service ever since I first laid eyes on Azure SQL Database. It just makes sense to me. Take away the mechanics of server management and database management. Focus on the guts of your database. Backups, consistency checks, these easily automated aspects can just be taken care of. Same thing goes with some, not all, but some, index management. Azure SQL Database can manage your indexes for you. I call it weaponizing Query Store. Anyway, I needed a way to automate this for the book I'm writing. I couldn't find any good examples online, so I built my own. Queries in Need of Automatic Indexing Because I want this to be as simple and repeatable as possible, I'm using…

27Apr 2018 by Grant Fritchey No Comments

YouTube Channel Update: 27 April 2018

Professional Development

Despite a hiccup recently on Twitter where I managed to spam all my followers with links to my YouTube videos (so sorry about that), I am still posting videos. Please subscribe to the channel. Video Lists I created video lists. Here they are, in no particular order: SQL Server and Microsoft Data Platform GDPR and How It Relates to Your Data SQL Server Query Performance Tuning Redgate Software Videos DevOps and Databases I hope it helps to have them grouped up. Videos Here are the videos I've posted over the last few weeks. First, too many people take a "poke it and see what happens" approach to query tuning. I try to talk to that issue here: [embedyt] https://www.youtube.com/watch?v=gHxOrPEyPb8[/embedyt] Want to know where I go to learn about the GDPR?…

23Apr 2018 by Grant Fritchey 8 Comments

Presentation Tools

Professional Development

Let's face it, the core of a presentation is you. Your knowledge and your ability to share that knowledge through whatever means you choose, slides, demos, sock puppets, whatever, is the primary tools you need to hone. Know what you know and be ready to acknowledge what you don't. Put out the information as only you can. That's the key to presenting. But... A few things can help reduce your stress and make your life easier. Let's talk about them. My Presentation Tools Whether your travel to the event or not, chances are good, that whatever code camp, SQLSaturday, or awesome event where you are presenting is not your home or office. This means, you can't know what their set up is. Because of this, road warrior or not, if…

16Apr 2018 by Grant Fritchey 4 Comments

Execution Plan Shortcoming in Extended Events

SQL Server, SQL Server 2016, SQL Server 2017

I use Extended Events almost exclusively for capturing query metrics. They are the most consistent and lowest cost mechanism for getting the time and resources used by a query. They can be filtered, combined with other events, they're just marvelous... until you capture an execution plan. Execution Plans in Extended Events Don't get me wrong. Capturing execution plans with Extended Events is the way to go if you're attempting to automate the process of capturing plans on specific queries on an active system. It's step two that bugs me. So, we capture the plan. Here's an example script. Captures all the various plans and the batch, puts 'em together using causality tracking: CREATE EVENT SESSION ExecutionPlansOnAdventureWorks2014 ON SERVER ADD EVENT sqlserver.query_post_compilation_showplan (WHERE ( sqlserver.database_name = N'AdventureWorks2014')), ADD EVENT sqlserver.query_post_execution_showplan (WHERE…

11Apr 2018 by Grant Fritchey No Comments

Privacy and Protection, By Design

Redgate Software

With all the noise about the upcoming enforcement of GDPR, I know that people are starting to focus more on privacy and protection. Add in all the other news about data breaches and data leaks and suddenly, privacy and protection isn't just a business concern, it's personal. Where do you go if you want to learn more? How about the SQL Privacy Summit? SQL Privacy Summit Redgate is hosting the first SQL Privacy Summit on May 18th in London, brought to you by the producers of SQL in the City. Yes, lots of information on the GDPR and compliance will be available. However, we're going way beyond that. The reason it's called the Privacy Summit and not just "The GDPR Summit" is because we're talking about more than compliance. We…