Browse by Tags

Blog Post: How come sys.dm_exec_requests.cpu_time never moves?

SQL Server Support Content

Today, I want to point out another SQL Server 2012 SP2 fix that may affect your performance troubleshooting. When you are troubleshooting a long running query, chances are you will use sys.dm_exec_requests to look for progresses of the query. But one ...read more

on 11 Nov 2014
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: Having performance issues with table variables? SQL Server 2012 SP2 can help!

SQL Server Support Content

In a previous blog , I talked about how table variable can impact performance of your query. The reason is that the statements referencing the table variable are compiled when the table variable has no data in it. Therefore, cardinality estimate for the ...read more

on 11 Aug 2014
Blog Post: Slow query using non-deterministic user defined function

SQL Server Support Content

Recently we worked with a customer who reported a query that used to run a few seconds in SQL Server 2000 but it never finishes in SQL Server 2008 R2 following upgrade. We went around and tried quite a few things but couldn't get SQL Server 2008 ...read more

on 9 Jul 2014
Blog Post: REPL_SCHEMA_ACCESS wait type

SQL Server Support Content

Recently we have worked with a customer on replication latency issue with transactional replication. Customer has over 30 published databases on a single server. All of them are very active. Periodically, they will see up to 30 minutes latency from publisher ...read more

on 4 Jun 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: SQL Server 2014’s new cardinality estimator (Part 1)

SQL Server Support Content

One of the performance improvement in SQL Server 2014 is the redesign of cardinality estimation. The component which does cardinality estimation (CE) is called cardinality estimator. It is the essential component of SQL query processor for query plan ...read more

on 1 Apr 2014
Blog Post: SQL Nexus 4.0 Released to codeplex

SQL Server Support Content

We have just released SQL Nexus 4.0 ( https://sqlnexus.codeplex.com/) which supports latest SQL Server (2012) with enhanced reports. In addition to read release notes, make sure you also read the top issues . Please report any issues under the Issues ...read more

on 11 Mar 2014
Blog Post: sp_reset_connection – Rate Usage (Don’t fight over the grapes)

SQL Server Support Content

Discussions surrounding sp_reset_connection behavior and usage come up all the time and over the years I have discussed the subject 100s of times with customers. Blogs, API documentation and Books Online describe SQL Server, pooled connections behavior ...read more

on 3 Mar 2014
Blog Post: switchoffset built-in function can cause incorrect cardinality estimate

SQL Server Support Content

Recently, we received a call from a customer reported that a query was slow. Upon further investigation, his query has a predicate that look like this: select * from t o where c1 >switchoffset (Convert(datetimeoffset, GETDATE()), '-04:00') ...read more

on 2 Mar 2013
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: [SQL 2012 query plan enhancement] I want to know why my query is not parallelized

SQL Server Support Content

In the past, we have got repeated questions from customers on why a particular query is not parallelized. We didn’t have a good way to let customer know the reason until SQL 2012. Starting SQL Server 2012, XML showplan is enhanced to include ...read more

on 28 Aug 2013
Blog Post: Spatial Index is NOT used when SUBQUERY used

SQL Server Support Content

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

on 9 Dec 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: Back to Basics: SQL Parameter Sniffing due to Data Skews

SQL Server Support Content

In Denzil’s recent post about correlating XE query_hash he alluded to a relatively common issue. Most of the time a proc will run just fine, but sometimes it will run very slow. This is an issue that I tend to be seeing often as of late ...read more

on 27 Aug 2013
Blog Post: Tracking down Power View Performance Problems

SQL Server Support Content

The scenario was that we saw sluggishness on the initial load of a Power View Report and also when we went to use one of the filters on the report - like a Pie Chart Slice. For the given report the customer had showed us, the initial load was taking 13 ...read more

on 29 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
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: 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: How It Works: Maximizing Max Degree Of Parallelism (MAXDOP)

SQL Server Support Content

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

on 27 Sep 2013

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: How come sys.dm_exec_requests.cpu_time never moves?

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: Having performance issues with table variables? SQL Server 2012 SP2 can help!

Blog Post: Slow query using non-deterministic user defined function

Blog Post: REPL_SCHEMA_ACCESS wait type

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: SQL Server 2014’s new cardinality estimator (Part 1)

Blog Post: SQL Nexus 4.0 Released to codeplex

Blog Post: sp_reset_connection – Rate Usage (Don’t fight over the grapes)

Blog Post: switchoffset built-in function can cause incorrect cardinality estimate

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

Blog Post: [SQL 2012 query plan enhancement] I want to know why my query is not parallelized

Blog Post: Spatial Index is NOT used when SUBQUERY used

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

Blog Post: Back to Basics: SQL Parameter Sniffing due to Data Skews

Blog Post: Tracking down Power View Performance Problems

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

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

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

Blog Post: How It Works: Maximizing Max Degree Of Parallelism (MAXDOP)