Ever have a database log file grow to epic proportions on the weekend? I have. Ever forget to set up job monitoring on that server? I have. Ever get so pissed off that you decided to write a job to automatically shrink the log files whenever they grow? I have.
This Is How We Handle Problems
Jun 11, ’10
11:45 PM
I had a production issue tonight. Still am, actually. I’ve admitted to it and here’s the email I’m sending to my management.
At 9:00PM I took a backup of database_a and database_b prior to running the database migration scripts. Once the backups were finished, I began the migration process at approximately 9:20.
I stopped the migration process at 10:15 after multiple failures and restarts. There are too many unknown cross-dependencies to go on with the roll forward. At this time I called SUPPORT PERSON and explained the situation. I also called MANAGEMENT and left a voice mail. I then began the process of restoring the production databases on SERVER_A.
No changes were made to SERVER_FIGHTING_MONGOOSE or SERVER_C.
Once I had restored database_b and database_a on SERVER_A, I began seeing multiple failures from replication and the rest of SQL Server indicating severe problems with the physical disk structure. I immediately stopped all replication involving database_a and database_b on SERVER_A and I have begun a physical drive integrity check using SQL Server’s built-in integrity check tool: DBCC CHECKDB. The CHECKDB for database_b finished at 11:15 with a clean bill of health. database_a is still running as of 11:31PM.
Once the CHECKDB process for database_a is complete, I will begin re-initialize the subscription for the database_a database on SERVER_A. Following the successful completion of the database_a re-initialization, I will begin the process of re-initializing the subscription to database_b.
If you have any questions, feel free to contact me at 867-5309.
See what I did there?
- I stated how we got into this mess – I dropped a running chainsaw into the SAN.
- I outlined my decision making process and took ownership of rolling back our production migration.
- I described the situation after the migration was rolled back and provided an assessment based on what I had observed.
- I outlined a course of action to mitigate our problems and restore our production database to an operational state as soon as possible.
Am I proud? Not really. I like it when things work. Am I tired and cranky? Yes.
Will I get this fixed before I go to bed? Hell yeah.
Is this something that I, in a sick way, live for? Only because it reminds me to keep studying and to stay on my toes.
How I Analyzed the PASS 2010 Summit Submissions
Jun 9, ’10
8:00 AM
Want to know how I analyzed the Summit session data? I exported the data from the abstract selection system into a tab delimited file. Since I use a Mac at home, I used the tools available to me: PostgreSQL.
I loaded the data using the PostgreSQL COPY command to bulk load the report output and then did some data mojo using PostgreSQL. Most of it was exactly the same as it would be for SQL Server with a few differences. Here’s the code that I used:
SELECT COUNT(*) AS total_sessions
FROM abstracts;
GO
-- Yup, I used the GO statement separator. This is a byproduct
-- of the client that I use, not because of PostgreSQL.
SELECT COUNT(DISTINCT first_name || ' ' || last_name) AS name_count
FROM abstracts ;
GO
SELECT job_title,
COUNT(job_title) AS jt_count
FROM ( SELECT job_title,
first_name,
last_name
FROM abstracts
GROUP BY job_title,
first_name,
last_name
) AS x
GROUP BY job_title
ORDER BY jt_count DESC;
GO
WITH c AS (
SELECT job_title,
first_name,
last_name
FROM abstracts
GROUP BY job_title,
first_name,
last_name
)
SELECT SUM(CASE WHEN LOWER(job_title) LIKE '%engineer%' THEN 1 ELSE 0 END) AS engineer_count,
SUM(CASE WHEN LOWER(job_title) LIKE '%manager%' THEN 1 ELSE 0 END) AS manager_count,
SUM(CASE WHEN LOWER(job_title) LIKE '%developer%' THEN 1 ELSE 0 END) AS developer_count,
SUM(CASE WHEN LOWER(job_title) LIKE '%consultant%' THEN 1 ELSE 0 END) AS consultant_count,
SUM(CASE WHEN LOWER(job_title) LIKE '%business intelligence%' THEN 1
WHEN LOWER(job_title) LIKE '%b.i.%' THEN 1
WHEN LOWER(job_title) LIKE '%BI%' THEN 1
ELSE 0 END) AS bi_count,
SUM(CASE WHEN LOWER(job_title) LIKE '%architect%' THEN 1 ELSE 0 END) AS architect_count
FROM c;
GO
SELECT COUNT(DISTINCT company) AS company_count
FROM abstracts;
GO
SELECT DISTINCT
category,
track,
COUNT(track) OVER (PARTITION BY category) AS the_count_by_category,
COUNT(track) OVER (PARTITION BY category, track) AS the_count_by_track
FROM abstracts
ORDER BY category, track;
GO
SELECT AVG(author_count) AS author_average
FROM ( SELECT DISTINCT
first_name,
last_name,
COUNT(session_title) OVER (PARTITION BY first_name, last_name) AS author_count
FROM abstracts
) AS x;
GO
-- You might be weirded out by this string_to_array and array_upper.
-- PostgreSQL has an array data type. We're using an array of strings
-- to get an accurate word count
SELECT AVG(CHAR_LENGTH(abstract)) AS avg_char_count,
STDDEV(CHAR_LENGTH(abstract)) AS char_count_stddev,
AVG(array_upper(string_to_array(abstract, ' '), 1)) AS avg_word_count,
STDDEV(array_upper(string_to_array(abstract, ' '), 1)) AS word_count_stddev
FROM abstracts;
GO
-- Hey, look at that, we can use a single TRIM function to do all work
SELECT SUM(CASE WHEN TRIM(BOTH FROM presented_before) = 'YES' THEN 1 ELSE 0 END) AS presented_before,
SUM(CASE WHEN TRIM(BOTH FROM presented_before) = 'NO' THEN 1 ELSE 0 END) AS never_presented
FROM abstracts;
GO
SELECT level,
COUNT(*)
FROM abstracts
GROUP BY level
ORDER BY level;
GO
Push Ups and String Concatenation
Jun 1, ’10
8:00 AM
SQL sucks at string manipulation. It’s not just SQL Server, it’s the SQL language in general. I’ve mentioned this before on Stack Overflow in answer to the question “What are five things you hate about your favorite language?” It’s almost easier to return the raw rows to application code than it is to work with them inside of a database. There’s one place where SQL Server has made it easier to work with string data – string building.
How Much Space Am I Taking Up?
May 27, ’10
8:00 AM
Are you running out of space? Filling up drives? Don’t know where to go? Is your SAN administrator complaining about your continued demands for more spindles? Do you need to juggle databases around on different servers but don’t know how you’re going to figure out the size of your index, table, and materialized view filegroups as well as the filegroups you’ve created for every schema just to piss off the DBA?
Take a step back and relax. I’ve got it covered.
-- i hate you too, temp table
IF OBJECT_ID(N'tempdb..#db_files') IS NOT NULL
DROP TABLE #db_files ;
-- load up the temp table
SELECT DB_NAME([dbid]) AS database_name ,
[filename] ,
UPPER(LEFT([filename], 1)) AS drive_letter ,
( size * 8 ) / 1024 AS size_in_mb
INTO #db_files
FROM sys.sysaltfiles AS saf
ORDER BY saf.[dbid]
-- how full is each drive?
SELECT drive_letter ,
SUM(size_in_mb) AS size_in_mb
FROM #db_files AS df
GROUP BY drive_letter
ORDER BY drive_letter ;
-- how big is each database?
SELECT COALESCE(database_name, 'RESOURCE DB') AS database_name ,
SUM(size_in_mb) AS size_in_mb
FROM #db_files AS df
GROUP BY database_name
ORDER BY database_name ;
-- what do the details look like?
SELECT database_name ,
[filename] ,
size_in_mb
FROM #db_files ;
Free SQL Server Training in April
Mar 30, ’10
10:00 AM
I’m going to be speaking at a few events in April. I bet you want to come visit and hear the crazy sounds that I’m going to be making!
Thursday April 8th – Columbus, OH
Fundamentals of SQL Server Internals
The Abstract: Want to know what makes SQL Server tick?
Ever wonder what SQL Server is doing when you run a query?
Ever wonder which parts of SQL Server are responsible for specific functionality?
Jeremiah Peschka can’t promise answers to every question, but he can set you on the path to knowledge about the inner workings of SQL Server.
Location: Battelle for Kids – 1160 Dublin Rd Suite 500, Columbus, OH 43215
Time: 6:30 PM, but you can show up at 6:00 PM for refreshments and socializing.
The Details: This is a fun little romp around SQL Server’s internals. This isn’t intended to be an exhaustive introduction. It’s supposed to be a fun look at how SQL Server works internally and give you more information to get around.
Saturday April 10 – Richmond, VA
I’m speaking at SQL Saturday 30 in Richmond, Virginia!
Fundamentals of SQL Server Internals
This one starts at 8:30 in the morning, so bring your coffee!
A Dynamic World Demands Dynamic SQL
The Abstract: Dynamic SQL is a misunderstood and much maligned part of a DBA’s tool kit – it can be used to solve difficult business problems, respond to diverse data needs, and alleviate performance problems. Many DBAs reject dynamic SQL outright as a potential source of SQL injections, being poorly performing, or just for being a hacky solution in general. Not so! Jeremiah Peschka has been making extensive use of dynamic SQL throughout his career to solve a variety of problems. He’ll set about dispelling these misconceptions and demonstrate how dynamic SQL can become a part of every DBA’s tool kit.
The Skinny: I sometimes catch flak for this, but I use a ton of dynamic SQL on a daily basis. It’s the only way I could possibly build some of our most complex reports. Frankly, dynamic SQL can perform just as well as anything else that you’re doing so why not take advantage of the tools at your disposal? Come along and learn more.
Saturday April 17 – Chicago, IL
Double whammy! You can travel to Chi-town and see me present both of my talks again but in reverse order!
A Dynamic World Demands Dynamic SQL
Bring your dancin’ shoes we’re getting that party started at 9:00 AM right after the welcome and keynote.
Fundamentals of SQL Server Internals
Less dancing, more learning this one goes on at 3:00PM. Hopefully your brain won’t be full by this point in time. If it is, I suggest that you empty it.
Rounding to the Nearest X Minutes, the Lazy Way
Feb 24, ’10
1:00 PM
A lot of people use calendar tables. I’ve blogged about it before. They’re incredibly helpful. Now, have you ever needed a table of minutes?
You’re probably asking, “Jeremiah, why the heck would I ever need a table of minutes?” Well, dear reader, I’m going to tell you that. Please stop interrupting.
Let’s say you have a report. This report shows the sum of sales per 5 minute increment. You could do a lot of trickery with math to make this report happen, doing things like this to get the 5 minute interval:
SELECT
(DATEPART(mi, CAST('2009-01-01 00:01:00' as datetime))+4) / 5 * 5 AS [05]
It works, but it’s ugly as sin. I said to myself, “Self, there has to be a better way.” Turns out that there is a better way: table valued functions!
I created a table valued function to return 60 rows, one for each minute. It also rounds to the nearest 5, 10, 15, and 30 minute intervals. This makes it possible to change the reporting interval very easily by using a join. Check it out:
SELECT DATEPART(hh, s.SaleTime) AS TheHour,
mt.Five AS NearestFive,
SUM(s.SalesAmount) AS AmountSold
FROM dbo.Sales AS s
INNER JOIN dbo.MinutesTable() AS mt ON DATEPART(mi, s.SaleTime) = mt.[Minute]
WHERE -- something is true
GROUP BY DATEPART(hh, s.SaleTime), mt.Five
Why do I call this the lazy way to do this? Because now that I’ve written it once I never have to do it again. I can add new columns to dbo.MinutesTable() without having to worry about breaking anything or copying code incorrectly from one query to another.
Here’s the code to create the function:
CREATE FUNCTION dbo.MinutesTable ()
RETURNS @minutes TABLE (
[Minute] TINYINT,
Five TINYINT,
Ten TINYINT,
Fifteen TINYINT,
Thirty TINYINT
)
AS BEGIN
INSERT INTO @minutes VALUES (0,60,60,60,60)
(1,5,10,15,30),
(2,5,10,15,30),
/* you get the gist of it */
(59,60,60,60,60);
RETURN ;
END
Update: Thanks to Brad Schulz this has gone for a single post about my laziness to an example of a refactoring you can make. If you ever see a table being generated like this, you can take a look at it and determine how you can change it into an inline select. The query optimizer is going to do something completely different for each plan. With the inline select, SQL Server is able to determine that there are 60 rows in our table and can build a much more efficient execution plan.
CREATE FUNCTION [dbo].[MinutesTable] ()
RETURNS TABLE
AS
RETURN
SELECT 0 AS [Minute], 60 AS Five, 50 AS Ten, 60 AS Fifteen, 60 AS Thirty
UNION ALL
SELECT 1, 5, 10, 15, 30
UNION ALL
/* ... this still makes sense, right? ... */
UNION ALL
SELECT 59, 60, 60, 60, 60 ;
GO
A Simple Refactoring – Functions in the WHERE Clause
Feb 17, ’10
9:00 AM
Putting functions in the where clause of a SQL Statement can cause performance problems. If you aren’t careful, these problems can add up and bring a powerful production system to its knees.
For this example, I’m using the Northwind database, but you could do this on any database.
The first thing to do is put an index on the OrderDate column:
CREATE INDEX IX_Orders_OrderDate ON dbo.Orders (OrderDate) ;
Take a look at this first query:
SELECT COUNT(*) FROM dbo.Orders AS o WHERE DATEADD(MM, 2, o.OrderDate) < '1997-05-04' ;
Scanning the stars... or something.
The database is forcing a table scan because of the use of the DATEADD function on an indexed column. SQL Server will have to compute the value of every OrderDate plus 2 months in the database and then compare it to May 4th, 1997.
What happens if we flip this around? What if we change our query so that we’re doing the math on our input value of May 4th, 1997?
SELECT COUNT(*) FROM dbo.Orders AS o WHERE o.OrderDate < DATEADD(MM, -2, '1997-05-04') ;
Seek and ye shall find
So, there you have it: another easy fix that can save you a lot of headache when you’re trying to tune queries.
A Simple Refactoring – Avoiding Table Scans
Feb 11, ’10
10:30 AM
Refactoring SQL code can be pretty easy. Just like refactoring any other programming language, sometimes you have to look around to find the culprit.
We have a slow running query that frequently times out when run by the users. A quick look at the query told me what was wrong. I found this clause in the middle of an otherwise simple query:
LEFT JOIN (
SELECT DISTINCT
key,
column_a
FROM ImportantData
WHERE FieldName = 'VALUES'
UNION ALL
SELECT DISTINCT
key,
column_a
FROM ImportantData_History
WHERE FieldName = 'VALUES'
) AS t ON other.key = t.key
LEFT JOIN (
SELECT DISTINCT
key,
column_b
FROM ImportantData
WHERE FieldName = 'VALUESXY'
UNION ALL
SELECT DISTINCT
key,
column_b
FROM ImportantData_History
WHERE FieldName = 'VALUESXY'
) AS t2 ON other.key = t2.key
The problem with this query is that we’re reading each table twice for the same data. I checked in the execution plan and, sure enough, SQL Server was performing two scans on the underlying data. After some careful thought I realized that I could accomplish the same thing with a single query:
LEFT JOIN (
SELECT key,
VALUES AS column_a,
VALUESXY AS column_b
FROM (
SELECT DISTINCT
key,
FieldName,
CASE WHEN FieldName = 'VALUES'
THEN column_a
WHEN FieldName = 'VALUESXY'
THEN column_b
END AS content
FROM ImportantData
WHERE FieldName = 'VALUES'
OR FieldName = 'VALUESXY'
UNION ALL
SELECT DISTINCT
key,
FieldName,
CASE WHEN FieldName = 'VALUES'
THEN column_a
WHEN FieldName = 'VALUESXY'
THEN column_b
END AS content
FROM ImportantData_History
WHERE FieldName = 'VALUES'
OR FieldName = 'VALUESXY'
) AS source PIVOT( MAX(CONTENT) FOR FieldName IN ([VALUES],
[VALUESXY]) ) AS pvt
) AS t ON other.key= t.key
The upside to this unreadable pile of junk is that it scans each table once and only once. This reduces the load on disk, the amount of data that needs to be read into memory, and it most likely reduces the amount of data stored in memory. And, while this only reduced the estimated query cost by 40 (580 down to 540), we all know that the query cost is only one factor in what actually affects the overall run time of a query.
Many readers are probably mortified that there’s a SELECT DISTINCT in this query. I am too. That being said, I plucked the low-hanging fruit and managed to get a huge performance boost as a result – query execution time went from over 1 minute to 14 seconds. With an improvement like that, removing that distinct isn’t on the radar any more.
When is a Lookup not a Lookup?
Feb 2, ’10
1:00 AM
Execution plans are great things. They give us an insight into how SQL Server is putting together queries and why they run slowly.
One immediate thing I look for in an execution plan is a Key Lookup. In a Key Lookup operation, SQL Server has to reference the clustered index on the table because a value it’s looking for is not present in the index that was used to find the row.
Normally, when you look at the Key Lookup, you will see a list of output columns. This is a great way to help you modify your indexes and make sure that you can avoid these extra disk hits by sacrificing a tiny bit of storage space.
A Normal Output List from a Query Plan
Today, while troubleshooting a query that is never finishing, I ran across this gem.
Empty. Bereft of Meaning. Nothing.
There’s no output list in the Key Lookup. I, wrongly, thought that Key Lookups would always include an output list. What’s going on here?
Well, here’s the scoop. I have the following, sample, table:
CREATE TABLE dbo.ServiceList (
ServiceListId BIGINT NOT NULL PRIMARY KEY,
BillId BIGINT NOT NULL,
ServiceMonth DATETIME
);
In this query, ServiceList is referenced through a CROSS APPLY’d inline function. The inline function references the BillId and ServiceListId. There’s an index on ServiceList that contains both of these columns:
CREATE INDEX IX_ServiceList_BillId ON dbo.ServiceList
(
BillId
)
INCLUDE (ServiceListId, ServiceMonth);
This index is correctly being used by the query optimizer but in the past it’s only been used to perform lookups and push data to screen (or somewhere). So, what we’re seeing here makes sense. The CROSS APPLY needs to reference the BillId so the compiler checks the situation out and decides to use IX_ServiceList_BillId in order get the BillId. Since the index only includes ServiceListId, it has to perform a Key Lookup.
Protip: included data is only included in the index; it is not indexed.
To avoid this kind of behavior, I need to change this index to actually index on the combination of BillId and ServiceListId like so:
CREATE INDEX IX_ServiceList_BillId ON dbo.ServiceList
(
BillId,
ServiceListId
)
INCLUDE (ServiceMonth);
