Articles Posted in the " index " Category

  • NULL Values in Histogram

    During my cardinality estimator presentation in DBA virtual group there was a question asked about NULL values and histogram. I am quoting the question as is “SQL Server include null values in the index. How are statistics handle for null values?”

    I will answer the question in this blog post.

    For demo purpose I will be using WideWorldImporters database which you can download and restore from here. In table [Sales].[Orders] column ‘PickingCompletedWhen’ does allow NULL values.

    How many rows are NULL and how many are NOT NULL?

    USE [WideWorldImporters];
    GO
    SELECT 'NULL' AS [Value], COUNT(*) AS [NumOfRecord]
    FROM [Sales].[Orders]
    


  • TSQL to Find Status of SQL Server Statistics

    TSQL to Find Status of SQL Server Statistics

    About two years ago, I was working on an update statistics solution for 15 TB OLTP database.   First thing I needed to know, is the status of statistics.   With a google search I landed into this blog post by Erin Stellato (blog | Twitter) from SQLskills. Which gave me a perfect head start.

    I expanded that query to add more things in the result set.

    • Separate column for schema and object name.
    • Statistics ID.
    • Is this a temporary statistics (more here) ?
    • Column names (I wanted one row per statistics even with more than one column).
    • Ability

  • SQL Server Fullscan Statistics Being Overwritten with Sample Statistics

    SQL Server Fullscan Statistics Being Overwritten with Sample Statistics

    I observed this behavior only in following version of SQL Server while using Always On Availability Group feature.

    Version: Microsoft SQL Server 2012 (SP3) (KB3072779) – 11.0.6020.0 (X64) Oct 20 2015 15:36:27 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: )

    In this article by David Barbarin similar behavior was also observed in SQL Server 2014 SP2.

    Once I update my statistics with fullscan, with in 10~20 seconds some of the statistics on the same table are getting update on secondary with a sample pecent of rows. Meaning my best statistics are being …