info@techdevops.com
|
437-991-3573
|
Data Engineering Services
|
TechDevOps.com
|
| | | |
|
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance
|
| | | |
|
Move SQL Server TempDB Data & Log files
by BF (Principal Consultant; Architecture; Engineering)
2015-09-07
Solution:
Relocate TempDB files:
--Relocate TempDB Data File & adjust size
ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev,
FILENAME = 'H:\Data\tempdb.mdf',
SIZE = 2048MB,
MAXSIZE = 2048MB,
FILEGROWTH = 512MB)
GO
--Relocate TempDB Log File & adjust size
ALTER DATABASE tempdb
MODIFY FILE
(NAME = templog,
FILENAME = 'J:\Log\templog.ldf',
SIZE = 1024MB,
FILEGROWTH = 1024MB)
GO
System Message:
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.
Add more TempDB Data Files:
ALTER DATABASE [tempdb]
ADD FILE
(NAME = N'tempdb2',
FILENAME = N'H:\Data\tempdb2.ndf',
SIZE = 2048MB,
MAXSIZE = 2048MB,
FILEGROWTH = 512MB)
Relocate many TempDB Data Files to a new drive:
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'L:\tempdb.ldf')
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdb2, FILENAME = 'L:tempdb2.ldf')
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdb3, FILENAME = 'L:\tempdb3.ldf')
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdb4, FILENAME = 'L:\tempdb4.ldf')
*Restart SQL Service to active the move.
Note: Ensure to set the Size, MaxSize & FileGrowth to appropriate value to optimally handle your workload.
Common Guideline:
TempDB Data Files: For many workloads, adding more tempdb files will speed up SQL Server's performance level.
< 8 Cores/Logical CPUs: #TempDB Data Files = # of Cores/Logical CPUs
>= 8 Cores/Logical CPUs: #TempDB Data Files = 8
After that, if you still see TempDB contention, add 4 more TempDB Data Files at a time.
After that, if you still see TempDB contention still, you will need to change the T-SQL.
TempDB Log File:
Recommended only 1 TempDB Log File created.
Creating multiple TempDB Data Files avoids allocation problems that is seen typically on busy SQL Servers w/ busy TempDB activity.
i.e. More TempDB Data Files means more GAM, SGAM & PFS pages (allocation-tracking map pages) available for use across multiple data files. These are special pages that SQL Server uses internally to allocate objects created in T-SQL such as temporary tables.
PFS - Page Free Space. TEMPDB is getting contention when trying to record how much space is available and where.
GAM - Global Allocation Map. Records where objects are allocated.
SGAM - Shared Global Allocation Map. Records where objects are allocated.
To identify TempDB issues:
PAGELATCH_UP or PAGELATCH_EX - Examine the total waits in the system, since sql server startup, and look if these exists. If they are the cause of majority of waits then this is an indication of a TempDB performance issue. The goal next is to find out what T-SQL Code is leading to these wait types to showing up.
* Note that these wait types do not have an 'IO' in them. PAGEIOLATCH waits are different.
1. Reset the wait stats:
DBCC SQLPERF (N'sys.dm_os_wait_stats', CLEAR);
2. Run Application/Query in isolation, preferably, and check wait stats that have accumulated. As per below script, check Waits_Sec, Resource_Sec, Signal_Sec & Percentage and determine if the wait times are too long. Then, the key is to find the T-SQL causing the specific wait type, tune/optimize the T-SQL or Disk Subsystem or CPU or MEM, & re-run the Application again with the goal of getting lower accumulated wait stats. As one wait type gets removed or reduced, work on the next highest wait type if needed.
3. Check Wait Statistics
--In Seconds:
WITH [Waits] AS
(SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitSec],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceSec], --wait time for resource (locks, latches, network, disk I/O waits).
[signal_wait_time_ms] / 1000.0 AS [SignalSec], --wait time the waiting thread was signaled and when it started running.
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',
N'ONDEMAND_TASK_QUEUE',
N'PREEMPTIVE_XE_GETTARGETSTATE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_RECOVERY',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
AND [waiting_tasks_count] > 0
)
SELECT
MAX ([W1].[wait_type]) AS [WaitType],
CAST (MAX ([W1].[WaitSec]) AS DECIMAL (16,2)) AS [Wait_Sec],
CAST (MAX ([W1].[ResourceSec]) AS DECIMAL (16,2)) AS [Resource_Sec],
CAST (MAX ([W1].[SignalSec]) AS DECIMAL (16,2)) AS [Signal_Sec],
MAX ([W1].[WaitCount]) AS [WaitCount],
CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
CAST ((MAX ([W1].[WaitSec]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_Sec],
CAST ((MAX ([W1].[ResourceSec]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_Sec],
CAST ((MAX ([W1].[SignalSec]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_Sec]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- only show ones until the Percentage Sum < 95%
GO
--In Milliseconds:
WITH [Waits] AS
(SELECT
[wait_type],
[wait_time_ms] AS [Wait_MS],
([wait_time_ms] - [signal_wait_time_ms]) AS [Resource_MS], --wait time for resource (locks, latches, network, disk I/O waits).
[signal_wait_time_ms] AS [Signal_MS], --wait time the waiting thread was signaled and when it started running.
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',
N'ONDEMAND_TASK_QUEUE',
N'PREEMPTIVE_XE_GETTARGETSTATE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_RECOVERY',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
AND [waiting_tasks_count] > 0
)
SELECT
MAX ([W1].[wait_type]) AS [WaitType],
CAST (MAX ([W1].[Wait_MS]) AS DECIMAL (16,2)) AS [Wait_MS],
CAST (MAX ([W1].[Resource_MS]) AS DECIMAL (16,2)) AS [Resource_MS],
CAST (MAX ([W1].[Signal_MS]) AS DECIMAL (16,2)) AS [Signal_MS],
MAX ([W1].[WaitCount]) AS [WaitCount],
CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
CAST ((MAX (CAST([W1].[Wait_MS] AS DECIMAL(16,2))) / MAX ([W1].[WaitCount])) AS DECIMAL (16,2)) AS [AvgWait_MS],
CAST ((MAX (CAST([W1].[Resource_MS] AS DECIMAL(16,2))) / MAX ([W1].[WaitCount])) AS DECIMAL (16,2)) AS [AvgRes_MS],
CAST ((MAX (CAST([W1].[Signal_MS] AS DECIMAL(16,2))) / MAX ([W1].[WaitCount])) AS DECIMAL (16,2)) AS [AvgSig_MS]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- only show ones until the Percentage Sum < 95%
GO
Resources:
tempdb Database
Operations within tempdb are minimally logged so that transactions can be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.
Performance best practices for SQL Server in Azure Virtual Machines
Using SSDs in Azure VMs to store SQL Server TempDB and Buffer Pool Extensions
How temporary is Azure VM Temporary Storage?
Use the D: drive as a data drive on a Windows VM
Understanding the temporary drive on Windows Azure Virtual Machines
|
|
|
|
|
|
|
|
|
|