Glenn Berry's SQL Server Performance

Microsoft recently posted two new versions of SQL Server 2008 Express Edition. One is called SQL Server 2008 Express with Advanced Services, which includes a basic version of SQL Server Management Studio (SSMS), Integrated Full-Text Search  (iFTS), and the ability to run Reporting Services reports against local databases.  They also have SQL Server 2008 Express with Tools, which only includes the basic version of SQL Server Management Studio (SSMS).

Both of these still have the same 4GB database size limit, along with the one CPU and 1GB of RAM limit that regular SQL Server Express has had since the 2005 version of SQL Server Express. If your machine has more than one CPU or 1GB of RAM, you can still install and use any of the Express Editions, but SQL Server will only use one CPU and 1GB of RAM.

The nice thing about SQL Server Express is that it is free and it is completely compatible with its big brother editions of SQL Server. You can start out on Express Edition and migrate to something like Standard Edition when you need to, very easily.

Even though my previous teaching laptop is only about 18 months old, I decided it was time for an upgrade this past weekend.  The Best Buy in Parker had an "in-store special" on a Toshiba Satellite A305-S6843, which originally sold for $1249.00, but was on sale for $934.00.

This machine has a Core2 Duo T8100 CPU (which is a Penryn 45nm processor that runs at 2.1GHz, with 3MB of L2 cache and an 800Mhz FSB), 4GB of DDR2 RAM, two 200GB hard drives, and a pretty decent ATI Mobility Radeon HD3470 discrete graphics chip. It has a 15.4" 1280x800 screen. It also came with the 64-bit version of Windows Vista Home Premium SP1.

On the plus side, this machine is noticeably faster than my old Toshiba A135-S4447. The T8100 is 64-bit capable, and having two drive bays will give me more I/O capacity for running SQL Server 2008 and running virtual machines. The Radeon HD3470 has more horsepower than typical integrated graphics chips.

On the negative side, the new machine is not any lighter than my old A135. The stock 200GB hard drives are one 5400rpm and one 4200rpm model (which will need to be upgraded to 7200rpm or maybe SSD drives). The keyboard keys have a glossy, black finish which is very prone to fingerprints. Finally, Toshiba insists on loading their machines with a ton of "crapware", including about 30 shortcut icons on the desktop, which is very annoying.

Visual Studio 2008 SP1 runs extremely fast on this system, starting the IDE in less than a second. SQL Server 2008 Management Studio also starts up very quickly on this system. The machine has a Windows Experience Index score of 4.0, which is not too shabby for a unit in this price range.

Microsoft's Gopal Ashok and SQLskill's Paul Randal have a new whitepaper on how to use database mirroring in combination with SQL Server replication to provide high availability. I got the chance to be one of the technical reviewer's of the whitepaper, which was easy in this case because of the great job that Gopal and Paul did.

You can download the whitepaper here.

Now that SQL Server 2008 has released to manufacturing (RTM), you need to start planning how you will upgrade from a previous version of SQL Server to SQL Server 2008.  This will depend on which version of SQL Server you are currently running, your availability requirements, and your hardware budget.

The "best" approach, (assuming you have available hardware and budget) is to install a fresh copy of Windows Server 2008 on new hardware, with SQL Server 2008 installed as a default instance. Then, you can use one of the methods below to get your database(s) upgraded to SQL Server 2008 format.

1. Take a full backup in SQL Server 2000\2005. Copy the backup files to the new server and restore from the backup. Change the compatibility level to 10.0 and run sp_updatestats.
2. Detach your SQL Server 2000\2005 database. Copy the data files and transaction log files to the new server and attach the database. Change the compatibility level to 10.0 and run sp_updatestats.
3. If you are running SQL Server 2005, you can use database mirroring to move the data with one brief 15-20 second outage. You must be using the full recovery model to do this. Take a full backup in SQL Server 2005. Copy the backup files to the new server and restore from the backup with no recovery. Replay any subsequent transaction log backups on the new server with no recovery. Enable database mirroring and let the mirror get synchronized. Modify your connection strings to include a failover partner. Failover from SQL Server 2005 to SQL Server 2008, and then remove the mirror and change your connection strings. Change the compatibility level to 10.0 and run sp_updatestats.

Methods 1 and 2 are easier, but require some downtime. Method 3 is quite a bit more complicated, but can be used to migrate with virtually no downtime. Of course with all of these methods, you will have to migrate your logins and SQL Agent jobs to the new server. Even though Microsoft puts a lot of effort and testing into various "upgrade in place" scenarios, I would personally never go that route.

Microsoft has announced CU10 for SQL Server 2005 SP2. The CU is not yet available, but Microsoft has been pretty reliable about releasing Cumulative Updates every eight weeks.  I am wondering when they will actually release SP3 for SQL Server 2005. Back in April, Microsoft gave in to tremendous pressure from the SQL Server community, and announced that they would be releasing a Service Pack 3 for SQL Server 2005. I have not heard much about it since then, but some of the developers on the SQL Dev Team that I know have told me that is what they are working now since Katmai is RTM.

Intel announced the official name for their upcoming Nehalem family of processors. It will be called the Intel Core i7. 

Here is a little blurb about it:

Nehalem is Intel's dynamically scalable and innovative new processor microarchitecture -- Nehalem will provide dramatic performance and energy improvements to Intel's current industry-leading microprocessors. Nehalem is scalable with future versions having anywhere from 2 to 8 cores, with Simultaneous Multi-threading, resulting in 4 to 16 thread capability. Nehalem will deliver 4 times the memory bandwidth compared to today's highest-performance Intel Xeon processor-based systems. With up to 8 MB level-3 cache, 731 million transistors, Quickpath interconnects (up to 25.6GB per second), integrated memory controller and optional integrated graphics, Nehalem will eventually scale from notebooks to high-performance servers. Other features include support for DDR3-800, 1066, and 1333 memory, SSE4.2 instructions, 32KB instruction cache, 32KB Data Cache, 256K L2 data and instruction low-latency cache per core and new 2-level TLB (Translation Lookaside Buffer) hierarchy. These technical improvements will result in performance improvements as well as flexibility for a wide range of eventual products based on the Nehalem architecture.

In order to check and set the value of many configuration options in SQL Server 2008 (and 2000\2005), you can use the sp_configure system stored procedure. In order to see and set many of the options, you need to call it and enable ‘Show Advanced Options”, then issue a RECONFIGURE command (as you see below).  Then you can just run sp_configure by itself to see what your current values are.

Below are some of the configuration items that I like to change from their default values.  If you are running SQL Server 2008, I would enable backup compression (by default), and I would turn on “optimize for ad hoc workloads”.  If you have an OLTP workload (with lots of write activity and many frequently run, low cost queries), I would set “max degree of parallelism” to 1. I would also set your max memory to an appropriate value based on the amount of physical RAM on the server (as I wrote about here).

Finally, you will need to enable the Common Language Runtime (CLR), if you are going to use .NET assemblies inside of SQL Server.

-- How to use sp_configure to set some common Advanced Options

-- Turn on advanced options
EXEC sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO


-- See what the current values are
EXEC sp_configure



-- Turn on backup compression by default (new for SQL 2008)
EXEC sp_configure 'backup compression default', 1
GO
RECONFIGURE
GO

-- Turn on optimize for ad-hoc workloads (new for SQL 2008)
EXEC sp_configure 'optimize for ad hoc workloads', 1
GO
RECONFIGURE
GO

-- Set MAXDOP = 1 for the server (if you have an OLTP workload and you see CXPACKET waits)
EXEC sp_configure 'max degree of parallelism', 1
GO
RECONFIGURE
GO

-- Set max server memory for the server (based on how much physical RAM you have)
EXEC sp_configure 'max server memory (MB)', 6500 -- This value = 6.5GB
GO
RECONFIGURE
GO

-- Enable CLR (if you need it)
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

It is pretty important to make sure you set the Max server memory setting for SQL Server 2005/2008 to something besides the default setting (which allows SQL Server to use as much memory as it wants, subject to signals from the host OS that it is under memory pressure). This is especially important with larger, busier systems that may be under memory pressure.  This setting controls how much memory can be used by the SQL Server Buffer Pool.  If you don’t set an upper limit for this value, other parts of SQL Server, and the operating system can be starved for memory, which can cause instability and performance problems.

This is for x64, on a dedicated DB server.

Physical RAM                        MaxMem Setting

2GB                                        1500

4GB                                        3200

6GB                                        4800

8GB                                        6700

12GB                                     10600

16GB                                     14500

24GB                                     22400

32GB                                     30000

48GB                                     45000

64GB                                     59000

This is how much RAM should be available in Task Manager while you are under load (on Windows Server 2003)

Physical RAM             Target Avail RAM in Task Manager

< 4GB                                   512MB – 1GB

4-32GB                                 1GB – 2GB

32-128GB                             2GB – 4GB

> 128GB                               > 4GB

You can set this value with Transact-SQL like this:

-- Turn on advanced options
EXEC sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO

-- See what the current value is for 'max server memory (MB)'
EXEC sp_configure

-- Set max server memory = 2300MB for the server
EXEC sp_configure 'max server memory (MB)', 2300
GO
RECONFIGURE
GO

Or you can set it with SQL Server Management Studio (SSMS)

Microsoft has a new marketing campaign called “the Mojave Experiment”, where they bring in average computer users to see a preview of a new Microsoft operating system called Mojave.  It is in a focus group situation, complete with hidden cameras and microphones. Before they get to see “Mojave”, they are asked a series of questions about their impression of Windows Vista, which are pretty strongly negative.

Then they are shown this new operating system, and they are very impressed with it. Finally, they are told that it is actually Windows Vista, and they are shocked, shocked, I tell you!

This reminds me of the old Folgers Crystals commercials, where they “secretly replaced the coffee at this five star restaurant with Folger’s Crystals”, and the diners could not tell the difference.

I have been running both Vista Home Premium and Vista Ultimate on several machines for almost eighteen months, and I am pretty happy with it. It has definitely improved quite a bit since RTM. Of course I have pretty decent hardware, and I know what I am doing, unlike many of the Vista haters out there. Still, there is no doubt that there is a huge perception problem out there about Vista, and that Microsoft made many mistakes in its design and implementation.  They also did a pretty poor job of marketing, and they have not gotten the best support from third parties as far as drivers and application compatibility.

The Windows Vista Blog has some details about this here.

Maybe this experiment will change a few minds. Probably not, since the Vista hate is very strong out there.

A nice new feature in the RC0 build of SQL Server 2008 is what I call the "missing index warning" that shows up when you have the graphical execution plan turned on in SQL Server Management Studio (SSMS), as you can see in green, below.  In the pane where the SQL statement is shown, you can see "Missing Index (Impact 99.652....", complete with a skeleton index creation script.

Just in case you are curious, here is the query that generated that warning (using the AdventureWorks database). Since there is no index on CarrierTrackingNumber, and it is used in the WHERE clause of the query, SQL Server wants to do a clustered index scan of a 121,317 row table even though the query returns only 12 rows.  This generates 1240 logical reads, which is pretty costly.

  SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber
  FROM Sales.SalesOrderDetail
  WHERE CarrierTrackingNumber = '4911-403C-98'

Adding the index (shown below) gives you an index seek, with 3 logical reads, which is quite an improvement in this case.

CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_CarrierTrackingNumber] ON [Sales].[SalesOrderDetail] 
(
    [CarrierTrackingNumber] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, 
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]