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: A Partitioned Table May Limit the Runtime MAX DOP of Create/Alter Index

    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.com/en-us/library/ms190787.aspx ) The lab...
  • Blog Post: Having performance issues with table variables? SQL Server 2012 SP2 can help!

    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 table variable is always 1. If you always insert...
  • Blog Post: Slow query using non-deterministic user defined function

    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 R2 to generate similar plan. Upon closer look at 2008...
  • Blog Post: REPL_SCHEMA_ACCESS wait type

    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 to distributor. When they see waits on REPL_SCHEMA_ACCESS...
  • Blog Post: Capping CPU using Resource Governor – The Concurrency Mathematics

    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.   This turned into a mathematical...
  • Blog Post: How It Works: Behavior of a 1 Trillion Row Index Build (Gather Streams from SORT)

    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 to understand. The index was taking just short...
  • Blog Post: Version 9.04.0013 of the RML Utilities for x86 and x64 has been released to the download center

            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-df73dd0fdd04/RMLSetup_X86.msi   This build...
  • 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: SQL Server 2014’s new cardinality estimator (Part 1)

    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 generation. Cardinality estimates are predictions...
  • Blog Post: SQL Nexus 4.0 Released to codeplex

    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 page under https://sqlnexus.codeplex.com/ . ...
  • Blog Post: sp_reset_connection – Rate Usage (Don’t fight over the grapes)

    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.   Under the covers SQL Server uses...
  • 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: How Simple Parameterization works

    Recently we got a customer who upgraded from SQL Server 2005 to 2008. But their performance degraded greatly. What happened was that they had an update query that was run many times in a batch. The query was submitted as ad hoc by the application with different values. Upon further investigation,...
  • 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: 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: Interpreting the counter values from sys.dm_os_performance_counters

    The performance counters exposed by SQL Server are invaluable tools for monitoring various aspects of the instance health. The counter data is exposed as a shared memory object for the windows performance monitoring tools to query. It is also available as a Dynamic Management View (DMV) within SQL Server...
  • Blog Post: [SQL 2012 query plan enhancement] I want to know why my query is not parallelized

    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 the reason why the plan is not or cannot be parallelized...
  • Blog Post: Optimizing partition split when the partition is not empty

    Some of our Field Engineers, Kal Yella and Denzil Ribeiro ( @DenzilRibeiro ) have posted a blog that discusses how to optimize adding a partition when either the right most, or left most partition is not empty..  It is well worth the read, so we are posting it on PSSSQL to help get it out there...
  • Blog Post: Tracking down Power View Performance Problems

    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-15 seconds to come up, versus 3-5 seconds on my...
  • Blog Post: A performance issue involving CLR garbage collection and SQL CPU affinity setting

    We have been troubleshooting a customer’s case and uncovered a GC behavior with SQL Server CPU affinity that is worth sharing here in a blog. Problem Customer reported that they had two instances of SQL Server 2008 running on a two-node cluster.  Let’s call them Instance1 and Instance2.  When...
  • Blog Post: switchoffset built-in function can cause incorrect cardinality estimate

    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')   Upon further investigation, we discovered that...
  • Blog Post: Under rare conditions, using IN clause can cause unexpected SQL behavior

    I want to make you aware of a latest SQL Server 2008 hotfix documented in http://support.microsoft.com/kb/2791745 .   Using large number of constants in IN clause can result in SQL Server termination unexpectedly.   When this happens, you won’t see anything in errorlog or any dumps...
  • Blog Post: Query hint QUERYTRACEON is now documented publically

    I just wanted to put a quick note that QUERYTRACEON is now publically documented in http://support.microsoft.com/kb/2801413/en-us .  If you have situations where you don’t want to enable an optimizer trace flag globally on your server instance, you can use this hint for a specific query.  ...
  • 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: How can I get that user out of my table quickly

    Recently, I worked on an customer issue to help tune their slow query. The query was fairly complex involving multiple table joins. The key issue is the inability to do seeks on a particular table. The table has a column that stores User Name like below. The values stored contain domain name\user...
Page 1 of 4 (86 items) 1234