Policy Based Management and Powershell

Introduction

Policy Based Management (PBM) can be used to inform DBAs when an incorrect setting is being used on a SQL instance and in some cases, can restrict changes being made to the SQL instance. Microsoft explain it as follows...

“Policy-Based Management is a policy based system for managing one or more instances of SQL Server. Use is to create conditions that contain condition expressions. Then, create policies that apply the conditions to database target objects. For example, as the database administrator, you may want to ensure that certain servers do not have Database Mail enabled, so you create a condition and a policy that sets that server option.”

Most settings within the database\instance environment can be monitored with PBM. The feature also includes the ability to use TSQL queries with the ExecuteSQL function. For processes outside of the SQL instance, the ExecuteWQL function can be used. Unfortunately, this is where I have run into problems in the past which has made me explore using Powershell as an alternative.

My PBM checks run daily and any failures populate the following tables

• syspolicy_policy_execution_history

• syspolicy_policy_execution_history_details

A stored procedure then queries these tables and sends an email to the DBA team showing which policies failed on each instance.

For this example, I will show how to use Powershell to check the status of SQL Browser and how to report if it fails the policy. The check will be triggered by a SQL Agent job.

Condition and Policy 

The following condition and policy are created.

The condition is deliberately set to check that 1= 0 which will obviously fail!

Powershell 

Two Powershell scripts are created. The first script is used to make the check. It takes in 3 parameters. The Hostname\Instance name the check is being executed on, the Policy name of the check and a hashtable containing any variables needed in the script.

There's a reference to the second Powershell script and the ability to write out the parameter values using the verbose switch. The second Powershell script needs to be in the location referenced. Although I've used C:\temp, this can be a network share.

The script then has the logic to test whether the check passes or fails. In this case, the required status and actual status of the service are retrieved. The required status is a value passed into the script from SQL Agent (which will be shown further down) and the actual status is found using the get-service cmdlet.

The values are compared and either the script finishes if the comparison is equal or the second Powershell script is executed if theres a difference.

The second Powershell script, or Execute-PBM as its called, is executed when the required status is different to the actual status. The script takes the Hostname\Instance name and Policy name as parameters. These values are used with the SQL provider for Powershell to expose and set the location to the PBM folder of the SQLServer drive. The Policy is then invoked.

This will then execute the Policy which is set to fail and populate the relevant DMVs.

SQL Agent Job

The process can be scheduled using SQL Agent.

A job or jobstep can be created to run the script. The jobstep should be of type Operating system (CmdExec).

And the command needs to call the first powershell script, saved as a ps1 script on the file system, with the 3 parameters from the script specified.

The HostnameInstanceName parameter uses a SQL Agent token, which passes in the @@ServerName value. The advantage of using the SQL Agent token is that each job\jobstep doesn't need to be manually updated for each instance. The PolicyName needs to be the same as the policy set up in SSMS. The variables parameter is set up as a hashtable and can accept multiple values as a key\value pair. In this example, the SQLBrowserRequiredStatus is passed into the script with the value of Running. If the script requires further variables then they can be added to the Variables parameter using key = value. Separate each key\value pair with a semi-colon.

Conclusion

Powershell can be used in conjunction with Policy Based Management to extend the scope of checks that can be performed.

TempDB Multiple Datafiles

Problem

Most DBAs are aware that (prior to SQL 2016) one of the most common, post installation changes that needs to be made in SQL Server is the addition of extra TempDB datafiles. What seems to be less well known is that ALL these files all need to be of equal size and need to expand in equal increments.

The addition of extra TempDB files is to alleviate contention on the PFS, GAM and SGAM pages (these are the pages that help SQL decide which pages to use to store data; meta-pages maybe?). The idea being that, basically, the extra datafiles means more PFS, GAM and SGAM pages, which means TempDB workloads have a greater number of pages they can use hence less time spent waiting for used pages to become unlatched. The reason for the EQUALLY sized datafiles is the way SQL allocates the data to each datafile. SQL will check to see if all the datafiles have the same amount of free space. If so, it will add data in a “round robin” fashion, where each datafile will take it in turn to store data. This is how the contention on the PFS, GAM and SGAM pages are reduced. If one of the TempDB files is of a greater size than the rest and has more free space, SQL will prioritise this one file over the others and use it until the free space becomes the same across all files in the filegroup.

Example

In the following example, i've used sys.dm_io_virtual_file_stats to capture the MB written and IO count on each file in TempDB. A baseline is taken initially, then a query is executed 5 times which creates a temporary table and fills it with data and finally another snapshot of sys.dm_io_virtual_file_stats is taken and the results used to find the totals.

Queries

Before starting the TempDB workload the following query is executed 

SELECT GETDATE() as dtStart,
iovfs.*,
mf.name,
mf.type_desc
INTO #dm_io_virtual_file_stats_start
FROM sys.master_files mf
INNER JOIN sys.dm_io_virtual_file_stats(2,NULL) iovfs
ON mf.database_id = iovfs.database_id and mf.file_id = iovfs.file_id

The following query is executed 5 times to generated a workload in TempDB

SELECT TOP ( 2000000 )
fn = LEFT( o.name,  64 ),
ln = LEFT( c.name,  64 ), em = LEFT( o.name,  LEN ) c.name )% 5+1 ) + '.'
+ LEFT( c.name,  LEN ( o.name )% 5+2 ) + '@'
+ RIGHT( c.name, LEN ( o.name + c.name )% 12 + 1 )
+ LEFT( RTRIM ( CHECKSUM ( NEWID ())), 3 ) + '.com' ,
a  = CASE WHEN c.name LIKE '%y%' THEN 0 ELSE 1 END
INTO #temptest
FROM sys.all_objects AS o
CROSS JOIN sys.all_columns AS c

And finally the following SQL is executed to compute the results.

SELECT
DB_NAME ( t1.[database_id] ) [DatabaseName] , t1.[name] [LogicalName] ,
(SUM(t2.[num_of_bytes_written])-SUM(t1.[num_of_bytes_written]))/1048576
AS [Written_MB]
, SUM(t2.[num_of_reads]+t2.[num_of_writes])-SUM(t1.[num_of_reads]+t1.[num_of_writes])
AS [IO_Count]
FROM #dm_io_virtual_file_stats_start AS t1
INNER JOIN
(SELECT iovfs.*,mf.[name],mf.[type_desc]
FROM sys.master_files mf
INNER JOIN sys.dm_io_virtual_file_stats (2,NULL) iovfs
ON mf.[database_id]= iovfs.[database_id] AND mf.[file_id] = iovfs.[file_id]) t2
ON t1.[database_id] = t2.[database_id] AND t1.[file_id] = t2.[file_id]
GROUP BY DB_NAME(t1.[database_id]), t1.[name]
ORDER BY IO_Count DESC

Results

1 TempDB file

Using a single TempDB datafile...

the following values were observed.

2 Equal TempDB files

Using two equal TempDB files

the following values were observed. Note the almost equal Written_MB and IO_Count.

2 Unequal TempDB files

Using two unequal TempDB files

the following values were observed. Note the unequal Written_MB and IO_Count.

4 Equal sized TempDB files

Using four equally sized TempDB files

the following values were observed.

4 Unequal TempDB files

And finally, Using four unequally sized TempDB files

the following values were observed.

Solutions

There are a couple of ways, when creating multiple TempDB files, to make sure they stay evenly sized. My preference is to dedicate TempDB datafiles to its own drive. Divide the drive size by the number of datafiles, create each datafile that size and dont allow them to autoextend. This can cause problems if an errent query uses all the space in TempDB, in which case the query will need to rewriten to use less TempDB space or additional space will need to be added to the drive.

A trace flag, -T1117, can be also be used. This trace flags grows all datafiles in a filegroup at the same time. Beware that this is a global trace flag and will affect all database on an instance.

Conclusion

Its important, when creating multiple TempDB files, that each file is of equal size. Be aware of TempDBs usage patterns and how each datafile will grow if fully used.