Browse by Tags

Blog Post: A Partitioned Table May Limit the Runtime MAX DOP of Create/Alter Index

SQL Server Support Content

I was working with a 1.3 trillion row table in the Microsoft lab when I learned more about the ins and outs of this behavior. This issue is alluded to in SQL Server Books Online but allow me to expand on the behavior a bit more. ( http://msdn.microsoft ...read more

on 4 Sep 2014
Blog Post: Read this if you have transactional replication configured and plan to upgrade from SQL 2008/2008 R2 to SQL 2012/2014

SQL Server Support Content

SQL Server online documentation makes very clear that you need to 'drain' your replicated transactions before doing any upgrade if you have replicated databases. Below are requirements for transactional replication: Make sure that the Log ...read more

on 17 Jul 2014
Blog Post: How It Works: FileStream (RsFx) Garbage Collection–Part (2)

SQL Server Support Content

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 ...read more

on 4 Apr 2014
Blog Post: AppDomain unloading messages flooding the SQL Server error log

SQL Server Support Content

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 ...read more

on 30 Jan 2013
Blog Post: How It Works: SQL Server (NUMA Local, Foreign and Away Memory Blocks)

SQL Server Support Content

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 but ...read more

on 14 Dec 2012
Blog Post: How It Works: SQL Server 2012 Database Engine Task Scheduling

SQL Server Support Content

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 ...read more

on 13 Aug 2013
Blog Post: How It Works: Gotcha: *VARCHAR(MAX) caused my queries to be slower

SQL Server Support Content

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 ...read more

on 3 Dec 2012
Blog Post: How It Works: The I/O Path: SQL Server Running in Windows Azure Virtual Machine (IaaS)

SQL Server Support Content

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 ...read more

on 3 Jun 2013
Blog Post: How It Works: CMemThread and Debugging Them

SQL Server Support Content

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 ...read more

on 20 Dec 2012
Blog Post: SQLIOSim Checksum Validations

SQL Server Support Content

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 ...read more

on 5 Apr 2013
Blog Post: T-SQL Update Takes Much Longer Than The Matching Select Statement

SQL Server Support Content

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 ...read more

on 21 Sep 2012
Blog Post: Strange Sch-S / Sch-M Deadlock on Machines with 16 or More Schedulers

SQL Server Support Content

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 ...read more

on 31 Aug 2012
Blog Post: When Does sp_prepare Return Metadata

SQL Server Support Content

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 ...read more

on 23 Jul 2013
Blog Post: How Can Reference Counting Be A Leading Memory Scribbler Cause?

SQL Server Support Content

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 ...read more

on 12 Nov 2012
Blog Post: SQLCLR and sp_OA* procedures are not compatible

SQL Server Support Content

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 ...read more

on 20 Dec 2012
Blog Post: Every time I ‘ATTACH DATABASE’ SQL logs error 1314 for SetFileIoOverlappedRange

SQL Server Support Content

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 ...read more

on 16 Oct 2013
Blog Post: Don’t change value of that parameter

SQL Server Support Content

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 ...read more

on 3 Feb 2013
Blog Post: How It Works: Online Index Rebuild - Can Cause Increased Fragmentation

SQL Server Support Content

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 ...read more

on 5 Sep 2012
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!

SQL Server Support Content

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 ...read more

on 11 Dec 2012

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support options

More support

Not an IT pro?

SQL Server KBs and Blogs

Browse by Tags

Blog Post: A Partitioned Table May Limit the Runtime MAX DOP of Create/Alter Index

Blog Post: Read this if you have transactional replication configured and plan to upgrade from SQL 2008/2008 R2 to SQL 2012/2014

Blog Post: How It Works: FileStream (RsFx) Garbage Collection–Part (2)

Blog Post: AppDomain unloading messages flooding the SQL Server error log

Blog Post: How It Works: SQL Server (NUMA Local, Foreign and Away Memory Blocks)

Blog Post: How It Works: SQL Server 2012 Database Engine Task Scheduling

Blog Post: How It Works: Gotcha: *VARCHAR(MAX) caused my queries to be slower

Blog Post: How It Works: The I/O Path: SQL Server Running in Windows Azure Virtual Machine (IaaS)

Blog Post: How It Works: CMemThread and Debugging Them

Blog Post: SQLIOSim Checksum Validations

Blog Post: T-SQL Update Takes Much Longer Than The Matching Select Statement

Blog Post: Strange Sch-S / Sch-M Deadlock on Machines with 16 or More Schedulers

Blog Post: When Does sp_prepare Return Metadata

Blog Post: How Can Reference Counting Be A Leading Memory Scribbler Cause?

Blog Post: SQLCLR and sp_OA* procedures are not compatible

Blog Post: Every time I ‘ATTACH DATABASE’ SQL logs error 1314 for SetFileIoOverlappedRange

Blog Post: Don’t change value of that parameter

Blog Post: How It Works: Online Index Rebuild - Can Cause Increased Fragmentation

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!