Microsoft SQLCAT ISV Program Management Team

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...

With 64-bit servers becoming more mainstream and SQL Server customers running more deployments on native 64-bit Windows there is a common misperception that running on 64-bit will always provide an increase in performance over 32-bit. Many customers are...

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...

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...

Upgrading to SQL Server 2005 has many benefits . As we look back at the past year and the deployment of SQL Server 2005 with our ISV partners, one important but under-advertised feature in SQL Server 2005 became increasingly visible; the ability to control...

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...

I have been asked by many customers and partners, especially those migrating from Oracle, this question: how to create an autonomous transaction in SQL Server? It turns out to be a tricky thing to do since SQL Server doesn't have built-in autonomous transaction...

SQL Server Optimizer is full of surprises… Optimizer uses many transformations to find the best possible path of execution. One of those happen in cases where you do a query that looks like the following; -- displays the duration, cpu, reads, writes...

When SQL Server has an optimal index that satisfies the search predicates of a query the optimizer performs an index SEEK operation as opposed to an index (or table) scan to retrieve the required rows; this is desirable. Based on this, one may be led...

Did you know that SQL Server 2008 is right around the corner, and that the SQL development team has just publicly released the latest Community Technology Preview (CTP 6) version? This latest beta version of SQL 2008 includes many exciting new features...

SQL Server 2008 has a new feature called Filestream, which allows you to save large binary files in the file system, instead of in the database. This is targeted directly at the scenario that many document management and web applications have today where...

You have probably heard many times, from different sources, that as a best practice; avoid using TSQL cursors. During a recent visit to a partner we ran into a common cursor case, which I wanted to use as an example to demonstrate why you should avoid...

In SQL Server 2008 Microsoft has introduced a number of new date and time data types. One of these is the datetimeoffset data type. This data type includes an offset from UTC time as well as the datetime value and ensures that the datetime can be retrieved...

Recently many customers of an ISV I work with, reported intermittent connectivity issues when running the ISV application on SQL Server. Some customers reported the issue to be SQL Server 2005 specific. Others stated that they are experiencing the same...

For many ISVs that run into issues at customer sites it is sometimes difficult to isolate underlying problems, especially on 24x7 production environments, where limitations apply to real time troubleshooting and live debugging. In situations like this...

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...

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...

Many applications have a functional requirement for the underlying database to have a case-sensitive sort-order, implying that all character data related operations are case-sensitive. With SQL Server, such databases are created with either the ‘binary...

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...

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...

Please have a look at four new Best Practices Articles SQL Server 2005 Predeployment I/O best practices http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx by SQLISVPM team member Mike Ruthruff SQL Server 2005 Deployment...

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...

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...

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...

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...