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: SQL Server 2014: TEMPDB Hidden Performance Gem

    I ran across a change for TEMPDB BULK OPERATIONS (Select into, table valued parameters (TVP), create index with SORT IN TEMPDB, …) that you will benefit from. For example, I have a Create Index … WITH SORT IN TEMPDB that takes ~1 minute in SQL Server 2012.   On the same machine using a SQL...
  • 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: 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: As The World Turns: SQL Server NUMA Memory Node and the Operating System Proximity

    It felt a bit like ‘As The World Turns’ as I unraveled how the following worked so the title is a bit of a tribute to my grandmother.  She could not miss here ‘stories’ in the afternoon. Proximity Before I dive into the details I would like to talk about NUMA node proximity.    The...
  • Blog Post: Spatial Index is NOT used when SUBQUERY used

    I have found the following link to be invaluable when working with and tuning SQL Server Spatial indexes:  http://technet.microsoft.com/en-us/library/bb895265.aspx However, the link is not as clear as it could be about the Spatial index selections made by the SQL Server query processing.  Here...
  • 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: 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: 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: 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: After applying Service Pack 1 for SQL Server 2012 you may encounter a known issue! Details inside…..

    I’d like to make you aware of an issue that may occur after installing Service Pack 1 for SQL Server 2012. Some of the symptoms are: The Windows Installer process (MSIExec.exe) repeatedly starts and attempts to repair specific SQL Server assemblies. Your Windows Application event log will contain entries...
  • 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: SharePoint Adventures : Claims, Named Pipes and Kerberos

    I ran into a new Kerberos Scenario that I hadn’t hit before when I was working on the cases related to this blog post . It’s rare that I actually see a case related to the Named Pipes protocol.  When I do, it is usually a customer trying to get it setup with a Cluster deployment.  I have never...
  • Blog Post: SharePoint Adventures : When connectivity is not connectivity

    I’m always amazed that issues usually come in batches.  I was looped into a few cases that had the following symptoms.   They were running SharePoint 2010 and Reporting Services 2012 SP1.  When they went to use a data source with Windows Authentication, they were seeing 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: Using PowerShell to discover Reporting Services 2012 in SharePoint

    I was recently going through an exercise of documenting how to discover certain aspects of Reporting Services for some Kerberos work.  RS 2012 in SharePoint is a totally different game though.  The easiest way I could discover certain items about Reporting Services within SharePoint was with...
  • Blog Post: AlwaysON - HADRON Learning Series: lock_redo_blocked/redo worker Blocked on Secondary Replica

    The topic I received most in my inbox this week was redo blocked on a secondary while attempting to acquire SCH-M (schema modify) lock. First of all, this is expected behavior and you can monitor for this with your standard blocking activities (sys.dm_exec_requests, blocked process TRC event, blocked...
  • Blog Post: AlwaysON - HADRON Learning Series: HADR_SYNC_COMMIT vs WRITELOG wait

    The distinction between these two wait types is subtle but very helpful in tuning your Always On environment. The committing of a transaction means the log block must be written locally as well as remotely for synchronous replicas.   When in synchronized state this involves specific waits for...
  • 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: Don’t change value of that parameter

    Parameter sniffing is a well known among SQL User community.   But I have seen variations of this frequently that need a bit creative handling and solution may not that straight forward. One of the variation is that a user changes the value of the parameter inside the procedure.  When...
  • 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: 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: AlwaysOn ERROR: Log backup for database "MyDB" on secondary replica created backup files successfully but could not ensure that a backup point has been committed on the primary.

    This error surprised me when it showed up in the error log so I decided to dig into it a bit more. “Log backup for database "MyDB" on secondary replica created backup files successfully but could not ensure that a backup point has been committed on the primary.  This is an informational...
Page 1 of 2 (48 items) 12