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: 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...
  • 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: Uneven query executions with parallelism

    We had a customer who was doing stress testing on a machine with 40 cores.   They designed a program that would launch multiple connections to execute the same query repeatedly based on their requirement to handle multiple concurrent executions.   The query was very CPU intensive...
  • 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: 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: 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...
  • 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: PowerShell and AlwaysOn - Gotcha - Exception setting "ConnectionString": "Keyword not supported: 'applicationintent'."

    Here is the an issue I saw come across an alias that is a gotcha! I’m running into a problem connecting to an AlwaysOn read-intent secondary and I was wondering if someone could help me out.  I have the .Net Framework 4.5 installed and the newest SQL Client install for SQL Server 2012.  Running...
  • 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...
  • 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: Where Did My Availability Group (AG) Go?

    I ran into an issue where the AG was no longer present on a specific node of my cluster but I had NOT dropped the AG from another node in the cluster.  ( http://blogs.msdn.com/b/psssql/archive/2012/06/13/how-it-works-drop-availability-group-behaviors.aspx )   Note: Use the XEvent files captured...
Page 1 of 2 (36 items) 12