Assessment for configurations on MSSQL instances

Here are the list of things that I think are misconfigured on SQL Server and should have been gone through RTPA process when they were configured:

1) Max degree of parallelism :

This configuration tells SQL Server how CPU’s should be utilized for parallel processing,
so here is the thing we have multiprocessor servers, we are paying license cost for SQL Server based on cores but are we using multiprocessing ?

No, Which means lag in performance, sure this might result in blocking sometimes but it’s worth, looking at the performance gain that you can get.(Ever wondered why you were not getting blocking issues on PRD nodes irrespective of transactions it handles? Guess what this value set to 1 is answer which is at cost of loosing parallelism)

Note: Setting max degree of parallelism to 1 suppress use of parallel plans and here is the situation in 3 of out Prod instances out of 5 btw rest 2 prod instances also have this value set to 2 even when all these 5 instances have 4 CPU available:

From Microsoft : To enable the server to determine the maximum degree of parallelism, set this option to 0, the default value. Setting maximum degree of parallelism to 0 allows SQL Server to use all the available processors up to 64 processors. To suppress parallel plan generation, set max degree of parallelism to 1. Set the value to a number from 1 to 32,767 to specify the maximum number of processor cores that can be used by a single query execution. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the max degree of parallelism value is ignored.

How to fix this?

Guidelines to correct this which applies in our case is 1(highlighted) which makes 3 or 4 as ideal value :

Sno.Numa NodesLogical ProcessorsMaxdop Value
1Server with single NUMA nodeLess than 8 logical processorsKeep MAXDOP at or below # of logical processors
2Server with single NUMA nodeGreater than 8 logical processorsKeep MAXDOP at 8
3Server with multiple NUMA nodesLess than 8 logical processors per NUMA nodeKeep MAXDOP at or below # of logical processors per NUMA node
4Server with multiple NUMA nodesGreater than 8 logical processors per NUMA nodeKeep MAXDOP at 8

2) Cost threshold for parallelism:

How to fix this?
This value used to be 5 when CPU and RAM were limited in Servers back in old days but as now we don’t have those similar limitations, market standard\good value to start with for this parameter now a days is 50.

Why fixing this is required ?
Prevents timeouts for smaller queries: This helps in preventing high resource eating heavy queries in utilizing all the resources and keeping a chunk aside for giving room for smaller queries to still execute without timeouts even when large queries are hammering SQL Server.

3) Read_Committed_Snapshot:

We are using isolation level as Read_committed only but we are asking developers to add nolock in all the queries which means we are reading data which not completely committed anyways then why not switch the option for Read_Committed_Snapshot on?

4) Disabled Indexes, Duplicate Indexes, Non cluster Index Hoarding ,Wide indexes & Unused Indexes with 0 reads and 0 writes:
  • There are duplicate indexes in the database tables and also there are various tables which has more than 5 non clustered indexes up to 11 in some cases which  can really slow down updates and inserts, and result in long update stats and index maintenance time.
  • There are also indexes present in database which are not even being used but taking space resulting in long backup time and size and also long maintenance time.
  • There are many wide indexes with up to 27 columns ,7 or more columns are considered as borderline in usual cases.
  • There are lots of dta_*_indexes created using database tuning adviser which might not be well planned and created as its automatically suggested by that tool to solve a specific query issue and might result in problems in long run, you might not want to take these to your new environment with migration also you might want to give them proper names according to standard naming conventions to prevent creations of duplicates in future as databases don’t warn you of duplicates when you create same index with different names.
5) Other Warnings:
>> WARNING: Multiple .mdf files instead of .ndf used for naming secondary datafiles which can result in confusions.
>> WARNING: There are datafiles on disk C: which is not recommended.
>> WARNING: The files of the Tempdb database are not on a unique disk.
>> WARNING: There are 1 database files with the auto-growth parameter less than or equal to 1MB.
>> WARNING: There are databases with the recovery model in FULL and no log backup.
>> WARNING: Disk D has both database and backup files.
>> WARNING: There are 8 databases without satisfactory execution of CHECKDB in the last month

Note: These settings(MAXDOP & COST THRESHOLD FOR PARALLELISM) were changed and set to 4 and 50 respectively in POC long back and tested to be working fine.


Popular posts from this blog

Connecting Microsoft SQL Server using PHP

Enabling fixed Sidebar for blogger Contempo template for screen sizes smaller than 1440px

Adding copy to clipboard button to every google code prettify pre blocks in blogger