SQL Server 2012 partitioned table statistics update behavior change when rebuilding index

Jack Li from MS SQL Server support blogged about the change in

http://blogs.msdn.com/b/psssql/archive/2013/03/19/sql-server-2012-partitioned-table-statistics-update-behavior-change-when-rebuilding-index.aspx

Here is what he said:

In past versions, when you rebuild an index, you will get statistics update equivalent to FULLSCAN for free. This is true regardless if the table is partitioned table or not.

But SQL Server 2012 changed the behavior for partitioned table. If a table is partitioned, ALTER INDEX REBUILD will only update statistics for that index with default sampling rate. In other words, it is no longer a FULLSCAN. This is documented in http://technet.microsoft.com/en-us/library/ms188388.aspx. But lots of users do not realized that. If you want fullscan, you will need to run UPDATE STATISTCS WITH FULLSCAN. This change was made because we started to support large number of partitions up to 15000 by default. Previous versions did support 15000 partitions. But it’s not on by default. Supporting large number of partitions will cause high memory consumption if we track the stats with old behavior (FULLSCAN). With partitioned table, ALTER INDEX REBUILD actually first rebuilds index and then do a sample scan to update stats in order to reduce memory consumption.

Another behavior change is actually a bug. In SQL 2012, ALTER INDEX REBUILD doesn’t preserve norecompute property for partitioned tables. In other words, if you specify norecompute on an index, it will be gone after you run ALTER INDEX REBUILD for SQL 2012. We have corrected this issue in a newly released CU 3 of SQL Server 2012 SP1. Here is the KB: http://support.microsoft.com/kb/2814780

Comments

Joining Windows 10 to Azure AD Domain

As of October 2016 to join Windows 10 computers to Azure AD Domain service requires these steps: Create a VNET in the classic portal . The VNET must be placed to a region where Azure AD domain service is available (( https://azure.microsoft.com/en-us/regions/services/ ) In the classic portal go to Directory -> Configure and enable the domain service. And wait for ~ 30 min When completed the IP address will be populated Go back to the VNET configuration and add a DNS server with the IP (10.0.0.4 in this case) Create the "AAD DC Administrator" administrators group (again in Directory -> Group). Members of this group are granted administrative privileges on machines that are domain-joined to the Azure AD Domain Services managed domain. Add to the group your users who are supposed to have the administrative access on a Windows 10 computer go to Settings -> Accounts (this is true for Windows 10 version 1607) then select 'Access work

Create 3-Node Windows 2012 Multi-subnet Cluster

Environment There are two Data centers connected via a WAN link. Two Windows 2012 Servers (called SQLDEV1 and SQLDEV2) are located in the Primary Data Center (on the IP subnet 192.168.79.0/24) and the third server is placed in the Secondary Data Center with the 192.168.69.0/24 subnet. We’ll be creating a three-node Windows cluster with no shared storage on the multi subnet network with a file share witness at the Primary Data Center. We’ll be using a file share witness to protect from the cluster failure in a situation when the network between the Data Centers is unavailable and one of the servers in the Primary Data Center is also down (or being rebooted). The final state will look like depicted above: - Two Virtual IP’s will be assigned (192.168.76.218 and 192.168.69.134) to the cluster - The servers at the Primary Data Center will have a vote (Vote=1) and the server at the Secondary Data Center will have no vote (Vote=0). The file share witness al

Generate Calendar Table in Power BI with Fiscal Year Attributes

In Power BI go to Get Data --> Blank Query and paste into the Function windows the text below. This function takes as parameters: - StartYear (e.g., 2012) - EndYear (e.g., 2018) -FiscalYearStartMonth (e.g., 4) And it will generate a calendar table for dates from Jan-1-<StartYear> till Dec-31-<EndYear> and columns for Fiscal Year, Fiscal Month, Fiscal Quarter, Fiscal Year Day where the Fiscal year begins on FiscalYearStartMonth = (StartYear as number, EndYear as number, FiscalYearStartMonth as number)=> let //Capture the date range from the parameters StartDate = #date(StartYear, 1, 1), EndDate = #date(EndYear, 12, 31), //Get the number of dates that will be required for the table GetDateCount = Duration.Days(EndDate - StartDate)+1, //Take the count of dates and turn it into a list of dates GetDateList = List.Dates(StartDate, GetDateCount, #duration(1,0,0,0)), //Convert the list into a table DateListToTable

legi, intellexi

Search This Blog