Browse by Tags

Blog Post: A faster CHECKDB – Part III

SQL Server Support Content

Bob Ward introduced Part 1 and Part 2 of ‘A faster CHECKDB’ as highlighted in the following links. Part 1: http://blogs.msdn.com/b/psssql/archive/2011/12/20/a-faster-checkdb-part-i.aspx Part 2: http://blogs.msdn.com/b/psssql/archive/2012/02/23 ...read more

on 10 Nov 2014
Blog Post: SQL Server MAX DOP Beyond 64 – Is That Possible?

SQL Server Support Content

I recently posted a blog outlining how the partitions of a table can be used in the calculation for the achievable max degree of parallelism (MAX DOP). http://blogs.msdn.com/b/psssql/archive/2014/09/04/a-partitioned-table-may-limit-the-runtime-max-dop ...read more

on 30 Oct 2014
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: VSS backup of AlwaysOn Secondaries

SQL Server Support Content

Hi Everyone, Today I’m going to highlight one of the changes brought by SQL Server 2012 SP2 , which is the way we handle VSS Backup requests on AlwaysOn Secondary Databases . Until now, any request for a FULL database backup (VSS_BT_FULL) thru VSS against ...read more

on 12 Aug 2014
Blog Post: How It Works: XEvent Output and Visualization

SQL Server Support Content

Each and every day I use XEvent more and more as I uncover the powerful feature set. I am finding it helpful to understand some of the input and output capabilities in order to leverage the power of XEvent. Server File Output When setting ...read more

on 25 Jul 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: Capping CPU using Resource Governor – The Concurrency Mathematics

SQL Server Support Content

Here is what you need to know: A = πr 2 Okay, not really as that is the formula for the area of a circle but it does set the stage for this discussion. I have been working with the CAP_CPU_PERCENT (RESOURCE POOL) setting as it relates to concurrency ...read more

on 21 May 2014
Blog Post: How It Works: Behavior of a 1 Trillion Row Index Build (Gather Streams from SORT)

SQL Server Support Content

I ran into this behavior working on a 1 trillion row, spatial index build but the behavior can apply to any Gather Streams operator, retaining the sort order as rows pass though it. I was just surprised a bit by the behavior until I dug deeper ...read more

on 29 Apr 2014
Blog Post: Version 9.04.0013 of the RML Utilities for x86 and x64 has been released to the download center

SQL Server Support Content

X64 : http://download.microsoft.com/download/0/a/4/0a41538e-2d57-40ff-ae85-ec4459f7cdaa/RMLSetup_AMD64.msi X86 : http://download.microsoft.com/download/4/6/a/46a3217e-f523-4cc6-96e9 ...read more

on 24 Apr 2014
Blog Post: I think I am getting duplicate query plan entries in SQL Server’s procedure cache

SQL Server Support Content

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

on 3 Apr 2014
Blog Post: Spatial Indexing: From 4 Days to 4 Hours

SQL Server Support Content

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

on 19 Nov 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: All about RollbackSnapshotTempDB...

SQL Server Support Content

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

on 7 Nov 2013
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: 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: SQL Server–Storage Spaces/VHDx and 4K Sector Size

SQL Server Support Content

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

on 15 May 2013
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: 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 AlwaysOn Lease Timeout

SQL Server Support Content

The lease is used between the SQL Server resource DLL and the SQL Server instance to prevent split-brain from occurring for the availability group (AG). The lease is a standard signaling mechanism between the SQL Server resource DLL and the SQL Server ...read more

on 7 Sep 2012
Blog Post: DReplay Message: “Active connections exceed 8192, connection 8409 is waiting.”

SQL Server Support Content

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

on 23 Jul 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: Upgrade from SQL 2005 to SQL 2012 fails with error “Service 'SQLSERVERAGENT' start request failed.”

SQL Server Support Content

I was working on a 2005 to 2012 upgrade issue and came across an interesting issue which I thought of sharing with you guys. Whilst running an upgrade from 2005 SP4 to 2012 RTM (and/or slipstreamed copy) it failed half way through, giving the error ...read more

on 24 Jul 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

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 faster CHECKDB – Part III

Blog Post: SQL Server MAX DOP Beyond 64 – Is That Possible?

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

Blog Post: VSS backup of AlwaysOn Secondaries

Blog Post: How It Works: XEvent Output and Visualization

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: Capping CPU using Resource Governor – The Concurrency Mathematics

Blog Post: How It Works: Behavior of a 1 Trillion Row Index Build (Gather Streams from SORT)

Blog Post: Version 9.04.0013 of the RML Utilities for x86 and x64 has been released to the download center

Blog Post: I think I am getting duplicate query plan entries in SQL Server’s procedure cache

Blog Post: Spatial Indexing: From 4 Days to 4 Hours

Blog Post: How It Works: CMemThread and Debugging Them

Blog Post: All about RollbackSnapshotTempDB...

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

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: SQL Server–Storage Spaces/VHDx and 4K Sector Size

Blog Post: When Does sp_prepare Return Metadata

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

Blog Post: How It Works: SQL Server AlwaysOn Lease Timeout

Blog Post: DReplay Message: “Active connections exceed 8192, connection 8409 is waiting.”

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: Upgrade from SQL 2005 to SQL 2012 fails with error “Service 'SQLSERVERAGENT' start request failed.”

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