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 The instance of SQL Server contains a single, background worker performing the garbage collection activities.   The FSGC worker, wakes up every ~5…

2

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 idea…

0

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 are a few additional tidbits that may assist you.  (Note:  Similar tips…

7

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 will be discussed (click for a…

0

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 account; which requires locked pages privilege. …

2

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. This post outlines a few of the changes that were made…

0

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 to SQL 2012 or it is a…

0

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 heap corruption…

1