SQL Server SPN & Kerberos Authentication

SQL Server uses NTLM Authentication if the SPN Fails to be created when SQL Server is started. It can be configured to use Kerberos by registering the SPN for the SQL Server;

NTLM Vs Kerberos What’s it all about?

NTLM & Kerberos are authentication protocols. NTLM was the default protocol used in old windows versions, but it’s still used today. If for any reason Kerberos fails

Kerberos	NTLM
Kerberos supports delegation of authentication in multi-tier application.	NTLM does not support delegation of authentication.
Kerberos supports two factor authentication such as smart card logon.	NTLM does not provide smart card logon.
Kerberos has the feature of mutual authentication.	NTLM does not have the feature of mutual authentication.
Kerberos provides high security.	While NTLM is less secured as compared to kerberos.

So what does this have to do with me?

The main reason we are interested in it for SQL Server is the delegation of authentication. meaning we can start connecting to sql server through SSMS from other servers. Without Kerberos Authenticaiton, when you try and connect to SSMS remotely, you will get an “Cannot generate SSPI context’ Error.

Gotcha, So how do we use it?

In order to use Kerberos authentication, you need to register the Service Principal Name (SPN) for SQL Server. SQL Server tries to do this automatically when the SQL Server Service Starts. you will see an entry on the SQL Error log, declaring if the SPN Was created Successfully or not.

SQL Server can only create the SPN Automatically if the SQL Server Service Account has permissions to create the SPN in Active Directory. The permissions required for this are the “Read servicePrincipalName” and “Write servicePrincipalName”.

And how do we apply the permissions to register the SPN Automatically?

Open Active Directory Users and Computers.
To open Active Directory Users and Computers, click Start, click Run, type dsa.msc, and then press ENTER.
Click View, and verify that the Advanced Features check box is selected.
Click Advanced Features, if it is not selected.
In the console tree, right-click the node that represents the domain to which you want to allow a disjoint namespace, and then click Properties.
On Security tab, click Advanced.
On the Permissions tab, click Add.
In Enter the object name to select, type the group or user account name to which you want to delegate permission (This is the SQL Server Service account), and then click OK.
Configure the Apply onto box for Computer objects.
At the bottom of the Permissions box, select the Allow check box that corresponds to the Validated write to service principal name permissions, and then click OK on the three open dialog boxes to confirm your changes.
Close Active Directory Users and Computers.

But I cannot give these permissions, cant i create a SPN Manually?

Of course. however it’s recommended that the permissions are granted.

Run the following command in an elevated command prompt, with your Server/Account Details;

setspn -A MSSQLSvc/<Server Name> <Account Name>

Alternatively, Microsoft have a very useful tool called “Microsoft Kerberos Configuration Manager” This tool lets you connect to the server and fix any SPN Issues you might have

High Virtual Log File(VLF) Count in SQL Server

So what is a VLF?

The SQL Server Database Engine divides each physical log file internally into a number of virtual log files (VLFs). Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file.

Virtual log file (VLF) creation follows this method:

• If the next growth is less than 1/8 of current log physical size, then create 1 VLF that covers the growth size (Starting with SQL Server 2014 (12.x))
• If the next growth is more than 1/8 of the current log size, then use the pre-2014 method:
  • If growth is less than 64MB, create 4 VLFs that cover the growth size (e.g. for 1 MB growth, create four 256KB VLFs)
  • If growth is from 64MB up to 1GB, create 8 VLFs that cover the growth size (e.g. for 512MB growth, create eight 64MB VLFs)
  • If growth is larger than 1GB, create 16 VLFs that cover the growth size (e.g. for 8 GB growth, create sixteen 512MB VLFs)

Right? so how could this be an issue?

When you log grows in small increments, it’s going to create a VLF for each growth event. if you’ve grown your log by 1mb 1000 times, you’re going to have 1000 additional VLF’s. having too many VLF’s is going to cause Database Backup and Recovery tasks to slowdown, and in extreme cases it can even effect DML(Insert/Update/Delete) Performance.

So what’s ‘high’?

Well, this depends on transaction log size, 100-300 VLF’s isn’t unreasonable, and I’ve never seen performance issues with those amounts. It’s When the VLF Count starts going over 1000 you should look at sorting the issue out. I’ve only seen performance issues with the VLF Count over 1000.

How can I tell how many logs VLF’s I have?

Either of these 2 scripts should give you the information;

DBCC LOGINFO(<DatabaseName>)

SELECT [name], s.database_id,
COUNT(l.database_id) AS ‘VLF Count’,
SUM(vlf_size_mb) AS ‘VLF Size (MB)’,
SUM(CAST(vlf_active AS INT)) AS ‘Active VLF’,
SUM(vlf_activevlf_size_mb) AS ‘Active VLF Size (MB)’, COUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS ‘In-active VLF’, SUM(vlf_size_mb)-SUM(vlf_activevlf_size_mb) AS ‘In-active VLF Size (MB)’
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name], s.database_id
ORDER BY ‘VLF Count’ DESC
GO

I’ve got 300million VLF’s how do I fix it?

Okay there might be some exaggeration there but you get the idea.

Shrinking the transaction log is the only way to reduce the number of VLF’s. but you don’t want the issue to occur again, so you will need to regrow the log back to normal operation size after the shrink is finished. Growing the log in 1 operation to the correct size will ensure SQL Server allocates the correct number of VLF’s for that size of log file(Based on the above algorithm).

Remember

Shrinking a log file is a blocking operation!

Oh! And set some reasonable growth parameters while you’re at it…. this will ensure the problem will be kept at bay

SQL Server Configuration: MODEL Database

The “Model” Database is the template database SQL Server uses when a new User Database is created, any Database Settings/Sizes are copied from the Model Database to a New blank database – Obviously if you’re doing a restore it’s going to restore with the settings the database was backed up with, so this only applies to new databases.

What can be changed?

Any database level configuration option can be changed on the Model Database, and these will be replicated to your new blank database, things like; Read Committed Snapshot Isolation Level, Collation, Compatibility level etc…

This means if you know what settings databases will need on the new instance, you can stay ahead of the curve and set them on Model. I Would advise leaving most settings as standard unless you know something is going to be specifically required for future databases

What should I change then?

Growth! Auto Growth settings is my biggest annoyance when it comes to databases, As default (< SQL Server 2016) Auto growth is set to a percentage of the data/log file. This means on small databases there are going to be lots of growth events. when you’re looking through growth history & seeing 100’s if not 1000’s of 1mb growths it makes it hard to see what happened. It’s also going to increase your Virtual Log Files(VLF’s) by a dramatic amount (which will cause performance issues)

It could also be worth setting the Initial Size of the data & log files to something sensible….

Recommendations?

Again it depends on your instance size, if it’s a small instance, I would suggest setting growth to 256 or 512. basically you want to avoid lots of growth and balance that with the datafile taking “Too much space” if it’s a 100mb database, It would be a little excessive setting auto growth to 1gb.

ALTER DATABASE Model
MODIFY FILE (NAME = ModelDEV, FILEGROWTH = 1024);
ALTER DATABASE Model
MODIFY FILE (NAME = ModelLOG, FILEGROWTH = 1024);

Anything larger I would suggest 1gb growth is fine. this can always be changed on the actual database once it’s in use, after all, remember this is just configuring the template database.

The main benefit to configuring the model database this way, is; if lots of databases have been created, all with bad auto growth settings, you’re going to have to go back and correct them all at some point; setting up all their growth parameters & shrinking the log to reduce VLF’s… it’s just time consuming work that can be avoided!

Forgotten Maintenance – SP_CYCLE_ERRORLOG

DBA’s tend to get caught up in the technical performance configuration of SQL Server (Like; Setting MaxDOP, Configuring TempDB, Installing Index Maintenance and Integrity Checks) that they sometimes forget the basics; Cycling the Error Log!

Why Bother?

Yeah sure, it’s not going to give users a performance boost, but when you’re investigating an outage, and you want to look in the error log for any issues, do you really want to open it with millions of entries? I’ve lost count of the times where I’ve had to sit there for 10mins waiting for the error log to open because it’s not been cycled in 6months+

Oh So Simple

And it’s so easy to implement error log maintenance. The error log is only cycled on 2 events;

• When SQL Server is restarted.
• When you execute sp_cycle_errorlog

Outside of this, without intervention it just grows, and if you’re logging user login activity it grows fast and big.

So How?

So first we want to put a limit on how many error logs we want to keep, this is basic house keeping,

• Expand the “Management” folder.
• Right click on “SQL Server Logs”
• Select “Configure”
• Check the box “Limit the number of error log files before they are recycled”
• Pick some value to put in the “Maximum number of error log files” box
• Click “OK

Then we want to setup a SQL Agent job to Cycle the error log, all it has to do is run EXEC sp_cycle_errorlog on a schedule which suits you, obviously if you have a particularly noisy log, you might want to schedule it more often.

Generally I limit my SQL Servers to keep 26 logs and to cycle every week, this gives us about 6 months of Error log. you might want to schedule your cycle monthly or even daily (Which is totally fine) just make sure you’re increasing or decreasing the max number of log files to suit. You want enough logs to diagnose any historic problems but you don’t want so many that they will use unnecessary disk space.

SQL Server Configuration: Parallelism

Max degree of parallelism (MAXDOP)

MaxDOP is the the parameter used to specify the maximum number of processors to use on a single execution of a SQL Statement. 0 Is the default value for MaxDOP, This allows SQL Server to use all the available processors (up to 64 processors). To suppress parallel plan processing you can set MaxDOP to 1. this will limit SQL to using 1 processor per query.

Cost threshold for parallelism

Cost threshold for parallelism specifies the threshold at which SQL Server creates and runs parallel plans for queries. when the cost of the query exceeds the threshold the plan attempts to run in parallel depending on the MaxDOP Setting. The threshold value is the estimated cost of running the query on a specific set of hardware, not a unit of time. the default value for Cost threshold for parallelism is 5 (this is very low)

Wait what? what does that mean?

Think of a classroom, the database engine is the teacher & the students are each of the cores available to SQL Server. In this example we will say there are 4 students. The teacher asks the students to count all the jellybeans in a jar, so each of the 4 students grabs a handful of jellybeans, counts them, then grabs more until the jar is empty, the teacher then collects the results from each student. this is great if there are 100’s of beans in the Jar. what if there are only 4 beans in the jar, well with a low cost threshold for parallelism, each student would take an equal share (1 bean each) count that, then the teacher would collect the results…. terribly inefficient.

Guidelines

To avoid simple plans going parallel; For a new installation or a system that not previously had the Cost threshold for parallelism configured it’s recommended that it’s set to 50. This can be changed depending on your workloads

MaxDOP however depends on the CPU Configuration of your Server;

MaxDOP and Cost threshold for Parallelism can both be configured on the Advanced tab of the SQL Server properties screen in SSMS.

SQL Server Installations

During the installation of SQL Server 2016+, There is now a MaxDOP Configuration page, which automatically suggests the MaxDOP Settings. which is great, however there are some applications that do not work very well with MaxDOP and it’s recommended by the software vendor that MaxDOP is set to 1. SharePoint is one of these, Don’t get caught out!

SQL Server Configuration: Fill-Factor

Fill-factor determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. This only takes effect when an index is created or rebuilt.

For example ; Setting fill-factor to 90 means that 10 percent of each leaf level page will be left empty. This provides space for index expansion as data is added. The empty space is reserved between the index rows rather than at the end of the index.

SPACE

There are space implications with leaving this white space is that your indexes. for a 100mb index you would need 10% more space, making the index 110mb. that doesn’t sound to bad does it? what if you index was 5gb? that’s now 5.5gb for 1 index… now imagine that for all the indexes on your database….

PAGE SPLITS

By leaving this free space at the leaf-level rows there is the potential to reduce index fragmentation and page splits. Page Splits occur when there is now room within the index page for data to be written, the database engine moves approximately half of the rows on the page to a new page to make room for this new data. this is a fairly resource intensive operation which can cause an unnecessary increase to disk i/o. Trying to avoid (Too Many) page splits from occurring should be the main reason you’re setting a fill-factor.

READ PERFORMANCE

Configuring the fill-factor of an index may reduce the occurrences of page splits, it will also decrease the read performance as the database engine will have more index pages to search through. Microsoft clearly states; “a fill-factor value of 50 can cause database read performance to decrease by two times”

CONFIGURATION

Configuring a correct Fill-Factor shouldn’t be done on a mass scale. and in my opinion it shouldn’t be done at instance level. It’s very likely that it will hurt your performance more than it helps.

Workloads should be taken into account, and you should definitely be checking your performance counters to see how SQL Server is currently coping with the current workload. if a large amount of page splits are occurring then it’s worth drilling down to index level analysis to see where the page splits are occurring and setting the fill-factor on that individual index.

BUT I READ ONLINE IT CAN REDUCE PAGE SPLITS

It’s not uncommon for me to look at an instance that has been under the care of another dba and a default fill-factor has been set (Usually 80!) Other than the wasted space within the index, it could decrease your read performance by 20%!

STOP IT!

Find where your pain is, and target your pain relief there. This blanket setting is just going to cause you other issues further down the line.

SQL Server Configuration: Optimise For Ad Hoc Workloads

Optimise for Ad Hoc Workloads changes the way in which SQL Server retains Query Plans in the Plan Cache. When this option is turned on, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This reduces the size of the Plan Cache and stops reusable Plans being forced out of the Cache when the Cache threshold is reached. It can also helps to alleviate memory pressure due to the plan cache requiring less memory.

There are only extreme edge cases where enabling this option would have a negative performance impact; like lots of query’s compiling twice over a short period of time. In 99.9% of cases; SQL Server will benefit more from the memory you’re saving by freeing up this Cache space than the few CPU cycles you’re wasting having to compile a plan for the second time.

You can use this query to find all the single use plans in your SQL Server’s Plan Cache;

Query

SELECT objtype, cacheobjtype, AVG(usecounts) AS Avg_UseCount, SUM(refcounts) AS AllRefObjects, SUM(CAST(size_in_bytes AS bigint))/1024/1024 AS Size_MB FROM sys.dm_exec_cached_plans WHERE objtype = ‘Adhoc’ AND usecounts = 1 GROUP BY objtype, cacheobjtype;

Turning on the Optimize for ad hoc workloads Setting only affects new plans; plans that are already in the plan cache are unaffected. You would need clear the plan cache or restart SQL Server to flush out all your single use plans.

I Always turn Optimize for ad hoc workloads on by default.