Category Archives: Administration

Decrypting encrypted stored procedures, views, functions in SQL server 2005,2008 &R2

Any stored procedure or view or function can be encrypted when stored in the sql server , so a user can not see the code inside even if the user has SYSADMIN role and it is also not recommended to use this encryption option to hide the code because once you use the encryption option Microsoft sql server does not provide the option or the feature to reverse the setting or decrypt the object , so when you use the encryption option , save your code in a separate instance































TITLE: Microsoft SQL Server Management Studio
——————————

Script failed for StoredProcedure ‘dbo.test_encrp’. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Script+StoredProcedure&LinkId=20476

——————————
ADDITIONAL INFORMATION:

Property TextHeader is not available for StoredProcedure ‘[dbo].[test_encrp]‘. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=TextHeader&LinkId=20476

First we will see how it looks in the sql_modules catalog views, in the process we will also list all the encrypted

stored procedures in the databases

to find all the encrypted objects in the sql server database


here is the query used above

select o.name,s.definition,o.type_desc,

case

when definition is null then ‘yes’
else ‘no’
end as ‘is_encrypted’

from sys.sql_modules s inner join

sys.objects o on s.object_id = o.object_id

where type in (‘p’,'tr’,'FN’,'tf’,'v’)

now we got know how it is encrypted , and where to find the encrypted objects now we will look in to how to decrypt them

There are two ways you can do

  1. Using a script/stored procedure using a DAC connection
  2. Using third party tools

first we will look at the script method

first you need to have SYSADMIN role assigned to you , then you have to setup a DAC connection to the sql server instance that you

want to decrypt the objects

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

here is the code to decrypt

 

/****** Object:  StoredProcedure [dbo].[sp__procedure]    Script Date: 03/06/2012 19:56:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp__procedure]
(@procedure sysname = NULL)
AS
SET NOCOUNT ON

DECLARE @intProcSpace bigint, @t bigint, @maxColID smallint,@intEncrypted
tinyint,@procNameLength int
select @maxColID = max(subobjid)
–,@intEncrypted = encrypted
FROM
sys.sysobjvalues WHERE objid = object_id(@procedure)
–GROUP BY encrypted

–select @maxColID as ‘Rows in sys.sysobjvalues’
select @procNameLength = datalength(@procedure) + 29

DECLARE @real_01 nvarchar(max)

DECLARE @fake_01 nvarchar(max)

DECLARE @fake_encrypt_01 nvarchar(max)

DECLARE @real_decrypt_01 nvarchar(max),@real_decrypt_01a nvarchar(max)

select @real_decrypt_01a = ”

– extract the encrypted imageval rows from sys.sysobjvalues
SET @real_01=(SELECT imageval FROM sys.sysobjvalues WHERE objid =
object_id(@procedure) and valclass = 1 and subobjid = 1 )

– create this table for later use
create table #output ( [ident] [int] IDENTITY (1, 1) NOT NULL ,
[real_decrypt] NVARCHAR(MAX) )

– We’ll begin the transaction and roll it back later
BEGIN TRAN
– alter the original procedure, replacing with dashes
SET @fake_01=’ALTER PROCEDURE ‘+ @procedure +’ WITH ENCRYPTION AS
‘+REPLICATE(‘-’, 40003 – @procNameLength)

EXECUTE (@fake_01)

– extract the encrypted fake imageval rows from sys.sysobjvalues
SET @fake_encrypt_01=(SELECT imageval FROM sys.sysobjvalues WHERE objid =
object_id(@procedure) and valclass = 1 and subobjid = 1)

SET @fake_01=’CREATE PROCEDURE ‘+ @procedure +’ WITH ENCRYPTION AS
‘+REPLICATE(‘-’, 40003 – @procNameLength)
–start counter
SET @intProcSpace=1
–fill temporary variable with with a filler character
SET @real_decrypt_01 = replicate(N’A', (datalength(@real_01) /2 ))

–loop through each of the variables sets of variables, building the real variable
–one byte at a time.
SET @intProcSpace=1

– Go through each @real_xx variable and decrypt it, as necessary
WHILE @intProcSpace<=(datalength(@real_01)/2)
BEGIN
–xor real & fake & fake encrypted
SET @real_decrypt_01 = stuff(@real_decrypt_01, @intProcSpace, 1,
NCHAR(UNICODE(substring(@real_01, @intProcSpace, 1)) ^
(UNICODE(substring(@fake_01, @intProcSpace, 1)) ^
UNICODE(substring(@fake_encrypt_01, @intProcSpace, 1)))))
SET @intProcSpace=@intProcSpace+1
END

– Load the variables into #output for handling by sp_helptext logic

insert #output (real_decrypt) select @real_decrypt_01
– select real_decrypt AS ‘#output chek’ from #output — Testing

– ————————————-
– Beginning of extract from sp_helptext
– ————————————-
declare @dbname sysname
,@BlankSpaceAdded int
,@BasePos int
,@CurrentPos int
,@TextLength int
,@LineId int
,@AddOnLen int
,@LFCR int –lengths of line feed carriage return
,@DefinedLength int
,@SyscomText nvarchar(4000)
,@Line nvarchar(255)

Select @DefinedLength = 255
SELECT @BlankSpaceAdded = 0
–Keeps track of blank spaces at end of lines. Note Len function ignores trailing blank spaces
CREATE TABLE #CommentText
(LineId int
,Text nvarchar(255) collate database_default)

– use #output instead of sys.sysobjvalues
DECLARE ms_crs_syscom CURSOR LOCAL
FOR SELECT real_decrypt from #output
ORDER BY ident
FOR READ ONLY

– Else get the text.

SELECT @LFCR = 2
SELECT @LineId = 1

OPEN ms_crs_syscom

FETCH NEXT FROM ms_crs_syscom into @SyscomText

WHILE @@fetch_status >= 0
BEGIN

SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = LEN(@SyscomText)

WHILE @CurrentPos != 0
BEGIN
–Looking for end of line followed by carriage return
SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText,
@BasePos)

–If carriage return found
IF @CurrentPos != 0
BEGIN
–If new value for @Lines length will be > then the
–set length then insert current contents of @line
–and proceed.

While (isnull(LEN(@Line),0) + @BlankSpaceAdded +
@CurrentPos-@BasePos + @LFCR) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) +
@BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText,
@BasePos, @AddOnLen), N”))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N”) +
isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N”)
SELECT @BasePos = @CurrentPos+2
INSERT #CommentText VALUES( @LineId, @Line )
SELECT @LineId = @LineId + 1
SELECT @Line = NULL
END
ELSE
–else carriage return not found
BEGIN
IF @BasePos <= @TextLength
BEGIN
–If new value for @Lines length will be > then the
–defined length
–
While (isnull(LEN(@Line),0) + @BlankSpaceAdded +
@TextLength-@BasePos+1 ) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength -
(isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText,
@BasePos, @AddOnLen), N”))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded =
0
END
SELECT @Line = isnull(@Line, N”) +
isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N”)
if LEN(@Line) < @DefinedLength and charindex(‘ ‘,
@SyscomText, @TextLength+1 ) > 0
BEGIN
SELECT @Line = @Line + ‘ ‘, @BlankSpaceAdded = 1
END
END
END
END

FETCH NEXT FROM ms_crs_syscom into @SyscomText
END

IF @Line is NOT NULL
INSERT #CommentText VALUES( @LineId, @Line )

select Text from #CommentText order by LineId

CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom

DROP TABLE #CommentText

– ————————————-
– End of extract from sp_helptext
– ————————————-

– Drop the procedure that was setup with dashes and rebuild it with the good stuff
– Version 1.1 mod; makes rebuilding hte proc unnecessary
ROLLBACK TRAN

DROP TABLE #output
GO

the  second way is to use the third party tools , there many third party tools that are available to download

one of the best is the Optillect sql decrypter which is a free ware, good thing about it is it does not need SYSADMIN role as it does not

use DAC , rather it uses DBCC ind, and DBCC PAGE for the required information for the SYS.OBJVALUES table

http://download.cnet.com/Optillect-SQL-Decryptor/3640-10254_4-75300271-1.html

 

download and install it

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Wait stats BACKUPIO, and BACKUPBUFFER

At in order to understand how we accumulate these waits over time first we need to know how  SQL server backup works

Backup and restore work very similar so I will only cover the backup example in this post.

When a backup starts it creates a series of buffers, allocated from the memory outside the buffer pool.  The target is commonly 4MB for each buffer resulting in approximately 4 to 8 buffers.  Details about the calculation are located in: http://support.microsoft.com/kb/904804/en-us

The buffers are transitioned between the free and data queues.   The reader pulls a free buffer, fills it with data and places it on the data queue.   The writer(s) pull filled data buffers from the data queue, process the buffer and return it to the free list.

You get a writer per backup device, each retrieving from the data queue.   So a a backup command with four(4) to disk specifications will have four writers and a reader.   The reader uses async I/O so it can keep up with the writers.

image

For more details about the buffer sizes and counts refer to: http://msdn2.microsoft.com/en-us/library/ms186865.aspx

BUFFERCOUNT = { buffercount | @buffercount_variable }
Specifies the total number of I/O buffers to be used for the backup operation. You can specify any positive integer; however, large numbers of buffers might cause “out of memory” errors because of inadequate virtual address space in the Sqlservr.exe process.The total space used by the buffers is determined by: buffercount * maxtransfersize.

MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
Specifies the largest unit of transfer in bytes to be used between SQL Server and the backup media. The possible values are multiples of 65536 bytes (64 KB) ranging up to 4194304 bytes (4 MB)so there are lot of parameters considered here when we have buffer and io waits first and important thing is the backup media

everything depends on the backup media some times it is very slow when they use tape devices which accumulates the ASYNC_IO_COMPLETION , so the data buffer is waiting to become free buffer but it can not as backup device is slow so the writer thread has to wait for the IO operation to complete on the backup device

Always try to avoid the network backup,

Always test different test scenario using different buffer counters

This is not considered a best practical solution in case of VLDB proper testing should be performed

Refer

http://sqlcat.com/sqlcat/b/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx

Technical Case Study-Backup VLDB Over Network_Final

Wait stats : Page and pageio latch waits

as earlier in this post we have discussed the issue of latches in this post we will see how to fine tune them and find out why they are happening

I will be querying a table with a count of 172709683 rows and  the clustered index for the wild card search string , and I have DOP set to 2 and cost of threshold is set to 5 and expecting a parallel execution plan we also be observing lot of cxpacket wait same time

we will use below query on sys.dm_os_waiting_tasks for any waiting tasks

SELECT W.SESSION_ID,W.WAIT_DURATION_MS,W.WAIT_TYPE,W.RESOURCE_DESCRIPTION,T.TEXT,P.QUERY_PLAN

FROM SYS.DM_OS_WAITING_TASKS W

JOIN SYS.DM_EXEC_REQUESTS R ON W.SESSION_ID = R.SESSION_ID

CROSSAPPLYSYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T

CROSSAPPLYSYS.DM_EXEC_QUERY_PLAN(R.PLAN_HANDLE) P

WHERE W.WAIT_TYPE LIKE ‘PAGE%’

 

 

 

 

 

 

 

 

 

 

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE

procedure [dbo].[pagetoobject](@RESOURCE NVARCHAR(1024))

as

begin

declare @dbid int,@fileid int,@pageid int,@objectid int,@indexid int

SELECT @dbid=SUBSTRING(@RESOURCE,0,CHARINDEX(‘:’,@RESOURCE))

,@fileid = substring
(
lTRIM(RTRIM(SUBSTRING(@RESOURCE,CHARINDEX(‘:’,@RESOURCE)+1,LEN(LTRIM(RTRIM(@RESOURCE))))))
,0
,charindex(‘:’,lTRIM(RTRIM(SUBSTRING(@RESOURCE,CHARINDEX(‘:’,@RESOURCE)+1,LEN(LTRIM(RTRIM(@RESOURCE)))))))
)

,@pageid =SUBSTRING(@RESOURCE,CHARINDEX(‘:’,@RESOURCE,CHARINDEX(‘:’,@RESOURCE)+1)+1,LEN(@RESOURCE))

declare @page table (parentobj sysname,[object] sysname,field sysname, value sysname)

declare @params Nvarchar(1000)=

N’@dbid int,@fileid int,@pageid int’

insert into @page(parentobj,[object],field,value)

exec sp_executesql N’dbcc page (@dbid,@fileid,@pageid) with tableresults, no_infomsgs’, @params ,

@dbid= @dbid,@fileid = @fileid,@pageid = @pageid

select @objectid = value from @page

where field = ‘Metadata: ObjectId’

select @indexid = value from @page

where field = ‘Metadata: IndexId’

select t.name as’table name’,i.name as’index name’

from sys.tables t inner join sys.indexes i on t.object_id= i.object_id

where t.object_id= @objectid AND I.index_id = @indexid

end

GO

 

Introduction to wait stats – Part 3, I/O Waites – Latches

During the query execution the storage engine needs to bring the data from disk space to the buffer pool , or if a thread or task needs to access a specific part of the buffer in or order manage the access to and from the buffer the SQL server uses LATCHES

Latches are short-term synchronization objects , they are mainly used for synchronization of in-line memory access to the data pages. There are almost 144 different types of  latches and certain types of latches lead to blocking of specific resources in high concurrent systems, and diagnosing the latch blocking is very difficult because there is not much information available out there or documented to perform the rca

Latch waits are divided in to two main groups :

i) Page latch waits

ii) Non Page latch waits

pagelatch waits are subdivided into two main groups

i) PAGELATCH_XX

ii) PAGEIOLATCH_XX

Non page latch waits are sub divided into two main groups

i)LATCH_XX

ii)TRANMARKLATCH_XX

as you can see above mentioned all the latches have different modes (%_XX). These modes are NL,KP,SH,UP,DT,EX

what it means is that every latch with a mode has a purpose example SH mode means it is trying to read a page in a buffer or from the disk

KP stands for KEEP latch and DT stands for DESTROY latch .KP is compatible with all latch types except for DT , KP latch can be acquired by any thread which wants to read buffer , so that every thread needs to acquire a latch in order have an access to the buffer so that no other thread can destroy the buffer unless it acquires the DT latch. A DT latch is used to destroy the buf i.e to free the cache , so that any other thread can access it for loading a page

EX is called exclusive latch because this latch is held on the page when moving a page from buffer to I/O disk or from disk I/O  to buffer in order to modify the page so that no other latch can be held on the page

Blocking latches, Latch waits

So  the blocking task information is known only when the latch is held in UP, EX or DT modes the common factor among these is that these can be held single time by  a single task only , where as KP, SH can be held by multiple tasks and multiple times

and one important thing is that the mode specified in the wait type is the request mode not the blocking mode

Latches grant order

Latches are granted almost in FIFO , latches also try to avoid starvation by having two exceptions to the grant order

i) KP requests never need to wait unless current mode is in DT mode

ii) when granting waiting list all compatible locks are granted first in regard less to the FIFO list  Eg:  if current mode is UP and first waiter is UP and second waiter is EX and third waiter is SH and kp, SH but when up latch is released all the SH latches are granted although EX is arrived first , to prevent starvation

Latch wait time , the wait time in SYS.DM_OS_WAIT_STATS, and SYS.DM_OS_WAITING_TASKS the wait time in here is the time task is idle waiting for the latch in wait type

Page Latches

Page latches are used to provide the synchronization to the physical access to the individual pages stored in the buffer pool, access to the on disk data pages is controlled by the buffer pool

The difference between pagelatch wait and Pageiolatch is actually very small it depends on the pending I/O request

To read the page it need ths SH page latch , to write the page a ex latch is held , in case row versioning and usage of temp db needs UP mode

PAGEIOLATCH_EX wait problems

if there are ten tasks requesting a single page for modification , first task may wait for in order to get PAGEIOLATCH_ex but other nine tasks may wait to untill the data got in to the buffer by first task , after time out they may try to re load the data which will lead to a contention

Introduction

Recently, we performed a lab test that had a large OLTP workload in the Microsoft Enterprise Engineering Center. The purpose of this lab was to take an intensive Microsoft SQL Server workload and see what happened when we scaled it up from 64 processors to 128 processors. (Note: This configuration is supported as part of the Microsoft SQL Server 2008 R2 release.). The workload had highly concurrent insert operations going to a few large tables.

As we began to scale this workload up to 128 cores, the wait stats captured were dominated by PAGELATCH_UP and PAGELATCH_EX. The average wait times were tens of milliseconds, and there were a lot of waits. These waits were not expected, or they were expected to be a few milliseconds only.

In this TechNote we will describe how we first diagnosed the problem and how we then used table partitioning to work around it.

Diagnosing the Problem

When you see large waits for PAGELATCH in sys.dm_os_wait_stats, you will want to do the following. Start your investigation with sys.dm_os_waiting_tasks and locate a task waiting for PAGELATCH, like this:

SELECT session_id, wait_type, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE ‘PAGELATCH%’            

Example Output:

The resource_description column lists the exact page being waited for in the format: <database_id>:<file_id>:<page_id>.

Using the resource_description column, you can now write this rather complex query that looks up all these waiting pages:

SELECT wt.session_id, wt.wait_type, wt.wait_duration_ms          
, s.name AS schema_name          
, o.name AS object_name          
, i.name AS index_name          
FROM sys.dm_os_buffer_descriptors bd
JOIN (          
  SELECT *            
  , CHARINDEX(‘:’, resource_description) AS file_index            
  , CHARINDEX(‘:’, resource_description
  , CHARINDEX(‘:’, resource_description)) AS page_index           
  , resource_description AS rd          
  FROM sys.dm_os_waiting_tasks wt          
  WHERE wait_type LIKE ‘PAGELATCH%’                     
  ) AS wt          
    ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index)          
    AND bd.file_id = SUBSTRING(wt.rd, wt.file_index, wt.page_index)          
    AND bd.page_id = SUBSTRING(wt.rd, wt.page_index, LEN(wt.rd))
JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
JOIN sys.partitions p ON au.container_id = p.partition_id
JOIN sys.indexes i ON  p.index_id = i.index_id AND p.object_id = i.object_id
JOIN sys.objects o ON i.object_id = o.object_id

JOIN sys.schemas s ON o.schema_id = s.schema_id

The query shows that the page we are waiting for is in a clustered index, enforcing the primary key, of a table with this structure:

CREATE TABLE HeavyInsert ( 
  ID INT PRIMARY KEY CLUSTERED  
  , col1 VARCHAR(50)
) ON [PRIMARY] 

What is going on here, why are we waiting to access a data page in the index?

Background Information

To diagnose what was happening in our large OLTP workload, it’s important to understand how SQL Server handles the insertion of a new row into an index. When a new row is inserted into an index, SQL Server will use the following algorithm to execute the modification:

  1. Record a log entry that row has been modified.
  2. Traverse the B-tree to locate the correct page to hold the new record.
  3. Latch the page with PAGELATCH_EX, preventing others from modifying it.
  4. Add the row to the page and, if needed, mark the page as dirty.
  5. Unlatch the page. 

Eventually, the page will also have to be flushed to disk by a checkpoint or lazy write operation.

However, what happens if all the inserted rows go to the same page? In that case, you can see a queue building up on that page. Even though a latch is a very lightweight semaphore, it can still be a contention point if the workload is highly concurrent. In this customer case, the first, and only, column in the index was a continuously increasing key. Because of this, every new insert went to the same page at the end of the B-tree, until that page was full. Workloads that use IDENTITY or other sequentially increasing value columns as primary keys may run into this same issue at high concurrency too.

Solution

Whenever many threads need synchronized access to a single resource, contention can occur. The solution is typically to create more of the contended resource. In this case, the contended resource is the last page in the B-tree.

One way to avoid contention on a single page is to choose a leading column in the index that is not continually increasing. However, this would have required an application change in the customer’s system. We had to look for a solution that could be implemented within in the database.

Remember that the contention point is a single page in a B-tree. If only there was a way to get more B-trees in the table. Fortunately, there IS a way to get this: Partition the table. The table can be partitioned in such a way that the new rows get spread over multiple partitions.

First, create the partition function and scheme:

CREATE PARTITION FUNCTION pf_hash (TINYNT) AS RANGE LEFT FOR VALUES (0,1,2) 

CREATE PARTITION SCHEME ps_hash AS PARTITION pf_hash ALL TO ([PRIMARY]) 

This example uses four partitions. The number of partitions you need depends on the amount of INSERT activity happening on the table. There is a drawback to hash-partitioning the table like this: Whenever you select rows from the table, you have to touch all partitions. This means that you need to access more than one B-tree – you will not get partition elimination. There is a CPU cost and latency cost to this, so keep the number of partitions as small as possible (while still avoiding PAGELATCH). In our particular customer case, we had plenty of spare CPU cycles, so we could afford to sacrifice some time on SELECT statements, as long as it helped us increase the INSERT rate.

Second, you need a column to partition on, one that spreads the inserts over the four partitions. There was no column available in the table for this in the Microsoft Enterprise Engineering Center scenario. However, it is easy to create one. Taking advantage of the fact that the ID column is constantly increasing in increments of one, here is a simple hash function of the row:

CREATE TABLE HeavyInsert_Hash( 
  ID INT NOT NULL 
  , col1 VARCHAR(50) 
  , HashID AS CAST(ABS(ID % 4) AS TINYINT)  PERSISTED NOT NULL)

With the HashID column, you can cycle the inserts between the four partitions. Create the clustering index in this way:

CREATE UNIQUE CLUSTERED INDEX CIX_Hash
ON HeavyInsert_Hash (ID, HashID) ON ps_hash(HashID) 

By using this new, partitioned table instead of the original table, we managed to get rid of the PAGELATCH contention and increase the insertion rate, because we spread out the high concurrency across many pages and across several partitions, each having its own B-tree structure. We managed to increase the INSERT rate by 15 percent for this customer, with the PAGELATCH waits going away on the hot index in one table. But even then, we had CPU cycles to spare, so we could have optimized further by applying a similar trick to other table with high insert rates.

Strictly speaking, this optimization trick is a logical change in the primary key of the table. However, because the new key is just extended with the hash value of the original key, duplicates in the ID column are avoided.

The single column unique indexes on a table are typically the worst offender if you are experiencing PAGELATCH contention. But even if you eliminate this, there may be other, nonclustered indexes on the table that suffer from the same problem. Typically, the problem occurs with single column unique keys, where every insert ends up on the same page. If you have other indexes in the table that suffer from PAGELATCH contention, you can apply this partition trick to them too, using the same hash key as the primary key.

Not all applications can be modified, something that is a challenge for ISVs. However, if you DO have the option of modifying the queries in the system, you can add an additional filter to queries seeking on the primary key.

Example: To get partition elimination, change this:

      SELECT * FROM HeavyInsert_Hash 
      WHERE ID = 42


To this:

            SELECT * FROM HeavyInsert_Hash
      WHERE ID = 42 AND HashID  = CAST(ABS(42 % 4) AS TINYINT) 

With partition elimination, the hash partitioning trick is almost a free treat. You will still add one byte to each row of the clustered index.

tempDB contention

What is tempDB contention?

From the outside looking in, tempDB contention may look like any other blocking. There are two types of contention that tends to plague tempDB’s, especially when the tempDB is not configured to best practices (multiple, equally sized data files, located on a dedicated, high-speed drive, etc.). lets focus on latch contention on the allocation pages.

What are allocation pages?

Allocation pages are special pages in the data files that track and mange extent allocations. There are 3 types of allocation pages that can experience contention and bring a server to a slow crawl.

Global Allocation Map (GAM): Tracks which extents have been allocated. There is 1 GAM page for every 4 GB of data file. It is always page 2 in the data file and then repeats every 511,232 pages.

Shared Global Allocation Map (SGAM): Tracks which extents are being used as mixed (shared) extents. There is 1 SGAM page for every 4 GB of data file. It is always page 3 in the data file and then repeats every 511,232 pages.

Page Free Space (PFS): Tracks the allocation status of each page and approximately how much free space it has. There is 1 PFS page for every 1/2 GB of data file. It is always page 1 in the data file and then repeats every 8,088 pages.

Finding Latch Contention on Allocation Pages

You can use the dynamic management view (DMV) sys.dm_os_waiting_tasks to find tasks that are waiting on a resource. Tasks waiting on PageIOLatch or PageLatch wait types are experiencing contention. The resource description points to the page that is experiencing contention, and you can easily parse the resource description to get the page number. Then it’s just a math problem to determine if it is an allocation page.

The Resource Description (sample):

The resource description will be in the form of <database ID>:<file ID>:<page number>. The tempDB is always database ID of 2. A sample resource description may look like 2:3:18070499. We want to focus on the page ID of 18070499.

The formula for determining the page type is as follows:

GAM: (Page ID – 2) % 511232

SGAM: (Page ID – 3) % 511232

PFS: (Page ID – 1) % 8088

If one of these formulas equates to 0, then the contention is on the allocation pages.

The query

 

Select session_id,
    wait_type,
    wait_duration_ms,
    blocking_session_id,
    resource_description,
    ResourceType = Case
        When Cast(Right(resource_description, Len(resource_description)
            - Charindex(':', resource_description, 3)) As Int) - 1 % 8088 = 0 Then 'Is PFS Page'
        When Cast(Right(resource_description, Len(resource_description)
            - Charindex(':', resource_description, 3)) As Int) - 2 % 511232 = 0 Then 'Is GAM Page'
        When Cast(Right(resource_description, Len(resource_description)
            - Charindex(':', resource_description, 3)) As Int) - 3 % 511232 = 0 Then 'Is SGAM Page'
        Else 'Is Not PFS, GAM, or SGAM page'
    End
From sys.dm_os_waiting_tasks
Where wait_type Like 'PAGE%LATCH_%'
And resource_description Like '2:%'
 

Select session_id,wait_type,wait_duration_ms,blocking_session_id,resource_Description,Descr.*

From sys.dm_os_waiting_tasksas waits innerjoinsys.dm_os_buffer_Descriptorsas Descr

on

LEFT(waits.resource_description,Charindex(‘:’, waits.resource_description,0)-1)= Descr.database_id

and

SUBSTRING(waits.resource_description,Charindex(‘:’, waits.resource_description)+1,Charindex(‘:’, waits.resource_description,Charindex(‘:’, resource_description)+1)-(Charindex(‘:’, resource_description)+1))= Descr.[file_id]

and

Right(waits.resource_description,Len(waits.resource_description)-Charindex(‘:’, waits.resource_description, 3))= Descr.[page_id]

Where wait_type Like  ‘PAGE%LATCH_%’

 A modified one with more information

@source SQLsoldier

to know more about GAM,SGAM, PFS 
please use the below links
http://www.sqlskills.com/blogs/paul/post/Inside-The-Storage-Engine-GAM-SGAM-PFS-and-other-allocation-maps.aspx
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/07/08/under-the-covers-gam-sgam-and-pfs-pages.aspx
http://www.sqlservercentral.com/blogs/livingforsqlserver/2011/02/13/examining-pfs-gam-and-sgam-pages/
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/28/649884.aspx
 

Problem solution approach for parallel query processing- CxPacket,Insufficient Memory,Data Skews,Statistics

²http://sqljunkieshare.com/2011/12/29/understanding-and-controlling-parallel-query-processing-maxdopcost-thresholdparallel-execution-plansoperators/

as mentioned in the above article about parallel query processing , not only benefits but there are some problems with MAXDOP and parallel query processing

Problems

1) Insufficient Memory

2) Over head of synchronizing the worker threads (which leads CXPACKET waits)

3) Statistical Estimation Errors

4) Data Skew

>> Insufficient Memory during execution of parallel query plans

before we get into the detail we will first understand little bit about memory management in SQL server

as we know SQL server requires physical memory to execute queries

Total amount of memory required by the query to is divided in to two

         1) Required Memory

         2) Additional Memory

Required Memory is the must have memory by the SQL server in order to execute the query

Additional Memory is the memory that the query needs in order to fit additional data

lets learn this by an example

sorting a 1 million rows , lets say the size of one row is 10 bytes the memory required to perform one sort is 512KB

512 kb is the Required Memory

10 Mb is the Additional Memory required for the query to perform well

Now lets talk about execution plans for parallel query plans , as we know in order to have parallel query for a query the execution plan should have Parallel query operators called Exchange operators Distribute Streams, Repartition Streams, and Gather Streams

exchange operators An exchange operator is an operator in a query execution plan that provides process management, data redistribution, and flow control, basically these operators exchange rows between threads. A exchange operator internally has two iterators Producers and consumers  buffers are used to transfer batch rows between iterators  Producers reads the data from the subtree and assembles them in to packets and routes them to the consumer buffers , most of the times the number of required by the query are (DOP)²

So the required memory for the is quite higher in parallel execution plan when compared to the serial execution plan for the same query

 

 

 Solution:

Query degree of parallelism and memory grants can be monitored using the XML query plan. The MemoryFractions element in the run-time XML showplan gives the relative memory usage among operators in the query plan. The MemoryGrant attribute of the QueryPlan element also provides information regarding memory grants for a specific query. After a query is executed, the DegreeOfParallelism attribute of the QueryPlan element in the actual XML showplan, provides information of the actual degree of parallelism used

Using the DMV

    SYS.DM_EXEC_QUERY_MEMORY_GRANTS

  • This DMV contain all queries requesting Memory grants and one which are granted , Queries which are waiting for the memory grants will have NULL value in the grant tim column

                     SELECT *
                    FROM sys.dm_exec_query_memory_grants
                   WHERE is_next_candidate in (0,1)
                  ORDER BY is_next_candidate desc, queue_id, wait_order;

  • sys.dm_os_wait_stats: This DMV shows wait statistics for all server objects. Since memory grants use RESOURCE_SEMAPHORE type waits, you might notice significant waits on this wait type for complex memory intensive queries.

Worker Thread Balance

 Effective number of worker threads and Inter-thread communication will have an effective utilization of CPu

there should be a right balance on the max number of worker thread , too many worker thread or too less worker threads will degrade the performance which will take us to a solid point that proper testing should be done in testing environment in order achieve required results

Distribution of data

 when we are achieving the parallelism the data will be divided between the threads for the processing but the problem is the rows or data will not be divided equally across all threads for some reasons

one known reason is the Selectivity of the data or (Skewed data)

Lets look at the simple example how distribution of data affects the parallelism and query execution

Example

  100 rows  —   Serial Execution —-  100 secs

  100 rows  — Parallel Execution (Two threads)

              Thread 1  50  rows  1 to 50 secs

              Thread 2 50 rows 1 to 50 secs

1——————50 secs 

1——————50 rows thread 1

50—————–100 rows thread 2

Total time for the query is 50 secs

But sometimes this is not the case in reality

1——————75 secs

1—-25                            rows thread 1

25—————-100 rows thread 2

Now the total time for the query is 75 secs

one real world example is when an index is created on 2.5 million row  but one thread have been allocated with 1.5 millon rows , reason came out be the rest 1 million rows have null strings

the sort operator could not be truly parallelized and increasing the number of parallel worker threads did not improve performance

Solution:

partitioned indexes, Filtered indexes 

One of the biggest peoblem is

CXPACKET Waits

In the case of exchange operators, synchronization is required between producer and consumer threads that are accessing the same exchange buffers. The CXPACKET lock is used for this purpose, and it provides exclusive buffer access to requesting producer and consumer threads. In most parallel execution cases, the synchronization between producer and consumer threads seems to perform well. However, there are cases (for example, when either the producer or consumer thread is waiting for a resource such as I/O, CPU, or memory grants) in which excessive CXPACKET waits can occur. Other possible causes for high CXPACKET waits are underestimation of the number of rows flowing through an operator and highly skewed data.

There is no way that we can eleminate the total CXPACKET wait but we only need to worry if it is too high which may degrade the performance

 

 

 Solution:

If you reduce the degree of parallelism (setting MAXDOP to an explicit value greater than 0), you reduce the number of producer and consumer buffers by a quadratic factor. This change results in fewer CXPACKET locks and consequently fewer CXPACKET waits. The ideal MAXDOP setting for a system is highly dependent on a number of factors such as workload, data access, and data distribution patterns of the query. For OLTP workloads, generally, a MAXDOP value of 1 is reliable. For data warehousing workloads, MAXDOP values ranging between ¼ and ½ the number of logical processors provided by the machine typically works well. In addition, this again depends on a number of factors such as workload, data access, and data distribution. We recommend that you not modify the degree of parallelism value on production systems without sufficient testing.

As an example, if you are experiencing unpredictable multi-user parallel query performance on a machine with 32 logical processors, consider setting the max degree of parallelism sp_configure option to 16 or even 8. For large queries that need the best possible response time, consider reducing the number of concurrently running queries (for example, during a batch window that you control) and running them with a MAXDOP value of 32.

Using the DMV

 Select * from sys.dm_os_waiting_tasks where wait_type =’CXPACKET’

we can alos look the wait time for cxpacket

Understanding and Controlling Parallel query processing – MAXDOP,Cost threshold,Parallel execution plans,Operators

What is parallel query execution in SQL server

Lets go in to the details of parllel execution and how it is done behind the scenes

The default value of max degree of parallelism in SQL Server is 0, which is interpreted as follows: If MAXDOP = 0 is used for a query, either via the default or through explicit use of a MAXDOP query hint, SQL Server can potentially use all the processor cores to parallelize a query up to a maximum of 64. For example:

a)      If MAXDOP is set to 0 on a 32 logical processor machine, the maximum degree of parallelism used is 32 unless another value is explicitly specified.

b)      If MAXDOP is set to 0 on a 256 logical processor machine, the maximum degree of parallelism used is 64 unless another value is explicitly specified. 

In Step 1, if the MAXDOP value is equal to 1 or if the cost of a query plan is lower than the cost threshold of parallelism, the query optimizer generates a serial plan and the query is executed serially. In the case of a parallel plan, if MAXDOP is set to 0 or to a value greater than 1, the SQL Server query execution engine uses the information from the query plan to calculate an expected degree of parallelism value (Step 2). This degree of parallelism calculation is dependent on the plan shape, the availability of system resources, and the query cost, while honoring the specified MAXDOP value if MAXDOP is greater than 1.

In Step 3, the query execution engine in SQL Server calculates the resources required for a query to execute such as memory and the number of threads. In Step 4, SQL Server uses an internal facility called the resource semaphore to reserve the threads and the memory resources that are required for execution. The resource semaphore ensures that queries are able to satisfy the respective resource reservation values while keeping the overall resource usage within the set limits. First, the memory reservation values for a query are checked to make sure the query needs memory. For example: Simple serial queries without ORDER BY or GROUP BY clauses may not need a memory grant, and in such cases the query is dispatched for execution immediately. Next, SQL Server examines the maximum memory limit for a particular query. This value can be configured through the Resource Governor workload group REQUEST_MAX_MEMORY_GRANT_PERCENT value. If the memory grant request can be met (that is, if the memory grant request value is less that the per-query maximum limit) and the required thread resources can be obtained, the query is executed immediately. After a query is dispatched for execution, the degree of parallelism cannot change. 

If there are not enough resources readily available, the query is put into a resource semaphore queue to wait for resources to become available. The time-out value associated with a query waiting in the resource semaphore queue is proportional to the query cost. The resource semaphore uses an internal query cost estimate to prioritize memory grants and decide which query to run next when memory is available. While the query is waiting in the resource semaphore queue, if the query times out, an error is returned (8657 or 8645). If the query is picked as a candidate for execution from the resource semaphore queue but cannot obtain the required resources, SQL Server reduces the estimated degree of parallelism (DOP) by half (Step 5), and then it recomputes the memory and thread resources for the query (Step 3). SQL Server may ultimately downgrade the parallel plan to a serial plan. Downgrading to a serial plan causes removal of parallelism operators from the plan prior to execution.

Lets do some tests with max dop

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now lets disable the MAX DOP and see the diffrence

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

when using the parallelism in the execution another important is the number of threads

In SQL Server, the max worker threads sp_configure configuration value determines the maximum number of worker threads created for handling all simultaneous SQL Server requests. The default value of max worker threads in SQL Server 2008 is 0. If the default value (0) is used for max worker threads, SQL Server automatically configures the number of worker threads at startup. In this case, the value depends on the machine architecture (32-bit vs. 64-bit) and on the number of logical processors.

Depending on the degree of parallelism value and the different possibilities of data flow and execution order in a parallel query plan, the number of threads calculated by the query execution engine for running a query is estimated

Estimated number of threads = MAXDOP * Number of parallel data flow tasks

  so in the above case     =  2 * 1  + 1  (cordinator thread) = 3

as we know every coin has two sides somtimes parallelism will not improve performance it may degrade when compared to the serial execution

lets lists the problems

1) Insufficiant memory during execution of parallel query plans

Memory requred for exchange operator and memory buffers in parallel execution is proportional to (DOP)² , so memory grant request for parallel execution can be large

2) Synchronizing overhead of parallel operators

in case of exchange operators (exchange row data between threads) synchronization is required between producer and consumer threads as they are accessing same exchange buffers  

CXPACKET lock is used for exclusive acces of buffers for  requesting producer and consumer threads

The synchronization between producer and consumer threads seems to perform well. However, there are cases (for example, when either the producer or consumer thread is waiting for a resource such as I/O, CPU, or memory grants) in which excessive CXPACKET waits can occur. Other possible causes for high CXPACKET waits are underestimation of the number of rows flowing through an operator and highly skewed data

3) Statistical estimation errors

4) Data skew errors

Reference(MSDN Parallel query processing )

Recommendations

Start with the default value of MAXDOP and reduce it only if you identify performance problems. A good approach is to progressively test with powers of two values. If you only have one or a few concurrent queries running, the default for MAXDOP (0) will often be optimal. Following are some techniques you can use to understand problems related to parallel processing in SQL Server and tips for mitigating them.

(1)    If you observe excessive CXPACKET waits, reduce the value of MAXDOP:

  • ·         In an OLTP system, use a MAXDOP value of 1 (serial execution), if that is sufficient. Consider explicitly setting it to a higher value for individual queries that require parallelism.
  • ·         In a data warehousing system, where you need high parallelism for better overall response time, setting MAXDOP between ¼ the number of logical processors and ½ the number of logical processors generally works well. Experiment in preproduction environments to decide the MAXDOP value that gives you the best combination of throughput and response time for your environment.

(2)    Higher degree of parallelism queries generally require more memory to run. If several concurrently running complex queries each execute in parallel with a degree of parallelism that is greater than 1, the memory requirement may be significant. Consider using Resource Governor to throttle the degree of parallelism and total number of parallel queries by workload using the guidelines in [8].

(3)    If there is a lack of worker threads:

  • ·         Reduce MAXDOP.
  • ·         Use Resource Governor to throttle the degree of parallelism and total number of parallel queries by workload using the guidelines in [8].

(4)    If you are observing what appears to be a suboptimal query plan due to cardinality estimation errors, consider updating table statistics using the guidelines in [4].

(5)    Consider rewriting some queries so that they perform well in parallel environments using the guidelines in [2].

(6)    Remember that parallel query performance is multifaceted; there is no silver-bullet solution that can solve all parallel performance issues.

Introduction to Wait stats – performance monitoring and tuning

Hi,

Recently I started reading this book SQL wait stats joes 2 pros written by the famous author Pinal Dave, I found it very interesting and its a good for those who want to know more about  SQL server wait stats , and there are not many resources out there which explain these concepts

While reading it I found some interesting facts and I want to share them here

What are Wait stats?

When sql server executes a task , if for any reason if it needs to wait for the resources or if wait occurs when executing the task these waits are recorded by the SQL server, the recording of the wait in SQL server is known as a WAIT STAT

in order to understand the wait stat we need to understand the Query Execution Life Cycle which is very important

Now we know how the query execution takes place lets know about the waits which can take place during the cycle

Wait stats

All these waits are recorded in  a  dynamic management view called  ”SYS.DM_OS_WAIT_STATS”

Like the above mentioned waited SQL Server logs 490 different types of waits at Sql Server level, and Server level waits which is great  way to know the current waits on the server

SYS.DM_OS_WAIT_STATS

Code:

SELECT *

FROM SYS.DM_OS_WAIT_STATS

WHERE WAIT_TIME_MS > 0

AND WAIT_TYPE NOT IN

(‘CLR_SEMAPHORE’,'LAZYWRITER_SLEEP’,'RESOURCE_QUEUE’,'SLEEP_TASK’
,’SLEEP_SYSTEMTASK’,'SQLTRACE_BUFFER_FLUSH’,'WAITFOR’,'LOGMGR_QUEUE’,'CHECKPOINT_QUEUE’
,’REQUEST_FOR_DEADLOCK_SEARCH’,'XE_TIMER_EVENT’,'BROKER_TO_FLUSH’
,’BROKER_TASK_STOP’,'CLR_MANUAL_EVENT’,'CLR_AUTO_EVENT’,'DISPATCHER_QUEUE_SEMAPHORE’
,’FT_IFTS_SCHEDULER_IDLE_WAIT’,'XE_DISPATCHER_WAIT’,'XE_DISPATCHER_JOIN’
,’SQLTRACE_INCREMENTAL_FLUSH_SLEEP’)

ORDER BY WAIT_TIME_MS DESC

A modified version is below which provides more information

Above result is not considered as not so bad performace but can be better than that

the information in SYS.DM_WAIT_STATS are recorded from the time when database engine is started, we can refresh the view with out even restarting the server

with a simple DBCC command

DBCC SQLPERF(‘SYS.DM_OS_WAIT_STATS’,CLEAR)

CODE:

SELECT CAST(100.00 *SUM(SIGNAL_WAIT_TIME_MS)/SUM(WAIT_TIME_MS)

AS NUMERIC(20,4)) AS ‘% OF QUERIES WAITING FOR CPU’

,CAST(100.00 *SUM(WAIT_TIME_MS-SIGNAL_WAIT_TIME_MS)/SUM(WAIT_TIME_MS)

AS NUMERIC(20,6)) AS ‘% CPU SPENDING TIME RUNNING QUERIS OR SUSPENDED ‘

FROM SYS.DM_OS_WAIT_STATS

WHERE WAIT_TIME_MS > 0

AND WAIT_TYPE NOT IN

(‘CLR_SEMAPHORE’,'LAZYWRITER_SLEEP’,'RESOURCE_QUEUE’,'SLEEP_TASK’
,’SLEEP_SYSTEMTASK’,'SQLTRACE_BUFFER_FLUSH’,'WAITFOR’,'LOGMGR_QUEUE’,'CHECKPOINT_QUEUE’
,’REQUEST_FOR_DEADLOCK_SEARCH’,'XE_TIMER_EVENT’,'BROKER_TO_FLUSH’
,’BROKER_TASK_STOP’,'CLR_MANUAL_EVENT’,'CLR_AUTO_EVENT’,'DISPATCHER_QUEUE_SEMAPHORE’
,’FT_IFTS_SCHEDULER_IDLE_WAIT’,'XE_DISPATCHER_WAIT’,'XE_DISPATCHER_JOIN’
,’SQLTRACE_INCREMENTAL_FLUSH_SLEEP’)

Thank you

tempdb – Space Usage, Capacity planning, Performance Tuning

For every SQL Dba managing TempDB is a very important task in his Day to Day tasks , it is always better to understand the tempdb in a more practical way,

Because Tempdb is an important System database if it is not taken care properly it will definitely disrupts the services and applications accessing  SQL server

The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

  • Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
  • Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
  • Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
  • Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Operations within tempdb are minimally logged. This enables transactions to 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.

User Objects

User objects are explicitly created by the user. These objects may be in the scope of a user session or in the scope of the routine in which the object is created. A routine is a stored procedure, trigger, or user-defined function. User objects can be one of the following:

  • User-defined tables and indexes
  • System tables and indexes
  • Global temporary tables and indexes
  • Local temporary tables and indexes
  • Table variables
  • Tables returned in table-valued functions

Internal Objects

Internal objects are created as necessary by the SQL Server Database Engine to process SQL Server statements. Internal objects are created and dropped within the scope of a statement. Internal objects can be one of the following:

  • Work tables for cursor or spool operations and temporary large object (LOB) storage.
  • Work files for hash join or hash aggregate operations.
  • Intermediate sort results for operations such as creating or rebuilding indexes (if SORT_IN_TEMPDB is specified), or certain GROUP BY, ORDER BY, or UNION queries.

Each internal object uses a minimum of nine pages; one IAM page and one eight-page extent. For more information about pages and extents, see Understanding Pages and Extents.

Version Stores

A version store is a collection of data pages that hold the data rows that are required to support the features that use row versioning. There are two version stores: a common version store and an online-index-build version store. The version stores contain the following:

  • Row versions that are generated by data modification transactions in a database that uses snapshot or read committed using row versioning isolation levels.
  • Row versions that are generated by data modification transactions for features such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

The following table lists the features in SQL Server that create user objects, internal objects, or row versions in tempdb. Whenever possible, the methods for estimating disk space use are provided.

Feature tempdb use Additional information
Bulkload operations with triggers enabled Bulk-import optimizations are available when triggers are enabled. SQL Server uses row versioning for triggers that update or delete transactions. A copy of each deleted or updated row is added to the version store. See “Triggers” that follows later in this table. Optimizing Bulk Import Performance
Common table expression queries A common table expression can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.When the query plan for a common table expression query uses a spool operator to save intermediate query results, the Database Engine creates a work table in tempdb to support this operation. Using Common Table ExpressionsWITH common_table_expression (Transact-SQL)
Cursors Keyset-driven and static cursors use work tables that are built in tempdb. Keyset-driven cursors use the work tables to store the set of keys that identify the rows in the cursor. Static cursors use a work table to store the complete result set of the cursor.The disk space usage for cursors may vary depending on the query plan that is chosen. If the query plan is the same as earlier versions of SQL Server, the disk space usage is approximately the same. About Choosing a Cursor Type
Database Mail See “Service Broker” that follows later in this table. Database Mail
DBCC CHECKDB DBCC CHECKDB uses tempdbwork tables to hold intermediate results and for sort operations.To determine tempdb disk space requirements for the operation, run DBCC CHECKDB WITH ESTIMATEONLY. DBCC CHECKDB (Transact-SQL)Optimizing DBCC CHECKDB Performance
Event notifications See “Service Broker” that follows later in this table. Understanding Event Notifications
Indexes When you create or rebuild an index (offline or online) and set the SORT_IN_TEMPDB option to ON, you direct the Database Engine to use tempdb to store the intermediate sort results that are used to build the index. When SORT_IN_TEMPDB is specified and sorting is required, tempdb must have sufficient disk space to hold the largest index plus disk space that is equal to the value of the index create memory option. For more information, see Index Disk Space Example.Tables and indexes can be partitioned. For partitioned indexes, if the SORT_IN_TEMPDB index option is specified and the index is aligned with the base table, there must be sufficient space in tempdb to hold the intermediate sort runs of the largest partition. If the index is not aligned, there must be sufficient space in tempdb to hold the intermediate sort runs of all partitions. For more information, see Special Guidelines for Partitioned Indexes.Online index operations use row versioning to isolate the index operation from the effects of modifications that are made by other transactions. Row versioning removes the need for requesting share locks on rows that have been read. Concurrent user update and delete operations during online index operations require space for version records in tempdb. When online index operations use SORT_IN_TEMPDB and sorting is required, tempdb must also have the additional disk space previously described for intermediate sort results. Online index operations that create, drop, or rebuild a clustered index also require additional disk space to build and maintain a temporary mapping index. CREATE and UPDATE STATISTICS operations can use tempdb to sort the sample of rows for building statistics. For more information, see Disk Space Requirements for Index DDL Operations. tempdb and Index CreationSpecial Guidelines for Partitioned IndexesDisk Space Requirements for Index DDL Operations

Index Disk Space Example

How Online Index Operations Work
Large object (LOB) data type variables and parameters The large object data types are varchar(max), nvarchar(max), varbinary(max) text, ntext, image, and xml. These types can be up to 2 GB in size and can be used as variables or parameters in stored procedures, user-defined functions, batches, or queries. Parameters and variables that are defined as a LOB data type use main memory as storage if the values are small. However, large values are stored in tempdb. When LOB variables and parameters are stored in tempdb, they are treated as internal objects. You can query the sys.dm_db_session_space_usagedynamic management view to report the pages allocated to internal objects for a given session.Some intrinsic string functions, such as SUBSTRING or REPLICATE, may require intermediate temporary storage in tempdb when they are operating on LOB values. Similarly, when a row versioning-based transaction isolation level is enabled on the database and modifications of large objects are made, the changed fragment of the LOB is copied to the version store in tempdb. Using Large-Value Data Types
Multiple Active Result Sets (MARS) Multiple active result sets can occur under a single connection; this is commonly referred as MARS. If a MARS session issues a data modification statement (such as INSERT, UPDATE, or DELETE) when there is an active result set, the rows that are affected by the modification statement are stored in the version store in tempdb. See “Row versioning” that follows later in this table. Using Multiple Active Result Sets (MARS)
Query notifications See “Service Broker” that follows later in this table. Using Query Notifications
Queries Queries that contain SELECT, INSERT, UPDATE, and DELETE statements can use internal objects to store intermediate results for hash joins, hash aggregates, or sorting.When a query execution plan is cached, the work tables required by the plan are cached. When a work table is cached, the table is truncated and nine pages remain in the cache for reuse. This improves the performance of the next execution of the query. If the system is low on memory, the Database Engine can remove the execution plan and drop the associated work tables. Execution Plan Caching and Reuse
Row versioning Row versioning is a general framework that is used to support the following features:

  • Triggers
  • Multiple Active Result Sets (MARS)
  • Index operations that specify the ONLINE option
  • Row versioning-based transaction isolation levels:
    • A new implementation of read-committed isolation level that uses row versioning to provide statement-level read consistency.
    • A snapshot isolation level to provide transaction-level read consistency.

Row versions are held in the tempdb version store for as long as an active transaction must access it. The content of the current version store is returned in sys.dm_tran_version_store. Version-store pages are tracked at the file level because they are global resources. You can use the version_store_reserved_page_count column in sys.dm_db_file_space_usage to view the current size of the version store. Version-store cleanup must consider the longest running transaction that requires access to the particular version. The longest running transaction related to version store clean-up can be discovered by viewing the elapsed_time_seconds column in sys.dm_tran_active_snapshot_database_transactions. The counters Free Space in Tempdb (KB) and Version Store Size (KB) in the Transactions object can be used to monitor the size and rate of growth of the row-version store in tempdb. For more information, see SQL Server, Transactions Object.

To estimate how much space is required in tempdb for row versioning, you have to first consider that an active transaction must keep all its changes in the version store. This means that a snapshot transaction that starts later can access the old versions. Also, if there is an active snapshot transaction, all the version store data that is generated by transactions that are active when the snapshot starts must also be kept.

Here is a basic formula:

[Size of Version Store] = 2 *

[Version store data generated per minute] *

[Longest running time (minutes) of your transaction]

 Understanding Row Versioning-Based Isolation LevelsRow Versioning Resource Usage
Service Broker Service Broker helps developers build asynchronous, loosely coupled applications in which independent components work together to accomplish a task. These application components exchange messages that contain the information that is required to complete the task. Service Broker explicitly uses tempdbfor preserving existing dialog context that cannot stay in memory. The size is approximately 1 KB per dialog.Also, Service Broker implicitly uses tempdbby the caching of objects in the context of query execution, such as work tables that are used for timer events and background delivered conversations.Database Mail, Event Notifications, and Query Notifications implicitly use Service Broker. Overview (Service Broker)
Stored procedures Stored procedures can create user objects such as global or local temporary tables and their indexes, variables, or parameters. Temporary objects in stored procedures can be cached to optimize the operations that drop and create these objects. This behavior can increase tempdb disk space requirements. Up to nine pages per temporary object are stored for reuse. See “Temporary tables and table variables” that follows later in this table. Creating Stored Procedures (Database Engine)
Temporary tables and table variables

  • User-defined tables and indexes
  • System tables and indexes
  • Global temporary tables and indexes
  • Local temporary tables and indexes
  • table variables
  • Tables returned in table-valued functions
 Temporary tables and table variables are stored in tempdb. The disk space requirements for temporary table objects are the same as earlier versions of SQL Server. The method for estimating the size of a temporary table size is the same as estimating the size of a standard table. For more information, see Estimating the Size of a Table.A table variable behaves like a local variable. A table variable is of type table and is primarily used for the temporary storage of a set of rows that are returned as the result set of a table-valued function. The disk space that is required to hold a table variable depends on the size of the declared variable and the value stored in the variable.Local temporary tables and variables are cached when the following conditions are satisfied:

  • Named constraints are not created.
  • Data Definition Language (DDL) statements that affect the table are not run after the temporary table has been created, such as the CREATE INDEX or CREATE STATISTICS statements.
  • The temporary object is not created by using dynamic SQL, such as: sp_executesql N’create table #t(a int)’.
  • The temporary object is created inside another object, such as a stored procedure, trigger, user-defined function; or is the return table of a user-defined, table-valued function.

When a temporary table or table variable is cached, the temporary object is not deleted when its purpose ends. Instead, the temporary object is truncated. Up to nine pages are stored and reused the next time that the calling object is executed. Caching allows operations that drop and create the objects to execute very quickly and reduces page allocation contention.

For optimal performance, you should calculate the disk space that is required for cached local temporary tables or table variables in tempdb by using the following formula:

9 page per temp table

* number of average temp tables per procedure

* number of maximum simultaneous executions of the procedure

 CREATE TABLE (Transact-SQL)Using Variables and Parameters (Database Engine)DECLARE @local_variable (Transact-SQL)
Triggers The inserted and deleted tables that are used in AFTER triggers are created in tempdb. That is, the rows that are updated or deleted by the trigger are versioned. This includes all of the rows that are modified by the statement that fired the trigger. Rows that are inserted by the trigger are not versioned.INSTEAD OF triggers use tempdbin way similar to queries. The disk space usage for INSTEAD OF triggers is the same as earlier versions of SQL Server. See “Queries” previously in this table.When you bulk load data with triggers enabled, a copy of each deleted or updated row is added to the version store. CREATE TRIGGER (Transact-SQL)Optimizing Bulk Import PerformanceRow Versioning Resource Usage
User-defined functions User-defined functions can create temporary user objects, such as global or local tables and their indexes, variables, or parameters. For example, the return table of a table-valued function is stored in tempdb.The data types that are allowed for parameters and return values in scalar functions and table-valued functions include most LOB data types. For example, a return value can be of type xml or varchar(max). See “Large object (LOB) data type variables and parameters” previously in this table.Temporary objects in table-valued user-defined functions can be cached to optimize the operations that drop and create these objects. See “Temporary tables and table variables” previously in this table. CREATE FUNCTION (Transact-SQL)
XML Variables and parameters of type xml can be up to 2 GB. They use main memory as storage as long as the values are small. However, large values are stored in tempdb. See “Large object (LOB) data type variables and parameters” previously in this table.The sp_xml_preparedocument system stored procedure creates a work table in tempdb. The MSXML parser uses the work table to store the parsed XML document. The disk space requirements for tempdb is nearly proportional to the size of the specified XML document when the stored procedure is execute. Implementing XML in SQL Serversp_xml_preparedocument (Transact-SQL)Querying XML Using OPENXML

Capacity planning for temp db

Determining the appropriate size for tempdbin a production environment depends on many factors. As described previously in this topic, these factors include the existing workload and the SQL Server features that are used. We recommend that you analyze the existing workload by performing the following tasks in a SQL Server test environment:

  1. Set autogrow on for tempdb.
  2. Execute individual queries or workload trace files and monitor tempdb space use.
  3. Execute index maintenance operations, such as rebuilding indexes and monitor tempdb space.
  4. Use the space-use values from the previous steps to predict your total workload usage; adjust this value for projected concurrent activity, and then set the size of tempdb accordingly.

Monitoring and troubleshooting Tempdb disk space problems

SELECT

[NAME]                  AS [LOGICAL FILE NAME],

CASE TYPE_DESC

WHEN ‘ROWS’ THEN ‘DATAFILE’

WHEN ‘LOG’  THEN ‘LOGFILE’

END                     AS [FILE TYPE],

PHYSICAL_NAME           AS [FILE PATH],

[SIZE]                  AS [FILE SIZE IN KB],
[SIZE]/1024.00                  AS [FILE SIZE IN MB],
[SIZE]/(1024.00*1024)                  AS [FILE SIZE IN GB],

CASE GROWTH

WHEN 0 THEN ‘DISABLED’

ELSE ‘ENABLED’
END                     AS [AUTO GROWTH]

,GROWTH

,CASE      IS_PERCENT_GROWTH

WHEN 0 THEN ‘GROWTH IN KB’
ELSE ‘GROWTH IN PERCENTAGE’

END AS  ‘TYPE OF GROWTH’

FROM TEMPDB.SYS.DATABASE_FILES

Query

You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space used by the user objects, internal objects, and version stores in the tempdb files. Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using a large amount of tempdb disk space.

free space

Code:
–MONITERING TEMPDB SIZE
SELECT * FROM SYS.DM_DB_FILE_SPACE_USAGE

SELECT SUM(UNALLOCATED_EXTENT_PAGE_COUNT) AS ‘FREE PAGES’
,SUM(VERSION_STORE_RESERVED_PAGE_COUNT)AS ‘ALLOCATED PAGES TO VERSION STORE’
,SUM(USER_OBJECT_RESERVED_PAGE_COUNT) AS ‘ALLOCATED PAGES TO USER OBJECTS’
,SUM(INTERNAL_OBJECT_RESERVED_PAGE_COUNT) AS ‘ALLOCATED PAGES TO INTERANL OBJECTS’

FROM  SYS.DM_DB_FILE_SPACE_USAGE

– ONE PAGE = 8KB
–ONE MB = 1024 KB

SELECT (SUM(UNALLOCATED_EXTENT_PAGE_COUNT) * 8)/1024.00 AS ‘FREE SPACE IN MB’
,(SUM(VERSION_STORE_RESERVED_PAGE_COUNT) * 8 )/1024.00 AS ‘SPACE ALLOCATED TO VERSION STORE IN MB’
,(SUM(USER_OBJECT_RESERVED_PAGE_COUNT) * 8)/1024.00 AS ‘SPACE ALLOCATED TO USER OBJECTS IN MB ‘
,(SUM(INTERNAL_OBJECT_RESERVED_PAGE_COUNT) * 8 )/1024.00 AS ‘SPACE ALLOCATED TO INTERANL OBJECTS IN MB’

FROM  SYS.DM_DB_FILE_SPACE_USAGE

Error Is raised when
1101 or 1105 Any session must allocate space in tempdb.
3959 The version store is full. This error usually appears after a 1105 or 1101 error in the log.
3967 The version store is forced to shrink because tempdb is full.
3958 or 3966 A transaction cannot find the required version record in tempdb.

How to find out  query which utilized or  utilizing the maximum tempdb space

we will use the two other dynamic management views to answer this question

1)  sys.dm_db_session_space_usage

2) sys.dm_db_task_space_usage

Note: these views are only applicable for Tempdb

Sys.dm_db_task_space_usage  :  This views has entry for each task currently running in each session

Sys.dm_db_session_space_usage  :  This views has entry for each session currently running or completed task

code:

–SQLJUNKIESHARE
SELECT DM.SESSION_ID,SUM(USER_OBJECTS_ALLOC_PAGE_COUNT) AS ‘ALLOCATED PAGES’,SUM(USER_OBJECTS_DEALLOC_PAGE_COUNT) AS ‘DEALLOCATED PAGES’

, (SUM(USER_OBJECTS_ALLOC_PAGE_COUNT))/(DATEDIFF(SS,DME.START_TIME,GETDATE()))’PAGES PER SEC’, SUM(USER_OBJECTS_ALLOC_PAGE_COUNT) /128 AS ‘SPACE IN MB’

FROM SYS.DM_DB_TASK_SPACE_USAGE  DM INNER JOIN SYS.DM_EXEC_REQUESTS DME ON DME.SESSION_ID = DM.SESSION_ID

WHERE DATEDIFF(SS,DME.START_TIME,GETDATE()) != 0

GROUP BY DM.SESSION_ID,DATEDIFF(SS,DME.START_TIME,GETDATE())

ORDER BY SUM(USER_OBJECTS_ALLOC_PAGE_COUNT) DESC

code

SELECT SESSION_ID,SUM(USER_OBJECTS_ALLOC_PAGE_COUNT) AS ‘ALLOCATED PAGES’,SUM(USER_OBJECTS_DEALLOC_PAGE_COUNT) AS ‘DEALLOCATED PAGES’

FROM SYS.DM_DB_SESSION_SPACE_USAGE

GROUP BY SESSION_ID

ORDER BY SUM(USER_OBJECTS_ALLOC_PAGE_COUNT) DESC

in order  to find the query its self just modify the query SELECT DM.SESSION_ID,SUM(USER_OBJECTS_ALLOC_PAGE_COUNT) AS ‘ALLOCATED PAGES’,SUM(USER_OBJECTS_DEALLOC_PAGE_COUNT) AS ‘DEALLOCATED PAGES’

, (SUM(USER_OBJECTS_ALLOC_PAGE_COUNT))/(DATEDIFF(SS,DME.START_TIME,GETDATE()))’PAGES PER SEC’, SUM(USER_OBJECTS_ALLOC_PAGE_COUNT) /128 AS ‘SPACE IN MB’

,text

FROM SYS.DM_DB_TASK_SPACE_USAGE  DM INNER JOIN SYS.DM_EXEC_REQUESTS DME ON DME.SESSION_ID = DM.SESSION_ID

cross apply sys.dm_exec_sql_text(DME.sql_handle)

WHERE DATEDIFF(SS,DME.START_TIME,GETDATE()) != 0

GROUP BY DM.SESSION_ID,DATEDIFF(SS,DME.START_TIME,GETDATE()),text

ORDER BY SUM(USER_OBJECTS_ALLOC_PAGE_COUNT) DESC

automate the above steps by creating a job and polling those views for every three minutes or more as required and we can easily tune the tempdb

tempdb Size and Placement Recommendations

To achieve optimal tempdb performance, we recommend the following configuration for tempdb in a production environment:

  • Set the recovery model of tempdb to SIMPLE. This model automatically reclaims log space to keep space requirements small.For more information, see ALTER DATABASE (Transact-SQL) or How to: View or Change the Recovery Model of a Database (SQL Server Management Studio).
  • Allow for tempdb files to automatically grow as required. This allows for the file to grow until the disk is full.
    Note Note
    If the production environment cannot tolerate the potential for application time-outs that may occur during autogrow operations, preallocate space to allow for the expected workload.
  • Set the file growth increment to a reasonable size to avoid the tempdb database files from growing by too small a value. If the file growth is too small, compared to the amount of data that is being written to tempdb, tempdb may have to constantly expand. This will affect performance. We recommend the following general guidelines for setting the FILEGROWTH increment for tempdb files.
    tempdb file size FILEGROWTH increment
    0 to 100 MB 10 MB
    100 to 200 MB 20 MB
    200 MB or more 10%*

    * You may have to adjust this percentage based on the speed of the I/O subsystem on which the tempdb files are located. To avoid potential latch time-outs, we recommend limiting the autogrow operation to approximately two minutes. For example, if the I/O subsystem can initialize a file at 50 MB per second, the FILEGROWTH increment should be set to a maximum of 6 GB, regardless of the tempdb file size. If possible, use instant database file initialization to improve the performance of autogrow operations.

  • Preallocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment. This prevents tempdb from expanding too frequently, which can affect performance. The tempdb database should be set to autogrow, but this should be used to increase disk space for unplanned exceptions.
  • Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs.
  • Make each data file the same size; this allows for optimal proportional-fill performance.
  • Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.
  • Put the tempdb database on disks that differ from those that are used by user databases.

Free space left in tempdb (in KB)

SELECT

                    DB_NAME(database_id)    AS          [DATABASE Name],

                    [Name]                  AS     [Logical Name],

                    [filename]              AS    [FILE Name],

                    [size_on_disk_bytes] / 1024   AS    [Total SIZE (in KB)],

                    [num_of_bytes_written] / 1024 AS    [SPACE Used (in KB)],

                    ([size_on_disk_bytes] – [num_of_bytes_written]) / 1024 AS [FREE SPACE LEFT (in KB)]

FROM sys.dm_io_virtual_file_stats(2, NULL)

                    INNER join

      sys.sysfiles

                    ON sys.dm_io_virtual_file_stats.FILE_ID = sys.sysfiles.fileid

A long running transaction may prevent cleanup of transaction log thus eating up all log space available resulting space crisis for all other applications.

SELECT

                    transaction_id AS [Transacton ID],

                    [name]      AS [TRANSACTION Name],

                    transaction_begin_time AS [TRANSACTION BEGIN TIME],

                    DATEDIFF(mi, transaction_begin_time, GETDATE()) AS [Elapsed TIME (in MIN)],

                    CASE transaction_type

                                         WHEN 1 THEN ‘Read/write’

                    WHEN 2 THEN ‘Read-only’

                    WHEN 3 THEN ‘System’

                    WHEN 4 THEN ‘Distributed’

                    END AS [TRANSACTION Type],

                    CASE transaction_state

                                         WHEN 0 THEN ‘The transaction has not been completely initialized yet.’

                                         WHEN 1 THEN ‘The transaction has been initialized but has not started.’

                                         WHEN 2 THEN ‘The transaction is active.’

                                         WHEN 3 THEN ‘The transaction has ended. This is used for read-only transactions.’

                                         WHEN 4 THEN ‘The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.’

                                         WHEN 5 THEN ‘The transaction is in a prepared state and waiting resolution.’

                                         WHEN 6 THEN ‘The transaction has been committed.’

                                         WHEN 7 THEN ‘The transaction is being rolled back.’

                                         WHEN 8 THEN ‘The transaction has been rolled back.’

                    END AS [TRANSACTION Description]

FROM sys.dm_tran_active_transactions

Issues with TempDB and Indexes

Besides the disk space required for the source and target structures, temporary disk space is required for sorting, unless the query optimizer finds an execution plan that does not require sorting.

If sorting is required, sorting occurs one new index at a time. For example, when you rebuild a clustered index and associated nonclustered indexes within a single statement, the indexes are sorted one after the other. Therefore, the additional temporary disk space that is required for sorting only has to be as large as the largest index in the operation. This is almost always the clustered index.

If the SORT_IN_TEMPDB option is set to ON, the largest index must fit into tempdb. Although this option increases the amount of temporary disk space that is used to create an index, it may reduce the time that is required to create an index when tempdb is on a set of disks different from the user database. For more information about sorting in tempdb, see tempdb and Index Creation.

If SORT_IN_TEMPDB is set to OFF (the default) each index, including partitioned indexes, is sorted in its destination disk space; and only the disk space for the new index structures is required.

For an example of calculating disk space, see Index Disk Space Example.

When you perform index operations online, additional temporary disk space is required.

If a clustered index is created, rebuilt, or dropped online, a temporary nonclustered index is created to map old bookmarks to new bookmarks. If the SORT_IN_TEMPDB option is set to ON, this temporary index is created in tempdb. If SORT_IN_TEMPDB is set to OFF, the same filegroup or partition scheme as the target index is used. The temporary mapping index contains one record for each row in the table, and its contents is the union of the old and new bookmark columns, including uniqueifiers and record identifiers and including only a single copy of any column used in both bookmarks. For more information about online index operations, see Performing Index Operations Online.

Note Note
The SORT_IN_TEMPDB option cannot be set for DROP INDEX statements. The temporary mapping index is always created in the same filegroup or partition scheme as the target index.

Online index operations use row versioning to isolate the index operation from the effects of modifications made by other transactions. This avoids the need for requesting share locks on rows that have been read. Concurrent user update and delete operations during online index operations require space for version records in tempdb. For more information, see Performing Index Operations Online and Understanding Row Versioning-Based Isolation Levels.

If a sort operation is not required or if the sort can be performed in memory, the SORT_IN_TEMPDB option is ignored.

When you set the SORT_IN_TEMPDB option to ON, you must have sufficient free disk space available in tempdb to hold the intermediate sort runs, and enough free disk space in the destination filegroup to hold the new index. The CREATE INDEX statement fails if there is insufficient free space and there is some reason the databases cannot autogrow to acquire more space, such as no space on the disk or autogrow is set to off.

Click here to know more about index disk space requirements

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

Moving  tempDB

A. Moving the tempdb database

The following example moves the tempdb data and log files to a new location as part of a planned relocation.

Note Note
Because tempdb is re-created each time the instance of SQL Server is started, you do not have to physically move the data and log files. The files are created in the new location when the service is restarted in step 3. Until the service is restarted, tempdb continues to use the data and log files in existing location.
  1. Determine the logical file names of the tempdb database and their current location on the disk.
    Copy
    SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO
  2. Change the location of each file by using ALTER DATABASE.
    Copy
    USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
GO
  3. Stop and restart the instance of SQL Server.
  4. Verify the file change.
    Copy
    SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
  5. Delete the tempdb.mdf and templog.ldf files from the original location.