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.

Defragging Indexes Without Losing Index Stats - Part 5

The stored procedure

/*********************************************************************************
UPDATE: Bug fixes and increased FILLFACTOR control!
*********************************************************************************/
The first 4 posts of this series covered the concepts of recording index usage statistics for performance tuning while continuing to maintain a database’s tables and indexes. They also presented the individual SQL scripts to perform the various parts of that process.  The most recent post put all of those scripts together into a single set of queries that could be run on a single database.

Most of us, however, have more than one database on a SQL Server instance. Therefore, deploying that last script on a large number of databases is a bit messy. Any modifications will be difficult to manage.

In this post I will present a sample execute statement to run the stored procedure. It is designed to be run centrally, and will loop through all the databases in the instance (there is a parameter to include/exclude the system databases). I deployed the SP onto my Admin_DB where I also store the tables that keep the index usage statistics and table maintenance history.

Defragging Indexes Without Losing Index Stats - Part 4

Generate UPDATE STATISTICS scripts

In the previous post of this series I provided the script that both determines the indexes that need attention and builds the individual ALTER INDEX scripts. So, now that the indexes have been tended to it’s time to do the same for table and index statistics. 

The update statements created are based on the defrag type carried out on the table as well as a few thresholds to determine whether it is necessary to rebuild the statistics. Those thresholds are:
- (Time passed since last update: >3 days
- And Minimum row count of the table: >=1000 rows
- And Percentage change in number of rows: >20% more or less)
- Or Time passed since last update: >30 days

Script out the indexes in a database

I'm just about to begin a big performance improvement project. I'll be starting with an evaluation of indexes. This will involve synchronizing a development environment's indexes with those that are in live. I need to, however, keep a record of the existing indexes on that dev environment.

So I've written the code below. It will return a table with the object_id, table name, index name, index_id and a create index script. This script includes the drop and create statements for both primary keys and unique constraints. There are quite a few good scripts out in the wider web world, but I needed a few specific things so I just wrote my own.

Defragging Indexes Without Losing Index Stats - Part 3

In Part 1 and Part 2 of this series I discussed synchronizing index maintenance and index usage statistics logging as an important part of a DBA's performance tuning routine and the script for saving a database's index usage statistics. This part of the series will go into the query that both identifies the indexes that are fragmented and dynamically generates the defrag scripts.

Defragging Indexes Without Losing Index Stats - Part 2

In my last post, I introduced the concept of synchronizing index maintenance and index usage statistics logging as an important part of a DBA's performance tuning routine. In this post I will start delving into the individual scripts involved.

UPDATE: The full script (see link at end of post) has been both updated and tested (a bit)
                  I've also augmented the script below to clean up the atblIndexUsageStats table.

As a recap, below are the main steps involved:

1. Log Index usage stats
2. Determine which indexes need defragging and generate scripts
3. Generate update statistics scripts based on step 2
4. Log defrag operation to maintenance history table
5. Execute defrag and update stats scripts
6. Cleanup logging tables (optional)

Defragging Indexes Without Losing Index Stats - Part 1

Introduction

As part of my regular performance tuning and maintenance schedule I rely heavily on queries that interrogate the DMV sys.dm_db_index_usage_stats. Based on this DMV I can judge whether indexes should be removed, modified or left as they are. See my previous blog post on SQL Server DMVs. I also believe in the regular defragging of indexes in a database (although I recently read a post from Brent Ozar challenging this idea, but if your servers don’t have the RAM required for such caching as his article suggests and/or you'll never get the budget to upgrade, you’ll need to keep reading). 

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.

Data Encryption and Protection - Part 1

A client has recently revisited their need to protect sensitive data in their application and the database that it connects to. As a DBA it is easy to focus solely on the database but as I was considering their infrastructure and all the parts of their system it became clear that that was a small piece of the puzzle. In terms of protecting data for an entire IT solution there are quite a few other areas to consider. In this and a few subsequent posts I will discuss what options there are in securing data. I will attempt to touch on the major advantages and disadvantages of those options. Obviously, as I'm not a network or systems admin I will only touch on the concepts to consider that are beyond the scope of securing the database.

First I will cover what I know best: SQL Server.  There are 2 main encryption methods serving different purposes:

1.  Transparent Database Encryption (TDE) - available for SQL Server 2005+
2. Cell-level encryption

Quick Cheat to Shrink Database Log Files

This is a quick way to reduce the size of a database log file. As a caveat to beginners, this should not be used on mission critical db systems. It is intended as a quick and dirty fix for non-essential systems.

For those not familiar with the Attach and Detach commands:
--You need to make sure the database is not being used by anyone else/any other process. That is why there is a set single_user statement.
--If your database has multiple files/filegroups, you need only specify the primary file in the create statement (if you haven't made any file modifications after detaching).
-- You may need to enable xp_cmdshell therefore I've included the sp_configure statements.