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