It was 2 AM on February 4, 2008 and I was on the largest conference call in my life. There were dozens of people listening in, from developers to executives. The Super Bowl had concluded hours before and our Vice President of Marketing was asking me if the query was done yet so that everyone could call it a night. The problem was, the query had been running for hours and I had no idea when it would be done.
The query in question was running against a legacy SQL Server database that had grown to 2.5 terabytes and regularly reached 5,000 transactions per second. The database had been hastily developed the weekend before Super Bowl 2006 to facilitate reporting. Putting together a new, complex system just days before a major event is a pretty impressive feat. Unfortunately, best practices are rarely on the radar when developers are in “git ‘er done” mode. Two years and much added functionality later, it was time for a major redesign.
Add to that, this database was on the receiving end of some pretty heavy Super Bowl activity. Go Daddy’s 2008 Super Bowl commercial employed a marketing technique that was revolutionary at the time: drive visits to our website by airing a teaser and enticing the viewer to come to the website to see the commercial’s too-hot-for-TV conclusion. And it worked! We experienced one of the largest advertising spikes in the history of Super Bowl ads. This was great news for the company, but being downhill of that kind of activity wreaked havoc on the database server. While customer-facing systems handled the spike admirably, it took 3.5 hours before all pending inserts were written to the analytical database.
Let’s go back and examine what the query was trying to do. It was joining three large tables, the smallest of which had 200 million rows and the largest of which had 4.5 billion rows. The tables did not have partitioning or covering indexes, and all were clustered on a non-sequential GUID. This meant that we were essentially trying to scan and join every row in three very large tables to see if the results matched the aggregate condition. Later, I also discovered that the tables were 99.99% fragmented. No wonder everything was taking so long!
Fast forward to Super Bowl 2009. What a game! Santonio Holmes scored a touchdown in the last 35 seconds to win it for the Steelers. What’s more, the game set a record for viewership. With a U.S. audience of 98.7 million viewers, it was the most watched Super Bowl in history (at the time). That played no small part in Go Daddy setting its own records, including new customers and orders. This time, however, the database performed a little better:
|Time to Catch Up on INSERTs||3.5 hours||20 minutes|
|Peak Transactions per Second||15,000||27,000|
|Time To Complete Complex Query||2.5 hours||< 1 minute|
|When I Was Able to Leave the Office||2:30 AM (next day)||9:00 PM (same day)|
As you can see in the chart above, the database hit a sustained peak of 27,000 transactions per second. According to Microsoft SQLCAT, this was among the highest transactional rates in the world for a SQL Server instance at that time.
So what changed? Well, it certainly wasn’t the hardware. The database was still running on the same commodity Dell PowerEdge 6850 server and NetApp FAS3040 array from the previous year. It also wasn’t the application tier. They added a significant amount of new functionality but left the old code largely alone. The fact is, we were able to achieve an extraordinary boost in performance just by redesigning the database using best practices.
This post is the first in a series exploring database scalability and very large database (VLDB) performance tuning. In this series, we’ll discuss performance tuning strategies that we have successfully implemented at Go Daddy and, when possible, discuss some of the results I’ve seen firsthand.
A word of warning: I’m going to assume this isn’t your first database rodeo. Also, I really, really like code, so you can be sure that we’ll be getting down and dirty in T-SQL whenever possible.
Although few realize it, one of the biggest decisions a database developer makes is the data types he or she chooses. A well-intentioned but ill-conceived decision to store a critical date or numeric column as a character column can cause years of pain for developers and waste money for a business. One Fortune 500 company I worked with experienced this firsthand. For reasons known only to the original developer(s), a critical column, ProductNumber, was defined as a CHAR(4). Finally, after 20+ years of custom integrations and creative workarounds, the company had no option left but to upgrade the data type. What followed was a very long and expensive modification to nearly every piece of software in the company. Had the developer(s) decided to use a standard integer-based data type, the company would have been able to save a lot of money in its software integrations and avoid a costly data type conversion process.
Back to Go Daddy in February of 2006. In the absence of clear requirements and not having the time to spend on researching it, the developers chose to use the most common data types: all integer columns were stored as a 4-byte INT, all date columns were an 8-byte DATETIME, and all string columns were stored as an NVARCHAR column. 16-byte UNIQUEIDENTIFIER columns were also liberally used to store GUIDs. Web developers generally like GUIDs, or globally unique identifiers. They’re a great way to generate a unique value when a central issuing authority is not available. These are useful for all sorts of things, from uniquely identifying a person to maintaining a unique web session.
Let’s look at one of the largest tables in the database. For security reasons, I’ll replace actual column names with generic names.
CREATE TABLE dbo.BadTable ( myGuid1 UNIQUEIDENTIFIER , myGuid2 UNIQUEIDENTIFIER , myDate1 DATETIME , myDate2 DATETIME , myID1 INT , myID2 INT , myID3 INT , myID4 INT , myID5 INT , myID6 INT , myText1 NVARCHAR(2) , myText2 NVARCHAR(10) , myText3 NVARCHAR(12) , myText4 NVARCHAR(10) , myText5 NVARCHAR(15) , myText6 NVARCHAR(15) , myText7 NVARCHAR(100) , myText8 NVARCHAR(255) , myText9 NVARCHAR(255) CONSTRAINT PK_BadTable PRIMARY KEY (myGuid1) );
The developers chose to use NVARCHAR because they knew that Go Daddy does business internationally and would need to support international characters in some of its columns. Not having the time to identify which columns would need to support Unicode, the developers made the understandable decision to make everything Unicode. The problem with this is that character columns are usually some of the widest in a table, and Unicode consumes twice as much space as non-Unicode.
While we’re on the topic of space, let’s take a look at the storage requirements of the data types in the dbo.BadTable example above.
|Data Type||Values Supported||Storage Requirements|
|varchar(n)||A variable-length non-Unicode alpha-numeric string||actual length + 2 bytes|
|nvarchar(n)||A variable-length Unicode alpha-numeric string||((actual length) * 2)
+ 2 bytes
|char(n)||A fixed-length non-Unicode alpha-numeric string||n defined length|
|tinyint||0 to 255||1 byte|
|smallint||up to -/+ 32,767||2 bytes|
|int||up to -/+ 2,147,483,647||4 bytes|
|bigint||up to -/+ 9,223,372,036,854,775,807||8 bytes|
|smalldatetime||1900-01-01 to 2079-06-06, minute precision||4 bytes|
|datetime||1753-01-01 to 9999-12-13, sub-second precision||8 bytes|
|uniqueidentifier||36-character string in the form of xxxxxxxx- xxxx- xxxx- xxxx- xxxxxxxxxxxx, i.e. 6F9619FF-8B86-D011-B42D-00C04FC964FF||16 bytes|
You might be saying, “Why should I care about 2 bytes versus 4 bytes? Storage is cheap! I can buy a 1 TB drive for $100.” Well, all storage is not created equal, and the biggest bottleneck in most databases is IO. This leads me to the biggest performance tuning tip I can offer: The more data you can fit on a data page, the better your database will perform. It’s really that simple. If you can maximize the storage of data on a page, you can get great database performance.
Using the table of data types above, let’s do some math to figure out how much space dbo.BadTable will consume. To do this, we’ll need to know the average length of each variable-length column:
|Column||Data Type||Average Length||Bytes|
Tip: You can easily identify the average length of a column by selecting AVG(LEN(columnName)) on a table. Because it will scan an entire table, try to avoid running this type of query in peak hours.
Based on the data types specified, dbo.BadTable consumes 336 bytes of storage per row. While I won’t go into the particulars, you also need to be aware that there are an additional 11 bytes of overhead for each row in this particular table. Thus, in actuality, each row inserted into this table consumes 347 bytes of space.
If you are using SQL Server 2005 or later, you can see this for yourself using the following code. This code will insert a row that simulates the typical data footprint of the table and will output the data page of dbo.BadTable using the undocumented DBCC PAGE command.
INSERT INTO dbo.BadTable ( myGuid1 , myGuid2 , myDate1 , myDate2 , myID1 , myID2 , myID3 , myID4 , myID5 , myID6 , myText1 , myText2 , myText3 , myText4 , myText5 , myText6 , myText7 , myText8 , myText9 ) SELECT NEWID() , NEWID() , GETDATE() , GETDATE() , 2500 , 1150 , 185 , 11000 , 75 , 175 , REPLICATE('X', 2) , REPLICATE('X', 8) , REPLICATE('X', 6) , REPLICATE('X', 8) , REPLICATE('X', 6) , REPLICATE('X', 9) , REPLICATE('X', 7) , REPLICATE('X', 32) , REPLICATE('X', 45); DECLARE @dbName SYSNAME = DB_NAME() , @fileID INT , @pageID INT; SELECT @fileID = CONVERT (VARCHAR(6), CONVERT (INT, SUBSTRING (au.first_page, 6, 1) + SUBSTRING (au.first_page, 5, 1))) , @pageID = CONVERT (VARCHAR(20), CONVERT (INT, SUBSTRING (au.first_page, 4, 1) + SUBSTRING (au.first_page, 3, 1) + SUBSTRING (au.first_page, 2, 1) + SUBSTRING (au.first_page, 1, 1))) FROM sys.indexes AS i JOIN sys.partitions AS p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id JOIN sys.system_internals_allocation_units AS au ON p.hobt_id = au.container_id WHERE p.[object_id] = OBJECT_ID('dbo.BadTable') AND au.first_page > 0x000000000000; DBCC TraceOn (3604); DBCC PAGE (@dbName, @fileID, @pageID, 3); DBCC TraceOff (3604);
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
bpage = 0x000000026C7FE000 bhash = 0×0000000000000000 bpageno = (1:283)
bdbid = 5 breferences = 1 bcputicks = 0
bsampleCount = 0 bUse1 = 39082 bstat = 0xb
blog = 0x15ab215a bnext = 0×0000000000000000
m_pageId = (1:283) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0×0 m_level = 0 m_flagBits = 0×8000
m_objId (AllocUnitId.idObj) = 88 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594043695104
Metadata: PartitionId = 72057594039238656 Metadata: IndexId = 1
Metadata: ObjectId = 629577281 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 76 m_slotCnt = 2 m_freeCnt = 7398
m_freeData = 790 m_reservedCnt = 0 m_lsn = (31:366:2)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = -940717766 DB Frag ID = 1
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0×60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0×60 Length 347
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 347
Memory Dump @0x000000001165A060
The actual output is considerably longer; however, we only need this first section for the purposes of this example. Look at “Record Size.” This tells you how many bytes a row actually consumes. If you have variable-length columns, you will see the value change for each row. This value does include row overhead, so you will notice that the Record Size is 347, which is what I expected to see.
A data page can hold 8192 bytes. 96 bytes are reserved for the header, which leaves 8096 bytes available for data. That means a data page can only hold 23 rows in this table (8096 bytes / 347 bytes), assuming there’s no fragmentation. At the time of Super Bowl 2008, this table had 200 million rows. If you divide 200 million by 23 rows, it took 8,695,652 pages to hold our data. Multiply that by 8192 bytes, and you find that this table consumed 66 GB of storage space.
Now let’s look at this exact same table and select the appropriate data types based on our actual storage needs:
|Column||Values Actually Stored|
|myDate1||Precision needed to the minute|
|myDate2||Precision needed to the second (not subsecond)|
|myID1||0 to 2,500|
|myID2||0 to 1,150|
|myID3||0 to 185|
|myID4||0 to 11,000|
|myID5||0 to 75|
|myID6||0 to 175|
|myText1||Always 2 non-Unicode characters|
|myText2||Average 8 non-Unicode characters|
|myText3||Average 6 non-Unicode characters|
|myText4||Average 8 non-Unicode characters|
|myText5||Average 6 non-Unicode characters|
|myText6||Average 9 non-Unicode characters|
|myText7||Average 7 Unicode characters|
|myText8||Average 32 Unicode characters|
|myText9||Average 45 non-Unicode characters|
CREATE TABLE dbo.GoodTable ( myNewID INT IDENTITY(1,1) , myGuid1 UNIQUEIDENTIFIER , myGuid2 UNIQUEIDENTIFIER , myDate1 SMALLDATETIME , myDate2 SMALLDATETIME , myID1 SMALLINT , myID2 SMALLINT , myID3 SMALLINT , myID4 SMALLINT , myID5 TINYINT , myID6 TINYINT , myText1 CHAR(2) , myText2 VARCHAR(10) , myText3 VARCHAR(12) , myText4 VARCHAR(10) , myText5 VARCHAR(15) , myText6 VARCHAR(15) , myText7 NVARCHAR(100) , myText8 NVARCHAR(255) , myText9 VARCHAR(255) CONSTRAINT PK_GoodTable PRIMARY KEY (myNewID) );
Let’s take a minute to discuss some of the decisions made. First, notice that I added a new column, myNewID, to the table. This adds 4 bytes of space but serves a very valuable tuning purpose that I’ll discuss in a later blog post. UNIQUEIDENTIFIER is the correct data type to store a GUID, so I left those two columns as is. I then determined that only two of the string columns actually need to support Unicode, so I left those two columns alone as well. All of the other data types were changed. Why? The date columns were changed from DATETIME to SMALLDATETIME because I only needed minute-level precision. Also, 3 of my ID columns needed to store a value over 255 (the cutoff for a TINYINT) and under 2.1 billion (the cutoff for an INT), making SMALLINT an easy decision. But why did I choose to make myID3, which only needs to store values between 0 and 185, a SMALLINT instead of a TINYINT? When choosing the right data type, you need to choose the smallest data type that makes sense for the business. This ends up being equal parts art and science. My rationale is this: if I was already at 185 after only a year of data collection, there’s a good chance I would exceed the value limit of 255 sometime in the next year. Everyone has a different guideline, but mine is to allow for 3-5 years’ worth of reasonable growth in the business and application. Lastly, I was able to cut the storage requirements for all non-Unicode columns in half by switching from NVARCHAR to VARCHAR.
|Column||Data Type Needed||Average Length||Bytes|
The new table only requires 232 bytes to store the exact same data. Note that there is still an overhead of 11 bytes per row in the new table, too. You can see this for yourself, if you like, by re-running the DBCC PAGE code from earlier and replacing dbo.BadTable with dbo.GoodTable.
At the end of the day, here’s how my changes impacted the performance of the table:
|Defined Bytes Per Row||336||232||-31%|
|Total Bytes Per Row||347||243||-30%|
|Rows Per Page||23||33||45%|
|Pages to hold 200m rows||8,695,652||6,060,606||-30%|
|Table Storage Needs (GB)||66.34||46.24||-30%|
By using the right data types, I was able to reduce the size of my table by 30%, which saves space both on my disks and in my backups. Even better, I was able to improve IO by 45%! Not too shabby.
One last thing I’d like to mention. Don’t wait until you have a large table before paying attention to data types. Many of those small tables, such as reference or dimension tables, tend to become foreign keys in a much larger table. Therefore, selecting the right data types for those tables is equally important.
Database Lesson #1: Pay attention to your data types. They can have a huge impact on database performance.