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...
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...
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...
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...
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...
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...
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...
Date: 11/26/2008
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...
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...
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...
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...
Date: 06/26/2008
SQL Server Intermittent Connectivity Issue
Recently many customers of an ISV I work with, reported intermittent connectivity issues when...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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:...
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...
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...
Date: 01/22/2007
2.0 Diagnosing Plan Cache Related Performance Problems and Suggested Solutions
Plan cache related performance problems usually manifest in decrease in throughput (or increase in...
Date: 01/21/2007
Trouble Shooting Query Performance Issues Related to Plan Cache in SQL 2005 RTM and SP1
If after upgrading from SQL 2000 to SQL 2005 RTM and/or SP1, you notice decreased OLTP database...
Date: 01/20/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...
Date: 01/18/2007
10.0 Plan Cache Flush
In SQL Server 2005, certain database maintenance operations or certain dbcc commands such as “dbcc...
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...
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...
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...
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...
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";...
Date: 01/12/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...
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...
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...
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...
Date: 01/08/2007
Case-insensitive Search Operations
Many applications have a functional requirement for the underlying database to have a case-sensitive...
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...
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...
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...
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...
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...
Date: 08/16/2006
SQL Server 2005 UNICODE considerations.
ISV applications today often require international support. Migrating an existing non-Unicode...
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...
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...
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...
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...
Date: 06/17/2006