SQL Programmability & API Development Team Blog

All posts are AS IS, without any further guarantees or warranties.

Interesting issue with Filtered indexes.

Recently, an ISV I work with ran into an interesting problem with Filtered Indexes. The application...

Author: Peter Scharlock Date: 06/29/2009

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

Author: Peter Scharlock Date: 05/18/2009

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

Author: Peter Scharlock Date: 04/10/2009

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

Author: Peter Scharlock Date: 03/26/2009

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

Author: Peter Scharlock Date: 02/02/2009

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

Author: Peter Scharlock Date: 01/29/2009

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

Author: Peter Scharlock Date: 11/26/2008

In previous SQL Server releases it wasn’t possible to convert binary data to string characters in...

Author: Peter Scharlock Date: 10/31/2008

How to create an autonomous transaction in SQL Server 2008

I have been asked by many customers and partners, especially those migrating from Oracle, this...

Author: Peter Scharlock Date: 08/22/2008

UPDATE with OUTPUT clause – Triggers – and SQLMoreResults

NOTE: the code in this BLOG is TSQL instead of ODBC calls. Since ODBC can be hard to understand and...

Author: Peter Scharlock Date: 07/11/2008

Use SQL Server replay tools to reproduce and resolve customer issues

For many ISVs run that into issues at customer sites, it is sometimes difficult to isolate...

Author: Peter Scharlock Date: 06/26/2008

SQL Server Intermittent Connectivity Issue

Recently many customers of an ISV I work with, reported intermittent connectivity issues when...

Author: Peter Scharlock Date: 05/27/2008

Using time zone data in SQL Server 2008

In SQL Server 2008 Microsoft has introduced a number of new date and time data types. One of these...

Author: Peter Scharlock Date: 03/18/2008

Increase your SQL Server performance by replacing cursors with set operations

You have probably heard many times, from different sources, that as a best practice; avoid using...

Author: Peter Scharlock Date: 03/18/2008

Appending Data Using SQL 2008 Filestream

SQL Server 2008 has a new feature called Filestream, which allows you to save large binary files in...

Author: Peter Scharlock Date: 03/06/2008

Now is time to try SQL Server 2008

Did you know that SQL Server 2008 is just around the corner, and that the SQL development team has...

Author: Peter Scharlock Date: 02/25/2008

Detecting Overlapping Indexes in SQL Server 2005

When SQL Server has an optimal index that satisfies the search predicates of a query the optimizer...

Author: Peter Scharlock Date: 06/29/2007

New SQL Best Practice Articles now available

Please have a look at four new Best Practices Articles SQL Server 2005 Predeployment I/O best...

Author: Peter Scharlock Date: 06/05/2007

Will 64-bit increase the performance of my SQL Server application?

With 64-bit servers becoming more mainstream and SQL Server customers running more deployments on...

Author: Peter Scharlock Date: 04/30/2007

Upgrading to SQL Server 2005 and DEFAULT_SCHEMA setting.

Upgrading to SQL Server 2005 has many benefits . As we look back at the past year and the deployment...

Author: Peter Scharlock Date: 03/23/2007

why cannot i GRANT EXECUTE permission on assemblies anymore ?

Just a quick note that this is by design and no you don't need to use it. What are we talking about...

Author: RaviR Date: 02/22/2007

Best Practices for SQL Server 2005

Did you know there is a great place to get real-world best practices for SQL Server 2005:...

Author: Peter Scharlock Date: 02/08/2007

4.0 Useful Queries on DMV’s to understand Plan Cache Behavior

The following queries can be used to understand plan cache behavior. It should be noted that for...

Author: sangeethashekar Date: 01/23/2007

3.0 Changes in Caching Behavior between SQL Server 2000, SQL Server 2005 RTM and SQL Server 2005 SP2

3.1 Costing algorithm change between SQL Server 2000 and SQL Server 2005 RTM In SQL Server 2000 and...

Author: sangeethashekar Date: 01/22/2007

Plan cache related performance problems usually manifest in decrease in throughput (or increase in...

Author: sangeethashekar Date: 01/21/2007

If after upgrading from SQL 2000 to SQL 2005 RTM and/or SP1, you notice decreased OLTP database...

Author: sangeethashekar Date: 01/20/2007

12.0 Plan Cache Trace Events and Performance Counters

12.1 Trace Events 12.1.1 Performance Statistics Trace Event The performance statistics trace event...

Author: sangeethashekar Date: 01/19/2007

11.0 Temporary Tables, Table Variables and Recompiles

11.1 Temporary Tables versus Table Variables In order to determine if table variables or temporary...

Author: sangeethashekar Date: 01/18/2007

10.0 Plan Cache Flush

In SQL Server 2005, certain database maintenance operations or certain dbcc commands such as “dbcc...

Author: sangeethashekar Date: 01/17/2007

9.0 Memory Pressure Limits

The procedure cache responds to memory pressure like all other cache stores built using the common...

Author: sangeethashekar Date: 01/16/2007

8.0 Factors that Affect Batch Cache-ability

During batch compilation we make a determination regarding the cache-ability of the batch. Each...

Author: sangeethashekar Date: 01/15/2007

7.0 Costing Cache Entries

A uniform costing scheme has been implemented for all cache stores in SQL Server 2005. The cost of a...

Author: sangeethashekar Date: 01/14/2007

6.0 Best Programming Practices

In this section we will outline some programming practices for efficient plan cache usage: 6.1...

Author: sangeethashekar Date: 01/13/2007

5.0 Retrieving Query Plans from Plan Cache DMV’s

P.MsoNormal { FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman";...

Author: sangeethashekar Date: 01/12/2007

4.0 Query Parameterization

Using parameters or parameter markers in queries increases the ability of SQL Server to reuse...

Author: sangeethashekar Date: 01/11/2007

3.0 How Cache Lookups Work

Each cache store is implemented as a hash table. A hash value and cache key pair is used in order to...

Author: sangeethashekar Date: 01/10/2007

2.0 Sql_Handle and Plan_Handle Explained

2.1 What is a Plan_Handle Cached compiled plans are stored in the SQLCP or the OBJCP cache stores. A...

Author: sangeethashekar Date: 01/09/2007

1.0 Structure of the Plan Cache and Types of Cached Objects

The plan cache is comprised of 4 separate cache stores: Object Plans (CACHESTORE_OBJCP), SQL Plans...

Author: sangeethashekar Date: 01/09/2007

Plan Cache Concepts Explained

Since the release of SQL Server 2005 there have been several questions around how plan caching has...

Author: sangeethashekar Date: 01/08/2007

Case-insensitive Search Operations

Many applications have a functional requirement for the underlying database to have a case-sensitive...

Author: Peter Scharlock Date: 01/04/2007

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

Author: Peter Scharlock Date: 12/01/2006

How are GUIDs compared in SQL Server 2005?

In general, equality comparisons make a lot of sense with uniqueidentifier values. However, if you...

Author: mathh Date: 11/06/2006

Resolving Login Errors with Duplicated Databases

Many ISVs and ISV customers often have a need to duplicate their database server in entirety onto...

Author: Peter Scharlock Date: 10/17/2006

XML: Typed and Untyped

Occasionally we hear from people who are surprised to find that their XML data uses more space when...

Author: MSDN Archive Date: 09/26/2006

Implementing Login Statistics Using SQL Server 2005 Trace Feature

Introduction Login statistics is a useful mechanism to keep track of login activity on a server...

Author: ruslan Date: 08/16/2006

SQL Server 2005 UNICODE considerations.

ISV applications today often require international support. Migrating an existing non-Unicode...

Author: Peter Scharlock Date: 07/07/2006

SQL Server 2005 Debugging Requirements

There have been some questions about the sysadmin requirement of SQL Server 2005 Debugging, and I’d...

Author: Wang.Haitao Date: 06/30/2006

Three significant Cursor changes in SQL Server 2005

Many ISV applications use ODBC API Server Cursors. One source of developer confusion when coding and...

Author: RaviR Date: 06/29/2006

Introducing Multiple Active Result Sets (MARS) (2)

MARS is a powerful tool, but you may shoot yourself by the foot if you don't use it correctly. In...

Author: pingwang Date: 06/23/2006

Impersonation in SQL CLR

When the user code will try to access a resource outside of the SQL server it will run in the...

Author: sqlclr Date: 06/17/2006

Next>