Articles Posted in the " SQL Server " Category

  • 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

  • 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 …


  • 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 …


  • TSQL Tuesday #79 -JSON Support in SQL 2016

    TSQL Tuesday #79 -JSON Support in SQL 2016

    I thank Michael J Swart (b|t) for hosting TSQL Tuesday #79. Topic for this month is to write about SQL Server 2016. This is my first time joining in TSQL Tuesday and I am excited about it.

    TSQL2SDAY-300x300

    In my workplace we use MongoDB besides SQL Server. MongoDB save documents in binary form of  JSON called BSON. Our developers are excited to see JSON support in SQL 2o16.  I will give some simple examples about JSON support in SQL Server 2016.  Readers need to be aware that SQL Server is not supporting native JSON type.  In …