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: MAX DOP Level and Parallel Index Builds

    I have been working on an issue where rebuilding an index leads to additional fragmentation.   Using XEvents I debugged the page allocations and writes and was able to narrow in on the behavior. There are lots of factors to take into account when rebuilding the index.    I was able...
  • Blog Post: Running SQL Server on Machines with More Than 8 CPUs per NUMA Node May Need Trace Flag 8048

    Applies To:   SQL 2008, 2008 R2, 2012 and 2014 releases Note :  The number of CPUs is the logical count, not sockets.   If more than 8 logical CPUs are presented this post may apply. The SQL Server developer can elect to partition memory allocations at different levels based on the...
  • Blog Post: SQL Server and SSDs – RDORR’s Learning Notes - Part 1

    I am very hesitant to post anything that I don’t have the full details on.  However, with SSD deployments moving so rapidly I thought it might be helpful to share some of my learning's to date. I make no claims of being an expert in this area.   However, I have been doing research that...
  • Blog Post: A faster CHECKDB – Part IV (SQL CLR UDTs)

    I have been working on the various aspects of DBCC performance and SQL CLR based User Defined Data Types .     I encountered a few issues that I have outlined below.   1.       Memory Grant Bug There is a bug, prior to SQL Server 2014, causing the memory grant...
  • Blog Post: How It Works: sp_server_diagnostics – spinlock backoffs

    There are numerous articles outlining how spinlocks work so I won’t cover the details in this post.   Instead, I want to focus on the spinlockbackoffs value recorded in the sp_server_diagnostics output. Component = System <system spinlockBackoffs ="0" sick SpinlockType="none"...
  • Blog Post: A faster CHECKDB – Part III

    Bob Ward introduced Part 1 and Part 2 of ‘A faster CHECKDB’ as highlighted in the following links. Part 1: http://blogs.msdn.com/b/psssql/archive/2011/12/20/a-faster-checkdb-part-i.aspx   Part 2: http://blogs.msdn.com/b/psssql/archive/2012/02/23/a-faster-checkdb-part-ii.aspx   Recently, ...
  • Blog Post: SQL Server MAX DOP Beyond 64 – Is That Possible?

    I recently posted a blog outlining how the partitions of a table can be used in the calculation for the achievable max degree of parallelism (MAX DOP). http://blogs.msdn.com/b/psssql/archive/2014/09/04/a-partitioned-table-may-limit-the-runtime-max-dop-of-create-alter-index.aspx   Discussing this...
  • Blog Post: A Partitioned Table May Limit the Runtime MAX DOP of Create/Alter Index

    I was working with a 1.3 trillion row table in the Microsoft lab when I learned more about the ins and outs of this behavior.  This issue is alluded to in SQL Server Books Online but allow me to expand on the behavior a bit more. ( http://msdn.microsoft.com/en-us/library/ms190787.aspx ) The lab...
  • 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: How It Works: FileStream (RsFx) Garbage Collection–Part (2)

    In a previous post I outlined the basics of File Stream, garbage collection: http://blogs.msdn.com/b/psssql/archive/2011/06/23/how-it-works-filestream-rsfx-garbage-collection.aspx This post continues the discussion, outlining specific details as to how the garbage collection progresses. A Single GC Thread...
  • Blog Post: Optimizing partition split when the partition is not empty

    Some of our Field Engineers, Kal Yella and Denzil Ribeiro ( @DenzilRibeiro ) have posted a blog that discusses how to optimize adding a partition when either the right most, or left most partition is not empty..  It is well worth the read, so we are posting it on PSSSQL to help get it out there...
  • Blog Post: DReplay Message: “Active connections exceed 8192, connection 8409 is waiting.”

    This message was an interesting dive into the DReplay, session boundary logic that I thought I would share.  Internally DReplay maintains a progressive, session queue.  This queue is limited to 8192 entries and populated in connection replay order based on the connect/disconnect boundaries...
  • 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: Under rare conditions, using IN clause can cause unexpected SQL behavior

    I want to make you aware of a latest SQL Server 2008 hotfix documented in http://support.microsoft.com/kb/2791745 .   Using large number of constants in IN clause can result in SQL Server termination unexpectedly.   When this happens, you won’t see anything in errorlog or any dumps...
  • 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: 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: 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...
Page 1 of 7 (163 items) 12345»