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: 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: 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: Service status watcher in SQL Server Management Studio – How it works

    Have you ever wondered about the mechanism using which SQL Server Management Studio(SSMS) - Object Explorer shows the service status for SQL Server and SQL Agent service? We recently worked with a customer on a issue related to this and thought that this might be useful information to share out. So here...
  • 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: 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: 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...
  • 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 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: 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: How It Works: DROP AVAILABILITY GROUP Behaviors

    I just learned something new about the DROP AVAILABILITY GROUP command behavior that I didn't realize.  The comment on TechNet, alludes to how DROP works but we definitely need to update our documentation. Remove an Availability Group (SQL Server) - http://technet.microsoft.com/en-us/library/ff878113...
  • 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: HealthCheckTimeout Interval Activities

    As I wrote my recent blog posts and did more research I found that is would be helpful to highlight the HealthCheckTimeout behavior in more detail. Always On FCI (Failover Cluster Instance) vs Non-FCI Installations Documentation The first thing that I need to point out is the subtle wording difference...
  • Blog Post: How It Works: Failover Cluster/Availability Group XEL Logging Frequency

    I had a great question from my post on sp_server_diagnostics ( http://blogs.msdn.com/b/psssql/archive/2012/03/08/sql-server-2012-true-black-box-recorder.aspx ).  As ' luck ' would have it, Bob Ward, was working on some training and had a similar question.  As I investigated I uncovered a few...
  • 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: AlwaysON - HADRON Learning Series: Automated Failover Behaviors (Denali - Logging History Information, FCI and Default Health Capture, sp_server_diagnostics)

    Quite an encompassing title I will agree but the features all work together to build a really nice product.  I had this tucked away during beta and thought it would be helpful to post. SQL Server AlwaysON and "Denali" has undergone a significant upgrade to the way the cluster resource...
  • Blog Post: Analysis Services Thread Pool Changes in SQL Server 2012

    As part of the SQL Server 2012 release several changes were made to Analysis Services that should alleviate some issues previously seen on large NUMA node machines. Separation of IO and Processing Jobs To better understand how the changes that were made work, it is helpful to have some background...
  • 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: How It Works: Return codes from SQLIOSim

    I have been asked how to automate SQLIOSim on several occasions.  SQLIOSim is a utility to test SQL Server I/O integrity ( not performance ) patterns against a system without needing to install SQL Server on the system.  It ships with SQL Server 2008 and SQL Server 2008 R2 and is located in...
  • Blog Post: How It Works: FileStream (RsFx) Garbage Collection

    In a previous blog I outlined how file stream transactions work and retain the before and after images of the file to allow various forms of recovery.  Reading that blog should be considered a prerequisite: http://blogs.msdn.com/b/psssql/archive/2008/01/15/how-it-works-file-stream-the-before-and...
Page 1 of 4 (97 items) 1234