Articles Posted in the " Meta Data " 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]
    

  • View Multiple System Health Extended Event Files

    Last week I blogged about increasing retention of System Health session files. In this post I will show how you can open multiple files at once and work with the data as one set.

    If you select multiple System Health extended event files (with extension .xel) and click open each file will open in separate window of SQL Server Management Studio (SSMS).

     

    XEFiles1

    You can see here 4 files opened in 4 different windows. Now you cannot sort, group, apply filter, export data from all 4 windows as one data set.

    XEFiles2

     

    For opening all these files in one window …


  • SQL Server system_health Session Retention

    As per Microsoft’s books online

    The system_health session is an Extended Events session that is included by default with SQL Server. This session starts automatically when the SQL Server Database Engine starts, and runs without any noticeable performance effects. The session collects system data that you can use to help troubleshoot performance issues in the Database Engine. Therefore, we recommend that you do not stop or delete the session.

    What this article does not tell you is your individual file size is 5 MB and number of maximum rollover file is 4. Meaning you will only get 20 MB of …


  • Allocation Pages In SQL Server

    In order to understand how page/extent allocation works with in SQL Server for entities (objects) I was reading Paul Randal’s (B|T) blog. While reading different articles I thought of summarizing few critical information into a table. Which will help me consuming the information better and review easily when I need to. With Paul’s written consent I am sharing this and hope will help others.

    Allocation Page in SQL Server

    Download Poster


  • Manage SQL Agent Job History

    Recently in one of our server row count in MSDB.DBO.SysJobHistory table exceeded 100 Million and looking at job history was slow via SSMS. It was impossible via the GUI. Because we had about 50 jobs that were running frequently (called from a listener mostly once in every 2 to 3 minutes). I was curious if we can manage the job history for individual job or job category. I also wanted different retention for success vs failure of the job. Meaning for the jobs that run every minute few days of history for success run is enough whereas for failed ones …