This is a session about new features in SQL Server Management Studio and runtime data exposed via showplan. Microsoft’s goal is to make showplan, one stop shop for query performance troubleshooting. I presented the session to following user groups and events.

• Rhode Island SQL Server User Group on February 07, 2018
• PASS DBA Virtual Chapter on February 14, 2018
• SQL Saturday Rochester on March 24, 2018
• BostonSQL User Group on March 27, 2018
• SQL Saturday Orange County on April 12, 2018
• SQL Saturday Philadelphia on April 21, 2018
• SQL Saturday Albany on July 28, 2018

Abstract:
SQL Server Management Studio …

On February 15, 2018 SQL Server Management Studio 17.5 was released.  New feature called “Data Discovery & Classification” was launched with this release. You can read about this feature in the following posts.

• SQL Data Discovery and Classification
• What’s new in SSMS 17.5: Data Discovery and Classification

Since this feature was released a common question was being asked by many data professionals.  Does it just look at the column names to determine if it falls into a classification category or does it actually read data in the tables?  Answer was given by the program manager Ronit Reger in the comment …

There are many blog posts available online about reading SQL Server Error Log or SQL Server Agent Error Log. What I needed was to search for a string (or string within a string) in all available error logs. Save the results in a table. That way I can find the frequency and pattern of the occurrence. I am also aware that PowerShell commands can read error logs and there are few posts available online for doing that. I did not find one with everything I needed. I wrote 2 versions of this, one as a stored procedure that you can …

There was a question in dba.stackexchange.com titled “module_end extended events duration in microseconds?” that I answered and it was Microsecond in that instance.
Later on I questioned myself if the duration is always in Microseconds for extended events. I found, it is a mix of Millisecond, Microsecond and some are unknown meaning famous NULL. I wrote below tsql code to determine which is what.

SELECT p.name package_name,
       o.name event_name,
       c.name event_field,
       DurationUnit= CASE
                         WHEN c.description LIKE '%milli%' 
                         THEN SUBSTRING(c.description, CHARINDEX('milli', c.description),12)
                         WHEN c.description LIKE '%micro%' 
                         THEN SUBSTRING(c.description, CHARINDEX('micro', c.description),12)
                         ELSE NULL
                     END,
       c.type_name field_type,
       c.column_type column_type
FROM sys.dm_xe_objects o
JOIN 

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