SQL Grillen

I and many colleagues went to SQL Grillen last week,

It was in my opinion, the perfect balance between Learning and fun.

6 sessions back to back is not to be underestimated,  it’s not  only about the BBQ and booze ;).

 

The sessions

First session I attended was statistics behind the curtain by Uwe Ricken,

He was handed a beer by the organizators and off he went.

It was a nice refreshing session on statistics, with some good examples on some common statistics pitfalls when designing queries. And what to look out for in your production environment.

The next session was  a newcomer session on Recursive Cte’s: “Recursive CTEs – cursing again and again?”

We actually went to that session since a colleague had issues with designing a recursive CTE to get information from top to bottom, but also include the parents of all the childs referenced.

We came to the conclusion that that recursive cte design was probably not going to end wel… .

 

The last session before lunch was “Self Healing” Query execution in SQL 2017 by Christina Leo,

The start was good and clear cut. Unfortunately one demo on the “adaptive query processing” did not work, which made the session a bit more “all over the place”. I still liked the presenter’s presentation style and the new upcoming features look very cool.

 

After the lunch, I attended DevOps for the DBA by Grant Fritchey. Grant is an experienced presenter, and it shows. Very dynamic and open session on DevOps.

The next session was SQL Containers in the cloud, an intro to running docker in Azure, I was lost trying to follow the code, but it really showed how easy it is to deploy images to azure, and spin up one or more nodes that are running SQL Server, and a very interesting example why you should enable two factor authentication when using Azure.

 

The last session I followed was SQL Server Problem Tombola by Stijn Wynants, one of my colleagues.

What else can I say than that it was a very fun, entertaining session, and that I learned a very interesting fact about SQL Server. If you want to find out which fact(s), you will have to attend his session :).

 

Fun for the whole (SQL) family

It was  all in all a great event, I even entered in a raffle to win a drone (sadly, i did not win)!

I am really hoping to go next year as well!

Intro

When looking at your index usage stats, you might see that all of your indexes are being used, and that life is good.

But when you have similar indexes on tables in your database, they could both have some seeks / scans / lookups, since the queries could randomly be selecting one.

Duplicate indexes take up unneeded space, and will slow down update / insert / delete operations.

 

Tracking down these bad boys

 

The below script will help you track down these overlapping indexes, currently up to 3 key columns deep.

It will notify you if one, two or all three key columns match an other index in the same table.

Then it will also display other information about the index and the other similar indexes.

On my DB with +600 indexes, it worked almost instantly, but to be sure you should execute this on a dev copy of the production database.

 

 

/**
Script created by Randi Vertongen, first part to get the index + key columns is not mine, need to find where i got it from.
Created on : 30/05/2018.

Last edited on: 31/05/2018

This script will try to find all indexes with the same key_columns, at this point up to 3 levels deep. 
It will issue a print statement for eacht level (first key column matches, first and second, first,second and third)

Could be rewritten more efficiently.

**/



use Climate
create table #temp(
id int identity(1,1),
table_name nvarchar(255),
is_unique nvarchar(50),
key_columns nvarchar(max),
include_columns nvarchar(max),
index_id int,
index_name nvarchar(4000),
TypeIndex nvarchar(100))

insert into #temp(table_name,is_unique,key_columns,include_columns,index_id,index_name,TypeIndex)
SELECT 
    quotename(sc.name) + N'.' + quotename(t.name) AS table_name,
	  CASE WHEN si.is_unique = 1 then N'UNIQUE ' ELSE N'' END as Is_unique,
	key_definition as key_columns, 
	include_definition as include_columns,
    si.index_id,
    si.name AS index_name,
	case when si.index_id = 1 then 'CLUSTERED' else 'NONCLUSTERED' end as TypeIndex
FROM sys.indexes AS si
JOIN sys.tables AS t ON si.object_id=t.object_id
JOIN sys.schemas AS sc ON t.schema_id=sc.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS stat ON 
    stat.database_id = DB_ID() 
    and si.object_id=stat.object_id 
    and si.index_id=stat.index_id
LEFT JOIN sys.partition_schemes AS psc ON si.data_space_id=psc.data_space_id
LEFT JOIN sys.partition_functions AS pf ON psc.function_id=pf.function_id
LEFT JOIN sys.filegroups AS fg ON si.data_space_id=fg.data_space_id
/* Key list */ OUTER APPLY ( SELECT STUFF (
    (SELECT QUOTENAME(c.name) +N'^^ '
    FROM sys.index_columns AS ic 
    JOIN sys.columns AS c ON 
        ic.column_id=c.column_id  
        and ic.object_id=c.object_id
    WHERE ic.object_id = si.object_id
        and ic.index_id=si.index_id
        and ic.key_ordinal > 0
    ORDER BY ic.key_ordinal FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,0,'')) AS keys ( key_definition )
/* Partitioning Ordinal */ OUTER APPLY (
    SELECT MAX(QUOTENAME(c.name)) AS column_name
    FROM sys.index_columns AS ic 
    JOIN sys.columns AS c ON 
        ic.column_id=c.column_id  
        and ic.object_id=c.object_id
    WHERE ic.object_id = si.object_id
        and ic.index_id=si.index_id
        and ic.partition_ordinal = 1) AS partitioning_column
/* Include list */ OUTER APPLY ( SELECT STUFF (
    (SELECT N', ' + QUOTENAME(c.name)
    FROM sys.index_columns AS ic 
    JOIN sys.columns AS c ON 
        ic.column_id=c.column_id  
        and ic.object_id=c.object_id
    WHERE ic.object_id = si.object_id
        and ic.index_id=si.index_id
        and ic.is_included_column = 1
    ORDER BY c.name FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS includes ( include_definition )
/* Partitions */ OUTER APPLY ( 
    SELECT 
        COUNT(*) AS partition_count,
        CAST(SUM(ps.in_row_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_in_row_GB,
        CAST(SUM(ps.lob_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_LOB_GB,
        SUM(ps.row_count) AS row_count
    FROM sys.partitions AS p
    JOIN sys.dm_db_partition_stats AS ps ON
        p.partition_id=ps.partition_id
    WHERE p.object_id = si.object_id
        and p.index_id=si.index_id
    ) AS partition_sums
/* row compression list by partition */ OUTER APPLY ( SELECT STUFF (
    (SELECT N', ' + CAST(p.partition_number AS VARCHAR(32))
    FROM sys.partitions AS p
    WHERE p.object_id = si.object_id
        and p.index_id=si.index_id
        and p.data_compression = 1
    ORDER BY p.partition_number FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS row_compression_clause ( row_compression_partition_list )
/* data compression list by partition */ OUTER APPLY ( SELECT STUFF (
    (SELECT N', ' + CAST(p.partition_number AS VARCHAR(32))
    FROM sys.partitions AS p
    WHERE p.object_id = si.object_id
        and p.index_id=si.index_id
        and p.data_compression = 2
    ORDER BY p.partition_number FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS page_compression_clause ( page_compression_partition_list )
WHERE 
    si.type IN (2) /* heap, clustered, nonclustered */
ORDER BY table_name, si.index_id
    OPTION (RECOMPILE);
GO
set nocount on;
declare @tablename nvarchar(4000 ) 
declare @index_name nvarchar(4000 ) 
declare @index_name2 nvarchar(4000 ) 
declare @sql nvarchar(max)
declare @searchstring nvarchar(max)
declare @searchstring2 nvarchar(max)
declare @searchstring3 nvarchar(max)
declare @searchcolumn nvarchar(4000)
declare @searchcolumn2 nvarchar(4000)
declare @searchcolumn3 nvarchar(4000)
declare @pkname nvarchar(500)

create table #indexing(id int identity(1,1),
					  Tablename nvarchar(4000),
					  Indexname nvarchar(4000), 
					  Overlappingindex nvarchar(4000),
					  AmountOfColumnsMatching tinyint,
					  KeyColumnsMatched nvarchar(4000), 
					  AllKeyColumns nvarchar(4000), 
					  IncludedColumns nvarchar(4000),
					  IncludedColumnsOverlappingIndex nvarchar(4000),
					  KeyColumnsOverlappingIndex nvarchar(4000),
					  AmountofKeyColumns int )

-- Table that will be checked - cursor
declare c cursor for
SELECT distinct  table_name from #temp
open c
fetch next from c into @tablename
while @@FETCH_STATUS = 0
begin

	-- Index that will be checked - cursor
	declare D cursor for
	SELECT   index_name from #temp where table_name = @tablename
	open D
	fetch next from D into @index_name
	while @@FETCH_STATUS = 0
	begin	

			-- Other indexes on table cursor 
			declare F cursor for
			SELECT   index_name from #temp where table_name = @tablename and @index_name != index_name 
			open F
			fetch next from F into @index_name2
			while @@FETCH_STATUS = 0
			begin
				--Search for the first Column of other index
				set @searchstring= (
				SELECT case when charindex('^^',key_columns) = 0 then '' 
				else LEFT(key_columns,(charindex('^^',key_columns)-1)) end   
				FROM #temp p2  where  TABLE_NAME = @tablename and index_name = @index_name2 --get the first key column of  all other indexes
				FOR XML PATH(''))

				--Search for the first Column of own index
				select @searchcolumn = case when  (charindex('^^',key_columns)) = 0 then NULL else left(t1.key_columns,(charindex('^^',key_columns)-1)) end
				from #temp t1 where TABLE_NAME = @tablename and @index_name = index_name 

				--If it matches, proceed
				if (CHARINDEX(@searchcolumn,@searchstring)) > 0
				BEGIN
					insert into #indexing(tablename,indexname,overlappingindex,AmountOfColumnsMatching,AmountofKeyColumns ,KeyColumnsMatched,allkeycolumns,includedcolumns,keycolumnsoverlappingindex,includedcolumnsoverlappingindex)
					select @tablename,@index_name,@index_name2,1,(SELECT count(c.name)
					FROM sys.indexes i
					inner join 
					sys.index_columns AS ic on ic.index_id = i.index_id and ic.object_id = i.object_id
					JOIN sys.columns AS c ON 
					ic.column_id=c.column_id  
					and ic.object_id=c.object_id
					WHERE 
					i.name = @index_name
					and ic.key_ordinal > 0)
					,@searchcolumn, 
					(SELECT QUOTENAME(c.name)+','
					FROM sys.indexes i
					inner join 
					sys.index_columns AS ic on ic.index_id = i.index_id and ic.object_id = i.object_id
					JOIN sys.columns AS c ON 
					ic.column_id=c.column_id  
					and ic.object_id=c.object_id
					WHERE 
					i.name = @index_name
					and ic.key_ordinal > 0
					ORDER BY key_ordinal asc
					FOR XML PATH('')) ,
					(SELECT QUOTENAME(c.name)+','
					FROM sys.indexes i
					inner join 
					sys.index_columns AS ic on ic.index_id = i.index_id and ic.object_id = i.object_id
					JOIN sys.columns AS c ON 
					ic.column_id=c.column_id  
					and ic.object_id=c.object_id
					WHERE 
					i.name = @index_name
					and ic.key_ordinal = 0
					ORDER BY key_ordinal asc
					FOR XML PATH('')), (SELECT QUOTENAME(c.name)+','
					FROM sys.indexes i
					inner join 
					sys.index_columns AS ic on ic.index_id = i.index_id and ic.object_id = i.object_id
					JOIN sys.columns AS c ON 
					ic.column_id=c.column_id  
					and ic.object_id=c.object_id
					WHERE 
					i.name = @index_name2
					and ic.key_ordinal > 0
					ORDER BY key_ordinal asc
					FOR XML PATH('')) ,(SELECT QUOTENAME(c.name)+','
					FROM sys.indexes i
					inner join 
					sys.index_columns AS ic on ic.index_id = i.index_id and ic.object_id = i.object_id
					JOIN sys.columns AS c ON 
					ic.column_id=c.column_id  
					and ic.object_id=c.object_id
					WHERE 
					i.name = @index_name2
					and ic.key_ordinal = 0
					ORDER BY key_ordinal asc
					FOR XML PATH(''))

					--Check the second key columns
					set  @searchstring2 = (
					select case when charindex('^^',key_columns) = 0 then '' 
					when CHARINDEX('^^',RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns))))) =0 then ''
					else 
					LEFT(RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns)))),CHARINDEX('^^',RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns)+1))))) end 
					FROM #temp p2  
					where  TABLE_NAME = @tablename and index_name = @index_name2 --get the first key column of  all other indexes
					FOR XML PATH(''))

					

					select @searchcolumn2 =  
					case when charindex('^^',key_columns) = 0 then ''
					else 
					RIGHT(t1.key_columns,(len(t1.key_columns)-(charindex('^^',key_columns)+1)))end 
					from #temp t1 where TABLE_NAME = @tablename and index_name = @index_name

					if CHARINDEX('^^',@searchcolumn2) >0
					BEGIN
						set @searchcolumn2 = LEFT(@searchcolumn2,(charindex('^^',@searchcolumn2)-1))
					END
					ELSE
					BEGIN
					set @searchcolumn2 = ''
					END

					--If the second key columns match, proceed
					if (CHARINDEX(@searchcolumn2,@searchstring2)) > 0
					BEGIN
					update 
					#indexing
					set 
					AmountOfColumnsMatching=2 ,
					KeyColumnsMatched = KeyColumnsMatched +','+@searchcolumn2
					where indexname=@index_name and
					overlappingindex= @index_name2

						--Check the third key columns
						set  @searchstring3 = (
						select case when charindex('^^',key_columns) = 0 then '' 
						when CHARINDEX('^^',RIGHT(RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns)))),((len(key_columns)-(charindex('^^',key_columns))-CHARINDEX('^^',RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns))))))))) = 0 then ''
						else 
						LEFT(RIGHT(RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns)))),((len(key_columns)-(charindex('^^',key_columns))-CHARINDEX('^^',RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns))))))-1)),(CHARINDEX('^^',RIGHT(RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns)))),((len(key_columns)-(charindex('^^',key_columns))-CHARINDEX('^^',RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns))))))-1)))-1))
						end 
						FROM #temp p2  
						where  TABLE_NAME = @tablename and index_name = @index_name2 --get the first key column of  all other indexes
						FOR XML PATH(''))
			

						set  @searchcolumn3 = (
						select case when charindex('^^',key_columns) = 0 then '' 
						when CHARINDEX('^^',RIGHT(RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns)))),((len(key_columns)-(charindex('^^',key_columns))-CHARINDEX('^^',RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns))))))))) = 0 then ''
						else 
						LEFT(RIGHT(RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns)))),((len(key_columns)-(charindex('^^',key_columns))-CHARINDEX('^^',RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns))))))-1)),(CHARINDEX('^^',RIGHT(RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns)))),((len(key_columns)-(charindex('^^',key_columns))-CHARINDEX('^^',RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns))))))-1)))-1))
						end 
						FROM #temp p2  
						where  TABLE_NAME = @tablename and index_name = @index_name --get the first key column of  all other indexes
						FOR XML PATH(''))

						--If the third key column matches, proceed
						if (CHARINDEX(@searchcolumn3,@searchstring3)) > 0
						BEGIN
						update 
						#indexing
						set 
						AmountOfColumnsMatching=3 ,
						KeyColumnsMatched = KeyColumnsMatched +','+@searchcolumn3
					where indexname=@index_name and
					overlappingindex= @index_name2

						END
					END
				END
			fetch next from F into @index_name2
			end
			close F
			deallocate  F
		 
	fetch next from D into @index_name
	end
	close D
	deallocate  D

fetch next from c into @tablename
end
close c 
deallocate  c

drop table #temp

select id,Tablename,Indexname,Overlappingindex,AmountOfColumnsMatching,AmountofKeyColumns,KeyColumnsMatched,AllKeyColumns,IncludedColumns,KeyColumnsOverlappingIndex,IncludedColumnsOverlappingIndex from #indexing

drop table #indexing

 

 

 

 

 

 

 

 

 

 

 

Reasoning

Ever had some indexes in your database that just do not make sense?

For example:

Sales_12052017,

okay you might say, now we know when it is created.

But there are extended events or traces to capture these kind of things,  and that is not what naming your indexes is for.

 

The script

This script will check your indexes for non default naming (IX_) and rename them like this:

IX_TableName_ColumnName1_Columname2_Columname3

The script will also cut off the name if the name is too long.

 

DECLARE  @schemaname nvarchar(200),
		 @tablename nvarchar(200), 
		 @indexname nvarchar(200), 
		 @colname nvarchar(200), 
		 @indexcolumnid int, 
		 @cmd nvarchar(max),
		 @length int;

DECLARE c CURSOR FOR 
SELECT  DISTINCT S.name, 
				 T.name, 
				 I.name 
FROM sys.indexes I 
INNER JOIN sys.index_columns IC 
ON I.object_id = IC.object_id 
AND I.index_id = IC.index_id 
INNER JOIN sys.columns C 
ON ic.object_id = C.object_id 
AND IC.column_id = C.column_id 
INNER JOIN sys.tables T 
ON I.object_id = T.object_id 
INNER JOIN sys.schemas S 
on T.schema_id = S.schema_id
WHERE 
	 I.is_unique_constraint = 0 
	 AND I.is_primary_key = 0
     AND T.is_ms_shipped = 0 
     AND I.is_unique = 0 
	 AND (I.name not like '%IX%' and I.name not like'%PK%')
ORDER BY 
     t.name, I.name
	
open c
fetch next from c into @schemaname, @tablename, @indexname;
while @@FETCH_STATUS = 0
BEGIN
	SET @cmd = 'exec sp_rename N'''+@schemaname+'.'+@tablename+'.'+@indexname+'' +CHAR(39);
	set @length = len(@cmd);
	if @length is null
	BEGIN
	set @length = 0
	END
	
	DECLARE d CURSOR FOR
	SELECT  ic.index_column_id, 
			C.name  
	FROM sys.indexes I 
	INNER JOIN sys.index_columns IC 
	ON I.object_id = IC.object_id 
	AND I.index_id = IC.index_id 
	INNER JOIN sys.columns C 
	ON ic.object_id = C.object_id 
	AND IC.column_id = C.column_id 
	INNER JOIN sys.tables T 
	ON I.object_id = T.object_id 
	INNER JOIN sys.schemas S 
	on T.schema_id = S.schema_id
	WHERE 
	I.is_unique_constraint = 0 
	AND I.is_primary_key = 0
    AND T.is_ms_shipped = 0 
    AND I.is_unique = 0 
	AND (I.name not like '%IX%' and I.name not like'%PK%')
	AND I.name = @indexname
	ORDER BY 
	index_column_id ASC;

	OPEN d
	FETCH NEXT FROM d INTO @indexcolumnid,@colname
	WHILE @@FETCH_STATUS = 0
	BEGIN

	IF @indexcolumnid = 1 
	BEGIN
		set @cmd += ' , N''IX_'+@tablename+'_'+@colname+ ''
	END

	else
	BEGIN
		set  @cmd += '_'+@colname
	END

	FETCH NEXT FROM d INTO @indexcolumnid,@colname
	END
	CLOSE d
	DEALLOCATE d
	set @cmd = LEFT(@cmd,128+@length) -- 128 is the maxlength for the index name + prefixes
	SET @cmd += ''', N''INDEX'';'
	PRINT @cmd   
fetch next from c into @schemaname, @tablename, @indexname;
END
CLOSE c;
DEALLOCATE c;

 

 

 

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.