Note: This blog is based on behavior as of June 2013.  At Microsoft we continue to evolve and enhance our products so the behavior may change over time. The I/O path for SQL Server, running on Windows Azure Virtual Machines, uses Windows Azure storage (often referred to as XStore.)  The following...

This blog outlines a new twist to my previous blog outlining issues with 4K sector sizes. SQL Server - New Drives Use 4K Sector Size: http://blogs.msdn.com/b/psssql/archive/2011/01/13/sql-server-new-drives-use-4k-sector-size.aspx In the previous post I discussed that it was unsafe for the I/O subsystem...

I had a very specific question asked of me related to the SQLIOSIM.exe, checksum validation logic.  It is pretty simple logic (on purpose) but effective so here are the basics. The key is that there are multiple memory locations used to hold the data and do the comparison.   1.     ...

Parameter sniffing is a well known among SQL User community.   But I have seen variations of this frequently that need a bit creative handling and solution may not that straight forward. One of the variation is that a user changes the value of the parameter inside the procedure.  When...

This blog is built directly from a customer reported issue.  As I helped investigate the source of the issue I thought it would be of interest to a broader audience – hopefully you find this interesting, as well. Allow me to provide some history of the problem before I dive into extended details...

The wait type of CMemThread shows up in outputs such as sys.dm_exec_requests.  This post is intended to explain what a CMemThread is and what you might be able to do to avoid the waits.  The easiest way to describe a CMemThread is to talk about a standard Heap, HeapCreate and the options (with...

We ran into an issue today that is a bug you may need to be aware of because of its behavior.   When a SQLCLR procedure calls back into the SQL Server (in proc provider) and executes sp_OA*, during the callback activity, it triggers a bug (currently filed and being evaluated) that results in...

The NODE an operating system page, physically belongs to can be acquired using the QueryVirtualMemoryEx Windows API.  SQL Server uses this API to track locality of memory allocations. This blog is a very high level view of SQL Server behavior but I think it provides a sufficient picture as to what...

This issue was puzzling until we stepped through the code and studied it in some detail.   The report was “SQL Server won’t use the physical memory I expect it to use.” Scenario:   SQL Server 2008 R2, 32GB RAM  - SQL only using ~22GB of total memory and won’t use anymore. Let me try...

The scenario: Table has a NTEXT column that the customer wanted converted to NVARCHAR(MAX) Data has both small and large storage for different rows Issued ALTER TABLE … ADD COLUMN …NVarCharColumn… NVARCHAR(MAX) Issued update MyTable set NVarCharColumn = <<NTEXT DATA>> Issued ALTER TABLE ...

The concept of the memory scribbler comes up quite a bit in support.   The term can often be over used but I ran into a specific example that commonly fools people, including support engineers.  The random nature and even the resulting behaviors are so broad that these issues often take...

I realize the title is generic and that is because the problem is generic.   On the surface it would not surprise me that an update takes longer than a select. (A little bit anyway.) There is logging, updates to index rows, triggers, replication needs, AlwaysOn needs, perhaps page splits and...

SQL Server Books Online alludes to the fragmentation possibility but does not fully explain that the Online Index rebuilding may increase fragmentation when it is allowed to run with MAX DOP > 1 and ALLOW_PAGE_LOCKS = OFF directives . The process of building an online index involves maintaining the...

Since it took me several days to track down this bug, and I did learn a couple of new things along the way, I thought I would share some of my work. 16 or More CPUS When a system presents SQL Server with 16 or more CPUs, and you are using a high end SQL Server SKU, SQL Server will enable lock partitioning...

Suresh and I have blogged about these issues before but this post will put another spin on the information, as it applies to BIG DATA . Previous Blog References http://blogs.msdn.com/b/psssql/archive/2008/07/10/sql-server-reports-operating-system-error-1450-or-1452-or-665-retries.aspx http://blogs.msdn...

FlushCache is the SQL Server routine that performs the checkpoint operation.  The following message is output to the SQL Server error log when trace flag ( 3504 ) is enabled. 2012-05-30 02:01:56.31 spid14s     FlushCache: cleaned up 216539 bufs with 154471 writes in 69071 ms...

I have seen several traces and questions relating to the output of the XEvent so I thought I would try to explain them a bit in an effort to reduce confusion. Terms Field == Event Data Action == Action Data - The action data is gathered from the current session/request state.  Let's look at the...

For some reason I have been looking at DMV output closely the last couple of weeks.    I just blogged about the pending I/O requests and now I have a behavior to outline for dm_tran_session_transactions for todays blog. select * from sys . dm_tran_session_transactions The scenario I was...

The increased number of cores per socket is driving NUMA designs and in SQL Server support we are seeing more NUMA machines and less pure SMP machines.    For whatever reason over the past 2 weeks I have fielded a bunch of questions around NUMA and the information is good for everyone...

Applies To:   SQL 2008, 2008 R2 and Denail builds The SQL Server developer can elect to partition memory allocations at different levels based on the what the memory is used for.   The developer may choose a global, CPU, Node, or even worker partitioning scheme.   Several of...

I have been asked how to automate SQLIOSim on several occasions.  SQLIOSim is a utility to test SQL Server I/O integrity ( not performance ) patterns against a system without needing to install SQL Server on the system.  It ships with SQL Server 2008 and SQL Server 2008 R2 and is located in...

The is more of an FYI blog post but I have read several blog and forum posts on this subject and I decided to dig into the behavior which revealed a trace bug. For the vast majority of events the Session Login Name represents the originating session credentials where as the Login Name represents the...

In a previous blog I outlined how file stream transactions work and retain the before and after images of the file to allow various forms of recovery.  Reading that blog should be considered a prerequisite: http://blogs.msdn.com/b/psssql/archive/2008/01/15/how-it-works-file-stream-the-before-and...

This post is directly from an issue I have been working on.  The behavior is very difficult to simulate because of the flags and timings involved.   In this post I will attempt to describe the scenario and provide you with a simple workaround for your applications. Reproduction Steps 1...

A .NET Security Update (MS11-028) may end up causing issues with applications that make use of the .NET Framework, mostly resulting in applications being unable to launch, or loss of functionality within Native Applications that make use of .NET. In some cases, it could lead to an application crash....