SQL Q&ACPU Bottlenecks, Restoring and Moving Databases, and More

Edited by Nancy Michell

Q My SQL Server™ CPU usage started spiking excessively all of a sudden—but nothing has changed. No new users were added, no hardware went down, no new tables were created. So what's going on?

Tip: Access during Index Creation

There are times when you want to create indexes on large tables (which can take a huge amount of time), but you want to have the data available during this process. How can you achieve both objectives?

Whenever a clustered index is created, dropped, or rebuilt, SQL Server puts a Schema modification (SCH-M) lock on the table, which prevents all user access to the underlying data for the duration of the operation. This is the case when you create a clustered index on a table. In contrast, when you create a non-clustered index on a column, SQL Server puts a Shared (S) lock on the table and this, while also preventing data updates to the underlying table, at least allows you to perform SELECT statements so you can read the data.

If read availability on the table is important during the creation of the clustered index, you can create an index on a table and make it an online operation. Here's the command:

CREATE UNIQUE CLUSTERED INDEX CLUST_IDX_SQLTIPS 
ON SQLTips (tip) with (ONLINE=ON) Go;

When using online index operations on tables, SQL Server will still put an SCH-M lock for a clustered index or Shared (S) lock for a non-clustered index on the underlying table, but only for a very short period of time—during the start and end phase of the index operation. Therefore, this option provides better access for querying and updating of the underlying table during the index creation process. Just note that online index creation/operations are available with SQL Server 2005 Enterprise edition only.

A A CPU bottleneck that happens suddenly and unexpectedly without any changes or additional load on the server may have several reasons at its core, but some of the common ones include a non-optimal query plan, a poor SQL Server configuration, improper application/database design, and insufficient hardware resources.

The first thing to do in a situation like this is to identify whether your server is CPU-bound and if it is, identify the statements that are the largest consumers of CPU on your local SQL Server system. You can use Performance Monitor to determine if the server is CPU-bound by looking at the PROCESSOR:% PROCESSOR TIME counter. If you find the value for time used per CPU to be equal to or greater than 75 percent, you have a CPU bottleneck.

You should also monitor SQL Server schedulers by querying the system Dynamic Management View (DMV) called SYS.DM_OS_SCHEDULERS to see the value of runnable tasks. A nonzero value indicates that tasks have to wait for their time slice to run; high values for this counter are also a symptom of a CPU bottleneck.

You can use the following query to list all the schedulers and look at the number of runnable tasks:

SELECT
 Scheduler_ID,
 Current_Tasks_Count,
 Runnable_Tasks_Count
FROM
 SYS.DM_OS_SCHEDULERS
WHERE
 Scheduler_ID < 255

To get the top 50 CPU-intensive SQL statements, use the query in Figure 1.

Figure 1 Top 50 CPU hogs

SELECT TOP 50 (a.total_worker_time/a.execution_count) AS [Avg_CPU_Time],
 Convert(Varchar,Last_Execution_Time) AS 'Last_execution_Time',
 Total_Physical_Reads,
 SUBSTRING(b.text,a.statement_start_offset/2,
 (case when a.statement_end_offset = -1 then len(convert(nvarchar(max), b.text)) * 2
 else
 a.statement_end_offset end - a.statement_start_offset)/2) AS Query_Text,
 dbname=Upper(db_name(b.dbid)),
 b.objectid AS 'Object_ID'
 FROM sys.dm_exec_query_stats a
 CROSS APPLY
 sys.dm_exec_sql_text(a.sql_handle) AS b
 ORDER BY
 [Avg_CPU_Time] DESC

Q I had to recover a SQL Server 2005 database from tape. After the restore, most of the permissions users had to the database were lost. Can you tell me what I did wrong during the restore? The data was fine, but the permissions were a mess.

A Most likely you didn't restore the master database to the same point in time and the login IDs in the user database don't match the current master database. You need to sync them up. The sidebar "Where to Get Help When Moving a Database" lists the most useful resources for getting to the bottom of the problems you experience when restoring or moving a database.

Q I developed an application in which I use a stored procedure that shreds XML data into relational tables using OpenXML in SQL Server 2005 SP1. The XML document is at most 5KB (the average is 2.5KB). The stored procedure is called many times in parallel (up to 50 times).

I'm facing serious lock contention problems and I think they might be due to OpenXML. What do you think?

A While OpenXML may be faster for shredding or converting data on a single thread than the nodes method, the nodes method normally scales better, especially if used in parallel. However, if you're going to use OpenXML at all, you should use the following guidelines to improve your overall OpenXML performance.

Instead of calling OpenXML five times with the same row pattern (as you noted you were doing in your solution), you should extract all the data with the same row pattern into a temp table and then perform your selects from the temp table. Try to release the memory with sp_xml_removedocument as early as possible. Also, it is a good idea to avoid using wildcards such as * and //, whenever possible; providing the explicit path will result in a better performing query.

Q DBCC SHRINKFILE is running really slowly on my server. Would I get better performance on a multi-processor machine? What can I do to improve the situation?

A DBCC SHRINKFILE is a single-threaded operation, which means it does not take advantage of multiple CPUs. It moves pages from the back end of the file to the front end of the file, one page at a time. And shrink, as it is often called, does not perform defragmentation; as a matter of fact, in a lot of situations, shrink increases logical fragmentation.

Some ways to improve shrink performance include moving pages in clustered indexes. If you have heaps and those heaps have many non-clustered indexes, the speed would be noticeably slow (compared to a clustered index situation).

Note also that moving pages for large object blob (LOB) data is slow, because in-row data has to be read to find the root of LOB data.

If most content of an index/table resides at the end of the file, you can rebuild the indexes to move them to the front end of the file. Rebuilding the indexes takes advantage of multiple CPUs and could use less log space in bulk_logged mode. Then when you run shrink, it will run faster.

For more information on shrinking operations, start with the entries at blogs.msdn.com/sqlserverstorageengine/archive/2007/03/29/how-does-your-schema-affect-your-shrink-run-time.aspxand blogs.msdn.com/sql-serverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx.

Where to Get Help When Moving a Database

Tip: Changing Ports

Anyone familiar with SQL Server knows that the default port number SQL Server listens on is 1433. While named instances of SQL Server are configured to use Dynamic Ports (meaning they can select any available port whenever a SQL Server instance starts), the default SQL Server instance always listens on port 1433. So if your server is listening on the default port and it's not tightly secured, it is at risk. But you can thwart attacks by changing the default port. Here's how.

Open SQL Server Configuration Manager and expand SQL Server 2005 Network Configuration, then Protocols. Next, double-click TCP/IP. The list of TCP/IP properties and their functions is shown in the chart below, so set accordingly.

Note that the SQL Server Database Engine can listen on multiple ports on the same IP address, so list the ports you want to use separated by a comma in the format 1433,1500,1501. If you want to configure a single IP address to listen on multiple ports, you must also set the Listen All parameter to no on the Protocols tab of the TCP/IP Properties dialog box.

Now, right-click each address and click Properties to identify the IP address you want to configure. If the TCP Dynamic Ports dialog box contains 0, indicating that the database engine is listening on dynamic ports, delete the 0. Within the IP Properties area, in the TCP Port box, type the port number you want this IP address to listen on, and then click OK. In the console pane, click SQL Server 2005 Services, and in the details pane, right-click SQL Server (<instance name>) and then click restart to stop and restart SQL Server.

After you have configured SQL Server to listen on a specific port, there are three ways for a client to connect to the port. You can run the SQL Server Browser service on the server to connect to the Database Engine instance by name; you can create an alias on the client, specifying the port number; or you can program the client to connect using a custom connection string.

Property Description
Active Indicates SQL Server is listening on the designated port. Not available for IPAll.
Enabled Enable or disable this connection. Not available for IPAll.
IP Address View or change the IP address used by this connection. Lists the IP address used by the computer, and the IP loopback address, 127.0.0.1. Not available for IPAll.
TCP Dynamic Ports Blank, if dynamic ports are not enabled. To use dynamic ports, set to 0.
TCP Port View or change the port on which SQL Server listens. By default, the default instance listens on port 1433. This field is limited to 2047 characters.

Thanks to the following Microsoft IT pros for answering this month's questions: Chad Boyd, Cindy Gross, John Hadden, Saleem Hakani, Stephen Jiang, Mahesh Nayak, Paul Randal, and Wayne Yu.

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