Escape the Winter and Learn About SQL Server Hardware in Tampa

On February 6-7, 2014, I will be teaching IEHW: Immersion Event on SQL Server Hardware in Tampa, FL. This is a great opportunity for you to learn how to properly select and configure your server hardware and storage subsystem to get the best performance and scalability for the lowest SQL Server licensing costs. It is also a good opportunity to escape the cold weather that has been gripping much of the United States for the past few weeks!

This two-day SQL Server hardware training class explains the core fundamentals and deeper details of database server hardware and storage subsystems for SQL Server database professionals. Many database professionals are unfamiliar with the details and nuances of modern server hardware and storage subsystems, while many server and storage administrators are unfamiliar with the specific workload demands of a SQL Server database server.

Attendees of this class will learn how to analyze, select, and size their server hardware and storage subsystems for different types of SQL Server workloads in order to get the best performance and scalability while minimizing their SQL Server license costs.

This class also covers how to properly configure and benchmark your database server hardware and storage subsystems, along with how to properly install and configure the operating system and SQL Server for the best performance and reliability. The class will show you how to diagnose and troubleshoot hardware and storage related performance issues, and will include coverage of how virtualization interacts with your database server hardware and storage subsystem. Note: the primary audience for this class is SQL Server database professionals, not general system/server admins who are already familiar with server/storage hardware.

I think it is very important for database administrators to know as much as possible about the critical details of their server hardware and storage subsystem, rather than trusting their fate to “Shon the server guy”, who may or may not know that much about modern server hardware (maybe he is a networking specialist). Even if Shon is very knowledgeable about hardware, he may not understand the different demands that SQL Server will create with different types of workloads. I want you to be able to successfully make the case for selecting the best hardware and storage subsystem components for your workload and budget.  You can read more about the registration details here.

Special Pricing Options and Referrals

  • Past attendee price: If you’ve attended an Immersion Event in the past, you can register any time for 75% of the full price ($1,099). Please contact us for instructions.
  • Refer someone: If you know someone who would benefit from this class, refer them to us and when they register, we’ll give you a $50 Amazon gift card. They or you just need to let us know you’re referring them, and when they register, we’ll match them to your referral and send you the gift card.

One Intel Processor Family to Avoid For SQL Server 2012/2014

On January 9, Intel launched the 22nm Intel Xeon E5-2400 v2 Product Family (Ivy Bridge-EN) of processors for two-socket servers. For SQL Server usage, this is not a good processor family to choose for a new server.

While these processors are a nice improvement over the older 32nm Intel Xeon E5-2400 Product Family (Sandy Bridge-EN) of processors, they are still a particularly poor choice for SQL Server 2012 and SQL Server 2014, when compared to a 22nm Intel Xeon E5-2600 v2 Product Family (Ivy Bridge-EP) processor with the same physical core count.

The reason for this is that Microsoft simply charges for physical core licenses with SQL Server 2012 and SQL Server 2014 (in non-virtualized servers). The performance characteristics of the processor do not matter at all to Microsoft (for licensing purposes). Given this fact, it does not make any sense to pick a lower performance processor with the same number of physical cores, at least from a performance or scalability perspective. From a strict economic perspective, a lower performance processor (with the same core count) will cost a little bit less money, and it is likely to use less electrical power and require less heat dissipation in your data center. These cost savings are pretty small compared to the cost of SQL Server core licenses, and you are giving up a lot of performance to save a relatively small amount of money.

If you compare the best models from the the entry-level E5-2400 v2 line to the best models from the E5-2600 v2 line, you will notice significantly higher base and turbo clock speeds, along with larger L3 cache sizes from the higher-end E5-2600 v2 line. You will also see higher QPI bandwidth, higher memory speed support and twice the memory capacity with the E5-2600 v2 line. The E5-2407 v2 processor does not have Turbo Boost or Hyper-Threading, which helps explain its very low price for a server-level processor.

Processor Cores Base Speed Turbo Speed L3 Cache QPI Price
E5-2407 v2 4 2.4GHz 2.4GHz 10MB 6.4GT/s $250.00
E5-2430 v2 6 2.5Ghz 3.0GHz 15MB 7.2GT/s $551.00
E5-2450 v2 8 2.5GHz 3.3GHz 20MB 8.0GT/s $1,107.00
E5-2470 v2 10 2.4GHz 3.2GHz 25MB 8.0GT/s $1,440.00

Table 1: Intel Xeon E5-2400 v2 Product Family Specifications

 

Processor Cores Base Speed Turbo Speed L3 Cache QPI Price
E5-2637 v2 4 3.5GHz 3.8GHz 15MB 8.0GT/s $996.00
E5-2643 v2 6 3.5GHz 3.8GHz 25MB 8.0GT/s $1,552.00
E5-2667 v2 8 3.3GHz 4.0GHz 25MB 8.0GT/s $2,057.00
E5-2690 v2 10 3.0GHz 3.6GHz 25MB 8.0GT/s $2,057.00
E5-2697 v2 12 2.7GHz 3.5GHz 30MB 8.0GT/s $2,618.00

Table 2: Intel Xeon E5-2600 v2 Product Family Specifications

Just to be clear, you won’t see these processors being offered in the same model servers. For example, the Dell PowerEdge R320, R420, and R520 servers will have the Xeon E5-2400 (Sandy Bridge-EN) or Xeon E5-2400 v2 (Ivy Bridge-EN) processors (which you don’t want for SQL Server usage). The Dell PowerEdge R620, R720 and R720xd servers will have the Xeon E5-2600 (Sandy Bridge-EP) or Xeon E5-2600 v2 (Ivy Bridge-EP) processors (which you do want for SQL Server usage).

As a final observation, the major server vendors are still offering the older 32nm Sandy Bridge along with the newer 22nm Ivy Bridge processors in most of their servers. In the cases I have seen, there is no discount for the older, slower, more power hungry Sandy Bridge processors, so there is really no good reason to choose one of the older Sandy Bridge processors.

SQL Server Diagnostic Information Queries for January 2014

I spent some time this month to true up the SQL Server 2005 version, with the other versions for the queries that are common between all of these versions, since some small differences had cropped up over time across those versions. I also added the drive-level latency query to the 2005 version.

Rather than having a separate blog post for each version, I’ll just put the links for all five major versions here. There are two separate links for each version. The first one on the top left is the actual query script, and the one below on the right is the matching blank results spreadsheet.

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results

SQL Server 2012 Diagnostic Information Queries

SQL Server 2012 Blank Results

SQL Server 2014 Diagnostic Information Queries

SQL Server 2014 Blank Results

About half of the queries are instance specific and about half are database specific, so you will want to make sure you are connected to a database that you are concerned about instead of the master system database.

The idea is that you would run each query in the set, one at a time (after reading the directions). You need to click on the top left square of the results grid to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the spreadsheet. There are also some comments on how to interpret the results after each query.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect.

I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their user databases in 80 compatibility mode, which breaks many DMV queries.

There is an initial query in each version that tries to confirm that you are using the correct version of the script for your version of SQL Server.

Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks!

Vote in the Tribal Awards

Simple-Talk and SQL Server Central have posted their nominations for the new Tribal Awards. There are eleven different categories for the awards, which you can read about here. I think it is pretty amazing that every member of SQLskills was nominated in at least one category for these awards, with Paul and Jonathan being nominated in three categories.

I was lucky enough to be nominated for the Best Free Script category, for my Diagnostic Information Queries (even though I am in some pretty esteemed company). If you like my Diagnostic Queries, I would be honored to get your vote!

You also might want to check out Paul’s recent post, where you can get free access to all of the 2012 Insider Videos for SQLskills.

SQL Server 2012 Diagnostic Information Queries (December 2013)

I have made quite a few updates and improvements to this set of queries for December 2013. I have added several new queries, and changed the order of some of the existing queries in the set, which now has a total of 63 separate queries. I also spent some time this month to true up the SQL Server 2014 version and the SQL Server 2012 version for the queries that are common between both versions.

The blank results spreadsheet has also been updated to match. You can download the queries and the results spreadsheet from the links below:

SQL Server 2012 Diagnostic Information Queries

SQL Server 2012 Blank Results

About half of these queries are instance specific and about half are database specific, so you will want to make sure you are connected to a user database that you are concerned about instead of the master system database.

The basic idea is that you would run each query in the set, one at a time (after reading the directions). You will need to click on the top left square of the results grid to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the results spreadsheet. There are also some comments on how to interpret the results after each query.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect. I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their databases in 80 compatibility mode, which breaks many DMV queries.

There is an initial query in each version that tries to confirm that you are using the correct version of the script for your version of SQL Server. In this case, you need to have SQL Server 2012 RTM (or newer) to pass that check.

Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks!

If you know someone who would benefit from our IE0: Immersion Event for Accidental/Junior DBA or IEHW: Immersion Event on SQL Server Hardware, refer them to us and when they register we’ll send you a $50 Amazon gift card. Either they or you just need to let us know you referred them, we’ll match it up to their registration and send you the gift card.

SQL Server 2014 Diagnostic Information Queries (December 2013)

I have made quite a few updates and improvements to this set of queries for December 2013. I have added several new queries, and changed the order of some of the existing queries in the set, which now has a total of 65 separate queries. The blank results spreadsheet has also been updated to match. You can download the queries and the results spreadsheet from the links below:

SQL Server 2014 Diagnostic Information Queries

SQL Server 2014 Blank Results

About half of these queries are instance specific and about half are database specific, so you will want to make sure you are connected to a user database that you are concerned about instead of the master system database.

The basic idea is that you would run each query in the set, one at a time (after reading the directions). You will need to click on the top left square of the results grid to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the results spreadsheet. There are also some comments on how to interpret the results after each query.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect. I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their databases in 80 compatibility mode, which breaks many DMV queries.

There is an initial query in each version that tries to confirm that you are using the correct version of the script for your version of SQL Server. In this case, you need to have SQL Server 2014 CTP2 (or newer) to pass that check.

Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks!

If you know someone who would benefit from our IE0: Immersion Event for Accidental/Junior DBA or IEHW: Immersion Event on SQL Server Hardware, refer them to us and when they register we’ll send you a $50 Amazon gift card. Either they or you just need to let us know you referred them, we’ll match it up to their registration and send you the gift card.

Performance and Stability Related Fixes in Post-SQL Server 2012 SP1 Builds

Updated to include SP1 CU9: There have already been nine Cumulative Updates (CU) for the Service Pack 1 branch of SQL Server 2012. There have been a fairly high number of hotfixes in every one of these Cumulative Updates, as more people are using SQL Server 2012 over the past eighteen months. SQL Server 2012 SP1 has all of the fixes through SQL Server 2012 RTM CU2. If you are running SQL Server 2012, I really think you should be running the latest SQL Server 2012 Service Pack and Cumulative Update. Right now, that means Service Pack 1, CU9 (Build 11.0.3412).

This table shows the SP1 CU builds that have been released so far.

Build Description Release Date
11.0.3000 SP1 RTM November 7, 2012
11.0.3321 SP1 CU1 November 20, 2012
11.0.3339 SP1 CU2 January 21, 2013
11.0.3349 SP1 CU3 March 18, 2013
11.0.3368 SP1 CU4 May 30, 2013
11.0.3373 SP1 CU5 July 15, 2013
11.0.3381 SP1 CU6 September 16, 2013
11.0.3393 SP1 CU7 November 18, 2013
11.0.3401 SP1 CU8 January 20, 2014
11.0.3412 SP1 CU9 March 17, 2014

Table 1: SQL Server 2012 SP1 CU Builds

You can follow the KB article link below to see all of the CU builds for the SQL Server 2012 Service Pack 1 branch.

The SQL Server 2012 builds that were released after SQL Server 2012 Service Pack 1 was released

Like I did in my previous post, I decided to scan the hotfix list for all of the Cumulative Updates in the SP1 branch, looking for performance and general reliability-related fixes for the SQL Server Database Engine. I came up with the list below, but this listing is completely arbitrary on my part. You may come up with a completely different list, based on what SQL Server 2012 features you are using.

Here are the fixes in the RTM branch, before Service Pack 1 was released (you will get these when you install SP1):

SQL Server 2012 RTM Cumulative Update 1  (Build 11.0.2316) , 65 total public hot fixes

FIX: Low CPU spikes at a set interval even without user activity in SQL Server 2008 R2 or in SQL Server 2012

FIX: Slow performance when an AFTER trigger runs on a partitioned table in SQL Server 2008 R2 or in SQL Server 2012

FIX: It takes a long time to restore a database in SQL Server 2008 R2 or in SQL Server 2008 or in SQL 2012

FIX: Poor performance when you run a query that contains correlated AND predicates in SQL Server 2008 or in SQL Server 2008 R2 or in SQL Server 2012

FIX: Slow performance occurs in SQL Server 2008 R2 or in SQL Server 2012 if high CPU usage is observed with contention over the QUERY_EXEC_STATS spinlock

FIX: Out-of-memory error when you run SQL Server 2012 on a computer that uses NUMA

SQL Server 2012 RTM Cumulative Update 2  (Build 11.0.2325) , 37 total public hot fixes

FIX: An access violation occurs intermittently when you run a query against a table that has a columnstore index in SQL Server 2012

 

Here are the fixes in the Service Pack 1 branch:

SQL Server 2012 SP1 Cumulative Update 1 (Build 11.0.3321), 44 total public hot fixes

FIX: “out of memory” error when you use ODBC with SQL Server 2012, SQL Server 2008 R2 or SQL Server 2008

FIX: Performance of a SELECT statement that contains a LIKE operator and an ESCAPE clause is low in SQL Server 2008 R2 or in SQL Server 2012

FIX: SQL Server 2012, SQL Server 2008 R2 or SQL Server 2008 stops responding and a “Non-yielding Scheduler” error is logged

FIX: Slow performance or deadlock when you restore a database and execute statements at the same time in SQL Server 2012

FIX: Worker threads do not wake up immediately when multiple I/O-intensive tasks are running at the same in SQL Server 2012

FIX: “Process appears to be non-yielding on Scheduler ” error message when you run a query in SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 2 (Build 11.0.3339), 50 total public hot fixes

A database transaction log continues to grow after you upgrade to SQL Server 2012, SQL Server 2008 R2 SP1, SQL Server 2008 SP2 or SQL Server 2008 SP3

FIX: The size of a database file is not reduced when you use the DBCC SHRINKFILE command in SQL Server 2008 R2 or in SQL Server 2012

FIX: High CPU usage when you query a binary large object column by using the NOLOCK hint in SQL Server 2008 R2 or in SQL Server 2012

FIX: Large queries that modify data run slower than expected when many locks accumulate in a SQL Server 2008 R2 or in SQL Server 2012

FIX: A transaction log restore operation takes longer than expected and a 3402 error occurs if a database file is removed in SQL Server 2008 R2 or in SQL Server 2012

SQL Server 2012 experiences out-of-memory errors

FIX: Deadlocks occur when you execute a stored procedure to alter a temporary table if lock partitioning is enabled in SQL Server 2008 R2 or in SQL Server 2012

FIX: Memory leak if you enable the AUTO_UPDATE_STATISTICS_ASYNC statistics option in SQL Server 2008, in SQL Server 2012 or in SQL Server 2008 R2

FIX: A “non-yielding” error occurs on a server for Service Broker Message Forwarding in SQL Server 2008 R2 or in SQL Server 2012

FIX: Error 17883 when you run a query on a server that has many CPUs and a large amount of memory in SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 3 (Build 11.0.3349), 38 total public hot fixes

FIX: Access violation when you run a query that contains many constant values in an IN clause in SQL Server 2008 or in SQL Server 2012

FIX: Memory leak when you run queries against a temporary table in a nested stored procedure in SQL Server 2012

FIX: Poor performance in SQL Server 2012 when you run a SQL Server trace

FIX: You experience poor performance when you run a query against an RCSI-enabled table in SQL Server 2012

FIX: CPU spike when there is no load on a server after you install SQL Server 2012 on the server

SQL Server 2012 experiences performance issues in NUMA environments

SQL Server 2012 SP1 Cumulative Update 4 (Build 11.0.3368), 38 total public hot fixes

FIX: Out-of-memory errors related to a memory clerk in SQL Server 2012

FIX: Out of memory error when you build a columnstore index on partitioned tables in SQL Server 2012

Update that improves the Service Broker when you send messages to remote servers in SQL Server 2012 is available

An update is available for SQL Server 2012 Memory Management

FIX: “Non-yielding Scheduler” error occurs when you insert a row in SQL Server 2012

FIX: You may experience performance issues in SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 5 (Build 11.0.3373), 27 total public hot fixes

FIX: Poor performance when you run a query that uses the LIKE operator in the WHERE clause in SQL Server 2008 R2

FIX: Non-yielding scheduler error when you run a stored procedure that uses a TVP in SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 6 (Build 11.0.3381), 24 total public hot fixes

FIX: Slow performance on SQL Server 2012 linked server when you update the data together with different collation on the remote server

FIX: A memory leak occurs when you enable AlwaysOn Availability Groups or SQL Server failover cluster in Microsoft SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 7 (Build 11.0.3393), 47 total public hotfixes

FIX: Access violation when you trace an RPC event class by using SQL profiler or XEvents in SQL Server 2012

FIX: Nonclustered index corruption may occur when you run a complex UPDATE statement together with a NOLOCK hint against a table in SQL Server 2008, SQL Server 2008 R2 or SQL Server 2012

FIX: The threads are not scheduled evenly in SQL Server 2012 Standard Edition

A memory leak occurs when a SQL Server Native Client OLE DB provider application calls the Prepare method in SQL Server 2012

FIX: “Non-yielding Resource Monitor” when you run a workload that executes concurrent queries in SQL Server 2012

FIX: Function sys.fn_hadr_backup_is_preferred_replica costs almost all the CPU usage in Log Shipping in SQL Server 2012

FIX: Slow performance in SQL Server when you build an index on a spatial data type of a large table in a SQL Server 2012 instance

FIX: Slow performance in SQL Server 2012 when you build an index on a spatial data type of a large table

An access violation occurs when “sys.dm_db_index_physical_stats” is used in an IF EXISTS statement in an SQL query in SQL Server 2012

FIX: Suboptimal execution plan is generated when you run a query in SQL Server 2012

Slow SQL Server performance and a memory leak occurs after you apply Cumulative Update 3 for SQL Server 2012 Service Pack 1

FIX: Slow performance in SQL Server 2012 when you build an index on a spatial data type of a large table

Slow performance or error messages are logged when you have a transactional replication publisher server in SQL Server 2012

FIX: Insufficient system memory error occurs when you try to create an index in a char, varchar, or nvarchar type column in SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 8 (Build 11.0.3401), 32 total public hotfixes

FIX: Data purity corruption in sys.sysbinobjs table in master database when you log on to SQL Server 2008 R2 or SQL Server 2012 by using the SA account and then run DBCC CHECKDB

FIX: Interrupted distributed query may return partial result set without any error in SQL Server 2008 R2 or in SQL Server 2012

FIX: Replication Log Reader Agent fails when you enable CDC and transactional replication for some columns in SQL Server 2008 R2 or SQL Server 2012

FIX: Query that you run against a partitioned table returns incorrect results in SQL Server 2008, SQL Server 2008 R2 or SQL Server 2012

FIX: A small number of rows are sampled in SQL Server 2008 R2 or in SQL Server 2012 when table statistics are updated automatically or together with specified sampling rate

FIX: Access violation may occur when you query sys.dm_server_memory_dumps in SQL Server 2008 R2 or SQL Server 2012

FIX: Replication Queue Reader Agent crashes when you run transactional replication in SQL Server 2008 R2 or SQL Server 2012

FIX: Validation of service accounts takes a long time when you set up SQL Server 2012

FIX: The system function sys.fn_hadr_backup_is_preferred_replica does not work correctly after you have CU7 for SQL Server 2012 SP1 installed

FIX: The query deadlocks when the ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT are enabled in SQL Server 2012

FIX: The clustered index table may take longer than you expect to be rebuilt when you use the ALTER INDEX REBUILD statement in SQL Server 2008 or SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 9 (Build 11.0.3412), 30 total public hotfixes

FIX: Access violation in SQL Server internal deadlock monitor when you run multiple applications together with Multiple Active Result Sets enabled in SQL Server 2008 R2 or in SQL Server 2012

FIX: Access violation in replication Distribution Agent in SQL Server 2008 R2 or SQL Server 2012 Transactional Replication

FIX: Poor performance when many table-valued parameters are used in SQL Server 2008 R2 or SQL Server 2012

FIX: Merge Agent fails or you experience non-convergence when you use a custom stored procedure conflict resolver in SQL Server 2008 or SQL Server 2008 R2 or SQL Server 2012

FIX: Performance problems occur when database lock activity increases in SQL Server 2012

FIX: Performance problems occur in NUMA environments during foreign page processing in SQL Server 2012

FIX: Operating system version information that is returned from SQL Server 2008 or SQL Server 2008 R2 or SQL Server 2012 is incorrect in Windows 8.1

FIX: Performance decreases after an ALTER INDEX…ONLINE operation is aborted in SQL Server 2012

FIX: Access violation may occur when trigger query joins large dataset in deleted/inserted table and runs in parallel in SQL server 2012

FIX: Database shows “recovery pending” state when you use TDE with EKM provider in SQL Server 2012

FIX: Incorrect usage of built-in FORMAT function brings down SQL Server 2012 Instance

FIX: SQL Server 2012 instance shuts down when you join database as secondary replica during AlwaysOn Availability Groups configuration

FIX: Merge agent fails when you apply snapshot in SQL Server 2012 instance if cross database dependencies are present

FIX: Distribution Agent skips applying sp_MSins_, sp_MSupd_, sp_MSdel_ stored procedures on a Subscriber that is initialized with Backup in SQL Server 2012

FIX: Access violation occurs when you update a table that has an XML calculated column in SQL Server 2012

FIX: SQL Server takes long time to open the databases after the recovery phase when the number of databases or database files or both is large in SQL Server 2012

FIX: Message is not removed from transmission queue even though the ACK is received successfully in an AlwaysOn availability group in SQL Server 2012

FIX: Non-yielding scheduler tries to close the listener for availability group in SQL Server 2012

 

Again, the idea here is to give you a lot of concrete reasons to want to stay current with the latest SQL Server 2012 SP and CU, by pointing out some of the more valuable fixes in each CU in the Service Pack 1 branch.

Affordable Intel Haswell Laptop

As we move towards the end of 2013, 4th generation Intel Core processors are becoming more readily available in new machines at a wider range of price points. Since I know a few people are looking for a new laptop this time of year, I thought I would pass along one pretty affordable possibility that is available this week in the United States.

Best Buy (which actually has been improving the quality of their computer offerings somewhat over the past year) is selling a Toshiba Satellite P55-A5312 for $529.99 (rather than the regular price of $629.99).

This particular 15.6” model comes with an Intel Haswell Core i5-4200U processor, 6GB of RAM (in two easily accessible memory slots), a 750GB 5400rpm drive and a 1080p, non-touch screen. You can bump the RAM up to 16GB with two 8GB sticks, and with a little more work (removing about twelve screws that are hidden behind rubber covers) you can replace the hard drive with a SATA III SSD of your choosing. If you want to go more hard-core, you can remove the optical drive and put a second SATA III SSD in its place.

Here is an example of a 1.35V 16GB memory kit that uses the slightly higher speed 1866MHz memory:

Crucial Ballistix Sport SODIMM 16GB Kit (8GBx2) DDR3 1866 MT/s (PC3-14900) CL10 @1.35V 204-Pin Memory BLS2K8G3N18AES4 (Make sure to get 1.35V RAM for this processor)

Here is an example of a 1TB SATA III SSD:

Samsung Electronics 840 EVO-Series 1TB 2.5-Inch SATA III Single Unit Version Internal Solid State Drive MZ-7TE1T0BW

You could also go with a smaller capacity SSD for less money, but I would not go any smaller than 250-256GB, since the smaller capacity SSDs don’t perform as well (especially for writes) and going any smaller than that will really restrict what you can do on the machine.

The Core i5-4200U processor in this machine is the exact same processor that is in the new Microsoft Surface 2 Pro and many mid-range Ultrabooks. It is a dual-core with hyperthreading, and its base clock speed is 1.6GHz, with a Turbo speed of 2.6GHz. It also supports VT-x for better virtualization performance.  Having 1920 x 1080 screen resolution (even in a cheaper TN panel) is unusual at this price point. Having 16GB of RAM and one or possibly two, fast SSDs would let you run multiple  simultaneous VMs pretty easily. The spec sheet says it weighs 5.3lbs, but several people on the Toshiba forums claim it actually weighs about 4.9lbs. It feels lighter than 5.3lbs to me, and it is pretty thin. The battery life should be about 6 hours after you replace the HDD with an SSD (especially one that supports the DevSleep mode).

Depending on your SSD choice, you could come in at under $1000.00 for a pretty competent machine. This is a decent alternative that you might want to consider. With 16GB of RAM and a decent SSD, it would have plenty of horsepower to run some virtual machines and to do some SQL Server development work.

Creating SQL Server Agent Alerts for Critical Errors

One necessary part of being a responsible DBA is to try to configure your SQL Server instances in such a manner so they will be as reliable as possible. This begins with how you initially install and configure the operating system and SQL Server, and what you do over time to maintain and monitor your SQL Server instances. I previously wrote a three part series about how to provision a new SQL Server instance that is available on the SimpleTalk web site. Part One is here, Part Two is here, and Part Three is here. If you read and follow my recommendations in those three articles, you will have a very good foundation to build on going forward.

One subject that I touched on in Part Three of that series was how to use some T-SQL to quickly create SQL Server Agent Alerts for a number of critical error conditions. SQLskills own Paul Randal previously wrote about this. SQL Agent Alerts will fire when certain types of errors occur, and you can configure them to notify a SQL Operator when this happens. This will let you find out about these types of problems much earlier than you otherwise probably would, which is a good thing!

The code in this script is an updated version of the script that I included in my article that automatically picks up the server name and uses that as part of the alert name. It also uses variables for the operator name and for the alert category name. I have also recently added an additional alert for Error 832, and for Errors 855 and 856 (if you are running SQL Server 2012 Enterprise Edition).

SQL Server 2012 Enterprise Edition has a new feature called Memory Error Recovery. It can detect (and sometimes correct) when clean database pages have become corrupted in memory. You need to have Windows Server 2012 or newer, and you must have a processor (such as an Intel Xeon 7500 series or Intel Xeon E7 series) that supports this functionality. If you have all of this, you will see this message in your SQL Server Error Log:

Machine supports memory error recovery. SQL memory protection is enabled to recover from memory corruption.

There are some more details about this in this KB article: Using SQL Server in Windows 8 and Windows Server 2012 environments. There is a PowerPoint deck about this feature here.

In this script, the delay between responses is set to 900 seconds, which is 15 minutes. That might be a little on the long side, but I did not want the operator to get spammed with e-mail alerts every couple of minutes. You can always adjust that value to whatever you see fit. You need to supply a valid SQL Operator name for my script to work correctly.

Performance Related Fixes in Post-SQL Server 2008 R2 Service Pack 2 Builds

As you may be aware if you have been reading my blogs or books over the years, I am a pretty strong advocate of staying current with both SQL Server Service Packs and SQL Server Cumulative Updates. This is also something that SQLskills recommends on our customer engagements. Despite this, it is still quite common to see people running very old builds of the major version of SQL Server that they are using. They might be running a SQL Server build that is 18, 24 or more months old. It might be so old that it is unsupported by Microsoft, because it is considered to be an “unsupported service pack”, meaning that Microsoft CSS will only provide limited troubleshooting support until you upgrade to a supported service pack.

As Microsoft releases new Service Packs for SQL Server, they eventually “retire” previous branches of code for that particular major release of SQL Server. When a new major version of SQL Server (such as SQL Server 2008 R2) is released, that initial build (which is what comes on the installation media unless you have built a slipstream installation) is called the release-to-manufacturing (RTM) build. Eight weeks after the RTM build, Microsoft will release a rollup of hot fixes called a Cumulative Update (CU). A SQL Server CU typically has anywhere from about ten to fifty hotfixes. Cumulative Updates are not fully regression tested like a full Service Pack is (but they have more regression testing than a stand-alone hot fix), but I have had very good luck with Cumulate Updates over the years.  The first CU for the SQL Server 2008 RTM branch was called SQL Server 2008 RTM CU1. Every eight weeks after that, Microsoft will release another CU for that RTM branch.

Eventually, Microsoft will release Service Pack 1 for that major version of SQL Server, which starts another separate release branch of the product. Eight weeks after that, the first CU for that new Service Pack will be released. So for example, you would have SQL Server 2008 R2 SP1 RTM, and then eight weeks later, you would have SQL Server 2008 R2 SP1 CU1.  Eventually, Microsoft will release Service Pack 2 for that major Version of SQL Server, which starts another separate release branch of the product. When SP2 is released, the original RTM release branch is retired, and the entire RTM release branch becomes an “unsupported service pack”, regardless of what RTM CU you have installed. That means you will have to upgrade to SP1 or SP2 to be on a supported service pack. Right now, only SQL Server 2008 R2 SP2 is a supported service pack.

As a database professional, you need to be aware of how this servicing system works, and what exact build of SQL Server that you are running on your various servers. Then you can make more informed decisions on when you want to plan, test, and implement SQL Server updates (whether you use only Service Packs or both SPs and Cumulative Updates), so that your servers are on a fully supported build of SQL Server. I also firmly believe that you will have fewer problems over time if you make a concerted effort to stay on a current build. Going through the planning and testing effort required to install a Service Pack or Cumulative Update on a production server is a good exercise for you and your organization.  It exercises your testing plan and your HA procedures, and it gives you more experience rolling out updates.

I recently was asked for some specific reasons why someone should upgrade from SQL Server 2008 R2 SP2 RTM (10.50.4000) to SQL Server 2008 R2 SP2 CU10 (10.50.4297). I decided to look through the Knowledge Base (KB) articles for every single Cumulative Update released after SQL Server 2008 R2 SP2 RTM was released, looking for specific hotfixes that seemed to affect database engine performance or basic reliability. I went from SQL Server 2008 R2 SP2 CU1 through CU10. Update: I have updated this post to include CU10, which was released on December 16, 2016.

You can look at the KB article list for SQL Server 2008 R2 Service Pack 2 to put together your own list of “important” fixes based on what features of SQL Server you are using, using the link below:

Microsoft SQL Server 2008 R2 Cumulative Update KB Article List

The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 Service Pack 2 was released

Here are all of the Cumulative Updates that I looked at, with the relevant fixes listed for each one. Keep in mind that Cumulative Updates are actually cumulative, meaning that a later CU will include all of the hotfixes from all of the previous cumulative updates. 

SQL Server 2008 R2 SP2 CU1  (10.50.4260.0)  July 24, 2012

FIX: Errors when a client application sends an attention signal to SQL Server 2008 or SQL Server 2008 R2

FIX: Access violation when you run DML statements against a table that has partitioned indexes in SQL Server 2008 R2 or in SQL Server 2012

FIX: The log shipping restore job restores a corrupted transaction log backup to a secondary database when you run a log shipping backup job on an instance of SQL Server 2008 R2 or an instance of SQL Server 2012

FIX: Performance of a SELECT statement that contains a LIKE operator and an ESCAPE clause is low in SQL Server 2008 R2

FIX: SQL Server 2008 R2 or SQL Server 2008 stops responding and a “Non-yielding Scheduler” error is logged

FIX: Errors when you run a query that uses the FOR XML clause in the PATH mode in SQL Server 2008 R2

 

SQL Server 2008 R2 SP2 CU2  (10.50.4263.0)  August 31, 2012

FIX: Incorrect results are returned when you run a query that uses parallelism in the query execution plan in SQL Server 2008, in SQL Server 2008 R2 or in SQL Server 2012

FIX: SQL Server Agent job fails if you set the job schedule type as “Start automatically when SQL Server Agent starts” in SQL Server 2008 SP2 or in SQL Server 2008 R2 SP2

FIX: Event ID 322 error even though operations complete successfully in SQL Server 2008 or in SQL Server 2008 R2 

 

SQL Server 2008 R2 SP2 CU3  (10.50.4266.0)  October 15, 2012

FIX: You receive error messages when you perform a population for a large full-text index in SQL Server 2008 or in SQL Server 2008 R2

FIX: “out of memory” error when you use ODBC with SQL Server 2012, SQL Server 2008 R2 or SQL Server 2008

FIX: SQL Server Agent logs verbose logs in the Application log when you start a failover cluster instance in SQL Server 2012 or in SQL Server 2008 R2

FIX: High CPU usage when you query a binary large object column by using the NOLOCK hint in SQL Server 2008 R2

FIX: A transaction log restore operation takes longer than expected and a 3402 error occurs if a database file is removed in SQL Server 2008 R2

FIX: Performance decreases when you run a parallel query after you upgrade from SQL Server 2008 to SQL Server 2008 R2

 

SQL Server 2008 R2 SP2 CU4  (10.50.4270.0)  December 17, 2012

FIX: Large queries that modify data run slower than expected when many locks accumulate in a SQL Server 2008 R2 or in SQL Server 2012

FIX: An access violation occurs when you query the missing indexes DMVs in SQL Server 2008 R2

FIX: Memory leak if you enable the AUTO_UPDATE_STATISTICS_ASYNC statistics option in SQL Server 2008, in SQL Server 2012 or in SQL Server 2008 R2

A database transaction log continues to grow after you upgrade to SQL Server 2012, SQL Server 2008 R2 SP1, SQL Server 2008 SP2 or SQL Server 2008 SP3

FIX: Incorrect result is returned when you query an indexed view by using the NOEXPAND hint in SQL Server 2008, in SQL Server 2008 R2 or in SQL Server 2012

FIX: You receive an incorrect result when you run a query against a partitioned table in SQL Server 2008 R2    

 

SQL Server 2008 R2 SP2 CU5  (10.50.4276.0)  February 18, 2013

FIX: Access violation occurs when you run a spatial query over a linked server in SQL Server 2008 R2 or in SQL Server 2012

FIX: Access violation when you run a query that contains many constant values in an IN clause in SQL Server 2008

FIX: A query that runs against a DMV enters an infinite loop in SQL Server 2008 R2

FIX: Data corruption occurs when LOB data is loaded into a Transact-SQL variable in SQL Server 2008 R2

FIX: Access violation when you restore transaction log backups in a SQL Server 2008 R2-based log shipping environment

FIX: “SqlDumpExceptionHandler: Process <spid> generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.” error when you run a query in SQL Server 2012, in SQL Server 2008, in SQL Server 2008 R2 or in SQL Server 2005

 

SQL Server 2008 R2 SP2 CU6  (10.50.4279.0)  April 15, 2013

FIX: Access violation or incorrect result when you insert data into or update a new partition of a partitioned table in SQL Server 2008 R2, SQL Server 2012 or SQL Server 2008

FIX: “Failed Assertion = ‘CheckValidInput (wsName, cbName)’” error when you use database mirroring in Microsoft SQL Server 2008 R2

FIX: Index size increases significantly after you rebuild the index online and the RCSI is enabled in SQL Server 2008 or in SQL Server 2008 R2

 

SQL Server 2008 R2 SP2 CU7  (10.50.4286.0)  June 17, 2013

FIX: An access violation occurs when you run a query that both uses the PIVOT operator and uses parallelism in the query execution plan in SQL Server 2008, in SQL Server 2008 R2 or in SQL Server 2012

FIX: SQL Server may freeze when an instance of SQL Server 2012, SQL Server 2008 or SQL Server 2008 R2 is shut down

FIX: Not all results are returned when you search for phrases by using Full-Text search in SQL Server 2008 or SQL Server 2008 R2 

FIX: Poor performance when you run a query that uses the LIKE operator in the WHERE clause in SQL Server 2008 R2

FIX: Access violation when you run queries that use the sys.dm_server_services DMV multiple times on several UNION clauses in SQL Server 2012 or SQL Server 2008 R2

 

SQL Server 2008 R2 SP2 CU8  (10.50.4290.0)  August 22, 2013

FIX: Query stops responding in SQL Server 2008 or in SQL Server 2008 R2 if the query alters or stops an Extended Events session

FIX: Slow performance when many connections to a database that run the same query together with different query options in SQL Server 2008 R2

FIX: Error 8985 when you run the “dbcc shrinkfile” statement by using the logical name of a file in SQL Server 2008 R2

 

SQL Server 2008 R2 SP2 CU9  (10.50.4295.0)  October 28, 2013

FIX: Index size increases significantly after you rebuild the index online and the RCSI is enabled in SQL Server 2008 or in SQL Server 2008 R2

FIX: Access violation in replication Distribution Agent in SQL Server 2008 R2 Transactional Replication

FIX: Poor performance when SQL Server 2008 or SQL Server 2008 R2 requires memory from a local node in a NUMA environment

FIX: It takes longer than expected to create a filtered statistic after you create a clustered index on a partitioned table in SQL Server 2008 R2

FIX: Nonclustered index corruption may occur when you run a complex UPDATE statement together with a NOLOCK hint against a table in SQL Server 2008 or in SQL Server 2008 R2

FIX: “Could not redo log record” error and log shipping stops working after an automatic failover in SQL Server 2008 R2

 

SQL Server 2008 R2 SP2 CU10 (10.50.4297.0) December 16, 2013

FIX: A small number of rows are sampled in SQL Server 2008 R2 when table statistics are updated automatically or together with specified sampling rate

FIX: Access violation when you query sys.dm_server_memory_dumps in SQL Server 2008 R2

FIX: Replication Queue Reader Agent crashes when you run transactional replication in SQL Server 2008 R2

 

Hopefully, seeing a partial list of the Post-SP2 fixes for SQL Server 2008 R2 may help convince more people to consider deploying Cumulative Updates for SQL Server 2008 R2.