Help for Migration to SQL Server 2008

SQL Server 2008 has launched the new Migration Solution Center.  On this site, the freely downloadable SQL Server Migration Assistants (SSMAs) which can help to move databases from Access, Oracle, and Sybase to SQL Server.  Check out these free tools from Microsoft.

Simple Sector Alignment

In my experience as a SQL Server consultant, about 5% of my customers have heard of Sector Alignment.  The fact that a properly aligned partition will out perform a non-aligned partition by 20% or more should make this the most important thing you read today.  Here it is as simple as I can make it.

First, find out what is your FAU (File Allocation Unit size) by running fsutil fsinfo ntfsinfo <drive letter like c:\> at a command prompt.  Read the line Bytes Per Cluster which is your FAU.  The FAU for my local workstations c: drive is 4096 bytes.

Next, and only if you are working on a SAN or RAID array, find the Stripe Size.  How to find this will vary by manufacturer or utility that you use.  For this example, we'll assume I have a Stripe Size of 32k.

Third is the Partition Offset.  This is found by running WMIC PARTITION LIST FULL.  The value that you look for is StartingOffset.  Mine is 32256 (the default) which is less than 32k.

Now divide the Partition Offset by the Stripe Size.  32256 / 4096 = 7.875.  To be properly aligned, the result should be a whole number.  Let's say I change my Partition offset to 64k or 65536 bytes.  65536 \ 4096 = 16.  Good so far.

Next, we need the FAU to be aligned with the Stripe Size.  Put another way, FAU / Stripe Size should be a whole number.  For me example, 4096 / 4096 = 1.  Good.

If you have trouble with all of the above, FAU = 64k, Starting Offset = 64K, and Stripe Size = 64K almost always works great for SQL Server. 

The biggest gotcha is that setting the Partition Offset must be done when you create the partition.  This means that if the partition is already in use it would need to be recreated which would require moving all data on the partition to another location and then moving it back once complete. You must also be using Windows Server 2003 SP1 or later.  The Partition Offset is made with the DISKPART utility.  Instructions are here: http://support.microsoft.com/default.aspx?scid=kb;en-us;923076&sd=rss&spid=3198.

I am now aligned and my IO system is smokin'!  I hope your results are the same.

Best practices for installing service packs, cumulative updates and hotfixes for SQL Server

Here is the short version:

1. Test on a test/dev SQL Server first and only after you have confirmed that all applications are working as expected then install it on a production SQL Server.
2. Review the Readme for Service Pack/cumulative update/hotfix. Any concerns/recommendations will be found in the readme.
3. Run DBCC CHECKDB on ALL databases (user and system databases) and ensure that there were no errors reported.
4. Backup ALL databases (user and system databases) and full-text catalogs (if applicable). This is NOT required but highly recommended.
5. Stop Monitoring and Anti-virus services
6. Make sure you have the proper permissions to install (administrative privilege on server/cluster node)
7. The below points are for clustered SQL Server instance

· Make sure ALL SQL resources come online on ALL cluster nodes

· Make sure that ALL disk resources (even the ones that not being used by SQL Server) are online and not in failed state.

· Verify that there are no dependencies other than those created by the SQL Server setup on any SQL Server cluster resources.

· Run MPSRPT_SQL.exe on all cluster nodes (not required but recommended)

· LOG OFF of all other nodes except the one from which you are running setup. Make sure all remote desktop connections are closed. You can connect to the node (you are running the setup from) using remote desktop connections but you should disconnect any remote connections to other cluster nodes.

· Make sure SQLSERVER.EXE and its disks are on the node from which you are running setup. We can run hotfix, Cumulative Updates, and Service Pack installs on a passive node (i.e a node where sqlservr.exe is not running). There is a switch called /passive that be used to do this.

 

For the long version, check out Installing SQL Server 2005.

 

Thanks to Uttam Parui for starting the email thread that resulted in this list.

Look for 825 Errors = Save yourself from Doom

SQL Server throws and 825 error when it could not read a page between one a three times, but only at level 10. Add a specific Agent alert for error 825 to get a jump on IO system problems.

From a post on SQLSkills.com by Paul S. Randal - http://www.sqlskills.com/BLOGS/PAUL/post/A-little-known-sign-of-impending-doom-error-825.aspx for more details

Testing Your New I/O Hardware

SQLIOStress simulates a very heavy SQL Server workload and should do a far better job of discovering flaws in your hardware setup than individual hardware vendors' diagnostics will.

Trace flag 806 will cause 'DBCC-style' page auditing to be performed whenever a database page is read into the buffer pool.

Trace flag 3422 will cause auditing of transaction log records as they're read win

 

Thanks to Paul S. Randal from SQLSkills for these tips.  Details at

http://www.sqlskills.com/blogs/paul/post/How-to-tell-if-the-IO-subsystem-is-causing-corruptions.aspx

Checksum

Checksum is only applied to an updated database when pages are read into cache, changed and written back to disk. Just turning on the CheckSum option makes no change to the pages. The checksum value is only calculated and applied once the page is read into cache, changed and written back to disk.

The only fix for a corrupt page is to delete the page and fix the links

Always use the WITH CHECKSUM option when taking backups if checksum option is on

CONTINUE_AFTER_ERROR option will force backup to do just that. CONTINUE_AFTER_ERROR will also allow a corrupt backup to restore.

Thanks to Paul S. Randal from SQLSkills for these tips. Details at

http://www.sqlskills.com/blogs/paul/post/Example-20002005-corrupt-databases-and-some-more-info-on-backup-restore-page-checksums-and-IO-errors.aspx

Need a Corrupt Database?

Corrupt databases (2000 and 2005) for testing available at http://www.sqlskills.com/blogs/paul/post/Example-20002005-corrupt-databases-and-some-more-info-on-backup-restore-page-checksums-and-IO-errors.aspx

 

Thanks to Paul Randall from SQLSkills for making these databases available.

MAXDOP in SQL Server

Setting MAXDOP = 1 in OLTP environment has been known to reduce total waits (25%) and CPU utilization (30%). MAXDOP = 0 is the best option for read-only/OLAP environments.

 

Thanks again to Jimmy May for this tip.  Details at

http://blogs.msdn.com/jimmymay/archive/2008/12/02/case-study-part-2-cxpacket-wait-stats-max-degree-of-parallelism-option-suppressing-query-parallelism-eliminated-cxpacket-waits-liberated-30-of-cpu.aspx

Performance and Virtual Log Files in SQL Server 2008

"It is definitely a significant performance issue in SQL Server 2008 to have a large number of virtual log files in a transaction log. Moreover, the test results show that the problem can be felt by common SQL operations such as insert, update, and delete in large batch processes."

From Linchi Shea's SQL Server blog: http://sqlblog.com/blogs/linchi_shea/archive/2009/02/09/performance-impact-a-large-number-of-virtual-log-files-part-i.aspx

Recommendations for Running SQL Server 2008 in a Hyper-V Environment

• Utilize either pass-through disks or fixed VHDs for your guest virtual machine storage. These are the best option for performance, and they should provide the best results for SQL Server workloads. Dynamic VHDs are not recommended due to performance reasons.
• Avoid using emulated devices and instead ensure that integration components for Hyper-V have been installed and synthetic devices are being used for I/O, network, and so on. Synthetic devices will provide the best performance with lowest amount of CPU overhead.
• The ability to use some of these techniques will depend on the hardware capabilities.
• For workloads that make heavy use of network resources, refer to the Virtualization and Network sections of the Windows Performance Tuning guide for best practices on optimizing network for your particular configuration. Test the performance with of your workload, as workload characteristics can vary greatly.

Observations from Running SQL Server 2008 in a Hyper-V Environment

• Hyper-V guest virtual machines are limited to a maximum of four CPU cores; therefore, you should run SQL Server within Hyper-V guest virtual machines only if your workload performance can be satisfied by no more than four CPUs.
• When compared against native configurations with comparable hardware resources, the same throughput can be achieved within a guest virtual machine at a cost of slightly increased CPU utilization. It is possible with Hyper-V to over-commit CPU resources when the total number of logical CPU cores configured across all guest virtual machines is more than the actual number of physical CPU cores available on the server. In these cases, we observed more CPU overhead and performance overhead when we ran SQL Server workloads. Proper hardware sizing is critical to SQL Server performance. You should ensure that cumulative physical CPU resources on a server are adequate to meet the needs the guest virtual machines by testing your workload in the planned virtualized environment
• Networking-intensive workloads will see higher CPU overhead and thus more performance impact.
• The information captured so far is specific to performance considerations; for your deployment, take functional considerations (i.e., supported configurations, options to achieve high availability, and so on) into account. There is more information in the appendix section of the paper (link below), which covers general Hyper-V functionality and current support policies related to running SQL Server within Hyper-V configurations.
• We found that there was minimal overhead of I/O performance when running SQL Server from within a guest virtual machine. Pass-through disk configuration provided the best I/O performance; however, we observed minimal overhead when we ran using fixed-size VHDs. The decision of which storage configuration to use should be made based on what makes sense for the particular deployment; virtual machines using VHDs are easier to move around than pass-through disks.
• For consolidation scenarios, the amount of storage resources available as well as the scenario will drive your decision. In our testing, we found acceptable performance in both the shared and dedicated configurations. In either case, you should size your storage with your workload and response time requirements in mind. Always follow best practices with respect to the underlying storage in Hyper-V environments, just as you would with any SQL Server deployment. For more information, see Predeployment I/O Best Practices for SQL Server.

These recommendations are from the whitepaper "Running SQL Server 2008 in a Hyper-V Environment" which is available for download here: http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/SQL2008inHyperV2008.docx

SQL Server 2008 Upgrade Whitepaper

A 490 Page whitepaper  on upgrading to SQL Server 2008 is available for download at:

http://www.microsoft.com/downloads/details.aspx?FamilyID=66d3e6f5-6902-4fdd-af75-9975aea5bea7&displaylang=en

Unexpected Behavior on Connection Pooling Resets

sp_reset_connection does NOT reset the transaction isolation level to the server default from the previous connection's setting.

All stored procs should start with:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

or whatever is appropriate for that proc.

Thanks to aspiring geek, Jimmy May for this tip.  Details at

http://blogs.msdn.com/jimmymay/archive/2009/02/02/sp-reset-connection-does-not-reset-transaction-isolation-level-unexpected-behavior-by-design.aspx

The First Real Post - Auto Page Repair in SQL 2008

Microsoft SQL Server 2008 Auto Page Repair with Mirroring

This feature is based on the fact that the principal and mirror databases are exactly the same. So, if a page becomes corrupt on the principal, SQL Server should be able to read the page from the mirror and use it to fix the principal. Similarly, if a page becomes corrupt on the mirror, the page can be read from the principal to fix up the mirror.

See http://www.sqlskills.com/blogs/paul/post/SQL-Server-2008-Automatic-Page-Repair-with-Database-Mirroring.aspx for the details.

Begin Here

I've been blogging for a bit over 4 years at this point. Most of my blogging has been about the parts of my life that I think could be of interest to others like poker, travel, and politics. One thing that I have not written much about is my profession and what I learn from it, SQL Server Consultant. Over the last 12 years, I've learned a couple things about the product and it's application.

I seem to have many of the basics down. What I pick up now are new features and little gems that somehow escaped me. I get much of my information about SQL Server from blogs, of which there are many. The world does not need another SQL blog that posts problems and solutions with lots of examples and detail. Well, maybe there can never be too much good information out there, but it is not my intent to do so.

I intend to make this a meta-blog. When I pick up a little known fact or use or problem with SQL Server in another blog, I will post a brief synopsis of it here with a link to the source blog. It is my hope that SQL professionals will set up an RSS feed or visit regularly to pick up the gems. It there is particular interest, you may want to go see the detail. I learn by searching and repeating. You can learn by visiting. I hope you come by often.

Special K