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: 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: 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: SQLCLR and sp_OA* procedures are not compatible

    We ran into an issue today that is a bug you may need to be aware of because of its behavior.   When a SQLCLR procedure calls back into the SQL Server (in proc provider) and executes sp_OA*, during the callback activity, it triggers a bug (currently filed and being evaluated) that results in...
  • 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 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: SetFileIoOverlappedRange Can Lead to Unexpected Behavior for SQL Server 2008 R2 or SQL Server 2012 (Denali)

    You should be aware the Windows bug with API = SetFileIoOverlappedRange .   This is used by SQL Server (2008 R2 and SQL 2012) only when Locked Pages has been enabled and is in use by the SQL Server. SQL Server Support has NOT encountered this in a production environment.  The issue was found...
  • 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: Clarifying The NUMA Configuration Information

    The increased number of cores per socket is driving NUMA designs and in SQL Server support we are seeing more NUMA machines and less pure SMP machines.    For whatever reason over the past 2 weeks I have fielded a bunch of questions around NUMA and the information is good for everyone...
  • 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...
  • Blog Post: SQL Server - New Drives Use 4K Sector Size

    In a lengthy discussion this past week I was reminded that Jan 2011 is when the hard drive manufactures agreed to focus on drives with sector sizes of 4K. I have read all the latest materials about this over the past week and you can too. Just search for 512e or Advanced Format Sector sizes and you will...
  • Blog Post: Discussion About SQL Server I/O

    I received a request today for help on how SQL Server I/O behaves.   As I was writing up my e-mail response I thought it would also make an interesting blog post. Sent: Friday, January 07, 2011 2:53 AM To: Robert Dorr Subject: Async I\O questions   … Background What we do know :  ...
  • Blog Post: Error 17953 - SidePageTable::Init() DeviceIoControl() : Operating system error 1(Incorrect function.) encountered.

    When attempting to run dbcc checkdb or create a snapshot database you may encounter the following errors when using a UNC location that does not support all sparse file operations. 2010-09-21 17:27:26.47 spid82 Error: 17053, Severity: 16, State: 1. 2010-09-21 17:27:26.47 spid82 SidePageTable::Init()...
  • Blog Post: InvalidReportParameterException with Data Driven Subscription

    Worked on a case yesterday where the customer was getting errors when running a Data Driven subscription.  In the RS Log, we saw something similar to the following: library!WindowsService_15!950!12/10/2009-10:49:11:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities. InvalidReportParameterException...
  • Blog Post: High CPU with Reporting Services 2008

    I’ve run into two cases which fell down the same path and figured we should have some information out there regarding it.  The issue was that the customer’s instance of Reporting Services would just spike to 100% CPU usage.  During this time the end users did not notice any interruption in...
  • Blog Post: Reporting Services: What’s my version?

    I was working on a case where I needed to try something on different Cumulative Updates (CU) to see what the result was in each.  Let me outline the version numbers for the Service Pack 1 release we have to date. Release Version Number SQL 2008 RTM 10.00.1600 SQL 2008 SP1 10.00.2531 SP1 Cumulative...
  • Blog Post: Zero Byte Attachments in Email Subscriptions

    I had an issue a while back with RS 2000 where we would get a Zero Byte attachment when hitting a Lotus Notes server.  This ended up being corrected in KB 872774 . So, when I was assigned a case with a similar description, but this time with RS 2005 SP3, my first question was “Are we hitting a Lotus...
  • Blog Post: SQL Server reports working set trim warning message during early startup phase

    In the previous posts we have discussed the working set trim message “ A significant part of sql server process memory has been paged out ” that gets written to the SQL Server Error log. You can find the details about the various parameters that determine when this message is written to the error log...
  • Blog Post: SQL, Powershell, Windows, Oh My!

    I was in the process of setting up a repro for another Blog I’m going to be posting and ran into an unexpected situation.  My VM was running Windows 2003 and I had installed SQL 2008.  I was then going to upgrade to Windows 2008 Server and came across the following message: When you install...
  • Blog Post: SQL Server 2008 SP1 and Cumulative Updates Explained.....

    NOTE: I've made a change to this blog post to account for the fact that actually 6 fixes in CU4 for 2008 RTM fixes DID made it into 2008 SP1. One of our readers sent me the following questions I think you will find valuable when understanding our recently posted SQL Server 2008 Service Pack 1 and our...
Page 1 of 2 (26 items) 12