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: Forced parameterization to the rescue

    Some of the features have been around for a long time.  But we keep seeing users not taking advantage of it.   I wanted to give you an example how forced parameterization can help you. Recently I worked with a customer with a very active system serving many concurrent users.  Here...
  • Blog Post: Pssdiag Manager update 12.0.0.1001 released

    We just released a pssdiag Manager update to codeplex. Where to download You can download both binary and source code at http://diagmanager.codeplex.com/ . What's New This version support SQL Server 2012 and 2014 Requirements Diag Manager requirements Windows 7 or above (32 or 63 bit) .NET framework...
  • Blog Post: What is RESOURCE_GOVERNOR_IDLE and why you should not ignore it completely

    If you have query that runs slow, would you believe it if I tell you that you instructed SQL Server to do so?  This can happen with Resource Governor. My colleague Bob Dorr has written a great blog about Resource Governor CPU cap titled “ Capping CPU using Resource Governor – The Concurrency Mathematics...
  • Blog Post: Understanding SQL Server’s Spatial Precision Filtering

    A spatial index is not precise on its own. The spatial index is grid design requiring a precision filter as part of the query plan. In this blog I will provide a high level (10,000 foot) overview of the design. The spatial index overlays a series of grids. If the shape has any area (representation) that...
  • Blog Post: Does statistics update cause a recompile?

    This my “statistics series” blog.   See “Statistics blogs reference” at end of this blog. In this blog, I will talk two scenarios related to recompile in conjunction with statistics update.  A statement can be recompiled for two categories of reasons.  First category is related to...
  • Blog Post: Does rebuild index update statistics?

    I recently did a talk to a group of SQL users. Quite a few facts ended up surprising the audience. I thought I’d share a few and wanted to start with index rebuild. If someone asks you the question “Does rebuild index update statistics?”, you probably will say “of course”...
  • Blog Post: How It Works: MAX DOP Level and Parallel Index Builds

    I have been working on an issue where rebuilding an index leads to additional fragmentation.   Using XEvents I debugged the page allocations and writes and was able to narrow in on the behavior. There are lots of factors to take into account when rebuilding the index.    I was able...
  • Blog Post: Running SQL Server on Machines with More Than 8 CPUs per NUMA Node May Need Trace Flag 8048

    Applies To:   SQL 2008, 2008 R2, 2012 and 2014 releases Note :  The number of CPUs is the logical count, not sockets.   If more than 8 logical CPUs are presented this post may apply. The SQL Server developer can elect to partition memory allocations at different levels based on the...
  • Blog Post: How come sys.dm_exec_requests.cpu_time never moves?

    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 of the column – cpu_time is not accurate prior...
  • Blog Post: A faster CHECKDB – Part III

    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/a-faster-checkdb-part-ii.aspx   Recently, ...
  • Blog Post: SQL Server MAX DOP Beyond 64 – Is That Possible?

    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-of-create-alter-index.aspx   Discussing this...
  • 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...
Page 1 of 4 (97 items) 1234