SSMS 17.3 has built-in Profiler

We all know that Profiler and its background functionality SQL Trace has been deprecated since 2012. Right?

And we have all switched to using Extended Events (XE), a long time ago, right?

No, the reality is that many of us still uses the old tracing infrastructure. This can be for various reasons, like:

  • Familiarity
  • Existing templates and stuff
  • Ease of use
  • Lack of functionality in the replacement

 

I have been gradually switching over to XE over the last few years. And I have been gradually becoming to like it more and more. It is a learning curve, for sure, but it is so superior and even the GUI is in my opinion more powerful if you are a) looking at a saved trace and b) use a trace file instead of the ring buffer. Just the ability to be able to sort, group and aggregate with a few clicks is for me great.

But enough about the general stuff, I’m actually going somewhere with this.

One functionally for which I haven’t been using XE much is to look at live data. I’m talking about scenarios such a “I wonder what happen when you press this button?”. Or, something I very frequently do is to look at the resource usage for SQL command from a trace instead of using commands such as SET STATISTICS IO ON. For these type of tasks, I often just fire up Profiler and use my own Profiler templates.

A very promising new functionality in SQL Server Manager Studio (SSMS) 17.3 is the new XE Profiler. This is a Profiler-like experience built-in to SSMS.

XE Profiler

Just double-click any of the two entries and you have a live trace window. This is built on the SSMS XE “Watch Live Data” functionality. There’s actually no magic going on here. What happens is that SSMS creates a trace session if it doesn’t exist, starts that session and opens a live data window for that trace session. There’s no target for the trace, live data doesn’t require a target. The event sessions that will be created are named:

  • Standard          QuickSessionStandard
  • TSQL               QuickSessionTSQL

The above corresponds to the Profiler templates with the same names. The really cool thing is that you can customize these sessions. I, for once, frequently want to see resource usage for queries. I modify QuickSessionTSQL to grab the completed events instead of the started events. Don’t worry if you mess it up – just delete the trace session and let SSMS re-create it for you next time you open a that Quick Session!

Here’s an example

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name = 'QuickSessionTSQL')
DROP EVENT SESSION QuickSessionTSQL ON SERVER
GO
CREATE EVENT SESSION QuickSessionTSQL ON SERVER
ADD EVENT sqlserver.rpc_completed(
ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.database_name,sqlserver.server_principal_name,sqlserver.session_id)),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.database_name,sqlserver.server_principal_name,sqlserver.session_id))
WITH (MAX_MEMORY=8192 KB, MAX_DISPATCH_LATENCY=5 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=PER_CPU)

When you Launch Session then by default it will show the columns that was relevant for the session definitions that shipped with SSMS. What you might want to do is to re-configure the columns that is shown.

To remove a column: Right-click on the column header that you don’t want to see and remove that column.

To add a column in the display: You can in the lower window, the “Details” section, right-click a field and “Show Column in Table”. Or you can in the column header in the top column header section right-click and “Choose Columns”.
Choose Columns 2Choose Columns

I might over time build some more “alternate” templates for XE Profiler, which I will add here.



        

Local Books Online, as of 2017 August

This seems to be a never ending story, having a locally installed Books Online for SQL Server, that is. Some of you recall that I have posted about this before. This post is about my experiences for the current state of Books Online. Things has changed since I last blogged this. Among other things, MS has re-worked the documentation, including moving the online bit from MSDN to docs.

Background: I have a new machine, and with that I of course want to have a local Books Online for SQL Server. Following any of my previous post didn’t quite work. Here is what I installed:

  • Windows 10, fresh installation
  • SQL Server 2016 (Db engine, SSIS, SSRS)
  • Patch above to current service pack and CU.
  • SQL Server Data Tools – BI
  • SSMS 17.2
  • Visual Studio 2017

There are of course other stuff on the machine as well, but I doubt that affect the SQL Server BOL experience. Here’s what I did to get local BOL inside SSMS:

  1. Help
  2. Add and Remove Help Content
  3. Under “SQL Server” header, I just added the bits that I found interesting. In my case everything.
  4. Update
  5. Now it took a long while, since I missed the VS installation a monster-sized doc for Framework. I later removed the VS bits.
  6. I was also afraid that I experienced a bug in Help Viewer, where it would hang. It took a long while with status something like removing temp files. But it ended just fine, I didn’t have to do the “kill, update help config file, and stuff”, like I did before.

Bottom line is that it worked just as I expected, or perhaps I should say “hoped for”. 🙂
(Well, almost. Some entries are doubled. I don’t actually bothered (much) about that so I just ignore that. For instance under “SQL Server Technical Documentation” I have two of both “Overview” and “Release Notes”.)

What more? Well, we probably want local BOL to be the “F1”-help:

  1. Help
  2. Set Help Preference
  3. Launch in Help Viewer

Also, your might want to create a shortcut (on desktop, start menu, or wherever), which points to:
C:\Program Files (x86)\Microsoft Help Viewer\v2.2\HlpViewer.exe” /catalogName SSMS17 /locale en-US

For reference, here are two prior posts on the subject. But don’t go there, they will confuse you. I only list them here as a reference, and *in case* you might end up with a hung help viewer etc…
Bol 2016 blog 1
Bol 2016 blog 2

Logging wait stats over time

We all know how valuable wait statistics can be when doing performance analysis. One thing I feel is missing in SQL Server is a trail of various measures, for instance wait stats. I really wish Microsoft could include something in this area, which can be used as a baseline. I recently fount this, from the Tiger Team. It looks promising, but I haven’t implemented it yet.

Anyhow, I have created a script that log wait stats over time. I keep a trail of logged value which is denser – for instance once per minute. And also a trail which is courser, for instance once per day.

The solution has a few tables and then you create a couple of Agent jobs. The logging and clean-up part is methinks pretty much done. The part that need more work is the analysis of the information. I.e., expect to find queries and views added. Suggestions are of course very welcome!

You find the stuff here.

Adjust autogrow setting for your database files

I very frequently see too small autogrow value for database files. Even the default in many cases. The defaults prior to SQL Server 2016 are 1 MB for data files and 10% for log files, adjusted to a little bit better values in 2016. One of the tings I often do when I start working on a SQL Server is to adjust the values to something more reasonable.

I have been wanting to automate this for a long time, but other things got in between. But now I have finally created my sp_dbfiles_autogrow procedure. (I’m sure I’m not the first one, but I wanted my own.) It creates and populates a table with the values to generate, based on the current size for the database file, and end result is that it outputs the ALTER DATABASE commands as text (using PRINT). I.e., it doesn’t really change anything, only generate the commands. You can find the procedure here. All comments and bug findings are of course welcome!

 

Keep more history for your Agent jobs

SQL Server Agent is in my opinion way too restrictive when it removes old job history information.

This information is store in the dbo.sysjobhistory table in the msdb database. Unfortunately, Agent will by default only keep 1000 rows in this table. I have on many occasions wanted to for instance check last execution of the weekly job. Or the trend for the weekly job, like how ling it takes to execute. Only to find that the information is no longer there.

I prefer to expand this to 50000 rows in total and 200 rows per job.

You can do this in the GUI: right-click Agent, Properties, History. Or just execute below SQL command on your SQL Servers:

EXEC msdb.dbo.sp_set_sqlagent_properties
@jobhistory_max_rows=50000
,@jobhistory_max_rows_per_job=200

Log Page Life Expectancy over time

You often see Page Life Expectancy referred to as an interesting performance monitor counter. And it can be! It indicates for how long a page is expected to stay in cache, from the time it was brought into cache.

But just looking at a snapshot value doesn’t say that much. It might be high, but that is because you haven’t had a high turnover of you pages for the past couple of hours. Or the other way around, you happen to look just after a very rare monster query. Furthermore, having a log can show you how PLE changes over time.

“The app was slow at two o’clock today.”

You look at the PLE log and you see how PLE dropped at that time, which indicates that something was executed that did lots of I/O. Or you don’t see PLE drop at all, and can assume that it probably was a blocking situation instead.

Some of you already have performance monitoring solutions that include PLE logging over time. For you that don’t, I created a very simple solution. It involved a table, a scheduled Agent job and queries to read the data. That’s it.

You find it at http://karaszi.com/log-page-life-expectancy-over-time .

Improvements and comments are of course appreciated. 🙂

Update for Books Online with functioning index (2016)

Good news, everybody! Microsoft recently released an update for the local Books Online, with a functioning index for the relational engine.

Earlier versions were missing those index bits, but not for everyone (a theory is that it depends on what server you hit when you downloaded the BOL bits). Basic steps to get them (see my earlier blog, linked to at the end of this, for details):

  • SSMS 2016
  • Help
  • Add and Remove Help Content. This opens Help Viewer 2.2 (HW).
  • If you have the old BOL bits, you should see that there is an update:
    • Remove them
    • Update
    • HW might hang at the end, force-finish it.
  • Add the relevant BOL bits (under Recommended Documentation), the ones under “SQL Server” is for 2012.
  • Update
  • HW might hang at the end, force-finish it.

There are other issues with BOL to hopefully be addressed. See my earlier blog post for details.

July SSMS 2016 changes transaction semantics

This can be a very nasty one if you’re not observant. The July update of SSMS now has the option to change XACT_ABORT setting for a query window using

  • Tools
  • Options
  • Query Execution
  • SQL Server
  • Advanced
  • SET XACT_ABORT
Now, exposing one more SET option can’t be bad, right? But Erland Sommarskog found out that this is checked by default. Now, using XACT_ABORT might now be a bad thing, au contraire. See this one of Erland’s series of error handle articles.
The problem is that it is a change. Quite of a sudden, your transaction semantics isn’t the same, and if you execute that script from somewhere else, or even an older SSMS, it doesn’t behave the same. For instance, if you don’t use TRY CATCH (which you should be using), then the batch will now stop and rollback on most errors, instead of allowing you to do the old eighties style IF @@ERROR <> 0 dance.
Personally, I SET my set commands instead of checking a checkbox in SSMS. But I don’t SET all set options there are, just the ones I want to change from default. I hope that this one can be reverted so it isn’t checked by default.

Books Online for SQL Server 2016

This one is for those of you who prefer to use a local SQL Server Documentation, a.k.a. Books Online (BOL), instead of using the web-based BOL. A local BOL is essential for me. Navigating the contents, switching between pages and searching is lightyears ahead and quicker in the local BOL.

(Update: Added bits about the missing index.)

(Update 2: there now is a functioning index.)

Erland Sommarskog (http://www.sommarskog.se/) is possibly even more adamant that I am about having a local BOL and he has been very helpful. Also thanks to Carla Sabotta and Jennifer Hubbard for the help and patience.

Getting a functional BOL for SQL Server 2016 is a bit more complicated than for 2014. In this earlier blog post I discussed how to get a proper Table of Contents for the 2014 BOL.

Some basics about the local BOL (from now on I call this BOL):

  • It uses HelpViewer.
  •    SQL Server 2012 and 2014 used HelpViewer 1.1.
  •    SQL server 2016 uses HelpViewer 2.2.
  • F1 from within SSMS will always take you to the web based BOL.
  • When you install the SQL Server 2016 engine (not SSMS) you get two program entries. Ignore these:
  •    “SQL Server Documentation” opens the old HelpViewer 1.1. There is no 2016 BOL for HelpViewer 1.1.
  •    “Manage Help Settings” takes us to the old Help Library Manager, which is for HelpViewer 1.1.

There is no stand-alone download for the 2016 books (which you would unpack on your harddrive, as we had to do for BOL 2014). You need to do it from inside the HelpViewer 2.2 program. Here’s how to do it:

From inside SQL Server Management Studio (SSMS):

  • “Help”
  • “Add and Remove Help Content”. This opens HelpViewer 2.2.
  • Make sure that “Online” is selected.
  • Add the SQL Server 2016 parts. Don’t do what I did and look for a “SQL Server 2016” header in bold typeface. The 2016 parts is right under your nose, under “Recommended Documentation”.
  • “Update”

Unfortunately there is currently a bug in HelpViewer 2.2 and it will freeze. See https://msdn.microsoft.com/library/mt654096.aspx on how to handle this. You only need to do these steps once, but you will probably need to end HelpViewer in the Task Manager whenever you update your content.

Now you should have the 2016 BOL. Selecting “Help”, “View Help” from SSMS will still open the web-based BOL, though (a web-browser inside SSMS). So to open the local BOL 2016 from inside SSMS, you will use:

  • “Help”
  • “Add and Remove Help Content”. (Yes, the same that you used to add the books.)

(Update) Note that the index isn’t there for the relational database engine, including T-SQL reference. If you for instance in the index type GROUP BY or @@SERVERNAME you will either get no hits, or be taken to the SQL Server 2012 documentation assuming you also installed that). We’ll see if MS will build an index for the relational database topics. My contacts at MS say that they do have an index, where I don’t. What do you see? Please let us know below if you, after installed BOL 2016 according to this blog post has a working index for the relational database engine. For instance if you get a 2016 hit for GROUP BY.(/Update)

(Update 2): I today, 2016-09-05, noticed that MS recently released an update for BOL 2016 and the index is there. Yay! (/Update2)

How about a program icon from which you can start BOL 2016 outside of SSMS? Create shortcut somewhere which point to below:
“C:\Program Files (x86)\Microsoft Help Viewer\v2.2\HlpViewer.exe” /catalogName SSMS16 /locale en-US

What if you don’t have SSMS installed, but still want a local BOL? There’s nothing from the SQL Server team to help you here. You would have to install HelpViewer 2.2 on your own (something I haven’t bothered to investigate).

See https://msdn.microsoft.com/en-US/library/mt736393(SQL.130).aspx and https://msdn.microsoft.com/en-us/library/mt703151(v=sql.1).aspx
(the later is a bit more generic).

Getting a functional Books Online

As you probably know, the SQL Server Documentation (aka Books Online or BOL) as of SQL Server 2012 takes us to a web-site. Searching and navigating a web-site when you want rapid and precise help is a horrible experience.

  • Fortunately, there’s still a local BOL.
  • Unfortunately, the local BOL for SQL Server 2014 has been broken for a long time.
  • Fortunately, there is a way to fix it.

The trick is to not do it how you are supposed to do it. At least not all steps. Here’s how to get a functional local BOL:

  1. Download the help files from https://www.microsoft.com/en-us/download/confirmation.aspx?id=42557
  2. Unpack the files somewhere
  3. Open the Manage Help Settings application
  4. “Choose online or local help”
  5.  Select “I want to use local help”
  6. OK
  7. Do not select “Install content from Online”. This is where you need to divert from how you (generally) are supposed to do it.
  8. “Install content from disk”
  9. Point to wherever you unpacked the files in step 2 above
  10. “Add” all books
  11. “Update”

So it is only a matter of downloading the files ourselves instead of letting the Help Library Manager (“Manage Help Settings”) tool do it.

If you wonder in what way the BOL is broken, then it is the Table of Content (TOC) which lacks a lot of subjects (for instance the T-SQL reference lacks bunch of things).