Knowing about ‘Forwarded Records’ can help diagnose hard to find performance issues.

Imagine a customer using an ISV application that stores certain product information in a varchar(200) column in a SQL Server database table. When the system was first being used nobody ever entered a product description with more than 10 characters. However, over time new products were added to the portfolio which required the introduction of a prefix…

1

Resolving Login Errors with Duplicated Databases

Many ISVs and ISV customers often have a need to duplicate their database server in entirety onto another computer system.  Such duplicated copies are required often required for development and test purposes and need to be identical to the original database server in all respects.   Copying the database itself is easy and can be…

1

SQL Server 2005 UNICODE considerations.

ISV applications today often require international support. Migrating an existing non-Unicode database to Unicode is a commonly discussed topic in the ISV application space. This BLOG entry discusses the general considerations and approaches to migrate a database to Unicode with SQL Server.   SQL Server Unicode Support   SQL Server Unicode data types support UCS-2…

5

Three significant Cursor changes in SQL Server 2005

Many ISV applications use ODBC API Server Cursors. One source of developer confusion when coding and debugging API Server Cursors against previous versions of SQL Server (6.5, 7.0, & 2000) was Implicit Cursor Conversions, also known as cursor degradation.   These cursor conversions could change the cursor type requested by the application to another cursor…

2

The Perils of Multi-valued logic

Perils of NULL usage: I know there is a religious war about the use of NULLs and I don’t want to go down that path.  But about 10 years ago I worked for another one of Bill Gate’s companies and I had to prepare a sales report for him from our database.  To cut to…

0

SQL 2000 SP4 behavior change when using API cursors to be aware of.

A few customers and ISVs who’ve applied SQL Server 2000 Service Pack 4 (SP4) have observed degradation in performance.  While there could be many causes that may result in performance degradation, there is one change introduced in SP4 which has been particularly common in resulting in such issues.  Below is an explanation of this problem…

0

SQL Optimizations – Manual Update statistics on small tables may provide a big impact.

Problem: When I analyzed a performance problem in an ISV data warehouse solution running on SQL Server 2000 I figured out that the query plan of a star join (join between a huge fact table and a bunch of small dimension tables) didn’t look right. Further investigation showed that certain optimizer estimates didn’t correspond to…

7

SQL Server 2005 Service Pack 1 Released

SQL Server 2005 SP1 is now generally available for download via the web at: http://www.microsoft.com/downloads/details.aspx?FamilyID=cb6c71ea-d649-47ff-9176-e7cac58fd4bc&DisplayLang=en. Additionally, a number of Knowledge Base articles could be useful: List of all new features and improvements by category: http://support.microsoft.com/kb/916940/en-us List of all changes by bug number: http://support.microsoft.com/kb/913090/en-us.  

0

How to Block fetch over a Default Result Set

Problem: When I was design/performance reviewing an ISV ODBC application, I turned on SQL Server profiler and noticed that the application was using server cursors. This application simply sent a select query to the server and read all the results into its local cache. So I asked the application developer why he was asking for…

3