Press "Enter" to skip to content

Category: T-SQL Tuesday

Single-Query Wait Stats

Published 2017-02-15 by Kevin Feasel

Robert Davis shows off a new wait stats-related feature in 2016 SP1:

Paul’s process gives you info on every instance of a wait the query experienced and it’s very easy to aggregate those results to see the top waits and their total effect on a query. Quite often though, you don’t need a lot of detail. You don’t need to know every wait, just the top several. If you are already generating the actual query plan to have a in-depth look at the plan, wouldn’t it be nice if the query wait stats were already in there for you?

Now they are. In SQL Server 2016 (I’m told it came in SP1, but I don’t have a non-SP1 instance to verify that), the actual execution plan includes the top waits for the query execution in the plan. You can see them by clicking on the left-most (first) operator in the plan and viewing the Properties (shortcut F4). It will list the top waits right there in the properties dialog for you.

Getting single-query wait stats in the execution plan makes life so much simpler.

Comments closed

Change Detection Temporal Tables

Published 2017-02-15 by Kevin Feasel

Adam Machanic shows how to find net changes using temporal tables:

For now, consider the following set of propositions, given that we’re asking at time Y for all changes since a prior time X.

  • INSERT: The key did not exist at time X but does exist at time Y.
  • DELETE: The key existed at time X but does not exist at time Y.
  • UPDATE: The key existed at both time X and at time Y, and at least one change occurred between time X and time Y.

Given these assumptions, we can begin work on a temporal queries that return the necessary rows. Solving for these conditions will require all rows that were active as of time X, rows that were (or are) active at time Y, and for the final case, all rows that were active in-between times X and Y. Since this is a range-based scenario, our best Temporal predication option will be either FROM or BETWEEN. The difference between these two is subtle: FROM uses an open interval (non-inclusive at both endpoints), whereas BETWEEN uses a half-open interval, inclusive on the end date. Given the choice in the scenario, BETWEEN makes more sense, as we can take advantage of the inclusive endpoint to avoid dropping a badly-timed row. But more on that in a moment.

Adam put a lot of thought into edge cases, making this a must-read.

Comments closed

Maintenance Plan Updates

Published 2017-02-15 by Kevin Feasel

Kevin Hill looks at maintenance plan updates in SQL Server 2016:

True to my typical post style which focuses on small shops, accidental DBAs, and junior DBAs I went looking for something that could very easily benefit people that are using the basic SQL Server features.  In this case they may not even realize how limited they were.

I chose to write about Index Maintenance in the built-in Maintenance Plan portion of SQL Server.

A brief summary of the built-in Maintenance Plans is that they allow you to drag-and-drop your way to basic SQL Server maintenance items such as Backups, Index maintenance, CheckDB, Statistics updating, etc.  This a tool that has been around since at least version 7 that I know of.  It wasn’t always very good, and it gets a bad rap from a lot of DBAs.  It has been dramatically improved over the years in flexibility and reliability.

Read on for the changes.  I’m really not a fan of maintenance plans, but if they’re going to exist, they should at least be as good as possible.

Comments closed

T-SQL Tuesday Roundup, Bugs Edition

Published 2017-01-19 by Kevin Feasel

Brent Ozar rounds up T-SQL Tuesday 86:

I dunno about you, but I got a big stocking full of coal. Next year, I’m gonna be better, and I plan on asking Santa for a whole bunch of Connect requests. For T-SQL Tuesday, I asked you to name your favorite SQL Server bugs & enhancement requests, and here’s what you want in your stocking next year.

If you agree with a feature, click on it, and upvote its Connect request. These bloggers took the time to make their case – now it’s time for you to vote.

29 separate links, so there’s a lot of reading here.

Comments closed

Quickstats Craziness

Published 2017-01-12 by Kevin Feasel

Lonny Niederstadt takes an in-depth look at the intersection of several features and discusses an issue:

Here’s the Connect Item involved – written by Michael Swart.

Trace flag 2390 can cause large compile time when dealing with filtered indexes. (Active)
https://connect.microsoft.com/SQLServer/feedback/details/2528743/

Huh.  “What does that have to do with clustered columnstore indexes?” you might ask.  No, really, it’d be a personal favor if you *did* ask…

Ask and then read on.  This is the kind of post I’d send to someone wanting to learn how to troubleshoot issues.

Comments closed

Query Store Connect Items

Published 2017-01-12 by Kevin Feasel

Erin Stellato link to two feature requests for Query Store:

Two of these requests have Connect items, which I’m listing below.  The first allows you to change the location of Query Store data to reside somewhere in the user database besides the PRIMARY filegroup:

Option to store query store data in a filegroup other than PRIMARY

The other request is related to exporting that data, which is technically possible now, but it’s not a supported method so it’s not something I really want to implement in a client environment.  I’ve had many people describe their process for testing which includes restoring database nightly.  If they’re using Query Store as part of testing, that data is lost every night by the restore.

Export Query Store tables separately from the database tables

Click through for more and vote up those items relevant to you.

Comments closed

Wanted: MDX Intellisense

Published 2017-01-12 by Kevin Feasel

Jens Vestergaard wants Intellisense for MDX:

The Connect Item I have chosen to write about is an old one and is about getting Intellisense for MDX in SQL Server Management Studio [SSMS]. Despite the fact that it was created back in 2009 by Jamie Thomson (b|l|t), it is still active and there has been a public acknowledgement back then, by the Analysis Service Team, that they will consider this request for an upcoming release. 2009, still active… True story.

Read on for more details and be sure to join Jens’s quixotic quest if you’d like to see MDX Intellisense.

Comments closed

Event Handling Items

Published 2017-01-11 by Kevin Feasel

Dave Mason looks at two Connect items related to working with extended events:

One Event Behind

I another post, I wrote that the XEvents event_stream target is regularly “one event behind”. There is an existing Connect item seeking a fix to this problem: QueryableXEventData and “Watch Live Data” one event behind. If you use the “Watch Live Data” grid for XEvents in SSMS, this is an important issue and worthy of your upvote. It’s also important if you ever want to access XEvent data programmatically with C# or PowerShell because the QueryableXEventData class uses the event_stream target and is also subject to the issue.

Read on for more details.

Comments closed

Last Log Backup Time

Published 2017-01-11 by Kevin Feasel

Kevin Hill comments on a recent Connect item by Ola Hallengren:

And his proposed solution:

Add a new column to sys.dm_db_log_space_usage or sys.database_recovery_status called LastLogBackupTime.

I LOVE this idea…back up the T-log more frequently during busy times, less often during off hours.  At my current client, there is almost nothing happening outside of a 12 hour workday window, so this would be perfect here.

Now, I am possibly misunderstanding Ola’s request or the intent…and that’s ok.  This query from the msdb..backupset table already contains this info via a relatively short amount of code:

Click through for more details as well as Ola’s Connect item.

Comments closed