What are forwarded records?

Forwarded records are records that reside on a new page when a heap table (table without clustered index) is updated, and the rows are no longer able to stay on the same page,

which causes a forwarding pointer to point to the new page that the row resides in.

Many forwarded records can cause serious I/O issues, and if this happens a lot, it is advised to create a clustered index on the table.

You can also rebuild the table to remove the forwarded records (high overhead).

 

 

sys.dm_db_index_physical_stats

sys.dm_db_index_physical_stats is the dmv used to find the forwarded records,

but this is a very I/O intensive dmv to query, which is why we will apply filtering in the script.

 

 

The script:

We will use a temp table to apply filtering, and only include the heap tables in our dmv checks.

 

SELECT O.*
INTO #temp
FROM   sys.objects O
inner join sys.tables AS T on o.object_id = T.object_id
INNER JOIN sys.schemas AS S 
ON T.schema_id = S.schema_id 
INNER JOIN sys.indexes AS I 
ON T.object_id = I.object_id 
AND I.type = 0;


SELECT  DB_NAME(db_id()) as DatabaseName,
		t.name as TableName,
		index_id, 
		index_type_desc,
		avg_fragmentation_in_percent,
		page_count,
		record_count, 
		forwarded_record_count 
FROM
#temp t
CROSS APPLY 
sys.dm_db_index_physical_stats
(
   DB_ID()
   ,t.object_id
   ,NULL
   ,NULL
   ,'DETAILED'
)
WHERE forwarded_record_count > 0 and t.is_ms_shipped =0

DROP TABLE #temp

 

 

I hope this script helped you achieve your goal, with the least amount of overhead.

 

 

Script to script out the default constraints in  a database:

 

select

'ALTER TABLE [' + SCHEMA_NAME(dc.schema_id) + '].[' +t.name + ']

ADD CONSTRAINT '+ dc.name +' DEFAULT ' + dc.definition +' FOR '+ c.name from sys.default_constraints  as dc

inner join sys.tables t

on dc.parent_object_id = t.object_id

inner join sys.columns c

on dc.parent_column_id = c.column_id

and dc.parent_object_id = c.object_id

What is the query store?

The query store is a very easy way to track the queries happening in your database, with pre made
charts and filters, queries can be aggregated on cpu, reads, execution count, ….
You can also force query plans, so a query always uses a certain plan. We will be using the force
query plan further down this blog post.

Scenario:

You just migrated to a newer version of SQL Server (SQL 2016 and upwards) and now, a query that
had no issues previously, is now behaving worse than it should.
The most common reason for this are the query optimizer changes added in SQL 2014. Which causes
this issue to occur when migrating from SQL 2012 or even older versions.
Most of the time, the query optimizer should pick the better plan, but in some cases, this does not go
as planned.

The reason(s):

This might be because your query outdated, uses syntax that is already not the best approach
(functions in joins or where columns, implicit conversions , … .)
If that is the case, the best approach would be to rewrite it. Sometimes that is not possible. Reasons
could be: the application does not allow it, it needs to be thoroughly tested first, etc.

If changing the query structure is not possible, the query store might be your solution.
An important thing to note is that, when forcing a plan for a specific query, not only plans in
compatibility mode 130 or 140 can be captured, but also in compatibility 110 or lower.

A extra factor is, that your queries need to be already parameterized, so they use the same query plan for different parameters.

Step 1 Change the compatibility of your database to 130 or 140, and turn the
query store on

 

alter database testdb set compatibility_level = 140;
ALTER DATABASE testdb SET QUERY_STORE = ON;

If you’d like to, you can execute the problematic query first, or directly execute it under a lower
compatibility level.

Step 2 Execute and monitor the query

1) First execute the query that is no longer problematic in the lower compatibility level.
2) Afterwards, find your query in the query store:
To find your query id in the query store, you could execute the following statemetns:

 

SELECT q.query_id, t.query_sql_text, object_name(q.object_id) AS parent_object
FROM sys.query_store_query_text t JOIN sys.query_store_query q
ON t.query_text_id = q.query_text_id
WHERE t.query_sql_text LIKE N'%Enter query here %'

For my query i executed the following statement:

 

SELECT q.query_id, t.query_sql_text, object_name(q.object_id) AS parent_object
FROM sys.query_store_query_text t JOIN sys.query_store_query q
ON t.query_text_id = q.query_text_id
WHERE t.query_sql_text LIKE N'%select top(@p5)%'

Result:

 

 

Go to the query store folder in your database,

and select tracked queries –>  view tracked queries

Enter the query id in the tracking query pane:

 

 

press enter

 

You see, that the one way up top, was when executing the query in compatibility level 140, which had way more reads and cpu time ( > 30 seconds elapsed vs < 1 second).

 

Step 3: Force the query plan that was used under compatibility mode 110 or lower

 

 

press yes.

 

Step 4: set the compatibility level back to 140

alter database testdb set compatibility_level = 140;

End notes

And that’s all there is to it, the better query plan in compatibility mode 110 will be used for future
executions in higher compatibility modes.
There are definitely better solutions than this, but this might be helpful for some of you.
There are ways that plan forcing can fail, for example if the database name changes, plan forcing will
stop working, since query plans use a three part naming convention.

 

Other ways to accomplish this

– Use the following trace flag to mimic pre 2014 query optimizer, but that also requires
changes to the query itself:
OPTION (QUERYTRACEON 9481).
– You could manually force a plan with the USE PLAN query hint.
– Change the databases compatibility level entirely.
– Change the query itself.
– See if statistics / index changes help the query.

I recently needed to script out some extended properties, and create them on a different database.

This might help some of you when migrating, scripting out your objects, …:

SELECT cast('EXEC sp_addextendedproperty N' +char(39)+
cast(ep.name as nvarchar(255))+char(39)+', N'+char(39)+
cast(ep.value as nvarchar(255))+char(39)+', '+ char(39)+'SCHEMA' + char(39)
+ ', N'+char(39)+ cast( S.name as nvarchar(255)) +char(39)+', '+char(39)+ 'TABLE'
+ char(39)+', N'+char(39)+ cast(T.name as nvarchar(255)) +char(39)+', '+char(39)+
'CONSTRAINT' + char(39)+ ', N'+ char(39)+ cast(fk.name as nvarchar(255))
+ char(39) as nvarchar(400))

FROM sys.extended_properties EP

inner join sys.foreign_keys fk on EP.major_id = fk.object_id

inner join sys.tables t on fk.parent_object_id = t.object_id

LEFT JOIN sys.schemas S on fk.schema_id = S.schema_id

LEFT JOIN sys.all_objects O ON ep.major_id = O.object_id

 

 

Example of the result:

EXEC sp_addextendedproperty N'FK_CHECK_Filter', N'(1 = 0)', 'SCHEMA', N'stp', 'TABLE', N'CRA', 'CONSTRAINT', N'FK_CRA_Country'

Intro

For a customer, I recently migrated one of their databases from 2012 to 2017, whilst putting compression on all the indexes.

The process of recreating the Indexes on 8 new filegroups took ~3hours for a 400GB database.

Specs

From SQL Server 2012 (11.0.7001)

To SQL Server 2017 CU 1 (14.0.1000)

Windows Server 2012 R2

 

Query execution

When manually executing the queries, life was bliss.

When putting the queries needed to complete the task in a job with a few steps for automation purposes, the job completed since there was try – catch logic present.

When I checked the errors captured I was greeted with this message, which was not present when I manually ran the queries:

“CREATE INDEX failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.”

 

What is the QUOTED_IDENTIFIER setting?

Setting this on means that characters enclosed in double quotes, “ “ are treated as identifiers instead of literals. The default for this is ON, and this is a general better practice.

From Microsoft docs:

“When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by double quotation marks are interpreted as object identifiers…”

“When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. “

“SET QUOTED_IDENTIFIER must be ON when you are creating a filtered index.” https://docs.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql

Since I had filtered indexes, QUOTED_IDENTIFIER needed to be ON at all times when executing the query’s.

 

Testing

If I execute the following statement under my account (sysadmin) on the database to be compressed

DECLARE @QUOTED_IDENTIFIER VARCHAR(3) = 'OFF';

IF ( (256 & @@OPTIONS) = 256 ) SET @QUOTED_IDENTIFIER = 'ON';

SELECT @QUOTED_IDENTIFIER AS QUOTED_IDENTIFIER;
QUOTED_IDENTIFIER
ON

 

When I put the query to check the quoted identifier in a job and run it, (in the user database to be compressed) this happens:

 

create table dbo.QUOTEDIDENTIFIER_CHECK(QUOTEDIDENTIFIER varchar(3)) 
DECLARE @QUOTED_IDENTIFIER VARCHAR(3) = 'OFF'; IF ( (256 & @@OPTIONS) = 256 ) 
SET @QUOTED_IDENTIFIER = 'ON'; SELECT @QUOTED_IDENTIFIER AS QUOTED_IDENTIFIER; 
insert into dbo.QUOTEDIDENTIFIER_CHECK(QUOTEDIDENTIFIER) select @QUOTED_IDENTIFIER

 


select * from dbo.QUOTEDIDENTIFIER_CHECK;


QUOTED_IDENTIFIER
OFF

 

A simple approach to resolve this was to set it to ON.

 

QUOTED_IDENTIFIER
ON

 

And that is what I did, added SET QUOTED_IDENTIFIER ON; to the executed statements.

But, I also wanted to know what causes this.

 

More testing

When tested with the job on a different server :11.0.7001.0

QUOTED_IDENTIFIER
OFF

On yet another server: 14.0.3015.40

QUOTED_IDENTIFIER
OFF

 

After playing around with the connections at server and database level, I found what caused this.

 

The Culprit

 

When checking the profiler when executing the job, I found that it gets explicitly set to off before the step execution. Under the SQLSERVERAGENT service account (also not best practice but it illustrates the point better).

Closer look:

Executed by the agent:

Seems that this is default behavior for the Agent service. For now, I have not found a better way than explicitly setting quoted identifier on .

The reason behind this behavior, is something that I am still looking for, since it is not documented in the Microsoft docs on quoted identifier.

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql

 

Easy management with customized dashboards

SQL Operations Studio is a standalone application that lets you put frequently used queries into a dashboard for a quick overview of all your databases. The software is lightweight and uses very little resources, so it can effortlessly run next to more demanding tools like SQL Server Management Studio.

Using queries on your databases to get information such as available disk size, isn’t new. Before, you could manually run scripts on each database to extract statistics and then combine them. However, if you need to check this data on a daily basis, it becomes quite cumbersome and time-consuming. After a one-time setup per query, Operations Studio runs them for you and presents the results in clear graphs on a dashboard. Want to see the status of available disk size the next day too? Just refresh. With SQL Operations Studio you can now get insight into your databases with a push of the button.

 

Common graphs to use in SQL Operations Studio

We’ve tinkered with queries and creating a dashboard in SQL Operations Studio. Below you’ll find a couple of useful graphs to include with the needed code. Keep in mind, though that not every graph works for every query.

 

Available disk size

Query:

SELECT DISTINCT SUBSTRING(volume_mount_point, 1, 1) as drive_letter,

(total_bytes/1024/1024 - available_bytes/1024/1024) as usedsize_mb,

available_bytes/1024/1024 as available_size_mb

FROM sys.master_files AS f

CROSS APPLY

sys.dm_os_volume_stats(f.database_id, f.file_id)

CROSS APPLY

sys.dm_io_virtual_file_stats(f.database_id, f.file_id)

Graph:

Fig1. Okay for a pie chart

 

 

If we convert this pie chart to a bar chart however, we get a distorted bar chart, and a very imprecise view of the data.

Fig2. Not okay for a bar chart.jpeg

 

And if you try to add even more values, like wait stats, things go terribly wrong:

Fig3. Whoops

 

How many databases are offline or online:

Fig5. Database state

Script:

select d.state_desc,

count(d.state_desc) as state

from   sys.databases d

where replica_id is NULL

group by d.state_desc

How many logins have sysadmin rights

Fig6. Sysadmins <-> NonSysAdmins  (and yes, my security policy sucks.)

Script:

SELECT (select count(*) from sys.syslogins where  sysadmin <> 1)   as amount_of_logins_not_sysadmin,
			(select count(sysadmin)  from sys.syslogins where  sysadmin = 1) as amount_of_sysadmins

How many jobs have been scheduled or need to be scheduled

Fig 7. Looks like some jobs need to be scheduled.

Script:

SELECT (select count(s.name) FROM   msdb..sysschedules s 
inner join msdb..sysjobschedules a on s.schedule_id = a.schedule_id 
inner join msdb..sysjobs j on a.job_id = j.job_id ) as amount_of_jobs_scheduled,

(select (select count(job_id) from msdb..sysjobs)  - 
(select count(s.name) FROM   msdb..sysschedules s 
inner join msdb..sysjobschedules a on s.schedule_id = a.schedule_id 
inner join msdb..sysjobs j on a.job_id = j.job_id )) as amount_of_jobs_not_scheduled

Get InstanceInformation

Fig 8. InstanceInformation

This one is a bit trickier. The script is bigger and the widget and chart are customized, since the default settings could not display the graph. We created the count chart using create insight and tweaked the JSON settings.

CLR

Intro

CLR assemblies are created to execute .NET code inside SQL Server.
All assemblies that are ‘Safe’ or have ‘external access’ as permission set will be treated as unsafe assemblies
in SQL 2017. This, because safe assemblies in theory don’t access external resources, can still happen.
This means that starting from SQL 2017 all assemblies need to be signed with either a certificate or an
assymetric key.

While implementing this is useful, you also need to be able to trust the assemblies and other external resources running on your
instance.

Example

 

The next snippet succeeds on SQL Server 2016, but fails on SQL Server 2017:

 

CREATE ASSEMBLY UnsignedAssembly FROM

 'C:\Users\vertora\Desktop\ \DLLFiles\SafeAssembly.dll' --> UnSigned "safe" Assembly

WITH PERMISSION_SET= SAFE;

 

Error message:

Msg 10343, Level 14, State 1, Line 45

CREATE or ALTER ASSEMBLY for assembly ‘SafeAssembly’ with the SAFE or EXTERNAL_ACCESS option failed because the ‘clr strict security’ option of sp_configure is set to 1. Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. Alternatively, you can trust the assembly using sp_add_trusted_assembly.

 

Remarks

You can disable this setting, but that is not advised, since assemblies are never safe, and should only be executed if they are trusted.

Be advised, when upgrading your SQL Server too SQL Server 2017, that your assemblies will stop working if they are not signed.

 

 

The new security features in SQL Server 2017 are something that every business could use.

In this 4 part series, i will be talking about the most important features in SQL Server 2017.

 

Dynamic data masking

Intro

Encrypt sensitive information on certain columns by using DDM.

Something to note with this, is that you need to make sure that ad hoc querying for these users is locked down, since they can still filter on these encrypted rows,

and find matching data based on predicates for example by adding a where clause to the queries.

 

Example

 

We create a table, apply a couple of masking functions on it , and fill it with some records,.

 

CREATE TABLE Kohera 
(ID int IDENTITY PRIMARY KEY, 
FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL, 
LastName varchar(100) NOT NULL, 
PhoneNumber varchar(12) MASKED WITH (FUNCTION = 'default()') NULL, 
EmailAddress varchar(100) MASKED WITH (FUNCTION = 'email()') NULL);
INSERT Kohera (FirstName, LastName, PhoneNumber, EmailAddress) VALUES 
('Randi', 'Vertongen', '0488434015', 'randi.vertongen@kohera.be'), 
('Frederik', 'Bogaerts', '0476673854', 'frederik.bogaerts@kohera.be'), 
('Stijn', 'Wynants', '0476348910', 'stijn.wynants@kohera.be');

If I select everything in the table (sysadmin) everything is returned:

SELECT * FROM Kohera;

IDFirstNameLastNamePhoneNumberEmailAddress
1RandiVertongen0488434015randi.vertongen@kohera.be
2FrederikBogaerts0412345678frederik.bogaerts@kohera.be
3StijnWynants0412345678stijn.wynants@kohera.be

 

But if a basic user has select permissions:

 

CREATE USER TestUser WITHOUT LOGIN; 
GRANT SELECT ON Kohera TO TestUser;
EXECUTE AS USER = 'TestUser'; SELECT * FROM Kohera;
REVERT;

 

The result is different:

 

IDFirstNameLastNamePhoneNumberEmailAddress
1RXXXXXXXVertongenxxxxxxxxxrXXX@XXXX.com
2FXXXXXXXBogaertsxxxxxxxxxfXXX@XXXX.com
3SXXXXXXXWynantsxxxxxxxxxsXXX@XXXX.com

 

Final thoughts

As noted above, if the user can apply ad hoc queries to the database,

you will need to remember that they can apply filters  on  the masked data.

But if handle accordingly, and restrict your user permissions, this can be a very useful tool.

Intro

In SQL Server 2016 SP1 Standard Edition, you can create Basic AG’s, which can only hold one database for each AG.

Recently a customer asked me to make sure that all databases stay primary on the same instance when one or more fail-over to the other side.

Setting:

SQL Server 2016 SP 1

3 Basic availability groups with 1 database each.

Reasoning:

To help him reach his goal, I created the following script, which will run via a job, every 10 seconds on  both servers, and if the server has exactly 2 primaries, then that instance will issue a command so the last secondary fails over to his side, which results in 3 primaries remaining on the same instance.

If an even amount of groups is used, you will need to come up with a plan so they don’t start failovers at the same time. ( Like adding another ‘Quorum’ AG or setting different start times).

Script:

if (
SELECT
	count(name)
FROM
 sys.availability_groups_cluster AS AGC
  INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
   ON
    RCS.group_id = AGC.group_id
  INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
   ON
    ARS.replica_id = RCS.replica_id
  INNER JOIN sys.availability_group_listeners AS AGL
   ON
    AGL.group_id = ARS.group_id
WHERE
 ARS.role_desc = 'PRIMARY' 
 ) = 2
 begin
		if (
			select count(*) from
			sys.availability_groups_cluster AS AGC
			INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
			 ON
			RCS.group_id = AGC.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
			ON
			ARS.replica_id = RCS.replica_id
			INNER JOIN sys.availability_group_listeners AS AGL
			ON
			AGL.group_id = ARS.group_id
			WHERE
			 name = 'AG1') = 1
			begin
				ALTER AVAILABILITY GROUP [AG1] FAILOVER;
			end
		if (
			select count(*) from
			sys.availability_groups_cluster AS AGC
			INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
			 ON
			RCS.group_id = AGC.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
			ON
			ARS.replica_id = RCS.replica_id
			INNER JOIN sys.availability_group_listeners AS AGL
			ON
			AGL.group_id = ARS.group_id
			WHERE
			 name = 'AG2') = 1
			begin
				ALTER AVAILABILITY GROUP [AG2] FAILOVER;
			end
		if (
			select count(*) from
			sys.availability_groups_cluster AS AGC
			INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
			 ON
			RCS.group_id = AGC.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
			ON
			ARS.replica_id = RCS.replica_id
			INNER JOIN sys.availability_group_listeners AS AGL
			ON
			AGL.group_id = ARS.group_id
			WHERE
			 name = 'AG3') = 1
			begin
				ALTER AVAILABILITY GROUP [AG3] FAILOVER;  
			end

 end