Incremental Servicing Model for SQL Server 2005

Posted on December 30, 2009 by Amit Banerjee

The Release Services team for SQL Server introduced a new servicing policy for SQL Server 2005 where in Cumulative Updates for the product are released every 2 months or 8 weeks cycle. Even though there is a KB Article and multiple blog posts surrounding the same, I have found the common question:

This fix is available in the list of available fixes in CU3 but I don’t see the bug/KB# in the Service Pack 3 KB Article.

Read on to find out why!! 🙂

So, if you are experiencing an issue mentioned in a KB Article, then you will find that the Bug# or the KB# for that issue mentioned in the list of available fixes in the next available Cumulative Update for SQL Server or Service Pack which ever is earlier. So, if you were looking for a bug# that you found mentioned in the KB Article for a Cumulative Update, then that KB Article/Bug# will not be repeated in the next available Cumulative Update or Service Pack. The reason for this is that all fixes that are released for Cumulative Update include all the fixes released in the prior Cumulative Updates.

For example, Service Pack 3 contains all the fixes that were released between time period of Service Pack 3 and Service Pack 1.

So, this is the reason why the Service Pack/Cumulative Update Pack sizes are getting progressively larger. So, another GOTCHA for all those folks who have been cribbing about the update package sizes. 🙂

CHECKDB message in the SQL Errorlog

Posted on December 30, 2009 by Amit Banerjee

Many a time, we have seen the following messages in the SQL Server after a SQL Service startup or after a database has been restored or a database was brought ONLINE:

2009-10-09 01:13:42.110 spid53 CHECKDB for database ‘msdb2’ finished without errors on 2008-11-07 19:59:09.643 (local time). This is an informational message only; no user action is required.

What does this message signify? There has been lot of confusion regarding this message. It doesn’t mean that SQL Server performed a CHECKDB against this database and reported the outcome. This is just an informational message to let you know when was the last time a CHECKDB was executed against your database. This information (last checkdb runtime) is stored in your database header which is picked up and reported in the SQL Errorlogs.

If you notice the highlighted information in the message above, you will see that the time reported in the message doesn’t correspond to the Errorlog entry time.

Decoding sp_cursor* call parameters

Posted on December 30, 2009 by Amit Banerjee

I have seen multiple occasions while troubleshooting performance cases, that people get baffled with the sp_cursor* call parameters specified

You can capture the sp_cursor* calls with the parameters if you capture SP:Starting and SP:Completed or RPC:Starting and RPC:Completed events in a SQL Profiler Trace.

The different stored procedures which are related to cursors in SQL Server are:

sp_cursor: Update a cursor
sp_cursorclose: Close a cursor
sp_cursorexecute: Open a prepared cursor
sp_cursorfetch: Fetch rows
sp_cursoropen: Open a cursor
sp_cursoroption: Set cursor options
sp_cursorprepare: Prepare a cursor statement
sp_cursorprepexec: Prepare a cursor statement and open
sp_cursorunprepare: Free a prepared cursor statement
sp_execute: Execute a prepared statement
sp_prepare: Prepare an SQL statement
sp_prepexec: Prepare and execute an SQL statement
sp_unprepare: Free a prepared statement

Scroll Options – Is the cursor scroll type. scroll_options is of integer data type with a default of 1 (keyset-driven), and can be a combination of these values ("exactly one of the first 5 must be specified").

Value Description
0x0001 Keyset-driven cursor.
0x0002 Dynamic cursor.
0x0004 Forward-only cursor.

0x0008 Static cursor.
0x0010 Fast forward-only cursor.
0x1000 Parameterized query.
0x2000 Auto fetch.
0x4000 Auto close.
0x8000 Check acceptable types.
0x10000 Keyset-driven acceptable.
0x20000 Dynamic acceptable.
0x40000 Forward-only acceptable.
0x80000 Static acceptable.
0x100000 Fast forward-only acceptable.

Concurrency Options – Is the cursor concurrency. concurrency_options is int, with a default of 4 (optimistic) and can be a combination of these values (exactly one of the first 4 must be specified). On return, @ccopt contains the type of cursor actually created, which may not match what was requested.

Value Description
0x0001 Read-only.
0x0002 Scroll locks.
0x0004 Optimistic. Checks timestamps and, when not available, values.
0x0008 Optimistic. Checks values (non-text, non-image).
0x2000 Open on any SQL.
0x4000 Update keyset in place.
0x10000 Read-only acceptable.
0x20000 Locks acceptable.
0x40000 Optimistic acceptable.

How to identify the correct values for scrollopt:

For Example: If the cursor call for sp_prepexec is like this:

declare @p1 int
set @p1=NULL
declare @p2 int
set @p2=0
declare @p5 int
set @p5=28688
declare @p6 int
set @p6=8193
declare @p7 int
set @p7=1

@P1 is the STATEMENT HANDLE
@P2 is the CURSOR HANDLE
@P3 is the PARAMETER DEFINITION LIST
@P4 is the STATEMENT
@P5 is the SCROLL OPTIONS
@P6 is the CONCURRENCY OPTIONS
@P7 is the ROW COUNT i.e. the variable declared to receive the number of affected rows

So if @P5 = 28688

Then, Decimal 28688 = Hex 7010 = 0x4000+0x2000+0x1000+0x0010 which translates to:

Fast Forward Only Cursor
Parameterized Query
Auto Fetch
Auto Close

Similarly, for ccopt values, if @P6 = 8193, then Decimal 8193 = Hex 2001 = 0x2000 + 0x0001 which translates to:

Read-Only
Open on any SQL

Now, the reason why I was stressing on the Completed events in the Profiler Trace was to help in identifying the Cursor Statement Handle. If the above example, the sp_prepexec that was shown was Starting event due to which @P1 parameter value was NULL. When you capture the RPC: Completed event, you will find that the Text Data column in the Profiler for the cursor call has @P1 populated. This will give you the statement handle. You can use this statement handle to track down any other statements that are executing this statement.

This can be quite helpful when you are chasing down the Top CPU consumer or Long Running Cursor statements as the only execution calls that you would see would be sp_cursorfetch or sp_execute along with the cursor handle. Using the Handle Value, you can track back in the profiler and find out what was the query that the cursor was prepared for.

Reference: http://jtds.sourceforge.net/apiCursors.html

PAGE_VERIFY option in SQL Server

Posted on December 30, 2009 by Amit Banerjee

A new feature is available in SQL Server 2005 and higher versions to enabled Page Checksum for a database page to help detect errors in the IO path.

The SQL Server storage engine has already blogged about this:

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/29/Enabling-CHECKSUM-in-SQL2005.aspx

The reason I’m posting an additional article is the misconceptions related enabling Checksum for a SQL Server database. You can find out what is the PAGE VERIFY option for your database using the system catalog sys.databases with the help of the query below:

select database_id, page_verify_option, page_verify_option_desc

from sys.databases

The PAGE VERIFY option has 3 possible values:

0 = NONE

1 = TORN_PAGE_DETECTION

2 = CHECKSUM

Let’s take an example here:

You created a database without CHECKSUM enabled. So, all pages that were written to disk now do not have the Checksum property was enabled on it. After you enable Checksum on the database all the objects that were created prior to enabling Checksum on the database will not have the Checksum written into the Page header. When these pages are brought from the disk into the SQL Server Buffer Pool (RAM) and the dirty bit of the page is set (when any DML operation modifies the page), only then would the Checksum get calculated and written into the page header before it gets written back to the physical media (disk). This happens during the Checkpoint operation. So, if you brought a page into the SQL Server Buffer Pool and didn’t dirty the page (i.e. only for a SELECT operation), then the checksum will not be written onto the page header.

One of the recommendations would be to dirty all pages using operations like DBCC DBREINDEX so that all pages that didn’t have Checksums written into the header now have Checksums written onto it after enabling Checksum on a database.

Why is this by-design?

The reason for this is we cannot estimate the size of the database and the number of database pages that would need checksums calculated and written onto the disk. This would involve a huge performance degradation which is avoided by the current design.

From SQL Server Books Online:

PAGE_VERIFY

When CHECKSUM is specified, the Database Engine calculates a checksum over the contents of the whole page and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value stored in the page header.

When TORN_PAGE_DETECTION is specified, a specific bit for each 512-byte sector in the 8-kilobyte (KB) database page is saved and stored in the database page header when the page is written to disk. When the page is read from disk, the torn bits stored in the page header are compared to the actual page sector information.

When NONE is specified, database page writes will not generate a CHECKSUM or TORN_PAGE_DETECTION value. SQL Server will not verify a checksum or torn page during a read even if a CHECKSUM or TORN_PAGE_DETECTION value is present in the page header.

Another interesting post by Bob Dorr (Principal Escalation Engineer – SQL Server):

http://blogs.msdn.com/psssql/archive/2008/05/22/how-it-works-sql-server-2005-possible-data-recovery-when-checksum-failure-reported.aspx

Federated Databases

Posted on December 30, 2009 by Amit Banerjee

This is something that not many people are aware of. Hence, I thought about putting up a brief overview of this side of SQL Server.

To achieve the high levels of performance required by the largest Web sites, a multitier system typically balances the processing load for each tier across multiple servers. SQL Server 2005 shares the database processing load across a group of servers by horizontally partitioning the data in a SQL Server database. These servers are managed independently, but cooperate to process the database requests from the applications; such a cooperative group of servers is called a federation.

A federated database tier can achieve very high levels of performance only if the application sends each SQL statement to the member server that has most of the data required by the statement. This is referred to as collocating the SQL statement with the data required by the statement. Collocating SQL statements with the required data is not a requirement unique to federated servers. It is also required in clustered systems.

Federated Server Tier

There is one instance of SQL Server on each member server.

Each member server has a member database. The data is spread through the member databases.

The tables from the original database are horizontally partitioned into member tables. There is one member table per member database, and distributed partitioned views are used to make it appear as if there was a full copy of the original table on each member server.

The application layer must be able to collocate SQL statements on the member server that contains most of the data referenced by the statement.

Backing Up and Restoring Federated Database Servers

In a federated-database-server tier that is built by using distributed partitioned views, the member servers form one logical unit. Therefore, you must coordinate the recovery of the member databases to make sure that they remain synchronized correctly.

SQL Server 2005 does not require that you coordinate backups across member servers. Backups can be independently taken from each database, without regard for the state of the other member databases. Because the backups do not have to be synchronized, there is no processing overhead for synchronization and no blockage of running tasks.

The most important aspect of recovering a set of member databases is the same as recovering any other database: Plan and test the recovery procedures before you put the databases into production. You must set up processes to restore all the databases to the same logical point in time. SQL Server includes features to support the recovery of all member databases to the same point in time.

Pros

1. Federated servers if implemented correctly are a great way to load balance a database server environment and is very similar to a database farm implementation.

2. This would greatly allow you to distribute the load on your servers based on any of the following criteria:

a. Geographic location

b. Traffic in terms of users

c. Traffic in terms of transactions

d. Database table size

3. Also, federated servers give you the option on partitioning data across servers with the help of distributed partitioned views

4. This gives you the option of horizontally partitioning the data across various servers which ultimately leads to greater throughput

5. It lets you control the traffic coming in and also helps in maintaining the load thresholds across the entire setup

6. Furthermore, if we have a middle tier in the entire setup, then design changes in the federated server environment will not affect the client side applications in any manner as they would be connecting to the middle tier and the middle tier will connect with the database server

Cons

1. One of the major drawbacks is disaster recovery. If one of the member server fails, there needs to be a failback plan in place which could cause minimum hindrance to the normal operations while implementation.

2. Also, if distributed partitioning is being implemented, then rules and constraints need to be strong enough to prevent any sort on inconsistencies from arising due to data modification

3. Also, if the performance of one of the member servers takes a hit, most of the distributed partitioned views would also take a hit in terms of operations done on them

4. The middle tier should be designed in such a robust manner that there is no ambiguity in resolving which server in the environment needs to targeted based on the query coming into the server

5. The backup/restore scenarios need to be designed and planned in such a way that all the member servers are all synchronized at all times

Useful Articles

The following whitepaper and TechNet articles should help a great deal here:

Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005 Solution

Scaling Out SQL Server with Data Dependent Routing

Designing Data Tier Components and Passing Data Through Tiers