Search Results for: automatic tuning

Correlated Datetime Columns

SQL Server is a deep and complex product. There's always more to learn. For example, I had never heard of Correlated Datetime Columns. They were evidently introduced as a database option in SQL Server 2005 to help support data warehousing style queries (frequently using dates and times as join criteria or filter criteria). You can read up on the concept here from this older article from 2008 on MSDN. However, doing a search online I didn't find much else explaining how this  stuff worked (one article here, that didn't break this down in a way I could easily understand). Time for me to get my learn on. The concept is simple, turning this on for your database means that dates which have a relationship, the example from MSDN uses OrderDate and…
Read More

Monitor Query Performance

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

Implicit Conversion and Performance

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

But I Don’t Have a Test Server…

I frequently see statements on forums along the lines of "I don't have a test server, so I'm going to do something I've never done before directly on my production servers..." This is usually followed by questions along the lines of: But that's OK and I won't get fired, right? So how do I know if it worked? Is that dangerous? The replies are: No. You should be fired You won't By All the Gods! Yes! It's stupidly, insanely, I don't really like my production server, my data, my boss, or my job, dangerous. Yes. I understand. You're working for a not-for-profit, so you can't afford tons of new servers. You're looking at a 50tb production server of which, you can't make a copy. Your bosses just don't want to spend the…
Read More

Statistics and Natively Compiled Procedures

Statistics are one of the single most important driving factors for the behavior of the query optimizer. The cardinality estimates stored within the statistics drive costing and costing drives the decision making of the optimizer. So, how does this work with the new SQL Server 2014 natively compiled procedures? Differently. In-memory tables do not maintain their statistics automatically. Further, you can't run DBCC SHOW_STATISTICS to get information about those statistics, so you can't tell if they're out of date or not or what the distribution of the data is within them. So, if I create some memory optimized tables, skip loading any data into them and then run this standard query: SELECT a.AddressLine1, a.City, a.PostalCode, sp.Name AS StateProvinceName, cr.Name AS CountryName FROM dbo.Address AS a JOIN dbo.StateProvince AS sp ON sp.StateProvinceID =…
Read More

Natively Compiled Procedures and Bad Execution Plans

I've been exploring how natively compiled procedures are portrayed within execution plans. There have been two previous posts on the topic, the first discussing the differences in the first operator, the second discussing the differences everywhere else. Now, I'm really interested in generating bad execution plans. But, the interesting thing, I wasn't able to, or, rather, I couldn't see evidence of plans changing based on silly things I did to my queries and data. To start with, here's a query: CREATE PROC [dbo].[AddressDetails] @City NVARCHAR(30) WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') SELECT a.AddressLine1, a.City, a.PostalCode, sp.Name AS StateProvinceName, cr.Name AS CountryName FROM dbo.Address AS a JOIN dbo.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID JOIN dbo.CountryRegion AS cr ON…
Read More

How to Set Up Managed Backups in SQL Server 2014

Earlier this week I introduced the concept of Managed Backups (and caused less of a turmoil than I thought I would). Now I want to show you how it works. It's really simple and quite well documented. Before you get to the, insanely simple, task of actually enabling Managed Backup, you will need to go through the prerequisites. First, and this should be obvious, but I'll state it, just in case, you need to set up an Azure storage account. That's so insanely straight forward that I'm not going to say more. Then, you have to set up encryption on your system. I used these commands to prep it: CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$qlserver2012queryperformancetuning'; CREATE CERTIFICATE CloudDojoCert WITH SUBJECT = 'Backup Encryption Certificate'; Again, shouldn't have to…
Read More