SQL Q&A Database Size, Mirroring, Marked Transactions, and More
A Generally, the following pitfalls can cause performance degradation when upgrading, so try to avoid them:
- You haven’t rebuilt the statistics after upgrading to SQL Server 2005.
- You have JOINS and WHERE clauses that compare two different data types, resulting in bad performance, especially if the server had been running SQL Server 2000 SP3 or earlier (see support.microsoft.com/kb/271566/).
- The SQL Server 2005 instance is not correctly configured; memory, address windowing extensions (AWE), drivers, and so on are configured incorrectly. The box running SQL Server 2000 has been tweaked, the person who did it left and did not document it, so it was never done on the SQL Server 2005 instance.
- The hardware is at fault. Often, new hardware is purchased because it looks good on paper, but doesn’t hold up in real life.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION -- Get The lock if available UPDATE ProcessingInstances SET LockHolder = @MessageId WHERE ( InstanceId = @InstanceId ) AND ( LockHolder IS NULL ) COMMIT TRANSACTION
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.