Site: http://blogs.msdn.com/b/sqlprogrammability/ Link: http://blogs.msdn.com/sqlprogrammability/rss.xml
by mssqlisv via SQL Programmability & API Development Team Blog on 6/29/2009 10:56:00 PM
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 (new in SQL 2008) to restrict the data such that it does not contain NULL values, in this case drastically reducing the number of rows in the index. See documentation here: http://msdn.microsoft.com/en-us/library/ms175049.aspx This all worked fine, ...
[ read more ]
by mssqlisv via SQL Programmability & API Development Team Blog on 5/18/2009 8:57:00 PM
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-clustered indexes. The sequence of events is as follows: · Step 1: Approximately 12 million rows are inserted into an existing table via some batch loading of the data. ...
by mssqlisv via SQL Programmability & API Development Team Blog on 4/10/2009 11:43:00 PM
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 following operations: · switch-out the partition which isn't needed anymore (or being archived) into an empty target table · merge / reset the partition function ranges of the partitioned s ...
by mssqlisv via SQL Programmability & API Development Team Blog on 3/27/2009 12:30:00 AM
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 timezone sensitive data properly using these APIs. As matter of fact, these APIs were deprecated starting JDK 1.1 (http://java.sun.com/j2se/1.5.0/docs/api/java/util/Date.html). But I am still seeing ISV developers use these APIs in their JAVA applic ...
by mssqlisv via SQL Programmability & API Development Team Blog on 2/2/2009 9:20:00 PM
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 guarantee that there was no insert, delete or update activity on the table. Needless to say, this was odd and baffled us for while and it was only when we analyzed the definitions of the tables that we could determine the sour ...
by mssqlisv via SQL Programmability & API Development Team Blog on 1/29/2009 7:19:00 PM
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 consists of these components: · An extensible data collector : ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_4deptrbl/html/b2c2bd5e-62c5-4129-a252-ef9439a05487.htm · & ...
by Peter Scharlock via SQL Programmability & API Development Team Blog on 1/29/2009 7:19:00 PM
by Peter Scharlock via SQL Programmability & API Development Team Blog on 11/27/2008 1:35:00 AM
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 be data type safe. See more about SQL Server parameterization Best Practices here: http://blogs.msdn.com/sqlprogrammability/archive/2007/01/13/6-0-best-programming-practices.aspx An application that I work with presented me with an interesting dil ...
by mssqlisv via SQL Programmability & API Development Team Blog on 11/27/2008 1:35:00 AM
by Peter Scharlock via SQL Programmability & API Development Team Blog on 10/31/2008 11:49:00 PM
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 CONVERT command converted binary data to character data in a one byte to one character fashion. SQL Server would take each byte of the source binary data, convert it to an integer value, and then uses that integer value as the ASCII value for the ...
by mssqlisv via SQL Programmability & API Development Team Blog on 10/31/2008 11:49:00 PM
by mssqlisv via SQL Programmability & API Development Team Blog on 8/22/2008 11:54:00 PM
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 support like Oracle. An Autonomous transaction is essentially a nested transaction where the inner transaction is not affected by the state of the outer transaction. In other words, you can leave the context of current transaction (outer ...
by Peter Scharlock via SQL Programmability & API Development Team Blog on 8/22/2008 11:54:00 PM
by Peter Scharlock via SQL Programmability & API Development Team Blog on 7/11/2008 11:51:00 PM
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 ISV I work with recently ran into an interesting problem; here is the description and solution. PROBLEM: Adding an unexpected trigger caused application code to fail due to incomplete SQL Syntax, and not reading through all ret ...
by mssqlisv via SQL Programmability & API Development Team Blog on 7/11/2008 11:51:00 PM
by mssqlisv via SQL Programmability & API Development Team Blog on 6/26/2008 8:13:00 PM
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, constructing a repro scenario in a separate environment would be ideal to minimize impact to live production system, and to speed up resolution process. SQL Server Profiler Allow me introduce SQL Profiler, which offers replay trace f ...
by Peter Scharlock via SQL Programmability & API Development Team Blog on 6/26/2008 8:13:00 PM
by Peter Scharlock via SQL Programmability & API Development Team Blog on 5/27/2008 8:49:00 PM
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 issue on both SQL Server 2000 and 2005. Due to the intermittent nature, and the variation of the issue, it took us quite a while to collect all the data, (odbc trace, netmon trace, sql trace…), analyse it, and understand the exact cause ...
by mssqlisv via SQL Programmability & API Development Team Blog on 5/27/2008 8:49:00 PM
by mssqlisv via SQL Programmability & API Development Team Blog on 3/19/2008 2:25:00 AM
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 in UTC or a particular timezone based on this offset. There are also new functions to allow for conversions between different time zones using the new function SWITCHOFFSET(). An example from SQL Server 2008 B ...
by mssqlisv via SQL Programmability & API Development Team Blog on 3/19/2008 1:49:00 AM
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 TSQL cursors in most cases, and how to convert cursor logic to simple set join operations. Now there are certain scenarios where using a cursor makes sense. For example, a cursor is ideal for row by row processing that can’t be accompl ...
by mssqlisv via SQL Programmability & API Development Team Blog on 3/6/2008 10:33:00 PM
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 they save some metadata in the database, with a column holding the path to the actual file. However, there is no transactional context between the two; therefore, the data in the database can be changed to no longer point to the actu ...
by Peter Scharlock via SQL Programmability & API Development Team Blog on 6/29/2007 6:56:00 PM
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 to believe that having an index for every possible query predicate set would result in all the queries executing optimally. While true, one has to keep in mind that the indexes need to be maintained when the underlying table data in the ...
by mssqlisv via SQL Programmability & API Development Team Blog on 6/29/2007 3:56:00 PM
by mssqlisv via SQL Programmability & API Development Team Blog on 6/5/2007 7:47:00 PM
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 Guidance for Web Hosting Environments http://www.microsoft.com/tech ...
by Peter Scharlock via SQL Programmability & API Development Team Blog on 5/1/2007 3:25:00 AM
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 surprised to find that some workloads actually run faster on 32-bit SQL Server than on the native 64-bit version. The assumption that 64-bit will always outperform 32-bit is not true and is determined largely by characteristics ...
by mssqlisv via SQL Programmability & API Development Team Blog on 5/1/2007 12:25:00 AM
by Peter Scharlock via SQL Programmability & API Development Team Blog on 3/23/2007 10:36:00 AM
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 the dynamic name resolution behavior. This capability applies to workloads where object names are NOT fully-qualified with a two or a three part name (for example: “database.schema.object”). If you already fully qualify your obj ...
by mssqlisv via SQL Programmability & API Development Team Blog on 3/23/2007 7:36:00 AM
by RaviR via SQL Programmability & API Development Team Blog on 2/22/2007 10:47:00 PM
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 > with SQL Server 2005 Your database holds your assemblies and as such you could grant / revoke limited set of permissions. We initially allowed you to grant execute permission on the assembly but now when you execute th ...
by RaviR via SQL Programmability & API Development Team Blog on 2/22/2007 7:47:00 PM
by mssqlisv via SQL Programmability & API Development Team Blog on 2/8/2007 8:48:00 PM
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 Practices Toolbox which contains scripts and other tools for performance tuning and troubleshooting, Top 10 lists, and best practice information compiled from the SQL Server Books Online documentation. Check it out, I'm sure you will&nbs ...
by sangeethashekar via SQL Programmability & API Development Team Blog on 1/23/2007 10:00:00 AM
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: select (sum(single_pages_kb) + sum(multi_pages_kb) ) * 8 / (1024.0 * 1024.0) as plan_cache_in_GB from sys.dm_os_memory_cache_counters where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP' go To view the cac ...
by sangeethashekar via SQL Programmability & API Development Team Blog on 1/22/2007 10:00:00 AM
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) and the number of memory pages, both from single and multi-page allocations. In SQL Server 2005, the original cost of the query is a power of two, the exponent being the sum of the contributions of disk IO’s, context switches and memo ...
by sangeethashekar via SQL Programmability & API Development Team Blog on 1/21/2007 10:00:00 AM
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 problems specifically. In order to determine and further isolate the cause of the slowdown the following steps are recommended: 2.1 Periodically query sys.dm_os_wait_stats and examine the waits CMEMTHREAD waits: This wait ...
The content of the postings is owned by the respective author. SQL Feeds is not responsible for the contents of the postings. This site is automatically generated and cannot be reviewed for abusive content. If you find abusive content on SQL Feeds, please contact us. Designated trademarks and brands are the property of their respective owners. All rights reserved.
