Number of Files for Tempdb

For those who have worked in SQL Server for some time, know that one of the best practices that is recommended for tempdb is to have multiple data files – 1 data file per CPU as seen by SQL Server since it creates a logical scheduler for each CPU that it can access.  You can read more on this post by Robert Dorr @ the CSS SQL Server Engineers blog post.

CPU usage reaching 100% with SQL Server 2005-SP3

Ran into an issue at a client site last week where the CPU on the DB Server was reaching closer to 100%.  The same application had been working fine for the past several months.  The only difference was the application of SP3 for SQL Server 2005.  So, searched the KBs for any known issue and there happens to be a bug for which a cumulative update is available.  You can read more in this KB article.

sys.dm_db_persisted_sku_features

SQL Server 2008 has a new DMV which you might need if you run into an issue similar to what we ran into last week.  At one of our client sites, they were using CDC and data compression features in SQL Server 2008.  When they tried to restore the database in their development environment, the restore operation failed.  The reason is that for certain features in SQL Server 2008 which are only supported in the Enterprise Edition, if you are using those features in your environment and you try to restore a backup of such a database on an instance that does not support those features, you will not be able to do so.  This is written in BOL as well:

Some features of the SQL Server Database Engine change the way that Database Engine stores information in the database files. These features are restricted to specific editions of SQL Server. A database that contains these features cannot be moved to an edition of SQL Server that does not support them Use the sys.dm_db_persisted_sku_features dynamic management view to list all edition-specific features that are enabled in the current database.

There are four features that fall into this category: CDC (Change Data Capture), Data Compression, TDE (Transparent Data Encryption) and partitioning.  It actually was the same issue in SQL Server 2005 as well when you would have used the partitioning feature.  In such scenarios, you can use the above mentioned DMV to see whether you are using any of those four features.

Do not use NOLOCK hint without knowing what it does

We have seen this in a majority of our consulting engagements related to SQL Server.  The developers are very trigger happy when it comes to blocking locks issue and their first answer to resolving it is to use the NOLOCK locking hint without even realizing the issues that they are going to create or the issues that they need to be aware of when using this locking hint.  A lot of it has to do with how things used to work prior to SQL Server 2005.  Writers (DML statements) used to block readers (Selects or implicit selects) regardless of the isolation level that you chose to go with.  So, developers used to working on Oracle were taken aback by this because their entire application was written with multiversion concurrency in mind.  So, instead of supporting SQL Server the right way, in comes the NOLOCK hint.

So, what does NOLOCK locking hint do really?  It is the same as the READ UNCOMMITTED transaction isolation level with the difference that this does it at the statement level rather than the session level.  If you are on version SQL Server 2005 or 2008, look into the snapshot isolation levels – you can read more on it here.  Using NOLOCK hint is fraught with dangers of ending up corrupting the data in the system (when you read uncommitted data and make decisions based on it and the other session rolls back the transaction) or other consistency issues like missing previously committed rows.

There are a lot of very good posts on why not to use the NOLOCK hint blindly (Yes – when we say blindly, it implies that there are some scenarios where it is fine to use it – when accuracy is not of prime importance and you are looking for just some high level analysis of the data – though beginning SQL Server 2005, there really is no need to use it).  Here are some of the posts for your reference and the next time someone tells you to use NOLOCK, point them to these posts and ask them to read them:

SQLCAT post over here.

MVP Tony Rogerson’s post over here.

Changing the database to be Read/Write

A few days back, we had written a post on how to go about changing a database to be in a read only mode and the scenarios when you would do that.  You can read more on that here.  A reader asked how to change it back to be a read/write DB.  It’s pretty simple and you can use the same set of commands as we had shown in that post – just change the READ_ONLY to be READ_WRITE.  Here is an example:

USE MASTER
GO
/*Mark it as Singe User*/
ALTER DATABASE DECIPHERTEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
/*Mark the database as Read Write*/
ALTER DATABASE DECIPHERTEST SET READ_WRITE
GO
/*Mark it back to Multi User now*/
ALTER DATABASE DECIPHERTEST SET MULTI_USER
GO

Guide to SQL Server Consolidation

One of the most common questions in our consulting engagements surround consolidation of the SQL Server environments.  There are a couple of ways to go about it and it varies client to client depending upon the type of applications that the SQL Server is being used for, versions & their OS’s, existing and proposed hardware infrastructure, the proper IT team in place to support it etc.

Here is an excellent whitepaper from Microsoft MVP’s that you can download in order to learn more about the different consolidation techniques – here.

Making a database Read Only

We were in the process of migrating the data from a legacy system to a newly architected system for a client of ours.  For the duration of the migration, they needed the data set to be available for reporting purposes but of course no data should be created in that legacy system during the time of the migration.  So, one of the obvious choices were to make the database as a read only database.  In SQL Server, there is an option in the “ALTER DATABASE” command to achieve that.  In order to do so, one first has to mark the database in a single user mode first, then make the change to mark the database as a read only database and then change it back to the multi-user mode.

Here is a sample script:

USE MASTER
GO
/*Mark it as Singe User*/
ALTER DATABASE DECIPHERTEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
/*Mark the database as Read Only*/
ALTER DATABASE DECIPHERTEST SET READ_ONLY
GO
/*Mark it back to Multi User now*/
ALTER DATABASE DECIPHERTEST SET MULTI_USER
GO

References:

• BOL entry for the ALTER DATABASE command – here.

Why does the IP address and subnet mask matter?

We had an interesting issue that relates to networking and SQL heartbeat with one of our clients that we would like to share with our readers.

One of our clients has their servers distributed across 3 data centers in US. Let us call them A, B and C. They have a VPN tunnel connecting all their sites together using Cisco ASA firewalls. Their IT staff access the entire infrastructure through one data center (A) which has remote access VPN enabled in the Cisco ASA. They recently reported an issue of not being able to access the servers in SQL cluster across remote access VPN located in data center (B). However, those servers are accessible from data center A, C and within B itself and others servers in data center B were accessible through remote access VPN.

As their remote VPN connection terminates at Cisco ASA at data center (A), various teams were involved to find out the cause. The following were checked to identify the cause of this issue:

1. It was ensured that the remote access VPN subnet (10.1.1.x) is added to the crypto-map on the site to site VPN configuration.
2. We also made sure all the servers are connected to the same switch and residing on same VLAN.
3. There was no specific access list (ACL) or firewall or IPS or F5 configuration that was blocking traffic to the database servers from remote access VPN subnet (10.1.1.x).
4. We ensured all the servers have the same IP default gateway configured.

During packet tracing, it is found that the traffic reaches to the ASA at data center A and also reaches data center B. It is found that the traffic was not going back from the servers to the remote access VPN subnet. We realized that there would be something wrong on the SQL cluster servers and started looking in depth on its network configuration. We identified that SQL server’s heart beat NIC was configured with the IP address of 10.0.0.x with a subnet mask of 255.0.0.0 (/8) allowing to have 16777214 hosts where only 2 IP addresses are needed for heart beat. So, all the incoming traffic from remote access VPN was forwarded to the heart beat NIC on the SQL servers and not going back to the remote access VPN ASA.

Having a subnet mask of 255.255.255.252 on SQL servers heart beat network would have allowed it to have only two IP addresses that are needed for heartbeat on the SQL cluster. As it is a production SQL network, we did not want to change the heart beat network’s IP address or subnet mask. As an alternative workaround, we used persistent route in Windows 2003 to configure the remote access VPN traffic to reach the correct NIC and gateway. A helpful article on windows 2003 routing can be found here. Once we added the persistent route, the remote access VPN users were able to access the SQL servers.

Lesson’s learned:

1. Make sure you aware of all the network addresses and subnets involved in all the locations.
2. Assign a subnet mask for the required number of host addresses.

Installing SP3 on SQL Server 2005 cluster

A good post by Sander Stad on SQLServerCentral on preparing SQL Server 2005 in a clustered environment for SP3 – here.  We are scheduled to do that in a couple of weeks for a client and were researching any known issues faced by other folks – this article would be very helpful when we start testing it in our lab next week.

Some more free training materials from MSFT

Windows 7 Introduction – here.

This is an older one but a very good one for those looking for an introduction to SSIS 2008 – here.

Windows 2008 Fundamentals – here.

Silverlight Fundamentals – here.

Windows Server 2008 R2, new features – here.