TEMP DB File Creation based on CPU

USE [master]

GO
DECLARE @cpu_count int,@file_count int,@logical_name sysname,@file_name nvarchar(520),@physical_name nvarchar(520),@size int,@max_size int,@growth int,@alter_command nvarchar(max)SELECT @physical_name = physical_name,@size = size / 128, @max_size
= max_size / 128,@growth = growth / 128FROM tempdb.sys.database_filesWHERE name = 'temp'select * from tempdb.sys.database_filesSELECT @file_count = COUNT(*)FROM tempdb.sys.database_filesWHERE type_desc = 'ROWS'SELECT @cpu_count = cpu_countFROM sys.dm_os_sys_infoWHILE @file_count < @cpu_count -- Add * 0.25 here to add 1 file for every 4 cpus, * .5 for every 2 etc.BEGINSELECT @logical_name = 'tempdb' + CAST(@file_count AS nvarchar)SELECT @file_name = REPLACE(@physical_name, 'tempdb.mdf', @logical_name + '.ndf')SELECT @alter_command = 'ALTER DATABASE [tempdb] ADD FILE ( NAME =N''' + @logical_name + ''', FILENAME =N''' + @file_name + ''', SIZE = ' + CAST(@size AS nvarchar) + 'MB, MAXSIZE = ' + CAST(@max_size AS nvarchar) + 'MB, FILEGROWTH = ' + CAST(@growth AS nvarchar) + 'MB )'PRINT @alter_command
--EXEC sp_executesql @alter_commandSELECT @file_count = @file_count + 1
END


Exec xp_msver 'processorcount' - Number of processor to verify

Memory Grants Pending

SELECT cntr_value AS [Memory Grants Outstanding]

FROM sys.dm_os_performance_countersWHERE OBJECT_NAME = 'MSSQL$ISQL01:Memory Manager'AND counter_name = 'Memory Grants Outstanding'SELECT OBJECT_NAME,cntr_value AS [Memory Grants Pending]FROM sys.dm_os_performance_countersWHERE OBJECT_NAME = 'MSSQL$ISQL01:Memory Manager'AND counter_name = 'Memory Grants Pending'

How can I reduce the SQL Wait Type - RESOURCE SEMAPHORE?
SQL Server Books online  defines the RESOURCE SEMAPHORE SQL wait stat type as “Occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts”
RESOURCE SEMAPHORE usually correlates with poor response time to all the users ,  so a high percentage of RESOURCE SEMAPHORE wait time is a critical indicator.
The Resource Semaphore manager reserves memory or throttles the queries if the memory requested by the queries exceeds the memory capacity.
Memory intensive queries  - using sorting and hashing are the main underlying reasons.  The GROUP BY clause and ORDER BY clause   use sorting and hashing.
A built in feature of SQL server as it doesn’t want to overcommit queries beyond the memory capacity. SQL Server checks to see if memory grant is necessary. If not , the query executes immediately. If there isn’t enough memory , the query queues.
How To Troubleshoot : RESOURCE SEMAPHORE
Step 1)  Capture and fine tune slow running queries  using SQL Server query tuning techniques
Step 2)  If step 1 doesn’t help, look to distribute queries across a wider time frame.
Step 3)  Use the DMV - sys.dm_exec_query_resource_semaphores
Step 4)  Use the DMV sys.dm_exec_query_memory_grants - especially if experiencing query timeouts. Read more on How to troubleshoot query timeouts
Displays queries with memory grant – or waiting for a memory grant. When RESOURCE_SEMAPHORE wait stat is reported – I usually check this DMV as an immediate check.
Step 5)  Cross check the wait type RESOURCE SEMAPHORE with the SQL Server perfmon counters
Step 5.1)          Memory Grants Pending  - If greater than 0 , there is a problem
Step 5.2)          Memory Grants Outstanding – look for a higher than normal value
Use these queries to access the current Memory Grants Pending  values and Memory Grants Outstanding.

Reference: http://www.sqlserver-dba.com/2011/06/sql-wait-type-resource-semaphore-and-how-to-reduce-it.html