Press "Enter" to skip to content

Category: Administration

DB File Space Usage

Published 2016-05-11 by Kevin Feasel

Andy Mallon looks into sys.dm_db_file_space_usage:

SQL Server 2016 is coming out next month, and it’s chock full of new features that I can learn. Row-level security is interesting. Dynamic data masking sounds fun.

If you’re a regular reader of my blog, you probably know I try to approach questions from a unique angle. Instead of blogging about something cutting edge or sexy, I decided to scroll through the list of system views until I found one I didn’t recognize.

sys.dm_db_file_space_usage

The name is pretty self-explanatory, but I never noticed this existed until now. Seems like the type of DMV that I should have known about, but I didn’t. Quick look at BOL, and I got the verbose description from Microsoft:

Andy goes on to compare the outputs from this DMV to methods he’s historically used.

Comments closed

Large Sorts And Hashes

Published 2016-05-10 by Kevin Feasel

SQL Sasquatch looks at a scenario in which large sorts or hash operations can cause CPU to skew compared to page lookups per second:

The graph above has tempdb footprint (light blue) stacked on top of used query memory (dark blue) against the left vertical axis.  The green period has very limited use of query memory.  During the yellow period, a moderate amount of query memory was used.  During the red period, a large amount of query memory was used and at a number of points operations spilled into tempdb.  As query memory was used more extensively, the CPU:lookups/sec correlation was more disrupted.

Once fully considered, this makes sense: query memory is “stolen” from the database page buffer pool.  References to pages in the page pool are “page lookups”, but each time stolen query memory is poked and prodded… well, that’s not a page lookup.  But it has CPU cost.

Check out the whole thing; this is a thoughtful look at an interesting data oddity.

Comments closed

Traces Can Be Expensive

Published 2016-05-09 by Kevin Feasel

Matt Slocum warns against having too many server-side traces running:

Before the client took the last ditch effort of just restarting SQL Server, I checked traces.  There were 9 user traces collecting a ton of trace events.

I manually killed them all and suddenly performance returned to normal.  Phew!  Crisis averted.

As a follow up from that issue, I created a script that stops and then deletes all user traces.  We are discussing setting up a job to run this script periodically to keep traces at bay and I am educating the group on proper trace discipline (and Extended Events).

Even Extended Events aren’t free, so the same advice applies.

Comments closed

Remote Server Installation Using Powershell

Published 2016-05-05 by Kevin Feasel

Slava Murygin gives tips on using Powershell and task scheduler to schedule remote SQL Server installations:

Finally I’ve nailed down that topic and hopefully that will be my last post dedicated to SQL Server installations on Windows Core.

In this post I will show how it is easy to install SQL Server from a remote computer without remoting to a server, without any GUI, just by using simple command line.

I admit that setting up installation as a scheduled task on the remote machine is not something that ever came to mind before.

Comments closed

Azure SQL Database Management With Powershell

Published 2016-05-05 by Kevin Feasel

Mike Fal shows a few administration steps with Azure SQL Database, including resetting an admin password:

Walking through this, we just need to create a secure string for our password and then use the Set-AzureRmSqlServer cmdlet and pass the secure string to -SqlAdministratorPassword argument. Easy as that and we don’t even need to know what the previous password was. With this in mind, I also want to call out that you can only change the password and not the admin login name. While this is not such a big deal, be aware that once you have an admin login name, you are stuck with it.

Mike promises that his next blog post won’t take a month to publish.  Here’s hoping he’s right.

Comments closed

Installing Windows Core

Published 2016-05-03 by Kevin Feasel

Slava Murygin has a screenshot-heavy walkthrough of how to install Windows Server 2016 Core in Hyper-V:

You are supposed to have pre-downloaded Windows Server Installation ISO image.
You can download Evaluation Windows Server from here: https://www.microsoft.com/en-us/evalcenter/evaluate-windows-server-technical-preview
For this example I’ve chosen Windows Server 2016 Technical Preview 5.
Note: Do not try to use 64-bit installation on 32-bit workstation. It won’t work.
After you specify the file click “Next”.

Read the whole thing.

Comments closed

Compatibility Level 130 With Legacy Cardinality

Published 2016-04-29 by Kevin Feasel

Jos de Bruijn notes that there is a way to use the legacy cardinality estimator even when you are in Compatibility Level 130 (new in SQL Server 2016):

In SQL Server 2016 there are a lot of goodies under Compatibility Level 130, such as performance improvements with Columnstore and In-Memory OLTP and all the Query Optimizer enhancements done over the years under Trace Flag 4199. So we are faced with a situation where there are workloads that can benefit from some enhancements that come with Compatibility Level 130, but that cannot use the new CE.

The solution is to use one of the new database-scoped configuration options, namely the Legacy Cardinality Estimation option. You can enable this using the following ALTER DATABASE command:

I’ve had a very positive experience with the new cardinality estimator, but I certainly appreciate the option being there just in case.

Comments closed

Conditional Alerting

Published 2016-04-29 by Kevin Feasel

Dave Mason revs up SQL Server alerts using tokens and conditional responses:

There are three tokens within the T-SQL (highlighted in yellow above): A-MSG, DATE, and TIME. SQL server replaces these three tokens as follows:

 

  • A-MSG: Message text. If the job is run by an alert, the message text value automatically replaces this token in the job step.
  • DATE: Current date (in YYYYMMDD format).
  • TIME: Current time (in HHMMSS format).

See the MSDN documentation for a list of tokens and their descriptions.

 

This is a great way of being smarter with alerts.  Your SQL Server instance has a lot of information at the ready, so get familiar with what’s up for offer.

Comments closed

How To Shrink A Database

Published 2016-04-29 by Kevin Feasel

If “shrink that database” makes you cringe, that’s a good sign.  Nevertheless, sometimes it has to happen.  On that rare occasion in which it needs to happen, Andy Mallon gives you a how-to guide:

Perhaps you just implemented data compression, and have 60% free space in your database.
Perhaps you just deleted a bunch of old data, and have significant free space in your database.
There are a handful of scenarios where you have free space in the database, you’ll never use it, and you need to shrink. Just don’t make a habit of it.

Shrink should not be part of your normal routine, but it does come in handy sometimes.  Auto-shrink, though?  Burn that to the ground.

Comments closed