• Article

SQL Q&ADatabase Size, Mirroring, Marked Transactions, and More

Edited by Nancy Michell

Moving a Cluster

Q I need to move a SQL Server 2000 cluster to new IP addresses for both the physical servers underneath, as well as the Windows® cluster and SQL Server™ cluster. Will I need to rebuild the entire solution?

A No, all you need to do is run the SQL Server setup and modify the IP addresses. Here is a Knowledge Base article that explains how.

Database Size

Q I have SQL Server 2000 SP4 in production with a mission-critical database of almost 10GB, a SIMPLE recovery model, a primary file of 9,850MB, and a transaction log file of 88MB. The database backup is also almost 10GB. Should I reduce the size of the database for better performance? If so, should I use DBCC SHRINKDATABASE or DBCC SHRINKFILE? (Unfortunately, I don’t have non-peak hours available for this maintenance.)

A DBCC SHRINKDATABASE or DBCC SHRINKFILE will only help if the database experiences many deletions and updates that result in a smaller volume of data. But the real question is why do you care? Disk space of 10GB is worth maybe $20 today. Wouldn’t it make more sense to add another 100GB or so of disk space and let it run? Performance will deteriorate as the space gets more fragmented, but if you can’t do any maintenance you have little choice. Sometimes people use a second copy of the database in cases like this. They keep a backup copy up-to-date with replication, do maintenance of the backup copy, and then switch the applications to the backup. This obviously requires some code changes and moving off the SIMPLE recovery model (to either FULL or BULK_LOGGED) but it may be a good long-term solution.

Upgrading and Performance

Q I need to move two applications from SQL Server 2000 to SQL Server 2005. How can I avoid any performance hits during the upgrade?

A Generally, the following pitfalls can cause performance degradation when upgrading, so try to avoid them:

  1. You haven’t rebuilt the statistics after upgrading to SQL Server 2005.
  2. 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/).
  3. 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.
  4. The hardware is at fault. Often, new hardware is purchased because it looks good on paper, but doesn’t hold up in real life.

Before taking a SQL Server instance into production, a performance baseline should be established to confirm that the performance you expect will be realized. This can then rule out the infrastructure as a possible problem. You need to look at Perfmon counters such as Disk, I/O, and memory, and compare them between instances.

Database Mirroring

Q I am using database mirroring and want to enable the READ_COMMITTED_SNAPSHOT database option. When I try to enable it after the mirroring has been set up, I get an exception stating that the db is in a mirroring session, and the command cannot be run.

A This happens because setting the READ_COMMITTED_SNAPSHOT option requires a database restart in order to take effect. Therefore, you need to break the mirroring session, set the option, and restart the database. After those steps are complete, you can reestablish mirroring. The mirror database will pick up the option after the session is established and will use it if failover happens.

Q I’m trying to set up asynchronous mirroring and I’m getting a 1418 error. Running Netstat -ano shows that SQL Server is listening on the correct port on all servers. The error appears when I try to start mirroring. What’s going on?

A One possibility is that your firewall might be blocking the communication; this is a relatively common issue that you should investigate. Check out the following whitepapers for more help: Troubleshooting Database Mirroring Setup at and MSSQLSERVER_1418.

Just note that this issue is not specific to asynchronous mirroring. In fact, it can happen with synchronous mirroring as well.

The error you’re getting ("The server network address "%.*ls" cannot be reached or does not exist. Check the network address name and reissue the command") is correct. Often it’s not that the remote partner doesn’t exist, it’s that the remote partner simply can’t be reached.

This could happen if the remote partner is down, does not listen to the port, or even if the endpoint is down (this might happen if the partners can’t negotiate the same encryption mechanism or have some other authentication issues). It can also happen if the partner is blocked, which can be the result of the firewall on the partner initiating the command.

Some other problems you might want to rule out include data source name (DSN) and name resolution issues. The generic recommendation is to use fully qualified domain names. So while your firewall might frequently be the problem, just remember that there are many potential causes.

Q I want to use SQL Server 2005 mirroring; however, I have heard that mirroring isn’t recommended when multiple applications connect to multiple databases on the same SQL Server instance. Is this correct?

A The answer actually depends on whether each application has its own database or the applications use cross-database or Distributed Transaction Coordinator (DTC) transactions. If cross-database transactions are used, then mirroring may introduce logical inconsistencies in which transactions are not committed as expected. A good explanation of what happens under these circumstances can be found here.

If there are multiple databases used by multiple applications and each app has its own database, then mirroring will not cause these problems.

Marked Transactions

Q What exactly are Marked Transactions, and are they something that can be used if one database is SQL Server and one is Oracle?

A Marking a transaction is something the DBA does periodically to put a mark at the same place in all the logs. This action, combined with the ability to restore a table to a transaction mark, allows you to restore all the databases to the same point. Doing this can be very painful because you will have to restore all the databases involved and data will be lost in all databases, so you should avoid this by making sure that you don’t lose your log files. Marked Transactions are SQL Server-specific, so if your distributed transactions include databases other than SQL Server, they can’t participate in transaction marking. In general, very few distributed database systems use transaction marks. They just treat the loss of one of the transaction logs as a catastrophic event that will require a lot of manual work to recover from.

Access-To-T-SQL Conversion Tool

Q Is there an automated Access™-SQL to T-SQL conversion tool available for stored procedures?

A Try the SQL Server Migration Assistant (SSMA) for Access, which you can download. There you will also find the SQL Server Migration Assistant for Oracle, SQL Server Migration Assistant for Sybase, and Migrating Informix Databases to Microsoft® SQL Server 2000.

For upsizing from Access to SQL Server, you could use the Office Upsizing Wizard, but the SSMA for Access, shown in Figure 1, has more features, including conversion assessment reports and network scanning. SSMA for Access also fixes many issues currently not handled correctly by the Office Upsizing Wizard.

Figure 1 SQL Server Migration Assistant for Access

Figure 1** SQL Server Migration Assistant for Access **(Click the image for a larger view)

Non-Clustered Indexes

Q I have a table in which the primary key is made of two GUIDs (say GUID1 and GUID2). I already had my unique clustered index on GUID1 and GUID2. Now for performance reasons, I want to create the second non-clustered index on GUID2 and GUID1. Will there be any performance overhead if I declare the index as unique?

A There is no performance penalty to declaring the non-clustered index as unique. In fact, doing so may result in fewer levels in the index tree.

When a non-clustered index is declared as non-unique, the row locator is appended to the key portion of the index entries in the root and non-leaf pages. This is done to facilitate deletion and update of index entries, which avoids scanning a synonym chain when there are duplicates for a key—the specific entry to be changed can be located by a system seek operation on the key plus locator. The result is that those entries are longer (considerably so with the cluster key you have chosen). As a result, the root and non-leaf pages will naturally fill up faster. If the index is unique, you should declare it as such.

The point is, regardless of whether the table has a clustered index, any non-clustered index on the table that is not declared as unique will have the locator appended to the index entries in the root and other non-leaf pages.

Also, if you use an int identifier column as a surrogate key and then add two unique indexes (GUID1, GUID2) and (GUID2, GUID1), this will likely improve performance since indexes will have a 4-byte clustering key instead of a 32-byte clustering key.

Update Locks

Q I have a stored proc with the following structure:

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

This stored proc allows only one caller to take the lock and will make any other callers wait. Can I achieve the same behavior by reducing the isolation level?

It seems like READ COMMITTED is the correct isolation level because this transaction has only one query, and if some other transaction is in the process of updating the same record, this transaction will wait on the other transaction. Is this correct?

A It’s not the serializable setting in your example that makes other callers wait—it’s the update itself. Yes, you can have isolation set at READ COMMITTED and the update will naturally take an update lock on whatever index it uses. This will cause problems for other processes executing the same statement, so they will block until the update is done. (You also don’t need the explicit transaction here if the update is the only statement; each statement is implicitly in its own transaction if it’s not in an explicit one.)

However, if there are no rows matching the WHERE clause in the UPDATE statement, then using the serializable isolation level would prevent such rows from being inserted or modified in other transactions. If you were to run the UPDATE in READ COMMITTED isolation, this would not happen and other transactions would be able to introduce rows that will qualify for the update. If the stored procedure code is like the code you showed, it may not make sense to take any of these actions (serializable or transaction); just perform the update.

Thanks to the following Microsoft IT Pros for their technical expertise: Gaurav Aggarwal, Anthony Bloesch, Todd Briley, Shaun Cox, Roberto Di Pietro, Michael Epprecht, Kevin Farlee, Umachandar Jayachandran, Chuck Ladd, Kaloian Manassiev, Luciano Moreira, Ward Pond, Mark Prazak, Arunachalam Thirupathi, Roger Wolter, Clement Yip, and Frankie Yuen.

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.