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: XEvent Timestamp is a large integer value not the expected datatime value

    The timestamp column for XEvent is stored internally as an offset from the start of the trace.   The XEvent header contains the starting, UTC time and each event stores the offset in ticks from the value stored in the header. On a system where the time is adjusted, for example daylight savings...
  • Blog Post: Understanding SQL Server’s Spatial Precision Filtering

    A spatial index is not precise on its own. The spatial index is grid design requiring a precision filter as part of the query plan. In this blog I will provide a high level (10,000 foot) overview of the design. The spatial index overlays a series of grids. If the shape has any area (representation) that...
  • 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: Do I really need to use DTC Transactions?

    It is sometimes common practice to enable Distributed Transaction (DTC) behavior but it can be unnecessary, and adds unwanted overhead.   DTC has the ability to determine single phase vs two phase commit requirements.  A DTC transaction involves resource managers (RMs) of which SQL Server...
  • 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: 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: VSS backup of AlwaysOn Secondaries

    Hi Everyone, Today I’m going to highlight one of the changes brought by SQL Server 2012 SP2 , which is the way we handle VSS Backup requests on AlwaysOn Secondary Databases . Until now, any request for a FULL database backup (VSS_BT_FULL) thru VSS against a DB that is an AlwaysOn secondary was failing...
  • Blog Post: How It Works: XEvent Output and Visualization

    Each and every day I use XEvent more and more as I uncover the powerful feature set.   I am finding it helpful to understand some of the input and output capabilities in order to leverage the power of XEvent. Server File Output When setting up an session to write to a file use per CPU partitioning...
  • Blog Post: Read this if you have transactional replication configured and plan to upgrade from SQL 2008/2008 R2 to SQL 2012/2014

    SQL Server online documentation makes very clear that you need to 'drain' your replicated transactions before doing any upgrade if you have replicated databases. Below are requirements for transactional replication: Make sure that the Log Reader Agent is running for the database. By default, the...
  • Blog Post: Capping CPU using Resource Governor – The Concurrency Mathematics

    Here is what you need to know: A = πr 2 Okay, not really as that is the formula for the area of a circle but it does set the stage for this discussion.   I have been working with the CAP_CPU_PERCENT (RESOURCE POOL) setting as it relates to concurrency.   This turned into a mathematical...
  • 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: Version 9.04.0013 of the RML Utilities for x86 and x64 has been released to the download center

            X64 : http://download.microsoft.com/download/0/a/4/0a41538e-2d57-40ff-ae85-ec4459f7cdaa/RMLSetup_AMD64.msi X86 : http://download.microsoft.com/download/4/6/a/46a3217e-f523-4cc6-96e9-df73dd0fdd04/RMLSetup_X86.msi   This build...
  • Blog Post: I think I am getting duplicate query plan entries in SQL Server’s procedure cache

    Before the post dives into the subject I need to point out that Keith did most of the work.  I just kept pestering him with various scenarios until he sent me the e-mail content I needed.   Thanks Keith – Keith devised a set of steps that you can use to collect information about the plans...
  • Blog Post: Spatial Indexing: From 4 Days to 4 Hours

    Over the past month I have been involved in the optimization of a Spatial Index creation/rebuild.  Microsoft has several fixes included in the SQL Server 2012 SP1 CU7 Release I have been asked by several people to tell the story of how I was able to determine the problem code lines that allowed...
  • 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: SQL Server 2012 Database Engine Task Scheduling

    Over the years the SQL Server scheduling algorithms have been documented in various publications.  Specifically, ‘ The Guru’s Guide to SQL Server Architecture and Internals’ has a chapter, written by the scheduler developer (Sameer) and Ken; and I reviewed the technical content, covering the details...
  • 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: 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: 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...
Page 1 of 3 (52 items) 123