Press "Enter" to skip to content

Month: December 2015

Optimizing Update Queries

Published 2015-12-22 by Kevin Feasel

Paul White has an article.  Read it:

The point is that there is an awful lot more going on inside SQL Server than is exposed in execution plans. Hopefully some of the details discussed in this rather long article will be interesting or even useful to some people.

It is good to have expectations of performance, and to know what plan shapes and properties are generally beneficial. That sort of experience and knowledge will serve you well for 99% or more of the queries you will ever be asked to tune. Sometimes, though, it is good try something a little weird or unusual just to see what happens, and to validate those expectations.

Optimizing update queries seems trivial at first, but as Paul shows, we have a few more tools at our disposal than is apparent at first glance.

Comments closed

Identifying Blocked Processes

Published 2015-12-22 by Kevin Feasel

Priyanka Chouhan talks about identifying and handling blocked processes:

In order to maintain data integrity within the database, locks are used on resources like tables, rows, pages etc. by any process that wishes to use them. This is done to ensure multiple process don’t alter the same resources at one time leading to data inconsistency. When a process wishes to lock a resource, it sends a request to the server and the server grants it. However, when a process requests lock on a resource that has already been locked by another process, the request is denied. The requesting process is thus placed on “hold” until the resource it is requesting for isn’t released. In this situation, the requesting process is called a blocked process, and such a process could put a halt on other subsequent processes and activities scheduled on the server.

Thus identifying a blocked process and releasing it requires a DBA team to check the application database blocking. Additionally, here are some other techniques that may be used to find out which processes are creating a block on the server:

My favorite method, not mentioned, is Adam Machanic’s sp_whoisactive.

Comments closed

Get Backup History

Published 2015-12-22 by Kevin Feasel

David Alcook grabs backup history:

It’s a very common task that we have to query backup timings and other bits of info from msdb.

Now its pretty straight forward to select this data for a particular database or use the MAX function for example to return the last backup but how do we get the last 5 FULL backups per database?

MSDB has a lot of useful backup information, so if you’ve never dug into it, I recommend taking your time and seeing what is available.

Comments closed

Git Support In SQL Source Control

Published 2015-12-22 by Kevin Feasel

Mickey Stuewe looks at Git support in Red Gate’s SQL Source Control tool:

At the beginning of October 2015, everything changed. That was when Redgate announced SQL Source Control users could now push to and pull from remote Git repositories.

And not just from within SQL Source Control – from within SQL Server Management Studio (SSMS).

Your developers are probably using Git, so you should too.  If your developers are using Mercurial, I applaud them.  If they’re using SVN, CVS, TFVC, Vault, or anything else (Visual SourceSafe?), flip a coin and decide to use Git/Mercurial or the generally accepted tool…

Comments closed

WMF 5 RTM

Published 2015-12-21 by Kevin Feasel

Windows Management Framework 5.0 is available, says Max Trinidad:

Finally! The Windows Management Framework version 5.0 RTM is available for download for all down level Operating systems: Windows 7, Windows 8.1, Windows Servers 2008 R2, Windows Server 2012, and Windows Servers 2012 R2.

There are several interesting features here.  My favorite one is “Just Enough Administration (JEA)”; after all, who wants too much or too little administration?

Comments closed

Availability Groups Over Mirroring

Published 2015-12-21 by Kevin Feasel

Kendra Little explains why Always-On Availability Groups are better than database mirroring:

SQL Server Availability Groups are growing up. SQL Server 2016 adds more features and improvements, and these include options to run SQL Server in different domains, or without a domain.

That brings Availability Groups closer to feature parity with Database Mirroring in SQL Server 2016.

The recent improvements caused me to think about the ways Availability Groups are better than Database Mirroring that aren’t listed as “big features”. (By “big features” I mean having more than one replica, having multiple databases fail over in a group, having multiple failover partners in SQL Server 2016, readable secondaries, having a basic load balancing concept in SQL Server 2016.) I think there’s a few.

Thinking about this for a small business with just enough technical expertise to get by but no dedicated DBA, mirroring seemed like a better choice because there were fewer moving parts.  With 2016 and AG bugfixes, that might change the calculus.

Comments closed

Using Extended Events For Login Tracking

Published 2015-12-21 by Kevin Feasel

Steve Jones shows us how to track logins with Extended Events:

I can select any number of fields for capture. You can see I’ve picked the client_hostname. I would also add the NT_Username and Username from the list. You could add more, but in this case, I’m more concerned with seeing who’s logged in.

I could add filters, but I choose not to. I click on Data Storage to determine where to store this data.

If you’re not already familiar with Extended Events, that grace period is slowly slipping away.  Profiler’s going away sometime, and it’ll be a rude shock for a lot of DBAs.  Don’t be one of those…

Comments closed

Scrubbing Data

Published 2015-12-21 by Kevin Feasel

Tom Norman has a series going on scrubbing data before moving it to lower environments.

Part 1:

Have you ever heard, “but it works on my machine”? Is this because of data perfection in Development and QA or having specific failure conditions? Can you think of all the data scenarios that accompany Production data? What about performance? Why did the application fail? What happens if I add this index?

Here are the reasons I believe you should get a scrubbed version of your production database into your Development, QA and UAT environments.

Part 2:

All of us have Production database servers and hopefully you also have additional database servers for Development, QA and UAT. Some IT shops will also have a Continuous Integration server and maybe other servers. If you only have Production servers this needs to be addressed and is outside the scope of this post. In the locations where I have worked, we also have a Scrub server. The question is, when a script executes, do you know what environment the query is executing in? Most scripts will not care what environment the script executes in but other scripts could cause damage in a Production environment. For example, if the script is removing email addresses so you don’t spam your clients with automated email messages, you would not want the script to execute in a Production environment.

So how do you make your database server environmentally aware?

 

The concept of a dedicated scrub server is interesting; it’s not something I’ve thought about before.  I’m looking forward to seeing the rest of the series.

Comments closed