SQL Server 2008 R2 SP1 Cumulative Update 2

Posted on August 16, 2011 by Glenn Berry

Microsoft has released SQL Server 2008 R2 SP1 Cumulative Update 2, which is Build 10.50.2772.0. I count 14 fixes in the KB for this Cumulative Update. The most interesting one in my opinion is: FIX: Recovery takes longer than expected for a database in a SQL Server 2008 or in a SQL Server 2008 R2 environment

As always, make sure you understand that this Cumulative Update is only for SQL Server 2008 R2, not for SQL Server 2008 SP2… Even though SQL Server 2008 R2 has been out for nearly two years, people still get confused about this. I really hope that Microsoft Marketing never comes up with a confusing name like that in the future…

Posted in Microsoft, SQL Server 2008 R2 | Tagged | Leave a comment

Gradually Deleting Data in SQL Server

Posted on August 13, 2011 by Glenn Berry

If you have a situation where you have a very large table in SQL Server, where you need to periodically delete tens of millions of rows of data, there are several ways to do it.

If you have a maintenance window (or your database is not required to be available 24 x 7 x 365), you can (and probably should) just delete all of the rows in one shot, using a set based operation. This would be the quickest way to delete a large number of rows, but you will probably end up getting lock escalation to a table lock, which essentially makes the table unavailable during the delete.

Another issue to consider is whether you have transactional replication on that table, and/or you have database mirroring in place on the database. Deleting a large number of rows from a table will generate a lot of log activity, which may cause transactional replication or database mirroring to fall behind. This of course depends on your hardware and network infrastructure. You also want to keep an eye on your transaction log, to make sure it is not filling up and having auto-grow kick in.

A safer, but much more time consuming way to delete millions of rows is to use some sort of looping mechanism, where you gradually delete a fairly small number of rows in a loop, to slowly nibble away at the table. This will take much longer than a set based operation, but, if done properly, will not cause concurrency problems, and will not overwhelm transactional replication or database mirroring.

At any rate, I recently faced a situation like this, so I decided to show one method to deal with it pretty easily. In this case, we want to delete every row that has a TransactionId lower than a certain number. We are going to delete 500 random rows that qualify in each delete, and loop 5000 times, with a slight delay between each delete. This will delete 2.5 million rows each time the query is run.  You can obviously adjust these numbers and the delay time so that it works best in your environment. You could also wrap this into a stored procedure.

-- Gradual Delete Sample
-- Glenn Berry 
-- August 2011
-- https://sqlserverperformance.wordpress.com/
-- Twitter: GlennAlanBerry

SET NOCOUNT ON;

-- Check space used by table before we begin
EXEC sp_spaceused N'dbo.BigLoggingTable';

-- Declare local variables
DECLARE @NumberOfLoops AS int;
SET @NumberOfLoops = 5000;

DECLARE @CurrentLoop AS int;
SET @CurrentLoop = 0

DECLARE @DeleteSize bigint;
SET @DeleteSize = 500;

DECLARE @HighWaterMark bigint;
SET @HighWaterMark = 382989078;

WHILE @CurrentLoop < @NumberOfLoops
    BEGIN
        -- Just delete any xxx rows that are below the HighWaterMark
        DELETE 
        FROM dbo.BigLoggingTable
        WHERE TransactionId IN 
            (SELECT TOP(@DeleteSize) TransactionId 
             FROM dbo.BigLoggingTable WITH (NOLOCK)
             WHERE TransactionId < @HighWaterMark);
             
        WAITFOR DELAY '00:00:00:50';
          
        SET @CurrentLoop = @CurrentLoop + 1;
    END

-- Check space used by table after we are done    
EXEC sp_spaceused N'dbo.BigLoggingTable';
Posted in SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server Denali | 23 Comments

Configuring, Benchmarking and Validating an I/O Subsystem for SQL Server, Part 1

Posted on August 11, 2011 by Glenn Berry

One of the main projects I have been working on lately is designing and implementing a completely new data and storage infrastructure for a high volume, 24×7 online environment. Some of the goals of this effort include improving OLTP performance, improving scalability, reducing the number of SQL Server processor licenses required, and improving the overall HA/DR story for the system as a whole. So, nothing too difficult to accomplish at all…

The starting point for this is to figure out how many, of what type of database servers that we need, along with the type and configuration of the required I/O subsystem. In order to do this, you need to have a pretty good idea about the type and volume of the workload you will be dealing with, whether it is more of an OLTP workload, DW/DSS workload,or a mixture of the two. The workload characteristics will have a direct impact on the processor type, server model, and type of I/O subsystem that you select.

I have argued many times in the past that the latest two socket machines have more than enough CPU, memory, and I/O capacity to handle many SQL Server OLTP workloads. If this is the case for your workload, you have the potential to reduce both your hardware costs and SQL Server processor license costs, along with the benefit of achieving better single-threaded OLTP performance than is possible with a more conventional four socket database server.

For an OLTP workload, I really like the Dell PowerEdge R710 two socket, 2U server.  It allows you to have two Intel Xeon 5600 series (32nm, six core, Westmere-EP) processors. It has an Intel 5520 chipset, and eighteen DDR3 DIMM slots (with a total capacity of up to 288GB of RAM). It has four PCI-E Gen2 expansion slots (two x8 and two x4), along with an x4 Storage slot. It also has eight 2.5” internal drive bays. This server model has been available for a couple of years now, with a good track record. If you decide to use a server that supports an Intel Xeon 5600 series processor, the exact processor you want is the 3.46GHz Intel Xeon X5690, which is their top-of-the-line model.

 

Purposeful, Customer-Inspired Design

Figure 1: Dell PowerEdge R710 Server

 

Starting in about ten days, I am going to get the opportunity to configure, test, and benchmark the I/O performance of several different types of storage devices (including both DAS and internal storage) that will be attached to a shiny, new R710 server (with two Xeon X5690 processors and 192GB of RAM). I will have two Dell PowerVault MD1220 SAS enclosures, two Dell PERC H800 1GB RAID controllers, a 640GB Fusion-io Duo card, and (32) 300GB Intel 320 MLC SSDs to try out in various different configurations.

There are a number of different configurations to try here. For example, the H800 RAID controller supports a new feature called CacheCade. This lets you use up to two SSDs as a read-only cache in front of a number of conventional magnetic SAS drives in RAID arrays controlled by each RAID controller. I want to measure what effect this has on read performance. Another option to test is the best configuration for which controllers go in which PCI-E slots. I have two x8 slots, and two x4 slots, so I am planning on putting the 640GB Fusion-io Duo card in one x8 slot, one H800 in the other x8 slot, with the other H800 in one of the x4 slots.

The overall objective here is to come up with the “best” standardized configuration available with the combination of all of these devices. I want to have a standard, identical configuration for drive letters, with a specific purpose and level of I/O performance (both IOPS and throughput) for each drive letter. You need to consider where the OS and SQL Server binaries will be located, where the OS page file will be located, where the SQL Server data files will be located, where the SQL Server log files will be located, where the TempDB files will be located, and finally, where the SQL Server backup files will be located.

You need to decide what RAID level to use, and how many spindles to use for each RAID array, along with the type of drive (SSD or conventional magnetic SAS). You also need to balance size vs. performance. I think this will be a lot of fun, and I plan to blog quite a bit about the different combinations that I try during this effort.

Posted in Computer Hardware, Processors, SQL Server 2008 R2, Storage Subsystems | 14 Comments

Microsoft SQL Server Denali CTP 3 Product Guide

Posted on August 11, 2011 by Glenn Berry

Microsoft has released a Product Guide for SQL Server Denali CTP3, which has datasheets, white papers, technical presentations, demonstrations, and links to videos that will help you evaluate Microsoft SQL Server Denali. You can get to Books Online for SQL Server Denali CTP3 here. There is also a Feature Pack for SQL Server Denali CTP3 that was released in early July.

SQL Server Denali is coming (perhaps sooner than you think), and I think it is a very good idea to pay attention to what new features will be available that will help you solve some of the daily challenges you face as a DBA. For example, in Denali, clustered indexes on tables that have  varchar(max), nvarchar(max), and varbinary(max) columns can now be rebuilt as an online operation. This is a very important availability feature!  You can read about some of the other Availability enhancements that are a part of SQL Server AlwaysOn here.

Posted in Microsoft, SQL Server Denali | Tagged | Leave a comment

Corsair 16GB Flash Voyager USB 3.0 Review

Posted on August 11, 2011 by Glenn Berry

I bought a little Corsair Flash Voyager USB 3.0 16GB USB Flash Drive at Micro Center yesterday, since most of my machines now have USB 3.0 ports. It is also available online from places like New Egg and Amazon

usb-3_0-flash-voyager-magento_1

For roughly $25.00 you can hardly go wrong here. One negative for the drive is that it is physically pretty large for a flash drive, both in length and width. The blue bulges on the side that make it “finger friendly” according to Corsair, can be a problem when you try to use it in a USB slot that is adjacent to other slots or ports on a laptop.

As with nearly all flash memory devices, smaller capacity models have much lower write performance than the larger models in the same product family. Keeping that in mind, you might want to invest in a larger capacity model, even if you think you don’t really need the extra space. For example, the 32GB version of this drive has nearly double the sequential write performance as the 16GB model.

I decided to run Crystal DiskMark on this drive while it was plugged into the USB 3.0 port on my Toshiba Portege R835-P55X laptop. You can see the results in Figure 1 below:

image

Figure 1: Corsair Flash Voyager USB 3.0 16GB USB Flash Drive

Just for comparisons sake, I ran the same Crystal DiskMark test on an older Patriot Xporter XT 16GB USB 2.0 flash drive plugged into the same USB 3.0 port on the Toshiba. The Patriot was one of the faster USB 2.0 drives, and I have been using it for a couple of years. It is likely to be much faster than the free USB drive that you got from some vendor!

image

Figure 2: Patriot Xporter XT USB 2.0 16GB USB Flash Drive

If you have not done it already, you really want to make the move to a machine that has a USB 3.0 port.  Not only are USB 3.0 flash drives readily available and affordable, you can also easily find 1TB USB 3.0 external drives for less than $100.00.

In a similar vein, you also should be seriously thinking about having a fast SSD drive for a boot device in your machine. The difference in everyday, useful, real-world performance with both of these features is quite dramatic, and definitely worth the money.

Posted in Computer Hardware | Tagged | 4 Comments

Two New TPC-E Submissions

Posted on August 4, 2011 by Glenn Berry

Two new TPC-E OLTP Benchmark Submissions have shown up in the last month or so since I last recapped three recent submissions back on June 15. The TPC-E benchmark is a very useful comparison and sizing tool for OLTP workloads. Assuming you have enough I/O performance to drive the workload, TPC-E performance is primarily limited by CPU performance. The TPC-E benchmark is not as dependent on a completely unrealistic, oversized I/O subsystem like the older TPC-C benchmark was. You are not allowed to use RAID 0 for your storage subsystem with TPC-E (like you could with TPC-C)

The TPC-E benchmark has results going back to 2007, with a number of different processor families and generations represented from both Intel and AMD (although there are only a few results for systems with AMD processors, which tells you something pretty significant). This means that you can probably find a submitted system that has very similar set of processors, if not an exact match for an existing or a planned system that you want to compare.

The first benchmark result is for an IBM System x3850 X5 submitted on June 27, 2011, that has a score of 2,862.61 tpsE. This is for a four socket server, with the 10-core, 2.4GHz Intel Xeon E7-4870 processor. With hyper-threading enabled, this gives you 80 logical processors, along with 1TB of RAM. This system has a total of 105 spindles, primarily using Direct Attached Storage (DAS), which is not an insane amount by any means. Especially since they are only 10K SAS drives in six RAID 5 arrays… It is also using an 11.6TB initial database size.

The second benchmark result is for a Fujitsu PRIMEQUEST 1800E2 submitted on July 27, 2011, that has a score of 4,414.79 tpsE. This is for an eight socket server, with the 10-core, 2.4GHz Intel Xeon E7-8870 processor. With hyper-threading enabled, this gives you 160 logical processors, along with 2TB of RAM. This system has a total of 360 spindles, using Direct Attached Storage (DAS). There are 360 64GB SLC SSDs, with RAID 5 for the data files and RAID 10 for the log file. It is also using an 18.4TB initial database size.

These are actually two pretty interesting benchmark submissions. The Fujitsu system is essentially twice the size of the IBM system (eight sockets vs. four sockets), with twice the RAM (2TB vs. 1TB), and what would seem to be a much more powerful I/O subsystem (SLC SSDs vs. 10K SAS drives), with over triple the spindle count for the bigger system, along with RAID 10 for the log file.

Yet despite all of this, the eight socket system does not have a tpsE score that is twice as high as the four socket system.  A score of 4,414.79 tpsE is only 1.54 times higher than a score of 2,862.61 tpsE. This means we do not see linear scaling as we go from four sockets to eight sockets using the current top of the line Intel Xeon E7 processor.

This is probably a limitation of Non Uniform Memory Access (NUMA), since we did see much closer to linear scaling  going from two sockets to four sockets with this same Intel Xeon E7 family. There was a recent benchmark for an IBM System x3690 X5 system submitted on May 27, 2011, that has a score 0f 1,560.70 tpsE. This is for a two-socket server, with the 10-core, 2.4GHz Intel Xeon E7-2870 processor. With hyper-threading enabled, this gives you 40 logical processors, along with 512GB of RAM. A score of 2,862.61 tpsE is actually 1.83 times higher than a score of 1560.70 tpsE. This is much closer to linear scaling as you go from a two socket system to a four socket system.

What this means for you is that you would probably be better off (from an overall CPU capacity perspective) with two, four socket servers instead of one, eight socket server, assuming you can split or partition your workload between the servers. You would also be better off with two, two socket servers instead of one, four socket server.

Posted in Computer Hardware, SQL Server 2008 R2, Windows Server 2008 R2 | Tagged , , | 2 Comments

Presenting Hardware 301 at 24 Hours of PASS on September 7

Posted on August 3, 2011 by Glenn Berry

PASS just announced today the next occurrence of 24 Hours of PASS (24HOP), which is called the Summit Preview version. I will be doing a session called Hardware 301: Diving Deeper into Database Hardware at 21:00 (9PM) GMT, which is 5PM Eastern US and 2PM Western US time on September 7.

Here is the abstract:

Making the right hardware selection decisions is extremely important for database scalability. Having properly sized and configured hardware can both increase application performance and reduce capital expenses dramatically. Unfortunately, there are so many different choices and options available when it comes to selecting hardware and storage subsystems, it is very easy to make bad choices based on outmoded conventional wisdom. This session will give you a framework for how to pick the right hardware and storage subsystem for your workload type. You will learn how to evaluate and compare key hardware components, such as processors, chipsets, and memory. You will also learn how to evaluate and compare different types of storage subsystems for different database workload types. This session will give you the knowledge you need to make sure you get the best performance and scalability possible from your hardware budget!

Shameless book plug coming…  If you want to get even more background information on this subject to prepare you to get more out of the session, you might consider buying my SQL Server Hardware book!

You can also read Configuring the Storage Subsystem on Simple-Talk.com to get some good background on storage subsystems.

Click here to register for 24HOP.

Posted in Computer Hardware, PASS, Politics, Teaching | Tagged | 3 Comments

August 2011 SQL Server 2008 Instance Level Diagnostic Queries

Posted on August 1, 2011 by Glenn Berry

Since it is August 1, 2011 (which is also my birthday), I thought it would be a good time to unveil a completely new set of Diagnostic queries that focus completely on SQL Server instance level metrics.

My previous DMV Diagnostic Queries are split between a number of instance level queries in the first half of the script, and then a number of database specific queries in the second half of the script. This is problematic if you have a large number of user databases running on your instance, since you would end up running the instance level queries multiple times (if you decided to run the complete diagnostic set for each user database).

Another problem is that many people who ran the complete set of old diagnostic queries would forget to change their database context to a specific database for the database specific queries, leaving it pointing at the master database. This meant that they would get a lot of pretty useless information about the master database.

This new set of Instance Level Diagnostic Queries is designed to be run first when you are investigating the configuration and performance of a SQL Server instance. Then, guided by the results of these queries, you will be able to run an upcoming set of separate Database specific diagnostic queries against the most interesting databases on the instance.

Many of these queries are DMV queries that require VIEW SERVER STATE permission in order to run. After all of this introduction, the August 2011 SQL Server 2008 Instance Level Diagnostic Queries are available to download from DropBox here.  I also have a matching, blank Excel spreadsheet, with labeled tabs available from DropBox here.

The idea here is that you should read the comments for each query, then run each query one at a time, and look at the results as you look at my comments on how to interpret the results. You can also paste the results from the results grid in SSMS into the matching tab in the spreadsheet, so you can keep them for baseline purposes.

Please let me know what you think of this new set of queries.  Thanks!

Posted in SQL Server 2008, SQL Server 2008 R2 | Tagged | 11 Comments

SQL Server Hardware Book News and Reviews

Posted on July 29, 2011 by Glenn Berry

Today was an exciting day, since I discovered that fellow SQL Server MVP Brent Ozar (Blog|Twitter) had posted a very nice review of my book SQL Server Hardware.

That means a lot to me, since I respect Brent, both as a person and as a technical expert (he is a Microsoft Certified Master in SQL Server 2008, after all). Brent typically does not pull any punches in his reviews, so the fact that he liked the book was a pretty big deal, IMHO…  Thanks Brent!

I also found out that SQL Server Hardware will be also available on the Kindle by August 8, 2011. I am very happy about this, since I think it just makes good sense to have technical books on a Kindle so that you can have them with you any time you need them. Plus, I was getting a lot of questions about when a Kindle version would be available!

Finally, I have been talking to Softpro Books in Centennial, CO about getting all of the Red Gate SQL Server books available in the store, and it looks like this may happen pretty soon.

Posted in Computer Hardware, Processors, SQL Server 2008 R2, Teaching | Tagged | 3 Comments

Some Common Disk Drives Compared

Posted on July 26, 2011 by Glenn Berry

Since I talked about how wonderful the newer Western Digital Black 6Gbps SATA III hard drives are a couple of days ago, I thought I would run CrystalDiskMark on a couple of different models in that line, along with a couple of other drives I had easy access to. First is a 128GB Crucial Real SSD C300 SATA III drive from last year, shown in Figure 1. This drive is plugged into a 6Gbps SATA III port on an X58 chipset motherboard. Write throughput is hurt because it is a 128GB drive (with fewer available NAND chips and channels compared to a 256GB model).

image

Figure 1: 128GB Crucial Real SSD C300

 

The second drive is a 500GB Seagate Momentus XT Hybrid drive, which has a 4GB SLC NAND chip that is used as a read cache in front of the conventional hard drive. As it turns out, a hybrid hard drive like this is no match for a real SSD from a performance perspective. It is shown in Figure 2.

 

image

Figure 2: 500GB Seagate Momentus XT Hybrid Drive

 

Next, we have a 2TB Western Digital Black WD2002FAEX SATA III drive. This is the model with a 64MB cache, and as you can see in Figure 3, it is quite fast. It actually has better sequential write throughput than the 128GB Crucial C300 SSD.

image

Figure 3: 2TB Western Digital Black SATA III Drive

 

Finally, I have a basically identical 1TB Western Digital Black WD1002FAEX SATA III drive.  It has pretty much the same performance as the larger model. It is shown in Figure 4. Either one of these WD drives would be a good choice for a transaction log drive in a desktop development or test system.

 

image

Figure 4: 1TB Western Digital Black SATA III Drive

Posted in Computer Hardware | Tagged , | 2 Comments