SQL Server – Temp Table Structure

Background

Created a temporary table on the fly.

But, before wrapping things up, I wanted to review the temp table’s structure.

 

SQL

sp_help

Syntax


exec [tempdb]..sp_help [table]

 

Sample


exec [tempdb]..sp_help [#cache]

Output

 

Generate Table Create Statement

dbo.itvf_getTempTableCreateStatement

Procedure


use [master]
go

/*

    drop function [dbo].[itvf_getTempTableCreateStatement]

*/
if object_id('[dbo].[itvf_getTempTableCreateStatement]') is null
begin

    exec
    ('
        CREATE FUNCTION [dbo].[itvf_getTempTableCreateStatement]
        (	
        )
        RETURNS TABLE 
        AS
        RETURN 
        (
            -- Add the SELECT statement with parameter references here
            SELECT [shell] = 1/0
        )
    ')

end
go

ALTER FUNCTION [dbo].[itvf_getTempTableCreateStatement]
(
    @object sysname	
)
RETURNS TABLE 
AS
RETURN 
(

    with cteColumnTypesWithColumnLength
    (
        [type]
    )
    as
    (
        select 'char'
        union all
        select 'nchar'
        union all
        select 'varchar'
        union all
        select 'nvarchar'

    )
    select 
              [columnID]
                = tblSC.[column_id]

            , [column]
                 = tblSC.[name]

            , [maxLength]
                 = tblSC.[max_length]

            , [userType] 
                = tblST.[name]

            , [isNullable]
                 = tblSC.[is_nullable]

            , [sqlClause] =

                case ( tblSC.[column_id] )
                    when 1 then '  '
                    else ', '
                end
                                
                +  quoteName(tblSC.[name])

                + ' '

                + tblST.[name]

                + case
                    when (
                            tblST.[name] in
                                (
                                    select [type] 
                                    from  cteColumnTypesWithColumnLength
                                )	 
                        )
                        then 
                            '('
                            + cast(tblSC.[max_length] as varchar(4))
                            + ')'

                    else ' '

                    end
                                
                + ' '

                + case tblSC.[is_nullable]

                    when 1 then ' null '
                    when 0 then ' not null '
                    else ' '

                  end

            , [colList] =

                case ( tblSC.[column_id] )
                    when 1 then '  '
                    else ', '
                end
                                
                +  quoteName(tblSC.[name])

    
    from   [tempdb].sys.objects tblSO

    inner join [tempdb].sys.columns tblSC

        on tblSO.object_id = tblSC.object_id

    inner join [tempdb].sys.types tblST

        on tblSC.[user_type_id] = tblST.[user_type_id]

    where (

            ( 
                tblSO.object_id 
                    = object_id
                        (
                            'tempdb..' + @object
                        ) 
            )

        )

)

go

Sample


use [tempdb]
go

set xact_abort on
go

set nocount on
go

declare @table sysname

set @table = '#cache'

if object_id('tempdb..#cache') is not null
begin

	drop table #cache

end

select top 10 *
into   #cache
from   sys.messages tblM

--exec [tempdb]..sp_help #cache

select *
from   [master].[dbo].[itvf_getTempTableCreateStatement]
		(
			@table
		)

if object_id('tempdb..#cache') is not null
begin

	drop table #cache

end
go


Output

 

Summary

Both sp_help and object_id work equally well with temp objects.

In the case of sp_help, please make sure that you reference ( temdpb) as in tempd..sp_help.

And, in the case of object_id please make sure that you are cross-referencing the system tables in tempdb.

And, that you use the full object’s name, tempdb..[objectname], when you attempt to get the object_id.

Entity Framework – Query – “SELECT 1 AS [C1]” – Workfile & WorkTable

Background

Sent a couple of goodwill posts towards Entity Framework.

Ever the curmudgeon, it is time to start taking some shots.

 

Sample Generated Query

Query Snippet – Original

Here is a very small portion of one of the queries generated by Entity Framework.


select 1

where 
(
	NOT EXISTS
         (

		SELECT 1 AS [C1]
            
		FROM
                (

		    SELECT
 
		        [Extent10].[End_Id] AS [End_Id],
                        [Extent10].[FootnoteParent_Id] AS [FootnoteParent_Id],
                        [Extent11].[Id] AS [Id2],
                        [Extent12].[Id] AS [Id3],
                        [Extent12].[BeginDate] AS [BeginDate]
             
		   FROM [dbo].[UcNoteContainerFootnotes] AS [Extent10]
				
		   INNER JOIN [dbo].[CourseUCTCA] AS [Extent11]
				 
			ON ([Extent10].[Container_Id] = [Extent11].[NoteContainer_Id])
			AND ([Extent11].[NoteContainer_Id] IS NOT NULL)
             
		  LEFT OUTER JOIN [dbo].[AcademicYears] AS [Extent12] 
			ON [Extent10].[Begin_Id] = [Extent12].[Id]

            
		  WHERE [Extent10].[Container_Id] IS NOT NULL 

			
	      ) AS [Filter2]
          
	     LEFT OUTER JOIN [dbo].[AcademicYears] AS [Extent13]
		   
		ON [Filter2].[End_Id] = [Extent13].[Id]

	) -- NOT EXISTS

) -- SELECT 1


Query Snippet – Revised

Here is a rewrite



select 1

where not exists 
(

	SELECT 
			1

	FROM [dbo].[UcNoteContainerFootnotes] AS [Extent10]
	
	INNER JOIN [dbo].[CourseUCTCA] AS [Extent11]
				 
		ON ([Extent10].[Container_Id] = [Extent11].[NoteContainer_Id])
	

) -- SELECT 1


Explanation

  1. We can discard is not null, due to the same columns being referenced in the “Inner Join
    • The Inner Join “ON ([Extent10].[Container_Id] = [Extent11].[NoteContainer_Id])
    • And, the where clause we can likely jettison are
      • AND ([Extent11].[NoteContainer_Id] IS NOT NULL)
      • WHERE  ( [Extent10].[Container_Id] IS NOT NULL )
  2. We can also likely discard the Left Outer Join, as they are quite not needed in a Not Exists Clause
    • LEFT OUTER JOIN [dbo].[AcademicYears] AS [Extent12]
      • ON [Extent10].[Begin_Id] = [Extent12].[Id]
    • LEFT OUTER JOIN [dbo].[AcademicYears] AS [Extent13]
      • ON [Extent10].[End_Id] = [Extent13].[Id]

 

Obviously, we will have to test things out.

 

 

Query Plan

Query Snippet – Original

Query Snippet – Revised

Explanation

  1. Query Comparison
    • The Original Query is at 17%
    • And, the revised is at 83%
  2. Join Type
    • Original Query uses “Hash Join
      • Cost is 79%
    • And, the revised is “Nested Loops
      • Cost is 2%

 

Statistics I/O

Query Snippet – Original

 

Query Snippet – Revised

Tabulate

Table Original Query Revised Query
 UcNoteContainerFootnotes Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 CourseUCTCA  Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 Workfile  Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 WorkTable  Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

 

Explanation

  • The reworked query appears to be less taxing in terms of IO
    • CourseUCTCA
      • Original Query :- Logical Reads is at 8
      • Revised Query :- Logical Reads is at 4
    • Workfile
      • Original Query because it is an Hash Join
    • WorkTable
      • Original Query, also because it is an Hash Join

 

Summary

In later posts, will talk more about WorkTable and Workfiles.

What they are and how to measure their cost.

And, very importantly, how to make sure code re-write is accurate.

Unfortunately, Entity Framework generated SQL Code can suffer from a little intemperance

 

Tempdb – Memory Uptake – Real Life Samples – Day 01

Background

Want to start identifying queries that can cause your Tempdb to grow.

When possible, we will delineate as to which Tempdb component ( Internal or User ) is more impacted.

 

Lab

Stored Procedure

dbo.sp_IdentifyQueriesWithMultipleQueryPlans

Intro

There is a Stored Procedure, dbo.sp_IdentifyQueriesWithMultipleQueryPlans, that we developed to identify queries that have multiple Query Plan entries.

We spoke about it here.

Ran it

We ran it a few times and noticed it was eating up Tempdb.

Metering

Code


use master
go

if object_id('[dbo].[sp_SessionResourceUptake]') is null
begin

	exec ('create procedure [dbo].[sp_SessionResourceUptake] as ')
end
go

alter procedure [dbo].[sp_SessionResourceUptake]
as

begin

	select
 
			  tblSP.[spid]
 
			, tblSP.[loginame]
 
			, tblSP.[hostname]
 
			, tblSP.[program_name]
 
			--, tblSS.memory_usage
 
			--, tblSS.[status]
 
			, tblSS.open_transaction_count
 
			, [sessionInternalObjectsAllocationInMB]
				= cast
					(
						(tblSBSSU.[internal_objects_alloc_page_count] *1.0 ) / ( 128)
						as decimal(30, 2)
					)
 
			, [sessionInternalObjectsDeAllocationInMB]
				= cast
					(
						(tblSBSSU.[internal_objects_dealloc_page_count] *1.0 ) / ( 128)
						as decimal(30, 2)
					)


			, [sessionUserObjectsAllocationInMB]
				= cast
					(
						 (tblSBSSU.user_objects_alloc_page_count *1.0 ) / ( 128)
						as decimal(30, 2)
					)
 
 
			, [sessionUserObjectsDeAllocationInMB]
				= cast
					(
						 (tblSBSSU.user_objects_dealloc_page_count *1.0 ) / ( 128)
						as decimal(30, 2)
					)
 
 
			, [taskInternalObjectsAllocationInMB]
				= cast
					(
						([tblSSBTSU].[internal_objects_alloc_page_count] *1.0 ) / ( 128)
						as decimal(30, 2)
					)
 
			, [taskInternalObjectsAllocationInGB]
				= cast
					(
						([tblSSBTSU].[internal_objects_alloc_page_count] *1.0 ) / ( 128 * 1000)
						as decimal(30, 2)
					)
 

			, tblSP.[cmd]
 
	from   [tempdb].[sys].[dm_db_session_space_usage] tblSBSSU
 
	inner join [tempdb].[sys].[dm_db_task_space_usage] tblSSBTSU
 
			on tblSBSSU.[session_id] = tblSSBTSU.[session_id]
 
	inner join [sys].[sysprocesses] tblSP
 
			on tblSBSSU.[session_id] = tblSP.[spid]
 
	inner join sys.dm_exec_sessions tblSS
 
			on tblSBSSU.[session_id] = tblSS.[session_id]

	where
 
		(
 
			(
				tblSBSSU.internal_objects_alloc_page_count
				+ tblSBSSU.user_objects_alloc_page_count
			) > 0
 
 
		)
 
	order by
 
			(
				tblSBSSU.internal_objects_alloc_page_count
				+ tblSBSSU.user_objects_alloc_page_count
			) desc


end
go

 

Clean up

Free Procedure Cache

DBCC FREEPROCCACHE with no_infomsgs;

 

Drop CleanBuffers

DBCC DROPCLEANBUFFERS with no_infomsgs;

 

 

Free System Cache

DBCC FREESYSTEMCACHE ('ALL') 
		WITH MARK_IN_USE_FOR_REMOVAL, no_infomsgs
		;  

 

Measuring

Each time we ran the  dbo.sp_IdentifyQueriesWithMultipleQueryPlans procedure, using [dbo].[sp_SessionResourceUptake] we measured the session’s memory uptake.

And, here is what we noticed.

 

Stage Session Internal Object Allocated Session Internal Object De-Allocated Session User Object Allocated Session User Object De-Allocated
After clearing cache
 Run – 01  93.75  93.75  21.31  2.25
 Run – 02  187.63  187.56  42.38  4.50
 Run – 03  281.38  281.31  63.44  6.75
 Run- 04  375.19  375.19  84.50  9.0
 Run – 05  468.94  468.88  105.56  11.25

 

 

Source Control

GitHub

DanielAdeniji/SQLServerIdentifyQueriesWithMultipleQueryPlans

Link

 

Connect Items

Opened a connect item this morning.

  1. Tempdb acquires memory yet GBs of unallocated memory is left unused
    Bug ID :- 3119422
    Date Opened :- 2017-Jan-23rd
    Link
  2. Steve Hood – TempDB holds excessive unallocated pages in memory
    Bug ID :- 2215297
    Date Opened :- 2016-Jan-7th
    Status :- Closed ( as not Reproducible )
    Link

 

Summary

As for internal objects they are being acquired and released.

On the other hand, user objects are being acquired at a quarter of the rate of Internal Objects.

But, unfortunately very little of the user objects are consequently released.

 

TempDB – Allocation & Deallocation Tracking – Internal & User Objects

Background

After quite  a long lapse, took this entire weekend to dig more into tempdb hyper growth.

We noticed that tempdb was using quite a bit of memory on a couple of boxes.

 

Earlier Work

  1. SQL Server – Query Plans with DesiredMemory
    Published :- 2016-12-02
    Link
  2. SQL Server – Memory Allocated/Unallocated per Database
    Published :- 2016-12-05
    Link
  3. SQL Server – Tempdb – v2012
    Published :- 2016-12-05
    Link

 

TroubleShooting

Instrumentation

Glossary

Here are the Dynamic Management Views (DMVs) that we will employ:

DMVInfo Information Link
 sys.dm_db_file_space_usage Returns space usage information for each file in the database.  Link
sys.dm_os_buffer_descriptions  Returns information about all the data pages that are currently in the SQL Server buffer pool. The output of this view can be used to determine the distribution of database pages in the buffer pool according to database, object, or type.  Link
sys.dm_db_session_space_usage Returns the number of pages allocated and deallocated by each session for the database.  Link
 sys.dm_db_task_space_usage  Returns page allocation and deallocation activity by task for the database.  Link

 

 

 

tempdb.sys.dm_db_file_space_usage

Code


set nocount on
go

use [tempdb]
go

select
 
          [runtime]
			= convert( varchar(30), getdate(), 100) 
 
         , [totalspaceMB]
			= ( SUM (total_page_count)*8 / 1024 ) 

         , [unallocatedSpaceMB]
			= ( SUM (unallocated_extent_page_count)*8 / 1024 ) 

        , [versionStoreMB]
			= ( SUM (version_store_reserved_page_count)*8  / 1024 )
 
        , [userObjectMB]
			= ( SUM (user_object_reserved_page_count)*8 / 1024 ) 
 
        , [internalObjectMB]
			= ( SUM (internal_object_reserved_page_count)*8 / 1024 )

        , [mixedextentMB]
			= ( SUM (mixed_extent_page_count)*8 / 1024 ) 
 
FROM [tempdb].[sys].[dm_db_file_space_usage]

 

Output

QA – 2017.01.22 9:14 PM

sys__dm_db_file_space_usage__20170122_0915pm

 

sys.dm_os_buffer_descriptions

Code


; with cte
( 
    [count]
)
as
(
  
    select [count] = count(*)
    FROM   sys.dm_os_buffer_descriptors
  
)
  
SELECT
        [Database]
            = case
                when (database_id = 32767) then 'ResourceDB'
                else DB_NAME(database_id)
              end  
  
        , [CachedSizeMB]
  
            = 
                cast
                    (
                        (
                            COUNT(*) * 8
                        ) 
                        / 
                        ( 
                            1024.0 * 1
                        )
                        as decimal (18, 2)
                    )
  
        , [CachedSizeGB]
            = 
                cast
                    (
                        (
                            COUNT(*) * 8
                        ) 
                        / 
                        ( 
                            1024.0 * 1000
                        )
                        as decimal (18, 2)
                    )
  
  
    
        , [UnusedSizeMB]
  
            = SUM 
				(
					CAST ([free_space_in_bytes] AS BIGINT)
				) 
				/ (1024 * 1024) 

        , [%]
            = cast
              (
                    (count(*) * 100.00)
                        / cte.[count] 
                    as decimal(10, 2)
              )
  
FROM sys.dm_os_buffer_descriptors
  
cross apply cte
  
GROUP BY
             case
                when (database_id = 32767) then 'ResourceDB'
                else DB_NAME(database_id)
             end   
 
            , [cte].[count]
  
ORDER BY
        count(*) desc
           
OPTION (MAXDOP 1, RECOMPILE)

Output

QA – 2017.01.22 9:56 PM

sys_dm_os_buffer_descriptors__20170122_0956pm

 

 

sys.dm_db_session_space_usage & sys.dm_db_task_space_usage

Code


use [tempdb]
go

select 

		  tblSP.[spid]

		, tblSP.[loginame]

		, tblSP.[hostname]

		, tblSP.[program_name]

		--, tblSS.memory_usage

		--, tblSS.[status]

		, tblSS.open_transaction_count

		, [sessionInternalObjectsAllocationInMB]
			= cast
				(
					(tblSBSSU.[internal_objects_alloc_page_count] *1.0 ) / ( 128)
					as decimal(30, 2)
				)

		, [sessionInternalObjectsDeAllocationInMB]
			= cast
				(
					(tblSBSSU.[internal_objects_dealloc_page_count] *1.0 ) / ( 128)
					as decimal(30, 2)
				)

/*
		, [sessionInternalObjectsAllocationInGB]
			= cast
				(
					(tblSBSSU.[internal_objects_alloc_page_count] *1.0 ) / ( 128 * 1000)
					as decimal(30, 2)
				)
*/

		, [sessionUserObjectsAllocationInMB]
			= cast
				(
					 (tblSBSSU.user_objects_alloc_page_count *1.0 ) / ( 128)
					as decimal(30, 2)
				)


		, [sessionUserObjectsDeAllocationInMB]
			= cast
				(
					 (tblSBSSU.user_objects_dealloc_page_count *1.0 ) / ( 128)
					as decimal(30, 2)
				)


		, [taskInternalObjectsAllocationInMB]
			= cast
				(
					([tblSSBTSU].[internal_objects_alloc_page_count] *1.0 ) / ( 128)
					as decimal(30, 2)
				)

		, [taskInternalObjectsAllocationInGB]
			= cast
				(
					([tblSSBTSU].[internal_objects_alloc_page_count] *1.0 ) / ( 128 * 1000)
					as decimal(30, 2)
				)

/*
		, [taskUserObjectsAllocationInMB]
			= cast
				(
					 ([tblSSBTSU].user_objects_alloc_page_count *1.0 ) / ( 128)
					as decimal(30, 2)
				)


		, [taskUserObjectsAllocationInGB]
			= cast
				(
					([tblSSBTSU].[user_objects_alloc_page_count] *1.0 ) / ( 128 * 1000)
						as decimal(30, 2)
				)

*/
		, tblSP.[cmd]

from   [tempdb].[sys].[dm_db_session_space_usage] tblSBSSU

inner join [tempdb].[sys].[dm_db_task_space_usage] tblSSBTSU

		on tblSBSSU.[session_id] = tblSSBTSU.[session_id]

inner join [sys].[sysprocesses] tblSP

		on tblSBSSU.[session_id] = tblSP.[spid]

inner join sys.dm_exec_sessions tblSS

		on tblSBSSU.[session_id] = tblSS.[session_id]

where

	(

		(
			tblSBSSU.internal_objects_alloc_page_count
			+ tblSBSSU.user_objects_alloc_page_count
		) > 0


	)

order by

		(
			tblSBSSU.internal_objects_alloc_page_count
			+ tblSBSSU.user_objects_alloc_page_count
		) desc



 

Output

QA – 2017.01.22 9:14 PM

sys__dm_db_session_space_usage__20170122_0933am

Explanation
  1. We see that sessions 61 & 63 are the highest users of tempdb
    • In this case they are using internal Objects as compared to user Objects
    • We also see that just about all the memory allocated are subsequent deallocated

 

Summary

In a nutshell, both tempdb.sys.dm_db_file_space_usage ( durable file allocation ) and  sys.dm_os_buffer_descriptors ( online memory ) have high values for tempdb uptake.

But,  sys.dm_db_session_space_usage & sys.dm_db_task_space_usage ( currently running session indicators ) agree with what we see in sys.dm_db_file_space_usage DMV that the storage is indeed unallocated.

It is just that SQL Server is reluctant to free up that space from memory and give is back to the system.

Admittedly, we got lucky here; lucky in the sense that the sessions that are using tempdb are still connected to the SQL Instance; otherwise, we will mistake them for system or phantom processes.

 

References

  1. Microsoft Developer
    • Arvind Shyamsundar
      • Tracking TEMPDB internal object space usage in SQL 2012
        Link
  2.  SqlSkills
    • Paul Randall
      • Performance issues from wasted buffer pool memory
        Link

 

SQL Server – Memory Allocated/Unallocated per Database

Background

Still on the trail of high TempDB Allocation in our Memory.

 

Glossary

Here is a quick outline of terms that we will cover.

DMV Description Note
 sys.dm_os_buffer_descriptors Returns information about all the data pages that are currently in the SQL Server buffer pool.  The output of this view can be used to determine the distribution of database pages in the buffer pool according to database, object, or type.
sys.allocation_units Contains a row for each allocation unit in the database When a data page is read from disk, the page is copied into the SQL Server buffer pool and cached for reuse. Each cached data page has one buffer descriptor. Buffer descriptors uniquely identify each data page that is currently cached in an instance of SQL Server. sys.dm_os_buffer_descriptors returns cached pages for all user and system databases.

 

 

Code


; with cte
(
	  [database_id]
	, [cnt]
	, [allocatedMB]
	, [isCleanMB]
	, [isDirtyMB]
	, [unallocatedMB]
	, [%unallocated]

)
as
(

	select
	          tblDOSBD.[database_id]

			, [cnt]
				= ( count(*))

			, [allocatedMB]
				= (
					sum
						(
							case
								when ( tblSAU.[allocation_unit_id] is not null ) then 1
								else 0
							end
						) * 8
				  )	
				  / 1024

			, [isCleanMB]
				= (
					sum
						(
							case
								when 
									( 
										    ( tblSAU.[allocation_unit_id] is not null ) 
										and ( tblDOSBD.is_modified = 0 )
									) then 1
								else 0
							end
						) * 8
				  )	
				  / 1024

			, [isDirtyMB]
				= (
					sum
						(
							case
								when 
									( 
										    ( tblSAU.[allocation_unit_id] is not null ) 
										and ( tblDOSBD.is_modified = 1 )
									) then 1
								else 0
							end
						) * 8
				  )	
				  / 1024
				  	
			, [unallocatedMB]
				= (
					sum
					(
						case
							when ( tblSAU.[allocation_unit_id] is null ) then 1
							else 0
						end
					) * 8

				  ) / 1024		

			, [%unallocated]
				= cast
					(
						sum
						(
							case
								when ( tblSAU.[allocation_unit_id] is null ) then 1
								else 0
							end
						) * 100.00
						/
						count(*)
						as decimal(10,2)
					)

	from  sys.dm_os_buffer_descriptors tblDOSBD

	left outer join sys.allocation_units tblSAU

		on tblDOSBD.allocation_unit_id = tblSAU.[allocation_unit_id]

	group by

		tblDOSBD.[database_id]
)
select 

	  [database]
		= case
				when ( cte.database_id = 32767) then 'Resource DB'
				else db_name(cte.[database_id])
          end

	, cte.[allocatedMB]

	, cte.[isCleanMB]
	, cte.[isDirtyMB]	
	
	, cte.[unallocatedMB]

	, cte.[%unallocated]

from   cte

order by
      	 cte.[unallocatedMB] desc

 

Output

memroyallocatedandunallocated-ayso-prod-20161205_1037am

 

Summary

In follow-up postings we will delve more into why we have such a high percentile for unallocated data compared to Allocated data.

Again, keep in mind that unallocated seems to mean that the data is in memory, but corresponding entries do not seem to be present on disk.

 

SQL Server – Determine Number of CPU Cores

Background

Ran SQL Server Best Practices and one of the flagged items is that TempDB might not be properly configured.

Here is the error.

Textual

Category: Performance

Issue: This instance of SQL Server has only one tempdb data file
Processor Count: 4, TempDB datafiles Configured: 1

Impact: Under a heavy load, the tempdb database can become a single point of contention and affect concurrency and performance

Resolution: To reduce contention in the tempdb database, increase the number of tempdb data files, and configure the required startup trace flags.

Image

sqlservertempdbdatabasenotconfiguredoptimally

Review

Let us ensure that we actually have that # of Processor Cores.

SQL Server

sys.dm_os_schedulers

Code


select 
		  tblOS.scheduler_id
		, tblOS.cpu_id
		, tblOS.[status]
		, tblOS.[is_online]

from   sys.dm_os_schedulers tblOS

where  tblOS.[status] = 'VISIBLE ONLINE'

 

Output

sys-dm_os_schedulers

Explanation

We have four schedulers

 

Windows Internals

Task Manager

Steps

  1. Launch Task Manager
  2. Access the Performance Tab
  3. Click on CPU radio button

 

perfomance-cpu

Explanation

  1. Read the “Virtual processors” and “Virtual Machines

 

WMI

Steps


WMIC CPU Get DeviceID,NumberOfCores,NumberOfLogicalProcessors

Output

wmic-cpu

SysInternals

Process Explorer

If you do not have a more recent edition of SysInternal’s Process Explorer, please download it from here.

BTW, as of this post, that version is 16.12.

Steps

  1. Launch Process Explorer
  2. Click on the menu items – View \ System Information…
  3. Click on CPU Tab

 

systeminformation-cpu

 

Explanation

  1. Read the Cores and Sockets
  2. BTW, on a busy system it might be instructive to make sure that “Show one graph per CPU” checkbox is checked

 

Coreinfo

Coreinfo is available here.

BTW, as of this post, that version is 3.31.

Steps

  1. It is Command Line Tool
  2. And, so launch a Command Shell
  3. Syntax
    • To get Processor\Core Info issue “coreinfo -c
    • To get Virtualization Info issue “coreinfo -v

Command Syntax – Processor/Core Info

Code

coreinfo -c

Output

coreinfo

 

Code

coreinfo -v

Output

hypervisor

 

CPUID

CPU-Z

CPUID’s CPU-Z is available here.

 

cpz-processor

Explanation

  1. It is interesting that CPUID/CPU-Z
    • Has Number of Cores as 1
    • And, the Processor Count is 4
  2. We are good for CPU Speed
    • Intel Xeon CPU E7-2870 @ 2.40GHz
    • Core Speed => 2376.25 MHz
      • It is likely that Power Savings is not enabled

Summary

Again, CPU-Z is giving us Number of Cores at 1.

Not sure if it is because it is a virtualized environment.

Nothing new here, but it is always good to base Engineering decisions on collaborative data.

 

References

  1. Tempdb Configuration
    • Recommendations to reduce allocation contention in SQL Server tempdb database
      Link

 

 

SQL Server – Tempdb – Management – Moving Files to another Drive

Background

Based on Storage Analysis, we determined that moving TempDB to a separate disk will likely yield performance gains.

Analysis

Please review “SQL Server – What is in your TempDB?“, here, to review TempDB utilization.

It is important to do so as:

  1. You will only be able to shrink to the extent that the Storage is not in active usage

 

Outline

Here are the steps that we will take:

  1. Review the current utilization size, content, and usage pattern of your TempDB
  2. Allocate new temp data and log files on your new Physical Drive
  3. Free up system resources and user sessions that are currently using Temp-db resources
  4. Disable auto-growth on original Temp-DB data and log files
  5. Shrink original Temp-DB data and log files
  6. Review new sizes on original files
  7. If shrinkage targets are not met, repeat Temp-db usage review and shrinkage

Code

Code – Add New Files


USE [master];
GO

ALTER DATABASE [tempdb] 
ADD FILE 
(
	  NAME = N'tempdev2'
	, FILENAME = N'G:\Microsoft\SQLServer\tempdb\tempdb_data_2.ndf' 
	, SIZE = 8GB 
	, FILEGROWTH = 200MB
);

ALTER DATABASE [tempdb] 
ADD LOG FILE
(
	  NAME = N'tempdbLog2'
	, FILENAME = N'G:\Microsoft\SQLServer\tempdb\tempdb_log_2.ldf' 
	, SIZE = 8GB 
	, FILEGROWTH = 200MB
);

Shrink TempDB Data File

Code – Free up TempDB Usage

Procedure & System Cache


DBCC FREEPROCCACHE -- clean cache
DBCC FREESYSTEMCACHE ('ALL') -- clean system cache
DBCC FREESESSIONCACHE -- clean session cache
Go

Checkpoint
Go


Disable Tempdb data file growth


ALTER DATABASE [tempdb]
MODIFY FILE
(
	  NAME = 'templog'
	, FILEGROWTH = 0MB
)
	

Shrink Tempdb data files


dbcc shrinkfile (tempdev, 1)
go

Shrink TempDB Log File

Code – Disable TempDB Log file growth


   ALTER DATABASE [tempdb]
   MODIFY FILE
   (
        NAME = 'templog'
      , FILEGROWTH = 0MB
   )

 

Code – Shrink TempDB Log File


   dbcc shrinkfile (templog, 1)
   go

 

Code – Get File Stats


Select  
		  [name]

		, physical_name

		, [SizeInMB]
			= [size] / 128.0 

                , [UsedInMB]
			= Fileproperty(name, 'SpaceUsed') / 128.0 

		, [AvailableSpaceInMB]
			= (size - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int))
				/128.0 

               , PercentFull
			= Cast((Fileproperty(name, 'SpaceUsed') * 100.0) / size As Int)

	       , is_percent_growth

	       , [growth]
			= case
					when is_percent_growth=0 then
						cast(	sd.growth/256 as varchar(30)) + ' MB'
					else cast(sd.growth as varchar(30)) + '%'
			  end	

from sys.master_files sd

where  sd.database_id = db_id('tempdb')


 

Output

postMoveFileStats

 

Instrumentation

Resource Monitor

ResourceMonitor-20160506-0404PM

Explanation

Here we see that tempdb usage …

  • is distributed among our new allocation on drive (G:)
  • And, the original location on Drive E:

 

Summary

Shrinking TempDB Log files is a lot easier.

On the other hand, TempDB data files are a lot more difficult to shrink.

The reasons are multiplex:

  1. Objects that are currently utilizing the data files will likely not be moved until their contents are freed.
    • This is more likely with user tables
    • System and internal tables stay on, until the SQL Instance is restarted

References

  1. Monitoring tempdb Transactions and Space usage
    https://blogs.msdn.microsoft.com/deepakbi/2010/04/13/monitoring-tempdb-transactions-and-space-usage/