Press "Enter" to skip to content

Category: T-SQL Tuesday

Wanted: Forced Parallelism

Published 2017-01-10 by Kevin Feasel

James Anderson would like a query hint to force parallelism:

I would like to see a hint that causes the optimiser to consider a parallel plan no matter the cost of the query. It’s possible to get this behaviour with trace flag 8649 but it’s unsupported by Microsoft and therefore unfit for production use.

I only tend to use query hints as a very last resort. It’s almost always better to allow the optimiser to make these decisions and continue to reevaluate these decisions as your data changes but sometimes they can be a get out of jail free card.

I’ve written about this before and so has Paul White. Paul created this connect item for something similar. Please vote it up if you think it could help with your query tuning.

Click through for the full argument, and then hit the Connect item if you agree.

Comments closed

String Or Binary Data

Published 2017-01-10 by Kevin Feasel

Lori Edwards wants to fix one of the most annoying error messages in SQL Server history:

The Connect item that I’m promoting today is Connect #339410 : Please fix the “String or binary data would be truncated” message to give the column name. This Connect item was opened in 2008 and has 1,328 upvotes. If we can create virtual realities, we should be able to add a column name to an error message. Help us Microsoft, you’re our only hope.

This error message is incredibly annoying, particularly because it seems the engine should know which record has an error and on which column that error is.  We finally got information on which row caused a primary or unique key constraint violation, and that was helpful; this would be at least as helpful.

Comments closed

Connect Items Galore

Published 2017-01-10 by Kevin Feasel

Aaron Bertrand has a series of Connect items of interest:

There are so many active Connect items, it’s difficult to choose just one, but after spending some time looking at the generate_series function in PostgreSQL, the one at the top of my list right now is this suggestion from Erland Sommarskog (blog):

Connect #258733 : Add a built-in table of numbers

I think it would be cheap and easy for SQL Server to provide a defaut table of numbers, always in memory, just like any other catalog view or DMV. This thing can use compression now, regardless of edition, so even a table with 2 billion rows will only take 13 MB, and should be easy to populate either at start-up or on first use. I could list out all of the potential uses for a numbers table, but they’re fairly well documented in the following sources (and most use cases don’t need anywhere near 2 billion values, so maybe the range could be defined using a sys.configurations or database-level SCOPED CONFIGURATION setting)

Click through for additional Connect items, and please vote on any Connect items which catch your eye.

Comments closed

Backup Up Analysis Services

Published 2016-12-16 by Kevin Feasel

Jens Vestergaard shows how to take backups of Analysis Services cubes:

I have not met a setup where applying compression was not an option, yet. Obviously this has a penalty cost on CPU while executing the backup, and will affect the rest of the tasks running on the server (even if you have your data and backup dir on different drives). But in my experience, the impact is negligible.

This may not be the case with the encryption option, as this has a much larger foot print on the server. You should be using this with some caution in production. Test on smaller subsets of the data if in doubt.
Another thing to keep in mind, as always when dealing with encryption, do remember the password. There is no way of retrieving the data other than with the proper password.

My goal is to be able to rebuild any cube from the relational database, but even with that goal in mind, it is smart to have backups.

Comments closed

Transaction Log Operations And Backups

Published 2016-12-15 by Kevin Feasel

John Deardurff explains what happens in the transaction log when you restore a backup:

In the example, the database performed a checkpoint at noon and a backup had been taken at that time. The restore process will capture all the transactions up until the point the database had been backed up. After the database has been restored, the recovery process will roll forward transactions 2 and 4 because they had been committed to the transaction log before the point of failure. Since transactions 3 and 5 did not commit before the time of system failure, the undo process will roll back the transactions to keep the data in a consistent state.

Read the whole thing.

Comments closed

Backup Basics

Published 2016-12-15 by Kevin Feasel

Aaron Bertrand covers reasons for backups, backup models, and also a vital part of the backup process:

Now, all of the above may be review for you, but a much more important part of this story is that you need to be TESTING your backups. I’ve seen many customers who have been happily taking backups and storing them on some drive somewhere, and then when disaster strikes and they actually need to restore them, they can’t – maybe they had been backing up corruption all along, or the backups were failing but they were ignoring alerts, or they weren’t taking log backups frequently enough to meet their RPO, or they were only taking full backups.

Testing backups is vital; just because the backup process reported success doesn’t mean that you’ll necessarily be able to restore that backup when the time comes that you need it.  It’s also good to drill people on restoration skills, as things get a bit more stressful when three levels of management are standing behind your chair asking you what’s taking so long.

Comments closed

Backing Up Extended Event Logs

Published 2016-12-15 by Kevin Feasel

Wayne Sheffield reminds us that backups aren’t just for databases:

So how does this talk of AGs pertain to this T-SQL Tuesday topic? It should be pretty obvious – we need to periodically grab all of the .xel files generated by the cluster, and move them to a different directory, with a different retention policy. Yup… we need to back up these files. Sometimes, we need to be backing up things other than the databases themselves.

I created a PowerShell script that takes a few parameters, then moves the files from the source directory to the destination directory. And then it deletes files from the destination directory that are over x days old.

Wayne goes into more detail, including permissions required to run the script.

Comments closed

Restoring To A Specific Time

Published 2016-12-14 by Kevin Feasel

Derik Hammer shows one of the most useful features of database restores:

In order to restore point-in-time, you need to restore the full backup with NORECOVERY. This tells SQL Server not to initiate crash recovery which is a process that performs the redo and undo operations on your database to roll back the uncommitted transactions and roll forward the committed ones.

Once the full backup is restored you will need to restore the rest of the LSN (log sequence number) chain in the appropriate order. If you are not using differential backups, this means that you need to restore each log file until you cover the point-in-time that you are targeting. If you do have one or more differential backups, just restore the most recent differential which was taken before your target point-in-time and then all log backups between then and the target.

Derik also discusses restoring to marked transactions, something I’ve never used before but which could be very useful for known, major changes (like database code rollouts).

Comments closed

Bacup With NORECOVERY

Published 2016-12-14 by Kevin Feasel

Robert Davis explains the NORECOVERY option when running a backup (rather than restore) command:

NORECOVERY applies only to log backups. When you run a log backup with NORECOVERY it takes the normal log backup and it also puts the database into a restoring state. This means that absolutely no transactions can run in the at database that isn’t covered by the log backup. At least not until someone recovered it. So is a scenario coming clear where that might be helpful? Think migrations.

Read on for more details.  It’s not something you’ll probably do frequently, but knowing it may make a future operation smoother.

Comments closed

Upgrading Backups

Published 2016-12-14 by Kevin Feasel

Steve Jones explains that you can restore most backups to a later version, but can never restore to an earlier major version:

I would hope that most people know that a SQL Server database backup has a version. This version corresponds to a version of SQL Server, and for the most part, we can’t restore a database backup to an earlier version of SQL Server. Some exceptions might be a similar CU version there the database format hasn’t changed, but certainly not to any prior Service Pack.

However, can you restore to a later version? Can I take a SQL Server 2012 database backup and restore it to a SQL Server 2016 instance? Sure I can. In fact, lots of people upgrade their systems this way. Install a new SQL Server instance, take a backup on the old version and bring it forward. In fact, you can restore (or attach) a SQL Server 2005 database backup on SQL Server 2016.

Read on for more details and caveats.

Comments closed