Tuesday, 2 January 2018

SQL 2016 Database Scoped Configuration: MAXDOP

SQL Server 2016 Microsoft expanded the range and flexibility of database level configuration settings. There are some new features, as well as features which that can be set at more than just the server or database scope. Now, among other things, we are able to:
A summary of the new options can be found on the Technet blog.

In this post I am going to focus on the MAXDOP feature. Having read a few articles and posts about this feature it wasn't clear to me exactly how this feature worked. Especially in regard to the relationship between the instance level setting and the database level setting.

The short answer is:
The query optimiser will use the instance level setting when the database setting is 0, otherwise the database setting overrides the instance setting. I will demonstrate this below.

I'm using a VM with 4 vCPUs. Below I set up 4 different scenarios to illustrate how the various settings affect CPU usage.

Scenario 1:
SQL Server Instance MAXDOP set to max and database scoped MAXDOP set to 0 (or inactive).

Scenario 2:
SQL Server Instance MAXDOP set to max and database scoped MAXDOP set to 2.

Scenario 3:
SQL Server Instance MAXDOP set to 1 and database scoped MAXDOP set to 0 (or inactive).

Scenario 4:
SQL Server Instance MAXDOP set to 1 and database scoped MAXDOP set to 4.

I hope the above has shed some light on how this feature works. I look forward to using this feature. I am a fan of aggregating databases as much as is practical and this will certainly help as performance tuning can be that much more granular.

Monday, 18 September 2017

Master Data Services Model Cloning

Yes! My company uses Master Data Services or MDS. Yes! It's a pain. And yes! I needed to learn how to use and administer it. For anyone that hasn't had this well meant tool forced upon on them, it feels ... (if I'm being kind) ... a little unfinished.

Based on my experience MDS administration requires an awkward mix of command line tools, the MDS web interface, and even some sql scripts and stored procs executed directly on the database. There are a few strange quirks, like the fact that individual users or groups can be set as administrators, but there is a single super-user. This super-user can only be an individual and not an AD group (although I'll be happy to be proved wrong on this!). Users in the System Administration role can deploy models, but still need to be given permissions to view and edit those models by the super-user (this has changed in SQL 2016 - multiple super-users are supported as well as AD groups are now supported as super-users).

Friday, 4 August 2017

Check for SQL Server Connectivity

My company is revamping its domain structure and tightening up network security. I am one of the guinea pigs and therefore have been put into the new domain and OU groups. Obviously, we are experiencing some teething problems. As a result I can't access things, namely SQL Server instances that I used to be able to connect to from my work station.

I needed to provide the network team a list of the instances I could no longer access and I certainly wasn't going to try to connect to each one manually. We have a very large estate and it would have taken me ages.

Friday, 28 July 2017

Cleanup SQL Server Logins

Cleaning up after someone leaves. We all do it right? Our companies all have water tight policies and procedures to handle that, or maybe they have policies but not procedures. Or maybe they just kind of don't.

Well, if your company is anything like most of the places I've worked, it's somewhere in the middle. And from a DBA perspective, especially on non-production environments, your list of logins and users becomes a bit long and messy after a while.

Friday, 5 May 2017

Compress your database tables

SQL Server has offered data compression since SQL 2008. It is a very good feature but, as with almost everything SQL Server, it needs to be deployed judiciously. Data compression has been written about and reviewed by many SQL experts who are much more qualified and knowledgeable than me, so I won't go into it too much.

First, a quick summary of what the feature does as well as its benefits and effects:
As the feature's name suggests, when it is enabled on a table and/or its index(es), SQL server compresses (or shrinks) the data before it is written to the database. There are two types: ROW and PAGE.

Friday, 2 September 2016

Query To Find SQL Audit Details

I've been deploying a SQL Server auditing solution for our new SQL 2014 estate. I know, we're a few years behind. But if you saw some of the legacy systems I work with you'd understand that migrating to SQL 2014 is quite a coup!

Anyway, I found a really great audit solution that I have used as a base on Colleen Morrow's blog. I will detail what I have added to her solution in a later post. But, for now, I would like to share a query that displays some basic information about existing audits, both at the server and database level.

I put this query together for the purposes of documentation. Our 2014 estate is getting large fast. And with so many installations, we need to keep track of what is installed and configured on all our servers.

Monday, 1 February 2016

Restarting a service with PowerShell

We use Redgate's SQL Monitor to keep an eye on our mission critical servers. I like the product. It is reasonably user-friendly, reliable and it helps my team monitor our servers efficiently. I've used other products before and I think SQL Monitor compares favourably. No, I'm not paid by RedGate!!!

With that in mind, my team has been struggling a bit with an ongoing issue: every Monday morning one of the SQL Monitor processes was running amok and maxing out CPU! This was strange, as the product is generally pretty stable. After some investigation, we realised that the security team had started performing regular vulnerability scans over the weekends. Part of this test involved intentional failed login attempts. SQL Monitor can't seem to handle this, and the web server, specifically the "xsp4" process goes haywire and trashes the CPU.

Monday, 7 December 2015

Databases With No Recent Backups

I've written the script below to easily identify databases that have not had backups taken within a certain amount of time. This could be useful if you need to build your own monitoring. However, even if you use a third party monitoring tool,as I do, it is helpful to run this query on the server for which I've received an alert.

Many of the servers I manage have a large number of databases. The alerts I receive for overdue backups are database specific. But, in general, if one database is overdue, chances are something has happened on the server. So this query can give me a quick overview.

Wednesday, 25 November 2015

Script to Alter File Growth

I recently inherited a large estate of SQL Servers. Over the years, before I started, it grew organically and was managed by an insufficient number of accidental DBAs. As a result many SQL server instances were built and left with their default settings.

One such setting is file growth, which, tends to be set either at a growth rate of 10% or 1MB. Given the number of databases that can be on a single server I wrote a little script to aid me in making the changes.

I realise that every server and database has different requirements. I am using this script to get my databases and servers to a basic level of "best practice" and avoid any performance issues that may arise.

Friday, 11 September 2015

Finding When A SQL Agent Job Ran

A quick post for today since I haven't added anything in quite a while.

Recently, I was investigating a deadlock that was occurring, like clockwork, on a particular server. A trace revealed that it was being executed by the SQL Server Agent account. The host machine that executed a query involved in the deadlock is our SSIS host. Therefore, there are loads of jobs with a dizzying array of schedules to weed through.