CHANGING SQL SERVER’S MAXDOP SETTING

MaxDOP is an advanced configuration option, so if your server is not already configured to do so, you’ll need to set it to display advanced configuration options:

EXEC dbo.sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

Now if you just issue the sp_configure command without any parameters, you can see what your current MaxDOP setting is:

:

You can see I’ve already changed my MaxDOP value from the default of zero to a value of 8. (The maximum value of 64 is the maximum value SQL Server will accept, not the maximum possible value for this system.)

If you are more of a GUI person, you can see this info in SSMS by right clicking the server in Object Explorer and choosing Properties, then the Advanced tab:

Changing the setting is realtively easy and does not require a server restart to take effect. If you are using the GUI, you can enter a new value directly in the screen shown above. For T-SQL, use the following:

EXEC dbo.sp_configure 'max degree of parallelism', 8;
GO
RECONFIGURE;
GO

Substitute whatever value you want for 8 in the above command, of course.

Now that you know how to change the value, how do you know if you should change the value?

Introducing the CXPACKET wait type

SQL Server can spend time waiting on all sorts of things – data to be read from disk, data to be written to disk, waiting for locks to be acquired or released, or, of particular interest to us, waiting for the results of parallelized queries to get recombined. This later wait is tracked via a wait type counter in SQL Server called CXPACKET. This counter is available in SQL Server 2000 through the DBCC sqlperf(waitstats) command, but in SQL 2005 and later, you can also see it with the sys.dm_os_wait_stats dynamic management view.

The dynamic management view will return a value, in milliseconds, of how long SQL has spent waiting to combine the results for parallelized queries since the last time the server was restarted or the last time the wait stats were cleared. Glenn Berry has, as part of his great diagnostic queries, a query that will list the top wait states on your servers. His query is:

-- Isolate top waits for server instance since last restart or statistics clear
WITH    Waits
          AS (SELECT    wait_type
                       ,wait_time_ms / 1000. AS wait_time_s
                       ,100. * wait_time_ms / SUM(wait_time_ms) OVER () AS pct
                                                             ,ROW_NUMBER() OVER (ORDER BY wait_time_ms DESC) AS rn
              FROM                                            sys.dm_os_wait_stats
              WHERE                                           wait_type NOT IN (
                                                              'CLR_SEMAPHORE',
                                                              'LAZYWRITER_SLEEP',
                                                              'RESOURCE_QUEUE',
                                                              'SLEEP_TASK',
                                                              'SLEEP_SYSTEMTASK',
                                                              'SQLTRACE_BUFFER_FLUSH',
                                                              'WAITFOR',
                                                              'LOGMGR_QUEUE',
                                                              'CHECKPOINT_QUEUE',
                                                              'REQUEST_FOR_DEADLOCK_SEARCH',
                                                              'XE_TIMER_EVENT',
                                                              'BROKER_TO_FLUSH',
                                                              'BROKER_TASK_STOP',
                                                              'CLR_MANUAL_EVENT',
                                                              'CLR_AUTO_EVENT',
                                                              'DISPATCHER_QUEUE_SEMAPHORE',
                                                              'FT_IFTS_SCHEDULER_IDLE_WAIT',
                                                              'XE_DISPATCHER_WAIT',
                                                              'XE_DISPATCHER_JOIN',
                                                              'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
                                                              'ONDEMAND_TASK_QUEUE',
                                                              'BROKER_EVENTHANDLER',
                                                              'SLEEP_BPOOL_FLUSH')
             )
     SELECT W1.wait_type
           ,CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s
           ,CAST(W1.pct AS DECIMAL(12, 2)) AS pct
           ,CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
     FROM   Waits AS W1
            INNER JOIN Waits AS W2 ON W2.rn <= W1.rn
     GROUP BY W1.rn
           ,W1.wait_type
           ,W1.wait_time_s
           ,W1.pct
     HAVING SUM(W2.pct) - W1.pct < 99
OPTION  (RECOMPILE); -- percentage threshold

There are a couple of nice things this query does. First, it converts the milliseconds value the DMV returns to seconds. Second, it orders the waits from largest to smallest and also provides a running total percentage.

So that’s how you see what your CXPACKET wait is. But how do you know if you’ve got a problem. Microsoft suggests that if your CXPACKET wait consists of more than 5% of your total wait time, then you may have a parallelism bottleneck. I will again point out the It Depends SQL caveat applies here, as it does with all general SQL configuration advice.

Tracking Your Wait Times

If you decide you need to change your MaxDOP setting, you’re going to want a baseline of your pre-change system operation to compare against to determine if you’ve actually improved performance. One way to do that is to manually reset the wait stats, let the system run for a set period of time, make the MaxDOP setting change, reset the wait stats again, then let the system run for the same length of time again. Ideally, your two test periods should include times of similar usage.

At this point, I feel the need to point out that you should always test system configuration changes on a test system before making them on production system.

You can reset your wait stats using the following command:

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)

For my test, I created two jobs. The first job cleared the wait stats and ran on at 8 AM on Monday morning. The second job executed Glenn Berry’s query above on the following Friday at 9 PM and wrote the results to a text file. I set up the schedules for the jobs to run twice. (I could have written the data to a table, but I was in a hurry setting this up, so I just wrote to a text file. If you do this, make sure you choose the Append option so you don’t overwrite existing data with your second execution.) After the first week, I had my baseline data. When I came in on the second Monday, I made sure I changed the MaxDOP setting prior to 8 AM. (No restart is required when you change this setting.) When the following Friday rolled around, I had my second set of performance data gathered after my changes.

My Results

I should point out, the server I am testing is a 16 core system in a production environment. It is NUMA-enabled and has 8 processor cores per NUMA node. Based on Microsoft’s recommendation, I determined I should set the MaxDOP value to 8. My baseline numbers:

wait_type	wait_time_s	pct	running_pct
OLEDB	370135.54	31.68	31.68
CXPACKET	295106.42	25.26	56.94
LCK_M_IS	187728.33	16.07	73.01
WRITELOG	98589.81	8.44	81.45
BACKUPIO	43881.44	3.76	85.21
LATCH_EX	38794.36	3.32	88.53
MSQL_XP	28966.19	2.48	91.01
SOS_SCHEDULER_YIELD	24027.27	2.06	93.07
PAGEIOLATCH_SH	17086.65	1.46	94.53
ASYNC_NETWORK_IO	16296.86	1.39	95.92
BROKER_RECEIVE_WAITFOR	16032.44	1.37	97.3
BACKUPBUFFER	7999.12	0.68	97.98
IO_COMPLETION	5165.44	0.44	98.42
SLEEP_BPOOL_FLUSH	4474.87	0.38	98.81
PAGEIOLATCH_EX	2892.76	0.25	99.05

After I made the change, these were my results:

wait_type	wait_time_s	pct	running_pct
OLEDB	383416.75	35.85	35.85
LCK_M_IS	209511.89	19.59	55.44
CXPACKET	134124.75	12.54	67.98
WRITELOG	102343.21	9.57	77.55
ASYNC_NETWORK_IO	71632.59	6.7	84.24
BACKUPIO	48025.45	4.49	88.73
MSQL_XP	29184.14	2.73	91.46
SOS_SCHEDULER_YIELD	24138.6	2.26	93.72
BROKER_RECEIVE_WAITFOR	17342.3	1.62	95.34
PAGEIOLATCH_SH	14518.61	1.36	96.7
BACKUPBUFFER	7990.42	0.75	97.44
LATCH_EX	7474.26	0.7	98.14
SLEEP_BPOOL_FLUSH	4436.36	0.41	98.56
IO_COMPLETION	3937.39	0.37	98.93
PAGEIOLATCH_EX	2875.57	0.27	99.2

So you can see, the time SQL Server spent waiting for parallelism to complete went down by 160,981,67 seconds – 44.7 hours! I just gave this server one man-week of additional computing time! It spent an additional 6 hours of that time waiting to acquire intent shared locks and an additional 3.7 hours waiting for OLE DB connections, but I’m completely OK with this because those wait times are indicative of a working system. An increase in OLEDB means the server is spending more time waiting for data to return from either a call to a linked server or for data to load from the bulk insert task or some other process that uses the SQL Server native client OLE DB provider. Which, in turn means, SQL is processing more work than it used to.

Now, I’m not going to say that OLEDB wait stat value is a good one. It now represents 35% of the time SQL Server spends waiting and if I can trim that down, I can squeeze some more performance out of this server. But that will require some deeper digging to find out what the OLE DB calls being made are and what, if anything, I can do about them.

If I take a step back to look at the overall picture and add up the total time spent waiting on the top 15 waits (which are the waits Glenn’s query returns), I can see my total time spent waiting dropped from 321 hours to 294 hours!

I’m also not going to claim this is a scientifically accurate test. For one, I had no control over the load the server experienced during this week, so the two workloads may have been significantly different. However, by looking at an entire week, I can more or less average out any one-time anomalies a large one-off query might introduce. I also know the nominal workload of my servers and I can make a reasonable assumption that the workload was more or less the same. Further, as a working DBA, I’m more concerned about my actual performance improvement than  a theoretical improvement. So all in all, for a first step in performance improvement, I’m very happy with this result!

Bonus: When analyzing performance data, I like to find alternative ways to validate my conclusions. My conclusion above was that this server was now spending more time processing requests. So if I use my PerfMon monitoring routine to look at the number of batch requests per second this server is handling, I should see an increase from the first week to the second week.

Sure enough, if you look at the average low point over the two weeks, you’ll see the lows were under 200 for the first week and over 200 for the second. Likewise, the highs are higher, going from typically under 600 to peaking over 600 multiple times.

Script to Setup SQL Server Database Mirroring for Multiple Databases

SET NOCOUNT ON

DECLARE @primary VARCHAR(128) = 'primaryservername.domain.com' ,
@secondary VARCHAR(128) = 'secondaryservername.domain.com' ,
@account VARCHAR(128) = 'domain\username' ,
@backupnetworkpath varchar(128) = '\\Servername\sharename\',
@command VARCHAR(MAX) ,
@command2 VARCHAR(MAX) ,
@rowcount TINYINT = 0 ,
@mirroringmode varchar(4) = 'Off'  --OFF or FULL
@newmirror BIT = 1 --if mirroring has not been setup before create the endpoints

IF @mirroringmode = 'OFF' OR @mirroringmode = 'FULL'
BEGIN
CREATE TABLE #databases ( name VARCHAR(128) )

INSERT INTO #databases
SELECT name
FROM sys.databases d
LEFT OUTER JOIN sys.database_mirroring m ON m.database_id = d.database_id
WHERE name NOT IN ( 'dba', 'tempdb', 'master', 'msdb', 'model' )
AND mirroring_role_desc IS NULL

SET @rowcount = @@rowcount

IF @rowcount > 0
BEGIN
PRINT ':CONNECT ' + @primary
SELECT 'ALTER DATABASE [' + name
+ '] SET RECOVERY FULL WITH NO_WAIT'
FROM #databases

IF @newmirror = 1
BEGIN
PRINT ''
PRINT 'IF  NOT EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N''Mirroring'')'
PRINT 'CREATE ENDPOINT [Mirroring] AUTHORIZATION [' + @account
+ '] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)'
PRINT ' GO'

PRINT ''
PRINT ':CONNECT ' + @secondary
PRINT 'IF  NOT EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N''Mirroring'')'
PRINT 'CREATE ENDPOINT [Mirroring] AUTHORIZATION [' + @account
+ '] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)'
PRINT 'GO'
END

PRINT ''
PRINT ':CONNECT ' + @primary
SELECT 'BACKUP DATABASE [' + name + '] TO  DISK = N''' + @backupnetworkpath + name
+ '.bak'' WITH NOFORMAT, COPY_ONLY, INIT,  NAME = N''' + name
+ '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
FROM #databases

PRINT ''
SELECT 'BACKUP LOG [' + name + '] TO  DISK = N''' + @backupnetworkpath + name
+ '.trn'' WITH NOFORMAT, COPY_ONLY INIT,  NAME = N''' + name
+ '-Transaction Log  Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
FROM #databases

PRINT ''
PRINT ':CONNECT ' + @secondary
SELECT 'RESTORE DATABASE [' + name + '] FROM  DISK = N''' + @backupnetworkpath + name
+ '.bak'' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE, STATS = 10'
FROM #databases

SELECT 'RESTORE LOG [' + name + '] FROM  DISK = N''' + @backupnetworkpath + name
+ '.trn'' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10'
FROM #databases

PRINT ''
PRINT ':CONNECT ' + @secondary
SELECT 'ALTER DATABASE [' + name + '] SET PARTNER = N''TCP://' + @primary + ':5022'''
FROM #databases

PRINT ''
PRINT ':CONNECT ' + @primary
SELECT 'ALTER DATABASE [' + name + '] SET PARTNER = N''TCP://' + @secondary + ':5022'''
FROM #databases

PRINT ''
SELECT 'ALTER DATABASE [' + name + '] SET SAFETY ' + @mirroringmode
FROM #databases

PRINT ''
SELECT 'ALTER DATABASE [' + name + '] SET PARTNER TIMEOUT 30'
FROM #databases
PRINT ''

IF @newmirror = 1
PRINT 'exec sys.sp_dbmmonitoraddmonitoring'

IF @newmirror = 1
BEGIN
PRINT ''
PRINT ':CONNECT ' + @secondary
PRINT 'exec sys.sp_dbmmonitoraddmonitoring'
END
END

DROP TABLE #databases
END

TDE

https://www.red-gate.com/simple-talk/sql/sql-development/encrypting-sql-server-transparent-data-encryption-tde/


https://www.mssqltips.com/sqlservertip/3146/configuring-transparent-data-encryption-with-sql-server-2012-alwayson-availability-groups/

Estimated Completion time for a query

select  T.text, R.Status, R.Command, DatabaseName = db_name(R.database_id)
        , R.cpu_time, R.total_elapsed_time, R.percent_complete,estimated_completion_time,
convert(varchar,(total_elapsed_time/(1000))/60) + 'M ' + convert(varchar,(total_elapsed_time/(1000))%60) + 'S' AS [Elapsed],
convert(varchar,(estimated_completion_time/(1000))/60) + 'M ' + convert(varchar,(estimated_completion_time/(1000))%60) + 'S' as [ETA]
from    sys.dm_exec_requests R
        cross apply sys.dm_exec_sql_text(R.sql_handle) T

Find Tempdb usage

/*
Lists the TempDB usage per each active session.
It helps identifying the sessions that use the tempdb heavily with internal objects.

When the internal objects usage is high, the session is probably using big hash tables or spooling in worktables. It could be a symptom
of an inefficient plan or a missing index.

Shrinking a TempDB full of internal objects will probably have no effect, because the engine will not release the deallocated space.


*/


;WITH task_space_usage AS (
    -- SUM alloc/delloc pages
    SELECT session_id,
           request_id,
           SUM(internal_objects_alloc_page_count) AS alloc_pages,
           SUM(internal_objects_dealloc_page_count) AS dealloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id <> @@SPID
    GROUP BY session_id, request_id
)
SELECT TSU.session_id,
       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       EST.text,
       -- Extract statement from sql text
       ISNULL(
           NULLIF(
               SUBSTRING(
                   EST.text,
                   ERQ.statement_start_offset / 2,
                   CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
               ), ''
           ), EST.text
       ) AS [statement text],
       EQP.query_plan
FROM task_space_usage AS TSU
--- Changed from inner join to left outer join to return rows for sessions that aren't currently actively running queries.
left outer join sys.dm_exec_requests ERQ WITH (NOLOCK)
    ON  TSU.session_id = ERQ.session_id
    AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC, 5 DESC

Database Backups for all databases For Previous Week

SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date

Top 10 Free SQL Server Tools

1. Plan Explorer

If you find yourself having to look at SQL Server execution plans (and you should be) this is THE tool for you. SQL Sentry Plan Explorer builds upon the graphical execution plan view in SSMS to make working with query plans a more efficient and “pleasurable” experience. No more scrolling through massive plans and hunting through property windows to find the metrics you need.

2. sp_Blitz

Like Batman’s utility belt, this tool does pretty much everything. If you’ve been handed a SQL Server instance and need to give it the once over in order to get a handle on things then this is what you use to get the job done. sp_Blitz helps you rapidly assess configuration, security, health, and performance issues. As if that were not awesome enough, the results are organised in a prioritized list and provide simple explanations of each risk, plus a column of links that you can copy/paste into your browser for more information about each issue. The authors, Brent Ozar (Blog|Twitter) and his team, have even put together a tutorial video on how this tool works. A wonderful example of #sqlWinning and most certainly worthy of a hat tip.

3. sp_WhoIsActive

Want to know what is happening on your SQL Server instance right now? This is the tool you use. It’s my favourite initial diagnosis instrument. When I’ve got a production issue on my hands and nothing more to go on than “is there something wrong with SQL Server?” this is my go-to weapon. The creation of Adam Machanic (Blog|Twitter), sp_WhoIsActive enables me to triage an instance of SQL Server so that I can immediately get a feel for what’s currently happening and quickly decide what my next steps should be. As well as all currently executing commands and what they’re waiting on, I can optionally pull back additional information like the execution plan and blocking chain.

4. Management Data Warehouse (MDW)

Yet another fantastic product feature available right out of the box that I’m surprised more folks are not taking advantage of. It contains a significant amount of performance monitoring insight and capability that many vendors will happily charge you for. Using the MDW I have been able to make production performance insight data directly available to development teams by extending the default implementation using SQL Reporting Services.

5. Index Defrag Script v4.1 

Everyone has their favourite Index Defrag script. Some folks (suckers) like to author their own index maintenance scripts and everyone else likes to use Ola Hellengren’s ;-) Of course not being one to follow the crowd, this little beauty written by Michelle Ufford(Blog|Twitter) is my own personal favourite. I’ve been very happily using this script for index maintenance of production environments for many years and many versions. It gets the job done extremely well and deservers more press.

6. sp_BlitzIndex

It’s natural to think that your indexes improve your performance. Sure that’s what they’re supposed to do sure but there are cases where having indexes can actually be a hindrance to performance. Things like duplicate and unused indexes can be silent killers, dragging your performance down quietly in the background. You can use sp_BlitzIndex to evaluate your overall index health and to identify areas where you can make adjustment and improvement to nip potential future problems in the bud.

7. Compression Estimator

SQL Server Compression is one of the more underused features of the product, most likely because a lot of folks don’t know how to use it to their gain. You can download this utility from Codeplex and use it in order to identify how much space you could save, as well as evaluate which type of compression you should consider (ROW or PAGE). Armed with this type of information, you can make a stronger case to both management and your customers as to why you should be taking advantage of the SQL Compression feature.

8. Central Management Server

Managing a large number of SQL instances can be tricky. CMS is a SQL Server feature for making the job of administering multiple servers easier. You can centralise your registered server lists, ensuring your entire team is working from the same reference and run multiple queries against “groups” of servers using a single query. It’s a great feature and I encourage you to learn more about how it can simplify the administration of your environment.

9. ClearTrace

Here’s a great tool for working with SQL Server trace files. ClearTrace imports SQL Server 2000, SQL Server 2005 and SQL Server 2008 trace and profiler files into SQL Server and displays summary performance information. It’s easy to use and helps you get to the bottom of performance problems fast.

10 . SQL Server 2012 Diagnostic Information Queries

SQL Server DMVs were are godsend when they were introduced into the SQL Server product. Just ask anyone who still has to touch a SQL 2000 instance how much fun they are having. DMV’s are of course not pure magic by themselves, they need to be built and combined together into useful T-SQL queries. Thanks to Glenn Berry (Blog|Twitter), the hard work has already been done for you. Glenn has put together a wide variety of awesome T-SQL queries that you can use in order to diagnose the health and performance of your SQL Server instances.