Archive for the ‘Design’ Category

The Problem

When checking the error logs on a SQL 2016 box I noticed this error message: Trace flag 1117 is discontinued. Use the options provided with ALTER DATABASE.
Trace flags T1117 and T1118 have been discontinued as of SQL Server 20016.

T1118 tells SQL to avoid mixed extents and use full extents.
T1117 makes sure all files in a filegroup grow at an even rate.

I now need to replicate this behavior on the SQL 2016 box

The Solution

T1118 doesn’t matter so much to me. The database where it has the most effect is tempdb and this now uses full extents by default.

T1117 has been replaced with an Alter Database Command:
ALTER DATABASE Adventureworks2016 MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES
GO
ALTER DATABASE Adventureworks2016 MODIFY FILEGROUP [PRIMARY] AUTOGROW_SINGLE_FILE
GO

Unfortunately you can’t apply this to the model db. So, to apply this to all current databases I wrote a script to loop through and check the status of the filegroup and change it if needed.

Please test this script before you run it. You might want to change the two Exec command with Print and so check the output.

Note: You can check the status of a database by running this script :
SELECT * from sys.filegroups
You will see there is a column is_autogrow_all_files. This should be set to 1

This script will not work if the database is in use

The Script

SELECT [name] as DBName, cast(0 as bit) as Checked
into #DBTest
FROM sys.Databases as db
where database_id > 4
and is_read_only = 0;

DECLARE @DBName as Varchar(250);
DECLARE @sql as Varchar(max);

While Exists (select 1 FROM #DBTest where Checked = 0)
BEGIN
SELECT top 1 @DBName = DBName
FROM #DBTest
where Checked = 0;

SET @sql = ‘USE ‘ + @DBName + ‘
DECLARE @sqlinner as Varchar(max);
DECLARE @fg as Varchar(250);
SELECT ”ALTER DATABASE ‘ + @DBName + ‘ MODIFY FILEGROUP [” + name + ”] AUTOGROW_ALL_FILES ” as sl , cast(0 as bit) as Checked, name as FG
INTO #FileGroups
from sys.filegroups
WHERE is_autogrow_all_files = 0;
While Exists (select 1 FROM #FileGroups where Checked = 0)
BEGIN
SELECT top 1 @sqlinner = sl, @fg = FG
FROM #FileGroups where Checked = 0;

EXEC(@sqlinner);

UPDATE #FileGroups
SET Checked = 1
WHERE FG = @fg;

END
DROP TABLE #FileGroups

exec(@sql);

UPDATE #DBTest
SET Checked = 1
WHERE DBName = @DBName;
END

DROP TABLE #DBTest

Advertisements

Adding extra files to Tempdb

Posted: November 3, 2015 in DBA, Design, Files, Tempdb

This isn’t actually a problem. When I build a new instance I like a tempdb file for each cpu. This script will create those files in the directory where the current tempdb data file is. Please note that it sizes tempdb data and log files too, you may want to change those values

/* Script Starts*/
DECLARE @cpuCount as int;
DECLARE @Files as int;

SELECT @Files = COUNT(*)
FROM tempdb.sys.database_files
WHERE type_desc = ‘ROWS’;

SELECT @cpuCount = cpu_count /hyperthread_ratio
FROM sys.dm_os_sys_info
–Print @cpuCount
–Print @Files

Alter Database Tempdb modify file(Name=tempdev, Size=300, filegrowth = 150MB);
Alter Database Tempdb modify file(Name=templog, Size=50, filegrowth = 50MB);

DECLARE @FileLocation as Varchar(750);

–You may need to check that the name of the tempdb data file is tempdb.mdf (select * from tempdb.sys.master_files)
Set @FileLocation = (SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N’tempdb.mdf’,
LOWER(physical_name)) – 1) DataFileLocation
FROM master.sys.master_files
WHERE database_id = 2 AND FILE_ID = 1)
–Print @FileLocation

DECLARE @diff as int;
SET @diff = @cpuCount – @Files
If @diff > 7
set @diff = 8 – @Files

DECLARE @x as TinyInt;
SET @x = @Files;
DECLARE @file as Varchar(10);
DECLARE @fileName as Varchar(250);
While @diff > 0
BEGIN
SET @file = ‘tempdev’ + Cast(@x as varchar(2))
SET @fileName = @FileLocation + ‘\’ + @file + ‘.ndf’;
DECLARE @sql as Varchar (8000);
SET @sql = ‘ALTER DATABASE TempDb ‘;
SET @sql = @sql + ‘ADD FILE ‘;
SET @sql = @sql + ‘( ‘;
SET @sql = @sql + ‘NAME = ‘ + @file + ‘, ‘;
SET @sql = @sql + ‘FILENAME = ”’ + @fileName + ”’, ‘;
SET @sql = @sql + ‘SIZE = 300MB, ‘;
SET @sql = @sql + ‘FILEGROWTH = 150MB’;
SET @sql = @sql + ‘);’;
Exec (@sql);
SET @diff = @diff -1;
SET @x = @x + 1;
END;

I recently added a filtered index to a table without any problems. Later that night jobs began to fail with the error:

Description: Executing the query “NameOfStoredProcedure” failed with the following error: “UNKNOWN TOKEN 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…

The problem was with the Quoted Identifier settings for the stored procedure. For the query to work the Quoted Identifier needs to be set to ON. The problem is that each stored procedure inherits the settings used when it was created.

You can check these settings by right-clicking on the stored proc and selecting properties. In the general tab there is a list of options which includes the QUOTED_IDENTIFIER option. These values will be set to either True or False.

The default Value for QUOTED_IDENTIFIER is ON.

From BOL

QUOTED_IDENTIFIER

When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure.

http://msdn.microsoft.com/en-us/library/ms174393.aspx

Create Procedure

The Database Engine saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a Transact-SQL procedure is created or modified. These original settings are used when the procedure is executed. Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored when the procedure is running.

Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a procedure is created or modified. If the logic of the procedure depends on a particular setting, include a SET statement at the start of the procedure to guarantee the appropriate setting. When a SET statement is executed from a procedure, the setting remains in effect only until the procedure has finished running. The setting is then restored to the value the procedure had when it was called. This enables individual clients to set the options they want without affecting the logic of the procedure.

Any SET statement can be specified inside a procedure, except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL. These must be the only statements in the batch. The SET option chosen remains in effect during the execution of the procedure and then reverts to its former setting.

http://msdn.microsoft.com/en-us/library/ms187926.aspx

This script looks at all the Primary Key Indexes in a database and pulls back the name of the column the key is on. It checks where there is a single column key. It then recommends a name for the column of tableNameID – columns which already match this are filtered out.
I have written this because I have a bad habit of naming surrogate Primary Key columns PKID.
In most cases the column which doesn’t match the name formulae it is because it already had a good name – like serverName in my server audit table.

select si.name as IndexName,
so.name as TableName,
sc.name as columnName,
so.name + ‘Id’ as recommendName
from sys.indexes as si
Inner Join sys.objects as so
on si.object_id = so.object_id
Inner Join sys.index_columns as ic
on si.object_id = ic.object_id AND si.index_id = ic.index_id
Inner Join sys.columns as sc
on ic.object_id = sc.object_id and ic.index_column_id = sc.column_id
where si.is_Primary_key = 1
AND 1 = (SELECT count(object_id)
FROM sys.index_columns icInner
WHERE si.object_id = icInner.object_id AND si.index_id = icInner.index_id)
AND so.name + ‘id’ <> sc.name;