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.
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.
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.