Press "Enter" to skip to content

Curated SQL Posts

The Importance Of Powershell

Published 2017-01-20 by Kevin Feasel

Kevin Hill explains why he’s advocating that DBAs learn Powershell:

2 very solid reasons (there are others) that every DBA should be learning and using PowerShell:

1 – Its very useful for admin at the O/S level.

At my current client I am team lead of System and SQL Admins, along with doing any of the work that comes our way.  This means we need to be able to manage the modest server farm we have.  Its big enough that we can’t log onto every server every day, but small enough nobody wants to buy a proper monitoring toolset.  So…PS to the rescue!

Read on for the other reason.  I think the relatively poor Powershell tooling with SQL Server (with respect to other groups like Exchange) limited general acceptance, but they’ve made some big improvements over the past year and there are some sharp minds in the community working to make Powershell even more important for DBAs.

Comments closed

ApplicationName On Invoke-SqlCmd2

Published 2017-01-20 by Kevin Feasel

Andy Levy improves the Invoke-SqlCmd2 cmdlet:

I decided to change this around so that it no longer uses string formatting, but instead a SqlConnectionStringBuilder. I had a couple reasons for this:

  • It will eliminate redundant code. There are several common elements in each of the ConnectionStrings above. If more complex logic is needed, there are potentially more copies of this ConnectionString kicking around.

  • It’s prone to copy/paste and other editing errors. If there’s a change that affects both versions of the ConnectionString and the developer just copies the line from one branch of the if statement to the other, code will be lost or invalid values will be substituted because of positioning.

This is something I’d like to see make it to the main cmdlet.

Comments closed

Switching Instead Of Renaming Tables

Published 2017-01-20 by Kevin Feasel

Kendra Little has an interesting solution to when you need to swap out an old table for a new version:

This pattern works in SQL Server 2014 and higher. And it even works in Standard Edition of 2014.

Some folks will see the word ‘Switch’ in this pattern and assume the pattern that I’m suggesting is Enterprise Edition only for versions before SQL Server 2016 SP1.

However, oddly enough, you can use partition switching even in Standard Edition, as long as the tables only have one partition.

And all rowstore tables have at least one partition! That happens automagically when you create a table.

Read the whole thing.

Comments closed

Data Professional Survey Results

Published 2017-01-20 by Kevin Feasel

Brent Ozar has a roundup of blog posts concerning the data professional survey for 2017:

We asked to see your papers, and 2,898 people from 66 countries answered.

Download the raw data in Excel, and you can slice and dice by country, years of experience, whether you manage staff or not, education, and more.

Community bloggers have already started to analyze the results:

There were several entrants and some good posts, so check it out.

Comments closed

Finding Suspect Objects

Published 2017-01-20 by Kevin Feasel

Wayne Sheffield shows how to find which specific objects are suspect without running a full CHECKDB:

You’d really like to know what tables are affected without having to wait. Luckily(?), this corruption was recorded in msdb.dbo.suspect_pages, and having just recently read Paul Randal’s post here, we know we can use DBCC PAGE to determine this information. And, after having read my prior blog post, you know that we can automate DBCC PAGE, so we can use our new friend “WITH TABLERESULTS” to find out what objects have been corrupted.

The suspect_pages table, documented here, has three particular columns of interest: database_id, file_id and page_id. These correspond nicely to the first three parameters needed for DBCC PAGE. To automate this, we need to know what information we need to return off of the page – and from Paul’s post, we know that this is the field “METADATA: ObjectId”. For this code example, let’s assume that this corruption is on page 11 of the master database (just change “master” to the name of your 2TB database).

Read on for a script, including a script which checks all such suspect pages, and the possibly-better solution as of SQL Server 2012.

Comments closed

Finding Transactions After A Crash

Published 2017-01-19 by Kevin Feasel

Paul Randal has a procedure which will find rolled-back transactions after a crash:

Then we can search in the transaction log, using the fn_dblog function, for LOP_BEGIN_XACT log records from before the crash point that have a matching LOP_ABORT_XACT log record after the crash point, and with the same transaction ID. This is easy because for LOP_BEGIN_XACT log records, there’s a Begin Time column, and for LOP_ABORT_XACT log records (and, incidentally, for LOP_COMMIT_XACT log records), there’s an End Time column in the TVF output.

And there’s a trick you need to use: to get the fn_dblog function to read log records from before the log clears (by the checkpoints that crash recovery does, in the simple recovery model, or by log backups, in other recovery models), you need to enable trace flag 2537. Now, if do all this too long after crash recovery runs, the log may have overwritten itself and so you won’t be able to get the info you need, but if you’re taking log backups, you could restore a copy of the database to the point just after crash recovery has finished, and then do the investigation.

Read on for the code, as well as a test.

Comments closed

Lead Blockers

Published 2017-01-19 by Kevin Feasel

Kenneth Fisher talks about fullbacks:

Blocking is just part of life I’m afraid. Because we have locks (and yes we have to have them, and no, NOLOCK doesn’t avoid them) we will have blocking. Typically it’s going to be very brief and you won’t even notice it. But sometimes you get a query or two blocked for long enough to cause a problem. Even more rarely you end up with a long chain of blocked sessions. Session 100, 101, and 102 are blocked by 67 which is blocked by 82, which is blocked by … Well, you get the idea. It can be very difficult to scan through all of those blocked sessions to find the root cause. That one or two session(s) that are actually causing the problem. So to that end I’ve written the following query. Among other things it will return any lead blockers, how many sessions are actually being blocked by it, and the total amount of time those sessions have been waiting. It will also give you the last piece of code run by the that particular session. Although be aware that won’t always tell you exactly what code caused the blocking.

Click through for the script.

Comments closed

Restoring An Encrypted Master Database

Published 2017-01-19 by Kevin Feasel

Dave Mason clarifies how to restore the master database to a new instance when the master database backup is encrypted:

This strikes me as an odd chicken-and-egg problem. I’d need to create the certificate to decrypt the [master] backup on the instance I’m restoring [master] to…and the certificate is stored in [master], which I’d be overwriting. As weird as it sounds, this is exactly what needs to happen. Maybe it’s not as complicated as it sounds.

Read on for the solution.  You might also want to check out that one time he met Larry Bird.

Comments closed

In-House Power BI

Published 2017-01-19 by Kevin Feasel

Paul Turley reports that the bits to publish a Power BI report to SSRS are in the latest vNext preview:

“Power BI reports in SQL Server Reporting Services: January 2017 Technical Preview now available”  This feature addition will allow Power BI reports to be published to a local SQL Server Reporting Services server, entirely-on-premises without using the Power BI cloud service.

The January 2017 Technical Preview can be downloaded from: https://www.microsoft.com/en-us/download/details.aspx?id=54610

Reza Rad shows us installation and deployment:

We are in a world that rapidly running towards cloud. Your files are in Dropbox, or OneDrive these days, Your photos uploaded to a cloud storage, your emails are all backed up in a cloud backup media, and I’m in this thinking that in next few years, we might eat our food from a cloud kitchen! However there are still businesses and companies who require some on-premises solutions, and as long as a requirement exists, there should be an answer for it. Power BI for On-Premises bring the power of self-service, interactive reports of Power BI to these businesses. Power BI for On-premises is a great big step towards utilizing better data insight in all environments.

This will probably help more companies than you might think—Power BI is really useful as a reporting tool, but it can be hard getting sign-off to go to Azure.

Comments closed

Perfmon Counters Of Interest

Published 2017-01-19 by Kevin Feasel

Allen White shares his list of interesting perfmon counters:

Paging File(_Total)\% Usage

When Windows runs out of memory it takes large chunks of memory and swaps it out to disk, to the Paging File. Unfortunately, the slowest operation in all computing is writing to disk, regardless of the physical media involved, so swapping memory to disk is naturally going to slow down the performance of your system. Keeping an eye on this counter will help you know when you are encountering memory issues, and you can then take action to resolve the conflicts.

There are dozens of interesting counters you could use, but I appreciate that Allen stuck with 15.

Comments closed