How It Works: The I/O Path: SQL Server Running in Windows Azure Virtual Machine (IaaS)

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 is a link to…

1

How It Works: Always On–When Is My Secondary Failover Ready?

I keep running into the question: “When will my secondary allow automatic failover?”   Based on the question I did some extended research and I will try to summarize in is blog post.  I don’t want to turn this post into a novel so I am going to take some liberties and assume you have read…

9

How It Works: CMemThread and Debugging Them

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 or without…

4

How It Works: SQL Server (NUMA Local, Foreign and Away Memory Blocks)

Applies to: SQL 2005, 2008, 2008 R2, and SQL 2012 versions. 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…

5

How It Works: Gotcha: *VARCHAR(MAX) caused my queries to be slower

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 … DROP COLUMN .. <<NTEXT>> Sounds harmless enough on the surface and…

3

How It Works: SQL Server AlwaysOn Lease Timeout

Nov 2013 Based in recent cases I needed to extend and clarify this post. The LeaseTimeout controls the lease mechanism and when the lease expires there is a very high probability of a system wide event taking place.   Let me explain more and why I make this statement. The SQL Server resource DLL is responsible…

5

How It Works: Online Index Rebuild – Can Cause Increased Fragmentation

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 active connection activity with that of the…

15

How It Works: XEL Display in SQL Server Management Studio (SSMS) Row Limit

This is a simple issue but if you don’t expect the behavior it can surprise you. The grid, used by SSMS, is limited to a maximum number of rows, that can be displayed, of 1 million.    Note:  There is no warning dialog or flashing toolbar. Shown in the figure below is the display vs event…

3

How It Works: SQL Server (BCP, Database I/O, Backup/Restore, …) Reports Operating System Error (665, 1450 or 33) when writing to the file – BIG DATA

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.com/b/psssql/archive/2009/03/04/sparse-file-errors-1450-or-665-due-to-file-fragmentation-fixes-and-workarounds.aspx http://blogs.msdn.com/b/psssql/archive/2009/01/20/how-it-works-sql-server-sparse-files-dbcc-and-snapshot-databases-revisited.aspx I ran into a 665 issue with a customer attempting to BCP data out of a database.  The scenario was that it worked if…

2