Press "Enter" to skip to content

Category: Administration

Logging WhoIsActive Output

Published 2016-07-27 by Kevin Feasel

Tara Kizer has a primer on storing WhoIsActive outputs for subsequent analysis:

Create a new job and plop the below code into the job step, modifying the first 3 variables as needed. The code will create the logging table if it doesn’t exist, the clustered index if it doesn’t exist, log current activity and purge older data based on the @retention variable.

How often should you collect activity? I think collecting sp_WhoIsActive data every 30-60 seconds is a good balance between logging enough activity to troubleshoot production problems and the storage needed to keep the data in a very busy environment.

I like having something like this in place because often times, when you need these results, it’s already too late.

Comments closed

Upgrades And Regressions

Published 2016-07-26 by Kevin Feasel

Kendra Little explains when upgrades can cause performance to suffer:

The cluster’s servers and SQL Server configurations were built to be as close to identical as possible to the previous instance (memory, cores, disk, maxdop, CTP, etc).

After the migration, I noticed that CPU utilization jumped from the normal 25% to a consistent 75%.

I did several other migrations with similar server loads with no issues, so I’m a bit puzzled as to what might be going on here. Could the upgrade from SQL Server 2008 R2 to SQL Server 2012 simply be exposing bad queries that 2008 was handling differently?

Kendra goes through a number of reasons, building a troubleshooting guide in the process.  This is a great read.

Comments closed

SQL Server’s Basic Installer

Published 2016-07-20 by Kevin Feasel

Derik Hammer walks through the new SQL Server Basic Installer:

When using the Basic Installer only the database engine and SQL Client Connectivity SDK are installed. I find this better than using the advanced installer’s Install with all defaults option. Typically any local user will not need Reporting Services, Integration Services, Analysis Services, or any other feature available.

I was a bit concerned about this when it was first announced—the default installer already allows you to make too many poor decisions—but I think it works within the use case Derik describes:  developers installing a local edition on their dev boxes.

Comments closed

Stretch Database

Published 2016-07-19 by Kevin Feasel

SQL Padre checks out Stretch database:

As you can see, we now have a new path in our query plan with an operator called “Remote Query”.  Basically the local server queries the remote query then using the local Primary key Concatenates them back together to produce the desired result. So can we update the data?

Nope, sure can’t.  Once the data lives in Azure, the data is READ ONLY.

Check it out.  He’s a bit more sanguine about Stretch than I am, so maybe it will fit your use cases.

Comments closed

Auditing Power Plan Settings

Published 2016-07-15 by Kevin Feasel

Drew Furgiuele writes a bit of Powershell to control power plan settings and expands upon this one-liner:

It’s a classic one-liner, but if you’re not used to reading it I’ll break it down for you. First, we useGet-ChildItem  to return a list of registered servers in our central management server (named PRO-CM-SQL in my example). This returns a series of objects that lists all the registered names on the central management server in each directory, so we need to filter out the directory names with a Where-Object  (objects that don’t have a “mode” value of “d” for directory). Once we have our list, we just select the names ( Select-Object ). Then we pipe the list of names over to a ForEach-Object  and execute the script each time. Finally, we tack on a Export-CSV  cmdlet to output the results to an easy to read file we can open in Excel (or notepad, or whatever).

Our script also doesn’t control output, so you leave that up to the user. They can put it on the screen or pipe it to a file. And that’s an important style point: never put your users on rails. You may like output one way, but someone else may not. Just think of the next person.Because some day you might be that next person.

This is a good post if you need to figure out how to find your servers’ current power settings, but a great post if you want to think about how to write helpful Powershell scripts.

Comments closed

More On DBCC CLONEDATABASE

Published 2016-07-15 by Kevin Feasel

Melissa Connors digs into the DBCC CLONEDATABASE command:

Finally, keep in mind that because the clone is a read-only, empty database, you should be able to test repeatedly without updating statistics and skewing your results. Since I wanted to see this for myself, I executed a set of updates and selects against the SQLSentryData and SQLSentryDataClone databases. As a result of the lack of data and read-only database status, there were no actual updates in SQLSentryDataClone. Consequently, the statistics were updated in the SQLSentryData database, but remained the same in the SQLSentryDataClone database:

Read the whole thing.

Comments closed

A Wish For Shutdown Procedures

Published 2016-07-13 by Kevin Feasel

Dave Mason wants shutdown procedures like how we can have startup procedures:

If ‘shutdown’ was valid for the @OptionName parameter, I know exactly what I’d do: configure sp_procoption to execute a stored proc that sends an email alert. It would be great to know when a SQL instance goes down, wouldn’t it? Looking at the log file, we can see entries indicating the instance was shut down:

Dave does mention potential failure scenarios, but I agree:  it’d be nice to have the ability to run procedures at shutdown to perform certain actions.

Comments closed

DBCC CLONEDATABASE

Published 2016-07-12 by Kevin Feasel

Kennie Pontoppidan talks about a new DBCC command:

This command enables you to take a copy of the schema and the statistics for an existing database into a new database. According to the documentation, DBCC CLONEDATABASE

  • Creates a new destination database that uses the same file layout as the source but with default file sizes as the model database.

  • Creates an internal snapshot of the source database.

  • Copies the system metadata from the source to the destination database.

  • Copies all schema for all objects from the source to the destination database.

  • Copies statistics for all indexes from the source to the destination database.

I knew there were ways of scripting out statistics, but DBCC CLONEDATABASE looks like a new and interesting beast.

Comments closed