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
12Sep 2016 by Grant Fritchey 6 Comments

The Clustered Index Is Vital To Your Database Design

Azure, SQL Server, SQL Server 2016
This is post #9 supporting  Tim Ford’s (b|t) initiative on #iwanttohelp, #entrylevel. Read about it here. You get one clustered index per table. That bears repeating, you get one clustered index per table. Choosing a clustered index is an extremely important and fundamental aspect of all your SQL Server work. The one clustered index that you get determines how the data in your table is stored. Because the clustered index determines how your data is stored, it also determines how your data is retrieved. Much of SQL Server is engineered around the clustered index because it is such a foundational object for the rest of all behavior. Without a clustered index, the data in your table is stored in what is called a heap. It is essentially a pile, a heap, of data,…
Read More
09Aug 2016 by Grant Fritchey 4 Comments

Azure SQL Database For Your First Database

Azure
This is post 8 supporting  Tim Ford’s (b|t) initiative on #iwanttohelp, #entrylevel. Read about it here. In post #6, I talked about Azure SQL Database as one of the choices you have when you're picking the type of SQL Server instance you want to run. I want to expand on why you should be considering moving into Azure SQL Database at the start of your career and some of the important differences you'll have to be aware of as you get going. Since you are right at the start of your career, you may as well plan on maximizing the life of the knowledge and skills you're building. By this, I mean spend your time learning the newest and most advanced software rather than the old approach. Is there still work for people who only know…
Read More
25Jul 2016 by Grant Fritchey 2 Comments

Monitor Query Performance

Azure, SQL Server, SQL Server 2016
Blog post #7 in support of Tim Ford’s (b|t) #iwanttohelp, #entrylevel. Read about it here. Sooner or later when you're working with SQL Server, someone is going to complain that the server is slow. I already pointed out the first place you should look when this comes up. But what if they're more precise? What if, you know, or at least suspect, you have a problem with a query? How do you get information about how queries are behaving in SQL Server? Choices For Query Metrics It's not enough to know that you have a slow query or queries. You need to know exactly how slow they are. You must measure. You need to know how long they take to run and you need to know how many resources are…
Read More
13Jun 2016 by Grant Fritchey 9 Comments

Choosing the Right SQL Server Edition

SQL Server 2016
Post #6 of #entrylevel #iwanttohelp in support of Tim Ford's (b|t) beginner's initiative. If you're just getting started with SQL Server, the choices you have in front of you are legion. Which drive do you install your instance on? Which drives hold the databases? How many files do you need for a database? What do the tables look like? Which column or columns should be the primary key? Clustered index? Stored procedures? In-Memory? MAXDOP? Et, as they say, cetera. Ad, as they also say, nauseum. Before any of that though, you need to pick the correct type of SQL Server to install. That's right, just saying "SQL Server" is not enough. You must pick between: SQL Server Developer's Edition SQL Server Express Azure SQL Database SQL Server Standard SQL Server…
Read More
12May 2016 by Grant Fritchey 9 Comments

Use The Correct Data Type

SQL Server, SQL Server 2016, T-SQL
Blog post #5 in support of Tim Ford’s (b|t) #iwanttohelp, #entrylevel. Read about it here. Saying that you should use the correct data type seems like something that should be very straight forward. Unfortunately it's very easy for things to get confusing. Let's take a simple example from AdventureWorks. If I run this query: SELECT a.ModifiedDate FROM Person.Address AS a WHERE a.AddressID = 42; The output looks like this: 2009-01-20 00:00:00.000 Normal right? You see the year, the month and the day followed by the time in hours, minutes, and seconds as a decimal. Ah, but there is an issue. This query is supposed to be for the reporting system, and the business only cares about the date that the values in the Person.Address table have been modified, so they don't want…
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
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