SQL Server 2014 SP1 Cumulative Update 2

On August 17, 2015, Microsoft released SQL Server 2014 SP1 Cumulative Update 2, which is Build 12.0.4422. This CU has 44 hotfixes in the public fix list, which is a relatively high number. This shows that Microsoft has been busy fixing important SQL Server issues, and it also underscores the importance of paying attention to cumulative updates and making an effort to stay current with your builds of SQL Server.

They also released SQL Server 2014 RTM CU9, which is Build 12.0.2553. This CU has 30 hotfixes in the public fix list. Personally, I think you should be moving to the SQL Server 2014 SP1 branch by now, or in the very near future, rather than staying on the RTM branch much longer.

New Benchmark Feature in CPU-Z 1.73

Franck Delattre has released CPU-Z 1.73, which adds more Windows 10 support, an L4 cache frequency display (useful for some Intel Broadwell processors), and a CPU Benchmark tab, that lets you run a very quick single-threaded CPU benchmark and a multi-threaded CPU benchmark. This benchmark test takes about 14-15 seconds on my workstation. There is a relatively small selection of “reference processors” that you can select from the Reference drop-down, so you can compare your processor to one of those processors.

You can also run a CPU stress test, that will peg all of your CPU cores at 100% CPU utilization until you stop the test. This is useful for seeing whether Intel Turbo Boost is in effect or whether it is not being used due to some form of power management.

image

Figure 1: CPUID Benchmark results for over-clocked Intel Core i7-3770K

 

image

Figure 2: Windows 7 Task Manager after starting Stress CPU

 

Just for fun, I grabbed the same information from my World of Tanks gaming rig (which has a slightly over-clocked Intel Core i7-4790K processor), as shown below.

Capture

Figure 3: CPUID Benchmark results for over-clocked Intel Core i7-4790K

 

Capture1

Figure 4: Windows 10 Task Manager after starting Stress CPU

SQL Server Diagnostic Information Queries for August 2015

This month, I have added a new query for SQL Server 2016, that checks your QueryStore options. The SQL Server 2016 version will continue to be improved with more SQL Server 2016-specific queries and new columns in existing queries over the next few months. I am also continuing to add more relevant comments and links to the other active versions of these queries in order to help better interpret the results of these queries.

Rather than having a separate blog post for each version, I have just put the links for all six major versions in this single post. There are two separate links for each version. The first one on the top left is the actual diagnostic query script, and the one below on the right is the matching blank results spreadsheet, with labeled tabs that correspond to each query in the set. 

Here are links to the latest versions of these queries for SQL Server 2016, 2014 and 2012:

SQL Server 2016 Diagnostic Information Queries (August 2015)

SQL Server 2016 Blank Results

SQL Server 2014 Diagnostic Information Queries (August 2015)

SQL Server 2014 Blank Results

SQL Server 2012 Diagnostic Information Queries (August 2015)

SQL Server 2012 Blank Results

 

Here are links to the most recent versions of these scripts for SQL Server 2008 R2 and older:

Since SQL Server 2008 R2 and older are out of Mainstream support from Microsoft (and because fewer of my customers are using these old versions of SQL Server), I am not going to be updating the scripts for these older versions of SQL Server every single month going forward.  I started this policy four months ago, and so far, I have not heard any complaints.

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results

 

The basic instructions for using these queries is that you should run each query in the set, one at a time (after reading the directions for that query). It is not really a good idea to simply run the entire batch in one shot, especially the first time you run these queries on a particular server, since some of these queries can take some time to run, depending on your workload and hardware. I also think it is very helpful to run each query, look at the results (and my comments on how to interpret the results) and think about the emerging picture of what is happening on your server as you go through the complete set. I have some comments in the script on how to interpret the results after each query.

You need to click on the top left square of the results grid in SQL Server Management Studio (SSMS) 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 blank results spreadsheet.

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. Running the database-specific queries while being connected to the master database is a very common mistake that I see people making when they run these queries.

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, or that someone is running an incorrect version of the script for their version of SQL Server.

It is very important that you are running the correct version of the script that matches the major version of SQL Server that you are running. There is an initial query in each script that tries to confirm that you are using the correct version of the script for your version of SQL Server. If you are not using the correct version of these queries for your version of SQL Server, some of the queries are not going to work correctly.

If you want to understand how to better run and interpret these queries, you should consider listening to my three latest Pluralsight courses, which are SQL Server 2014 DMV Diagnostic Queries – Part 1SQL Server 2014 DMV Diagnostic Queries – Part 2 and SQL Server 2014 DMV Diagnostic Queries – Part 3. All three of these courses are pretty short and to the point, at 67, 77, and 68 minutes respectively. Listening to these three courses is really the best way to thank me for maintaining and improving these scripts…

Finally, if you want to get even more information about how to run and interpret these queries, you should consider attending my half-day session Dr. DMV’s Troubleshooting Toolkit at the PASS Summit 2015 in Seattle this October.

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

Processor Selection for SQL Server

Over the past three years, I have written a number of articles and blog posts about processor selection for SQL Server 2012 and 2014. To make them easier to find for others (and myself), I have put together a list of links, in reverse chronological order.

I will update this post as I write new posts and articles.

SQLskills.com Blog posts:

Interesting Upcoming Intel Processors

Intel Xeon E7 v3 Product Family Released

TPC-E Single-Threaded Performance Leaderboard

Intel Xeon E5-2600 v3 Product Family and SQL Server

Recommended Intel Processors For SQL Server 2014 OLTP Workloads

Two New TPC-E Benchmark Results for Intel Xeon Ivy Bridge-EX Processors

Bigger Database Servers Get Faster

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

Updates on Intel Xeon Ivy Bridge Server Processor Launch Schedules

Using TPC-E OLTP Benchmark Scores to Compare Processors

Intel Xeon E5-2600 v2 Series Processors (Ivy Bridge-EP) in Q3 2013

New AMD-based TPC-E Benchmark Result

Deciding What Processor to Choose for SQL Server 2012

Two Database Server Models Compared

Recommended Intel Processors For SQL Server 2012 OLTP Workloads

 

Articles from SQLPerformance.com

Recommended Intel Processors for SQL Server 2014 – March 2015

Database Server Hardware Trends

Recommended Intel Processors for SQL Server 2014 Workloads

Selecting a Processor for SQL Server 2014 – Part 2

Selecting a Processor for SQL Server 2014 – Part 1

SQL Server Database Server Hardware Upgrade Case Study

Selecting a Processor for SQL Server 2012

SQL Server Cumulative Update Highlights

I have a series of blog posts (that I try to keep updated over time) that list I what I consider to be the more important hotfixes in each cumulative update for the respective branch of code for that version of SQL Server. In order to make it easier for people (and myself) to find these posts, I have put together a list of these posts below.

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

Performance and Stability Related Fixes in Post-SQL Server 2014 RTM Builds

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

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

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

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

Performance and Stability-Related Fixes in All SQL Server 2008 SP2 Cumulative Updates

The main point of these posts is to try to convince more people to stay up to date with SQL Server cumulative updates, especially with SQL Server 2012 and 2014, where most cumulative updates have had a large number of very significant hotfixes.

For people who don’t believe in installing SQL Server cumulative updates, this Microsoft KB article might help convince you.

Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 used with high-performance workloads

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

As of August 17, 2015, there have been two Cumulative Updates (CU) for the Service Pack 1 branch of SQL Server 2014. There were a large number of hotfixes in both cumulative updates. If you are running SQL Server 2014 SP1, I really think you should be running the latest SQL Server 2014 SP1 Cumulative Update. Right now, that means SP1, CU2 (Build 12.0.4422), which was released on August 17, 2015. 

Note: SQL Server 2014 Service Pack 1 was re-released on May 4, 2015, and SQL Server 2014 SP1 CU2 was released on August 17, 2015. SQL Server 2014 SP1 CU2 is synchronized with SQL Server 2014 RTM CU9, so I think it is now ok to migrate to the SQL Server 2014 SP1 branch after you have done your own application testing and are ready to make that move.

Table 1 shows the SQL Server 2014 SP1 CU builds that have been released so far.

Build Description Release Date
12.0.4416 SP1 CU1 June 19, 2015
12.0.4422 SP1 CU2 August 17, 2015

Table 1: SQL Server 2014 SP1 CU Builds

You can follow the KB article link below to see all of the CU builds for both the SQL Server 2014 RTM and the SQL Server 2014 SP1 branches.

SQL Server 2014 Build Versions

Like I have done for other versions and branches of SQL Server, I decided to scan the hotfix list for all of the Cumulative Updates in the RTM 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 specific SQL Server 2014 features you are using.

Here are the fixes in the SP1 branch:

SQL Server 2014 SP1 Cumulative Update 1 (Build 12.0.4416), 112 total public hot fixes

FIX: Performance issue occurs when you use sqlncli11.dll ODBC driver to access SQL Server in an application

FIX: Improved memory management for columnstore indexes to deliver better query performance in SQL Server 2014

FIX: A SELECT query that’s run as a parallel batch-mode scan triggers a deadlock in SQL Server 2014

FIX: Data loss might occur when you use Windows Azure Storage in SQL Server 2014 on high latency networks

Incorrect values are included in the description column when you query the sys.dm_xe_objects dynamic management object in SQL Server 2014

FIX: Cannot create statistics when you execute statement to create it directly or in the background in SQL Server 2014

FIX: Complex parallel query does not respond in SQL Server 2012 or SQL Server 2014

FIX: An I/O error that occurs on BPE files causes SQL Server 2014 to disable BPE

FIX: Access violation occurs when you connect to an instance of SQL Server 2014

“Non-yielding Scheduler” error occurs and SQL Server stops responding when a database has columnstore indexes on a Microsoft SQL Server 2014 instance

Incorrect results are returned in an indexed view after an Insert or Delete operation on the base table occurs in SQL Server 2014

FIX: Memory leak in USERSTORE_SCHEMAMGR and CPU spikes occur when you use temp table in SQL Server 2012 or 2014

FIX: DBCC CHECKDB and DBCC CHECKTABLE take longer to run when SQL CLR UDTs are involved in SQL Server 2012 or SQL Server 2014

FIX: Access violation occurs when you delete rows from a table that has clustered columnstore index in SQL Server 2014

FIX: Paging out in memory occurs when columnstore index query consumes large memory in SQL Server 2014

FIX: A severe error occurs in SQL Server 2014 during compilation of a query on a table with clustered columnstore index

Large query compilation waits on RESOURCE_SEMAPHORE_QUERY_COMPILE in SQL Server 2014

FIX: Error 1205 when you execute parallel query that contains outer join operators in SQL Server 2014

FIX: The transaction isolation level is reset incorrectly when the SQL Server connection is released in SQL Server 2014

FIX: SOS_CACHESTORE spinlock contention on ad hoc SQL Server plan cache causes high CPU usage in SQL Server 2012 or 2014

FIX: Memory leak occurs when you run DBCC CHECKDB against a database in SQL Server 2014

FIX: Error 8646 when you run DML statements on a table with clustered columnstore index in SQL Server 2014

FIX: SQL Server takes long time to open database after recovery to add or remove a data file in SQL Server 2014 or SQL Server 2012

FIX: Access violation and “No exceptions should be raised by this code” error occur when you use SQL Server 2012 or SQL Server 2014

FIX: Error 3624 occurs when you execute a query that contains multiple bulk insert statements in SQL Server 2014

FIX: Access violation occurs when you query against a table that contains column store indexes in SQL Server 2014

FIX: Incorrect choice of a nested loops join strategy causes poor query performance in SQL Server 2014

FIX: Error 33294 occurs when you alter column types on a table that has clustered columnstore indexes in SQL Server 2014

FIX: Access violation when a missing task for a background clear operation tries to delete a missing index entry

FIX: Error 33415 occurs when you restart the SQL Server service or alter database in SQL Server 2012 or SQL Server 2014

FIX: Error 35377 when you run a parallel query that contains MERGE statements in SQL Server 2014

 

SQL Server 2014 SP1 Cumulative Update 2 (Build 12.0.4422), 44 total public hot fixes

FIX: The Resource database is missing after you install updates or service packs for instances of SQL Server 2012 one after another and then restart the server

FIX: Incorrect result when you execute a full-text query with parallel execution plan in SQL Server 2012 or SQL Server 2014

FIX: CMEMTHREAD waits occur when you execute many ad hoc queries in SQL Server 2012 or SQL Server 2014

FIX: You cannot set a SQL Server 2012 or 2014 database to single-user mode if the database is read-only

FIX: Cannot enable “change data capture” on a table that uses a unique index with more than one included column in SQL Server 2012 or 2014

FIX: Error messages are logged when you execute a non-cacheable auto-parameterized query in SQL Server 2012 or 2014

FIX: Error 1635 occurs when you use Product Update to install SQL Server 2014 SP1 and SQL Server 2014 SP1 CU1

Restore Log with Standby Mode on an Advanced Format disk may cause a 9004 error in SQL Server 2008 R2 or SQL Server 2012

FIX: Increased HADR_SYNC_COMMIT wait types when AlwaysOn Availability Groups contain memory-optimized tables in SQL Server 2014 Service Pack 1

FIX: A stalled dispatcher system dump occurs forcing a failover and service outage on the availability groups in SQL Server 2014 Service Pack 1

FIX: Incorrect results occur in a rare scenario when you run a query that contains a nested loop join and performance spool in its inner side in SQL Server 2012 or 2014

FIX: An AlwaysOn database on a secondary replica shows a “NOT SYNCHRONIZING” status in SQL Server 2014

FIX: “Possible schema corruption. Run DBCC CHECKCATALOG” error occurs when you delete column in SQL Server 2014

FIX: Access violation and unexpected SQL Server shutdown occur when common criteria compliance is enabled

FIX: The change table is ordered incorrectly for updated rows after you enable change data capture for a Microsoft SQL Server 2012 or 2014 database

FIX: A deadlock occurs when you execute the sp_estimate_data_compression_savings stored procedure in SQL Server 2012

FIX: Hash or merge join hints may be ignored when you execute a query in SQL Server 2012 or SQL Server 2014

FIX: An online rebuild of indexes is unsuccessful when a table partition is read-only in SQL Server 2014 Service Pack 1

FIX: Maintenance Plan performs offline index rebuild for large object table in SQL Server 2012 or SQL Server 2014

 

The reason that I put these lists together is that I want to convince more people to try to keep their SQL Server instances up to date with Cumulative Updates. If you do the proper testing, planning and preparation, I think the risks from installing a SQL Server Cumulative Update are quite low (despite the occasional issues that people run into).

If you install a Cumulative Update or Service Pack on a Production system the day it is released, after doing no testing whatsoever, and then run into problems (and don’t have a plan on how to recover), then I don’t have that much sympathy for you. 

On the other hand, if you go through a thoughtful and thorough testing process, and you have a plan for how you will install the CU, and how you would recover if there were any problems, then you are much less likely to have any problems. You are also much more likely to avoid the issues that are fixed by all of the included fixes in the new build of SQL Server. You have done your job as a good DBA.

SQL Server Diagnostic Information Queries for July 2015

This month, I have added a couple of new queries for SQL Server 2016, including one that uses QueryStore. This version will continue to be improved with more SQL Server 2016-specific  queries and columns in existing queries over the next few months.

Since SQL Server 2008 R2 and older are out of Mainstream support from Microsoft (and because fewer of my customers are using these old versions of SQL Server), I am not going to be updating the scripts for these older versions of SQL Server every single month going forward.  I started this policy three months ago, and so far, I have not heard any complaints.

Rather than having a separate blog post for each version, I have just put the links for all five major versions in this single post. There are two separate links for each version. The first one on the top left is the actual diagnostic query script, and the one below on the right is the matching blank results spreadsheet, with labeled tabs. 

Here are links to the latest versions of these queries for SQL Server 2016, 2014 and 2012:

SQL Server 2016 Diagnostic Information Queries (July 2015)

SQL Server 2016 Blank Results

SQL Server 2014 Diagnostic Information Queries (July 2015)

SQL Server 2014 Blank Results

SQL Server 2012 Diagnostic Information Queries (July 2015)

SQL Server 2012 Blank Results

Here are links to the most recent versions of these scripts for SQL Server 2008 R2 and older:

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results

The basic instructions for using these queries is that you should run each query in the set, one at a time (after reading the directions for that query). It is not really a good idea to simply run the entire batch in one shot, especially the first time you run these queries on a particular server, since some of these queries can take some time to run, depending on your workload and hardware. I also think it is very helpful to run each query, look at the results (and my comments on how to interpret the results) and think about the emerging picture of what is happening on your server as you go through the complete set. I have some comments in the script on how to interpret the results after each query.

You need to click on the top left square of the results grid in SQL Server Management Studio (SSMS) 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 blank results spreadsheet.

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. Running the database-specific queries while being connected to the master database is a very common mistake that I see people making when they run these queries.

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, or that someone is running an incorrect version of the script for their version of SQL Server.

It is very important that you are running the correct version of the script that matches the major version of SQL Server that you are running. There is an initial query in each script that tries to confirm that you are using the correct version of the script for your version of SQL Server. If you are not using the correct version of these queries for your version of SQL Server, some of the queries are not going to work correctly.

If you want to understand how to better run and interpret these queries, you should consider listening to my three latest Pluralsight courses, which are SQL Server 2014 DMV Diagnostic Queries – Part 1SQL Server 2014 DMV Diagnostic Queries – Part 2 and SQL Server 2014 DMV Diagnostic Queries – Part 3. All three of these courses are pretty short and to the point, at 67, 77, and 68 minutes respectively. Listening to these three courses is really the best way to thank me for maintaining and improving these scripts…

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

Using Windows Resource Monitor to Find Unusual Performance Bottlenecks

I recently had an interesting issue with a new SQL Server 2014 Enterprise Edition instance that was going to be hosting about 200 user databases that each use the SQL Server Filetable feature. The reported problem was that this new instance was taking two-three times longer to restore each full database backup than the restores were taking on a different instance (that had similar hardware and storage resources).

I had several initial ideas about what the problem might be, including:

  1. Windows instant file initialization was not enabled
    1. This would force Windows to have to “zero-initialize” the database data file after it was created during the restore, which slows down the restore
    2. It was already enabled on this instance, so that was not the problem
  2. The transaction log files were particularly large for these user databases
    1. Windows instant file initialization does not work on log files, only on data files
    2. This issue would be present on any database instance, not just one
  3. The VLF counts were particularly high in the log files for these databases
    1. This causes the recovery portion for a full database restore (or crash recovery) to take much longer
    2. None of the VLF counts were above 300, plus this would behave the same on any database instance

After my initial ideas were found to be incorrect, I tried restoring a full backup of one of the user databases to the new database server, making sure to use the Stats = 1 option, so the restore progress statistics would update every 1%. This gives you more immediate and fine-grained feedback about the progress of the restore, which is always a good idea.

While the restore was running, I carefully watched the disk activity in Windows Resource Monitor.  You can sort the columns in Windows Resource Monitor to see the highest Reads and Writes (in bytes/second) while the database restore is running. This will show the reads from the database backup file, then the writes to the database data file(s) and the database log file. It also showed the writes to the filetable files, and then something that I did not expect…

The full database restore went very fast until it got to 92%. Then I saw a huge number of parallel reads by the MsMpEng.exe process (which is the Microsoft Antimalware service), which slowed the restore progress to an absolute crawl, also pegging one of the CPU cores on the instance (so it looks like that service is single-threaded).  This seemed to be a major factor in the slowness of the database restore as MsMpEng.exe process was scanning the 8,684 Filetable documents that were in this particular user database.

The solution to this issue was simply changing the exclusion rules for the Microsoft Antimalware Service to not scan the files in the filetable directories. This one change decreased the database restore time by 56%.

Here is the restore command and the results of the test.

-- Test restore of TestUserDatabase
-- This database uses Filetables
RESTORE DATABASE [TestUserDatabase] 
FROM  DISK = N'T:\BackupRestoreTest\TestUserDatabase_FULL.bak' WITH  FILE = 1,  
MOVE N'TestUserDatabase' TO N'D:\SQLDATA\TestUserDatabase.mdf',  
MOVE N'TestUserDatabase_log' TO N'L:\Logs\TestUserDatabase_log.ldf',  
MOVE N'DOCUMENTS' TO N'D:\SQLDATA\TestUserDatabase_DOCUMENTS',  NOUNLOAD,  STATS = 1;
GO

-- Actual elapsed time 6:21 (Before directory exclusion change)
--Processed 2358856 pages for database 'TestUserDatabase', file 'TestUserDatabase' on file 1.
--Processed 4 pages for database 'TestUserDatabase', file 'TestUserDatabase_log' on file 1.
--Processed 208905 pages for database 'TestUserDatabase', file 'DOCUMENTS' on file 1.
--RESTORE DATABASE successfully processed 2567764 pages in 378.708 seconds (52.971 MB/sec).


-- Actual elapsed time 2:52 (After directory exclusion change)
--Processed 2358856 pages for database 'TestUserDatabase', file 'TestUserDatabase' on file 1.
--Processed 4 pages for database 'TestUserDatabase', file 'TestUserDatabase_log' on file 1.
--Processed 208905 pages for database 'TestUserDatabase', file 'DOCUMENTS' on file 1.
--RESTORE DATABASE successfully processed 2567764 pages in 167.607 seconds (119.688 MB/sec).

 

Figure 1: T-SQL for Database Restore Test

 

One thing you might notice is that the filetable directory is on the same LUN as the PRIMARY data file for this database, which is not really the best location. Ideally this directory would be on a separate LUN from any other SQL Server database files, but this may not always be possible due to economic resource constraints.

Here is some more information about filestream performance:

High-performance FILESTREAM tips and tricks

Best Practices on FILESTREAM implementations

Upcoming Speaking Engagements

I found out on Friday that I will have two speaking slots at the PASS Summit 2015 in Seattle, WA. The annual PASS Summit is always a lot of fun and a great learning and networking opportunity.

 

I'm Speaking Graphic_Large

 

The first session will be a half-day session, which will let me go into much more detail about this subject:

Dr. DMV’s Troubleshooting Toolkit

Dynamic management views (DMVs) and functions allow you to easily see exactly what is happening inside your SQL Server instances and databases, with a high level of detail. You can discover your top wait types, identify the most CPU-intensive stored procedures, find missing indexes, and identify unused indexes, to name just a few examples. This session presents, demonstrates, and explains a complete set of diagnostic DMV queries that you can easily use to detect and diagnose configuration and performance issues in your SQL Server instances and databases. This session goes into exhaustive detail about how to interpret the results of each diagnostic query, including relevant background information about how to properly configure your hardware, storage subsystem, operating system, SQL Server instance, and databases to avoid many performance and scalability issues.

I will also be doing a regular 75 minute session:

High Availability/Disaster Recovery 101

How do you design a SQL Server 2014 infrastructure to meet specific Recovery Time Objective (RTO) and Recovery Point Objective (RPO) service levels? There are many aspects to consider, from technology choices and licensing to policies and procedures. This session outlines and compares the various high availability and disaster recovery technologies available in SQL Server 2014, showing how you can combine them to design and build a solution to help meet your goals. This session also teaches you how to formulate policies that enable effective use of technology in your organization.

The entire SQLskills team will be speaking at PASS Summit 2015, including Paul Randal, Kimberly Tripp, Jonathan Kehayias, Erin Stellato, and Tim Radney.

Before then, I will be speaking at a number of other events, some of which will be remote and some in person.

Pittsburg SQL Server User’s Group on Jun 30 at 5PM Eastern Time                    Dr. DMV’s Troubleshooting Toolkit

Denver WIT on July 8 at 5:30PM Mountain Time                                               Dr. DMV’s Troubleshooting Toolkit

Johannesburg SQL Server User Group on July 14 at 11AM Mountain Time           Dr. DMV’s Troubleshooting Toolkit

BIG PASS User’s Group on August 11 at 7PM Pacific Time                                 Dr. DMV’s Troubleshooting Toolkit

 

On Friday, September 18, 2015 I will be doing a full day pre-con for SQLSaturday #441 in Denver, CO. This should be very interesting and a lot of fun.

Analyzing and Improving I/O Subsystem Performance    

SQL Server is often I/O bound, but proving it to your storage or SAN administrator can be challenging! You will learn about the different types of storage that are available for SQL Server, and how to decide what type of storage to use for different SQL Server workload and file types. You will also learn useful tips and techniques for configuring your storage for the best performance and reliability for your workload. There will be extensive coverage on how to use disk benchmark tools like CrystalDiskMark 4.0, SQLIO and Microsoft DiskSpd, so you can confidently understand the performance that your I/O subsystem can deliver. We’ll also cover methods to effectively measure and monitor your storage performance from an OS and SQL Server perspective so that you will have valuable information and evidence available the next time you have to discuss I/O performance with your storage administrator. You will also learn a number of valuable OS and SQL Server configuration settings that will help you get the best I/O performance possible from your storage subsystem. 

Finally, I’ll be speaking at the Fall 2015 SQLintersection event in Las Vegas.

SQL Server 2014 RTM CU8 Available

Microsoft has released SQL Server 2014 RTM CU8, which is Build 12.0.2546. This cumulative update has 38 public hotfixes by my count. This CU may be less attractive now that Microsoft has also released SQL Server 2014 SP1 CU1. I say that because many people may be wanting to get on the SP1 branch, now that the first CU on top of SP1 has been released, and the branches are back in synch.

The official Microsoft build list for SQL Server 2014 is here.