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]

PC World has a very favorable review of SQL Server 2008 published on July 17.  I was quoted in a short piece at SearchDataBackup.com.  All indications are that Microsoft will easily meet their Q3 schedule for the RTM of Katmai (aka SQL Server 2008).

Below is a small collection of queries that can be used to gather a wealth of very useful information about a SQL Server 2005/2008 instance, and about any specific database on that instance.  These queries will tell you the exact version and edition of SQL Server, whether it is 32-bit or 64-bit, the OS version, and some information about the CPUs and memory in the server.  They will also reveal how many databases are present, their recovery models and file layouts.  We also find out what the top wait stats are, along with Page Life Expectancy (PLE).

For a specific database, we collect some information about the most frequently executed stored procedures, along with the most costly stored procedures from a couple of different perspectives. Finally, we collect some good information about possible bad indexes and missing indexes for a database.

Most of these are dynamic management view queries, which require VIEW SERVER STATE permission to run.

-- Diagnostic Information Queries
-- Glenn Berry July 2008
-- http://glennberrysqlperformance.spaces.live.com/


-- SQL Version information
SELECT @@VERSION AS 'Version Info'
 
-- Hardware Information
SELECT cpu_count AS 'Logical CPU Count', hyperthread_ratio AS 'Hyperthread Ratio',
cpu_count/hyperthread_ratio As 'Physical CPU Count', 
physical_memory_in_bytes/1048576 AS 'Physical Memory (MB)'
FROM sys.dm_os_sys_info

-- sp_configure values
EXEC sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
EXEC sp_configure


-- File Names and Paths 
SELECT dbid, fileid, filename 
FROM sys.sysaltfiles
WHERE fileid IN (1,2)

-- Recovery model 
SELECT [name], recovery_model_desc, log_reuse_wait_desc 
FROM sys.databases

-- Individual File Sizes
SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB'--, *
FROM sys.database_files;


-- Top Wait Stats
WITH Waits AS
(
  SELECT
    wait_type,
    wait_time_ms / 1000. AS wait_time_s,
    100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
    ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
  FROM sys.dm_os_wait_stats
  WHERE wait_type NOT LIKE '%SLEEP%' -- filter out additional irrelevant waits
)
SELECT
  W1.wait_type, 
  CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
  CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
  CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 90 -- percentage threshold
ORDER BY W1.rn;


-- Signal Waits
SELECT '%signal (cpu) waits' = CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)),
       '%resource waits'= CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
FROM sys.dm_os_wait_stats;


-- Page Life Expectancy
SELECT cntr_value AS 'Page Life Expectancy'
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Page life expectancy'


-- Buffer Pool 
SELECT TOP 10 [type], sum(single_pages_kb) AS [SPA Mem, Kb] 
FROM sys.dm_os_memory_clerks 
GROUP BY type  
ORDER BY SUM(single_pages_kb) DESC;

-- Switch to the database you are interested in before you run these: 

-- SP's By Execution Count
SELECT TOP 50 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',  
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, 
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id() -- Filter by current database
ORDER BY qs.execution_count DESC


-- SP's By Worker Time
SELECT TOP 20 qt.text AS 'SP Name', qs.total_worker_time AS 'TotalWorkerTime', 
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.execution_count AS 'Execution Count', 
ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second',
ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime', 
qs.max_logical_reads, qs.max_logical_writes, 
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id() -- Filter by current database
ORDER BY qs.total_worker_time DESC


-- SP's By Logical Reads
SELECT TOP 20 qt.text AS 'SP Name', total_logical_reads, 
qs.execution_count AS 'Execution Count', total_logical_reads/qs.execution_count AS 'AvgLogicalReads',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second', 
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
qs.total_logical_writes,
qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, 
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id() -- Filter by current database
ORDER BY total_logical_reads DESC


-- Bad Indexes
SELECT 'Table Name' = object_name(s.object_id), 'Index Name' = i.name, i.index_id,
       'Total Writes' =  user_updates, 'Total Reads' = user_seeks + user_scans + user_lookups,
        'Difference' = user_updates - (user_seeks + user_scans + user_lookups)
FROM sys.dm_db_index_usage_stats AS s 
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE objectproperty(s.object_id,'IsUserTable') = 1
AND s.database_id = db_id()
AND user_updates > (user_seeks + user_scans + user_lookups)
ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads' ASC;


-- Missing Indexes By Index Advantage
SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage, migs.last_user_seek, mid.statement as 'Database.Schema.Table',
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
ORDER BY index_advantage DESC;

This Thursday is is the July meeting for the Denver SQL Server User’s Group at the Microsoft office in the Tech Center.   Janis Griffin is giving a presentation on Wait-Time Based SQL Server Performance Management, which should be very interesting.  I will be there, and I think that Kevin Cox will also be in the area.

I use the queries below every day to keep an eye on what my top wait types on my various SQL Server instances.

    -- Clear Wait Stats
    DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);


    -- Isolate top waits
    WITH Waits AS
    (
      SELECT
        wait_type,
        wait_time_ms / 1000. AS wait_time_s,
        100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
      FROM sys.dm_os_wait_stats
      WHERE wait_type NOT LIKE '%SLEEP%'
      -- filter out additional irrelevant waits
    )
    SELECT
      W1.wait_type, 
      CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
      CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
      CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
    FROM Waits AS W1
    INNER JOIN Waits AS W2
    ON W2.rn <= W1.rn
    GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
    HAVING SUM(W2.pct) - W1.pct < 90 -- percentage threshold
    ORDER BY W1.rn;

There has been lots of coverage already about NewsGator’s release of a special version of NetNewsWire for the iPhone. ArsTechnica has it here. Mashable covers it here. Our own Greg Reinacker blogs about it here.  It looks like another great application from Brent Simmons.  Of course, it is also free…

That is what Mary Jo Foley says right here. Microsoft's Bob Kelly made that announcement at the Worldwide Partner Conference on July 9. The "official" schedule for the RTM of SQL Server 2008 is still Q3 2008 (which many people have joked means September 30 at 11:59PM).

August RTM seems pretty viable to me, based on publicly available information.  Time to start convincing management why you need to upgrade to 2008...