Sign in
SQL Programmability & API Development Team Blog
All posts are AS IS, without any further guarantees or warranties.
Translate This Page
Translate this page
Powered by
Microsoft® Translator
Options
About
Email Blog Author
RSS for posts
Atom
RSS for comments
OK
Search
Tags
Misc. Issues
Native Types and Methods
Pages
Procedure Cache
Sorting
SQL CLR Hosting
SQL Exception Handling
SQL Server 2000
SQL Server 2005
XML
Archive
Archives
June 2009
(1)
May 2009
(1)
April 2009
(1)
March 2009
(1)
February 2009
(1)
January 2009
(1)
November 2008
(1)
October 2008
(1)
August 2008
(1)
July 2008
(1)
June 2008
(1)
May 2008
(1)
March 2008
(3)
February 2008
(1)
June 2007
(2)
April 2007
(1)
March 2007
(1)
February 2007
(2)
January 2007
(18)
December 2006
(1)
November 2006
(1)
October 2006
(1)
September 2006
(1)
August 2006
(1)
July 2006
(2)
June 2006
(10)
May 2006
(8)
April 2006
(18)
March 2006
(4)
Posts
Subscribe via RSS
Sort by:
Most Recent
|
Most Views
|
Most Comments
Excerpt View
|
Full Post View
SQL Programmability & API Development Team Blog
Interesting issue with Filtered indexes.
Posted
over 4 years ago
by
Peter Scharlock
1
Comments
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...
SQL Programmability & API Development Team Blog
Why did the size of my indexes expand when I rebuilt my indexes?
Posted
over 4 years ago
by
Peter Scharlock
1
Comments
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 Programmability & API Development Team Blog
SQL Server 2005 / 2008 table partitioning : Important things to consider when switching-out partitions.
Posted
over 4 years ago
by
Peter Scharlock
2
Comments
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...
SQL Programmability & API Development Team Blog
Avoid using JDK Date APIs to handle timezone sensitive date and time
Posted
over 4 years ago
by
Peter Scharlock
3
Comments
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...
SQL Programmability & API Development Team Blog
Zeroing in on blocking on seemingly unrelated tables
Posted
over 4 years ago
by
Peter Scharlock
1
Comments
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...
SQL Programmability & API Development Team Blog
Using SQL Server 2008 Management Data Warehouse for database monitoring in my application
Posted
over 4 years ago
by
Peter Scharlock
1
Comments
SQL Server 2008 introduced what we call the Management Data Warehouse. The Management Data Warehouse is a relational database that contains the data that is collected from a server using the new SQL Server 2008 data collection mechanism. The Warehouse...
SQL Programmability & API Development Team Blog
OPTIMIZE FOR UNKNOWN – a little known SQL Server 2008 feature
Posted
over 5 years ago
by
Peter Scharlock
5
Comments
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...
SQL Programmability & API Development Team Blog
SQL Server 2008 : new binary – hex string conversion functionality can dramatically improve related query performance by orders of magnitude.
Posted
over 5 years ago
by
Peter Scharlock
0
Comments
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...
SQL Programmability & API Development Team Blog
How to create an autonomous transaction in SQL Server 2008
Posted
over 5 years ago
by
Peter Scharlock
5
Comments
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 Programmability & API Development Team Blog
UPDATE with OUTPUT clause – Triggers – and SQLMoreResults
Posted
over 5 years ago
by
Peter Scharlock
1
Comments
NOTE: the code in this BLOG is TSQL instead of ODBC calls. Since ODBC can be hard to understand and other API’s will have the same basic issues, I decided to use the simpler and more concise TSQL, which should also appeal to a wider audience. An...
SQL Programmability & API Development Team Blog
Use SQL Server replay tools to reproduce and resolve customer issues
Posted
over 5 years ago
by
Peter Scharlock
2
Comments
For many ISVs run that into issues at customer sites, it is sometimes difficult to isolate underlying problems, especially on a 24x7 production environment, where limitations apply to real time troubleshooting and live debugging. In situations like this...
SQL Programmability & API Development Team Blog
SQL Server Intermittent Connectivity Issue
Posted
over 5 years ago
by
Peter Scharlock
2
Comments
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...
SQL Programmability & API Development Team Blog
Using time zone data in SQL Server 2008
Posted
over 5 years ago
by
Peter Scharlock
6
Comments
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...
SQL Programmability & API Development Team Blog
Increase your SQL Server performance by replacing cursors with set operations
Posted
over 5 years ago
by
Peter Scharlock
2
Comments
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...
SQL Programmability & API Development Team Blog
Appending Data Using SQL 2008 Filestream
Posted
over 5 years ago
by
Peter Scharlock
1
Comments
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...
SQL Programmability & API Development Team Blog
Now is time to try SQL Server 2008
Posted
over 5 years ago
by
Peter Scharlock
1
Comments
Did you know that SQL Server 2008 is just 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 Programmability & API Development Team Blog
Detecting Overlapping Indexes in SQL Server 2005
Posted
over 6 years ago
by
Peter Scharlock
4
Comments
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...
SQL Programmability & API Development Team Blog
New SQL Best Practice Articles now available
Posted
over 6 years ago
by
Peter Scharlock
1
Comments
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...
SQL Programmability & API Development Team Blog
Will 64-bit increase the performance of my SQL Server application?
Posted
over 6 years ago
by
Peter Scharlock
3
Comments
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...
SQL Programmability & API Development Team Blog
Upgrading to SQL Server 2005 and DEFAULT_SCHEMA setting.
Posted
over 6 years ago
by
Peter Scharlock
2
Comments
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...
SQL Programmability & API Development Team Blog
why cannot i GRANT EXECUTE permission on assemblies anymore ?
Posted
over 6 years ago
by
RaviR
4
Comments
Just a quick note that this is by design and no you don't need to use it. What are we talking about ? Books online talks about granting assembly permissions. You used to do GRANT execute on ASSEMBLY :: <assembly_name> to <database_principal...
SQL Programmability & API Development Team Blog
Best Practices for SQL Server 2005
Posted
over 6 years ago
by
Peter Scharlock
1
Comments
Did you know there is a great place to get real-world best practices for SQL Server 2005: http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx This Best Practices site contians information such as Technical Whitepapers, Best...
SQL Programmability & API Development Team Blog
4.0 Useful Queries on DMV’s to understand Plan Cache Behavior
Posted
over 6 years ago
by
sangeethashekar
4
Comments
The following queries can be used to understand plan cache behavior. It should be noted that for systems with large plan caches, selecting all rows and joining with sys.dm_exec_sql_text is not recommended. To find the size of the plan cache use: ...
SQL Programmability & API Development Team Blog
3.0 Changes in Caching Behavior between SQL Server 2000, SQL Server 2005 RTM and SQL Server 2005 SP2
Posted
over 6 years ago
by
sangeethashekar
3
Comments
3.1 Costing algorithm change between SQL Server 2000 and SQL Server 2005 RTM In SQL Server 2000 and 2005, the cost of a query depends on 3 factors: number of disk IO requests, number of context switches during query compilation (in 4 ms quantums...
SQL Programmability & API Development Team Blog
2.0 Diagnosing Plan Cache Related Performance Problems and Suggested Solutions
Posted
over 6 years ago
by
sangeethashekar
2
Comments
Plan cache related performance problems usually manifest in decrease in throughput (or increase in query response time), and some times out of memory errors, connection time outs. None of these symptoms however point to plan cache related performance...
Page 1 of 4 (86 items)
1
2
3
4