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:

• set certain options both at the instance and the database level (MAXDOP)
• set instance level trace flag settings at the database level (LEGACY_CARDINALITY_ESTIMATION)
• the Trace Flag 1117 is now set at the FILEGROUP LEVEL
• the brand new database level CLEAR PROCEDURE_CACHE

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.

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.

SQL Server Optimization

This post aims to aggregate a lot of the best practice or otherwise recommended configuration for SQL Server performance. It is meant as a summary or quick-reference sheet. All of these concepts are discussed at length all over the internet, so I will not spend much time explaining them. 

As with any recommendations of this sort please use them carefully and test before deploying into a production environment. Also, depending on your specific environment's configuration, not all of the below may be advisable or bring about clear benefits. To quote Jonathan Kehayias: "While there is a lot of good guidance available for how to best configure SQL Server, the specifics of any given implementation is a very big “It Depends…”" What this post should achieve is providing the reader with an awareness of features and options that influence a SQL Server instance's operation. 

Add an Operator to All SQL Server Agent Alerts

I was investigating a new server I'd been assigned, looking for gaps in configuration using the trusty sp_Blitz tool. The organisation that owns this server instance is small and doesn't use third-party monitoring. I decided to add some basic alerting as recommended by Brent Ozar and Co. They've kindly provided a script to do this: Blitz Result: No SQL Server Agent Alerts Configured.

A little later on I realised that I needed to add an additional operator to these alerts and was loath to step through them one at a time and tick the appropriate box. So, I knocked out a quick and simple script (I've included two versions, one that adds the operator to all alerts and one that allows you to select a subset). I hope it is helpful.

Running DBCC CHECKDB on TEMPDB

First, a confession. I've been doing it wrong for a long time. Only recently have I learned that TempDB should be included in regular CHECKDB maintenance. Ok, now that I've got that off my chest, I can continue.

So now I've been diligently setting up this task on all servers. NB: TempDB is not available within Maintenance Plans, so you'll need to write the tsql in a job. I add it as a second step in my dbcc maintenance plan job.

Full List of SQL Server 2014 DMVs

I'm not sure how useful this will be to others, but I keep searching for a single, comprehensive list of all the SQL Server DMVs with short descriptions. I am looking to complete a certification and this should prove handy for studying. This is certainly no replacement for the full descriptions and examples on MSDN, but I thought it worth putting together.

Automating Sliding Partition Windows - Part 4

Recurring Tasks

In the previous posts of this series I both explained the concept of the sliding window partition as well as the way I have configured my system for an automated sliding window. This post will delve into the automated part of the process, the tasks that recur on a regular, in my case monthly, basis.

I have decided to use an SSIS package to execute the various steps of the sliding window, but it is just as valid to wrap the steps into cursors or loops (obviously, you only need a loop if you have more than one table, partition scheme and/or function in your database). Depending on how much you plan on automating there are some challenges in using SSIS. I will be explaining how I've constructed my SSIS package, but it's outside the scope of this post series to explain how to use SSIS. There are many resources on the web that can help you if needed.

An SP to Fix Logshipping After Adding a New Database File

UPDATE: 22 Jan 2014 - Fixed some bugs, added multi-file support and added a few notes

Every month I need to maintain the various sliding partition windows on several databases. If you've read some of my previous posts you may remember that this involves adding new files for the upcoming partitions. My company's databases are also logshipped as part of the disaster recovery strategy.

What invariably happens every month is I add new files make the necessary partition changes and then wonder why, a little later in the day, I'm receiving alerts for failed transaction log restores. The logshipped database obviously doesn't "know" about the new file and I end up with an error message like this:

Answer to the Partitioned Index Puzzle

A few weeks ago I posted a description of an error I encountered when creating a partitioned index on a SQL Server 2005 Standard Edition instance. It had me quite literally tearing my hair out. The error was a typically vague Microsoft one:

Msg 4436, Level 16, State 12, Line 1

UNION ALL view 'PartWindow.dbo.vwPartitionedView' is not updatable because a partitioning column was not found.

I spent quite a few hours staring at my SQL statements and re-reading documentation before the solution hit me.

If you haven't had a chance to read the post, please do before you skip down to the answer. It was, unfortunately, a total "duh!" moment. But at the same time quite easy to overlook.

A Quick Partitioned View Puzzle

I needed to create a few partitions on a legacy SQL Server 2005 Standard Edition instance. As partitions are not supported on standard edition, I needed to go the partitioned index route. In my research into how to  implement them, I found some really good resources:

• SQL Server Partitioning without Enterprise Edition
• MSDN: Using Partitioned Views

I'm not going to explain the basics of partitioning here, so you may want to read the above pages if you're not familiar with it before continuing. 

ISNULL vs. COALESCE

A friend of mine just asked me when COALESCE should be used. As I'm a DBA and don't do too much SQL development work, I never had a need to use COALESCE (or I never thought I had a need). Therefore, I was inspired to do a little digging. As there has been much written about the similarities and differences I will use this post as a jumping off point to a few good resources that shed light on the topic. Though, I'll highlight some of the points here as well.

SQL Server 2005+ Performance Tuning - Part 2

This series of posts aims to provide an introduction to resolving big and sudden performance degradation.
In my last post I covered the reasons for poor performance and the ways of pinpointing performance bottlenecks.
In this post I will detail some of the ways of resolving those performance bottlenecks.

SQL Server 2005+ Performance Tuning - Part 1

This next series of posts aims to provide an introduction to resolving big and sudden performance degradation. I will also add a few tips for general performance related database best practice and tidying.

Remove a Cursor From a Stored Procedure - Part 2

Why is it so slow?!?!?

In my last post, I used a stored procedure from a database I manage to illustrate a real world example of converting a cursor into set based SQL. In this post I will look into a the mechanics behind the scenes and explain at least one of the reasons why it is generally unwise to use cursors when a set based query will provide the same results.

Remove a Cursor From a Stored Procedure - Part 1

A huge problem DBAs encounter with system performance is the ever-present cursor. While there are some scenarios where cursors are unavoidable (or at least difficult to avoid), many instances are down to the developer being more comfortable writing cursors - or some myths as to why a cursor is better. That said, here is a short post highlighting some cursor pros or (non-cons): When are TSQL Cursors the best or only option?

In my organisation, and no doubt many others, the problem stems from the fact that the developers writing SQL are not SQL developers. Rather they are [insert relevant coding language] developers. And in many programming languages processing is best performed on a row-by-row basis. Not so SQL, especially T-SQL.

Logon Triggers

I just learned of a nice feature, Logon Triggers. A nice example of what they can do is limit a specific user to a certain number, 3 for example, concurrent logons. A nice little example of how to do this is as follows: Let's say we have a login name of login_test.

The interesting feature of this type of trigger is that the server authenticates the login but BEFORE the user is connected to the server or database the trigger is fired and the appropriate action is taken.

See the full explanation of logon triggers at http://msdn.microsoft.com/en-us/library/bb326598.aspx

There's also a good explanation of how these triggers relate to trace events and event notifications.

See my other post detailing a good use for such a trigger.

Comparing Rows in a Table

As all SQL professionals will know sqlservercentral.com is an amazing resource. In an entry titled Linking to the Previous Row the author, David McKinney, describes a simple way to compare rows within a table using the two functions introduced in SQL Server 2005: Common Table Expressions (CTE) and Rownumber().

To be honest, until I read this article, I didn't see much of a use for CTEs other than recursive queries (for more on that see: Recursive Queries in SQL Server 2005). But this example provides a tidy method for comparing, for instance, changes over time.

And if you don't already know, the Rownumber() function allows you to include, as the name suggests, a column of row numbers in your result set based on a sort order that you determine. In addition, the PARTITION BY allows you to group the results.

I will definitely be employing this

Parse a Comma Separated List

Here is a simple user-defined function to parse a comma separated (or any other character separated) list and return a table of the values in the list. The input variable must be a text data type (varchar). It returns the results as varchar so depending on your needs you may need to convert to int or datetime, etc.

A Quick Cheat to Create a Comma Separated List

The select statement below is an easy way of creating a comma separated list within a larger select query without the need for a cursor, user defined function or even a paramenter. It exploits the 'for xml' statement. By not defining any xml elements the column you select gets concatenated into a single line.

In my scenario I have keywords that can be members of multiple adgroups. Since my revenue figures are aggregated by keyword and not broken down by adgroup I need to display all adgroups in the same row as the keyword or I will get duplicate revenue figures.
I owe a lot to the colleague who showed this to me. It's saved me tremendous amounts of time and trouble over the past few years. I hope you find this as helpful as I did.

List tables files and filegroups

Here's a simple query to get a list of database tables and the files and filegroups they are assigned to:
I found this helpful tip on blog.sqlauthority.com and added a few little bits for better readability.