Browse by Tags

CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services
  • Blog Post: How It Works: Behavior of a 1 Trillion Row Index Build (Gather Streams from SORT)

    I ran into this behavior working on a 1 trillion row, spatial index build but the behavior can apply to any Gather Streams operator, retaining the sort order as rows pass though it.   I was just surprised a bit by the behavior until I dug deeper to understand. The index was taking just short...
  • Blog Post: All about RollbackSnapshotTempDB...

    I’ve been recently involved in several cases where Databases named RollbackSnapshotTempDB +<someGUID> were generating confusion. The purpose of this post is to clarify their origin and use and to enable SQL Server admins to know what to do if they need to deal with them. The following topics...
  • Blog Post: Every time I ‘ATTACH DATABASE’ SQL logs error 1314 for SetFileIoOverlappedRange

    Turns out this is an issue in the SQL Server code and the error is a bit noisy during attach database. When opening the database files, SQL Server calls SetFileIoOverlappedRange (when enabled properly) in order to help improve I/O performance.  This is commonly done under the SQL Server, service...
  • Blog Post: How It Works: Maximizing Max Degree Of Parallelism (MAXDOP)

    I was working on an index build issue for an 80 CPU system and kept seeing that only 64 CPUs were getting used. I had carefully studied sys . dm_os_spinlock_stats and sys . dm_os_wait_stats along with performance counters, memory usage pattern, and I/O activities.   In fact, I had an 80 CPU...
  • Blog Post: When Does sp_prepare Return Metadata

    I was running an RML Utilities Suite test pass and encountered varying behavior from our sp_prepare suite.  Here is what I uncovered. The command sp_prepare returns (or does not return) metadata depending on the server version.  For the client version, it is only significant whether it is prior...
  • Blog Post: 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...
  • Blog Post: SQL Server–Storage Spaces/VHDx and 4K Sector Size

    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...
  • Blog Post: SQLIOSim Checksum Validations

    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.     ...
  • Blog Post: AppDomain unloading messages flooding the SQL Server error log

    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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: How It Works: SQL Server 32 bit PAE/AWE on (SQL 2005, 2008, and 2008 R2) – Not Using As Much RAM As Expected!

    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...
  • Blog Post: 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 ...
  • Blog Post: How Can Reference Counting Be A Leading Memory Scribbler Cause?

    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...
  • Blog Post: T-SQL Update Takes Much Longer Than The Matching Select Statement

    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...
  • Blog Post: SQL Server: Correlating Timestamps From Various Data Points

    I was looking at data from a customer, in a different time zone (UTC+1) from mine, this week involving SQL Server AlwaysOn (HADRON) and found that lining up the timestamps in the various logs was challenging.   Some times are local to the SQL Server instance, others UTC and yet other utilities...
  • Blog Post: 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...
  • Blog Post: Strange Sch-S / Sch-M Deadlock on Machines with 16 or More Schedulers

    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...
  • Blog Post: 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...
  • Blog Post: How It Works: When is the FlushCache message added to SQL Server Error Log?

    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...
  • Blog Post: How It Works: XEvent Action vs Field data values.

    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...
  • Blog Post: How It Works: sys.dm_tran_session_transactions

    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...
  • Blog Post: How It Works: sys.dm_io_pending_io_requests

    I have been working an issue where the DMV was returning io_pending_ms_ticks incorrectly.  The following output is an example of ~164 hour delay. Unlikely to occur without any other side effects noted. select * from sys . dm_io_pending_io_requests In the process of this investigation I uncovered...
  • Blog Post: Assigning SQL Server, SQL Agent to a Processor Group (OOM, Hang, Performance Counters Always Zero for Buffer Pool, …)

    Suresh brought to my attention that we have been getting questions as to why SQL Server starts on group 1 and then group 2 and it is not predictable?  Then Tejas brought up another issues and since I worked on this way back before we released SQL 2008 R2 I went back to my notes to pull up some details...
  • Blog Post: SQL Server 2008/2008 R2 on Newer Machines with More Than 8 CPUs Presented per NUMA Node May Need Trace Flag 8048

    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...
Page 1 of 2 (41 items) 12