03May 2016 by Grant Fritchey 5 Comments

Implicit Conversion and Performance

SQL Server, SQL Server 2016, T-SQL
Letting SQL Server change data types automatically can seriously impact performance in a negative way. Because a calculation has to be run on each column, you can't get an index seek. Instead, you're forced to use a scan. I can demonstrate this pretty simply. Here's a script that sets up a test table with three columns and three indexes and tosses a couple of rows in: CREATE TABLE dbo.ConvertTest ( BigIntColumn BIGINT NOT NULL, IntColumn INT NOT NULL, DateColumn VARCHAR(30) ); CREATE INDEX BigIntIndex ON dbo.ConvertTest (BigIntColumn); CREATE INDEX IntIndex ON dbo.ConvertTest (IntColumn); CREATE INDEX DateIndex ON dbo.ConvertTest (DateColumn); WITH Nums AS (SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT 1 )) AS n FROM master.sys.all_columns ac1 CROSS JOIN master.sys.all_columns ac2 ) INSERT INTO dbo.ConvertTest (BigIntColumn, IntColumn, DateColumn ) SELECT Nums.n, Nums.n,…
Read More
12Apr 2016 by Grant Fritchey 8 Comments

A View Is Not A Table

Azure, SQL Server, SQL Server 2016, T-SQL
Blog post #4 in support of Tim Ford’s (b|t) #iwanttohelp, #entrylevel In SQL Server, in the T-SQL you use to query it, a view looks just like a table (I'm using the AdventureWorks2014 database for all these examples): SELECT * FROM Production.vProductAndDescription AS vpad;   SELECT vpad.Name, vpad.Description, vpmi.Instructions FROM Production.vProductAndDescription AS vpad JOIN Production.Product AS p ON p.ProductID = vpad.ProductID JOIN Production.vProductModelInstructions AS vpmi ON vpmi.ProductModelID = p.ProductModelID WHERE vpad.ProductID = 891 AND vpad.CultureID = 'fr'; The above query actually combines two views and a table. This is what is commonly referred to as a "code smell". A code smell is a coding practice that works, but that can lead to problems. In this case, we're talking about performance problems. The performance problems when using views to join to…
Read More
05Apr 2016 by Grant Fritchey 3 Comments

Views and Simplification

SQL Server, SQL Server 2016, T-SQL
I've been getting lots of questions on views lately. Must be something in the water. Because SQL Server allows you to treat a view as if it was a table, lots of people pretty much assume that it is a table since they get to treat it that way. The thing is, a view is not a table. It's a query. Let's explore this just a little bit. Here's a relatively straight forward view: CREATE VIEW dbo.PersonInfo AS SELECT a.AddressLine1, a.City, a.PostalCode, a.SpatialLocation, p.FirstName, p.LastName, be.BusinessEntityID, bea.AddressID, bea.AddressTypeID FROM Person.Address AS a JOIN Person.BusinessEntityAddress AS bea ON a.AddressID = bea.AddressID JOIN Person.BusinessEntity AS be ON bea.BusinessEntityID = be.BusinessEntityID JOIN Person.Person AS p ON be.BusinessEntityID = p.BusinessEntityID; GO I can query this view like this: SELECT * FROM dbo.PersonInfo AS pni…
Read More
07Mar 2016 by Grant Fritchey 2 Comments

SQL Server Backups Are A Business Decision

SQL Server, SQL Server 2016
Blog post #3 in support of Tim Ford’s (b|t) #iwanttohelp, #entrylevel Read more about Tim's challenge here. It's very easy to think of SQL Server backups as a technical problem. You have so much stuff going on, BACKUP DATABASE commands, recovery models, BACKUP LOG commands, Differential backups. Getting them all into the correct order and automating the processes sure seems like a technical problem. It isn't. It's all about the business. If you're taking on the duties of a DBA whether you're an accidental DBA, a reluctant DBA or you were voluntold into the DBA position, you need to plan to sit down with responsible parties from the business and get an understanding with them regarding RPO and RTO. RPO is a TLA for Recovery Point Objective. The easiest way to…
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
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
20Oct 2015 by Grant Fritchey No Comments

Statistics for the New Data Pro

PASS, SQL Server, SQL Server 2016, T-SQL
Next week at the PASS Summit I'll be presenting a session called Statistics for the New Data Pro. You can read the abstract at the link. I just want to emphasize that this is a beginner level session. I think way too many people who are just starting out with SQL Server don't understand the role that statistics play in determining how your queries are going to behave. What's more, too many people don't know how to get and read statistics to understand how it is that the optimizer thinks you have X number of rows in your database that match a given value. I'm going to make darned sure that the people who attend this session come out with a full understanding of how to read the statistics. This includes…
Read More
09Sep 2015 by Grant Fritchey 16 Comments

Yes, Foreign Keys Help Performance

SQL Server
I created this example several years ago that illustrates how foreign key constraints can help performance. It's a contrived example. Granted. I feel like it illustrates the point. However, over the years, people have questioned one aspect of it. The optimizer uses the foreign keys to figure out which tables can be eliminated from the query, making for a more efficient plan and making the query run faster. The pushback has always been, "Yeah, Grant, but nobody writes T-SQL where they include extra tables that they don't need." My initial response, after I stop laughing, is to point out any number of ORM tools. But, you know what, let's assume that's correct. No one would ever create a giant catch-all view that has all their JOINs in one place so they don't have…
Read More
10Aug 2015 by Grant Fritchey 3 Comments

Generating Estimated Plan and the Plan Cache

SQL Server, T-SQL
Does generating an Estimated Plan cause that plan to be loaded into the plan cache? No.   What? Still here? You want more? Proof? Fine. Let's first run this bit of code (but please, not on your production server): DBCC FREEPROCCACHE(); That will remove all plans from cache. Now, let's take this query and generate an Estimated Plan (CTL-L from your keyboard or by clicking on the "Display Estimated Execution Plan" button on the toolbar): SELECT * FROM Production.ProductModel AS pm; This will generate a trivial plan showing a scan against the Production.ProductModel table. Now, let's run another query: SELECT deqs.plan_handle FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.text = 'SELECT * FROM Production.ProductModel AS pm;'; That's just an easy way to see if a plan_handle exists.…
Read More
06Aug 2015 by Grant Fritchey 1 Comment

Reading Your Execution Plans

SQL Server
I'm putting the finishing touches on the all day seminar on execution plans that I'll be putting on at DevConnections this year. I have tons of examples to show you how to interpret execution plans as part of query performance tuning. We'll be able to fill our time. However, I've got a pretty high degree of flexibility on which examples I use. With that in mind, I'd love it if you could bring in your own execution plans that you'd like some help reading. I won't guarantee a solution for your query problems, but I promise to read through it and make some suggestions. If you have an execution plan that you don't mind sharing with the group and you'd like a little consulting time on tuning the query, bring it…
Read More