Asynchronous Population of a Cursor May Result in Performance Degradation

SQL Server offers an option to asynchronously populate API created keyset and static server cursors via the sp_configure ‘cursor threshold’ parameter.  When a cursor is asynchronously populated, a secondary thread is spawned to populate the cursor asynchronously in the background, while the original thread returns control to the client.   The default value for ‘cursor threshold’…


Interesting issue with Filtered indexes.

Recently, an ISV I work with ran into an interesting problem with Filtered Indexes. The application does all SQL INSERT, DELETE, and UPDATE operations using individual stored procedures. To improve performance, they decided to create a Filtered Index (new in SQL 2008) to restrict the data such that it does not contain NULL values, in…


Why did the size of my indexes expand when I rebuilt my indexes?

Recently I worked with a partner who was seeing some interesting behavior.  Upon rebuilding their indexes they noticed that the total space used by all indexes increased significantly.   The table has no clustered index but does have a total of nine non-clustered indexes.  The sequence of events is as follows: ·      Step 1: Approximately 12 million rows…


SQL Server 2005 / 2008 table partitioning : Important things to consider when switching-out partitions.

An ISV recently found an anomoly in their implementation of a “drop table partition” function which could lead to unexpected partitions being switched out. Typically, to do this “drop partition logic” SQL Server partitions are manipulated using the following operations: ·      switch-out the partition which isn’t needed anymore (or being archived) into an empty target…


Avoid using JDK Date APIs to handle timezone sensitive date and time

JDK APIs for Class “java.util.Date” and “java.sql.Timestamp” (subclass of Date) including getHours(), getMinutes(), getSeconds(), getTimestamp() allow you to retrieve date/time related information. However, the JVM (Java Virtual Machine) won’t handle timezone sensitive data properly using these APIs. As matter of fact, these APIs were deprecated starting JDK 1.1 ( But I am still seeing ISV…


Zeroing in on blocking on seemingly unrelated tables

In one of our recent lab tests we were surprised to see blocking occur on a table that did not participate in the transaction being reported as the cause of the blocking.  From the sp_lock output we noticed an ‘X’ lock being held on the table, but we could guarantee that there was no insert,…


Using SQL Server 2008 Management Data Warehouse for database monitoring in my application

SQL Server 2008 introduced what we call the Management Data Warehouse. The Management Data Warehouse is a relational database that contains data that is collected from a server using the new SQL Server 2008 Data Collection mechanism. The Warehouse consists primarily of the following components: ·       An extensible data collector: ·       A database schema which is indirectly extensible…


OPTIMIZE FOR UNKNOWN – a little known SQL Server 2008 feature

Using parameterized queries is a well known SQL Server Best Practice. This technique ensures caching and reuse of existing query execution plans (instead of constantly compiling new plans), as well as avoiding SQL injection by mandating that input data be data type safe. See more about SQL Server parameterization Best Practices here: An application…


SQL Server 2008 : new binary – hex string conversion functionality can dramatically improve related query performance by orders of magnitude.

In previous SQL Server releases it wasn’t possible to convert binary data to string characters in hex format directly, because SQL Server did not have a built-in Transact-SQL command for converting binary data to a hexadecimal string. The Transact-SQL CONVERT command converted binary data to character data in a one byte to one character fashion….


Designing Composite Indexes

 When it comes to creating composite indexes there are two questions I get asked most often by the ISVs I work with as well as their customers. 1.      What is the optimal number of columns to include in a composite index? 2.      What should the order of those columns be? While in most cases the…