Archive for the ‘Design’ Category

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,
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
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;

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


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.

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.

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 as IndexName, as TableName, as columnName, + ‘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 + ‘id’ <>;