Tempdb in SQL Server 2005

Working with tempdb in SQL Server 2005 :
http://technet.microsoft.com/en-au/library/cc966545.aspx


Optimizing tempdb Performance:
http://technet.microsoft.com/en-us/library/ms175527.aspx


Capacity Planning for tempdb:
http://technet.microsoft.com/en-us/library/ms345368.aspx

Below are five of the most common tempdb

http://sqlserverpedia.com/blog/sql-server-bloggers/5-common-mistakes-with-tempdb/

Physical Database Storage Design

http://technet.microsoft.com/en-au/library/cc966414.aspx

Update Statistics Before or After an Index Rebuild

http://benchmarkitconsulting.com/colin-stasiuk/2009/02/11/update-statistics-before-or-after-an-index-rebuild/


http://blog.sqlauthority.com/2010/01/25/sql-server-find-statistics-update-date-update-statistics/


Show Statistics:

DBCC SHOW_STATISTICS ('Table_Name', Index_Name) WITH STAT_HEADER


Update Table Statistics:

Update Statistics dbo.aou_order with FULLSCAN


Rebuild Index offline:

Standard Edition only supports offline,
Enterprise Edition supports online and offline rebuild.

ALTER INDEX PK_aou_order ON dbo.aou_order REBUILD with (online=off)


SQL 2000 Rebuild Index method:

DBCC DBREINDEX ("aou_order", PK_aou_order);


Check Indexes fragmentation of Tables using DBCC :


DBCC SHOWCONTIG ('aou_order')

Check Indexes fragmentation of Tables using DMs:

USE uwscal
GO
SELECT object_id, index_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID('uwscal'), OBJECT_ID('aou_order'), NULL, NULL, NULL);


SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID('uwscal'), NULL, NULL, NULL , NULL);

sys.dm_os_sys_info

X64-bit processors use the stack size 2 MB per stack and the Lock pages in memory option is available to reduce paging of buffer pool memory as per KB918483.

X32-bit processors use the stack size 0.5 MB per stack



select *
from sys.dm_os_sys_info


SELECT cpu_count AS [Logical CPUs]
,cpu_count / hyperthread_ratio AS [Physical CPUs]
--,cpu_count
--,hyperthread_ratio
,physical_memory_in_bytes / 1048576 AS 'mem_MB'
,virtual_memory_in_bytes / 1048576 AS 'virtual_mem_MB'
,max_workers_count
,os_error_mode
,os_priority_class
FROM sys.dm_os_sys_info

Black box Trace in 2005

use master
go
-- Create a procedure
-- to call sp_trace_create
create procedure StartBlackBoxTrace
as
declare @tid int
exec sp_trace_create @tid output, 8
exec sp_trace_setstatus @tid, 1
go
-- Call it now to start
-- the black box trace



exec StartBlackBoxTrace
go



-- Mark the procedure for execution
-- at SQL Server startup
exec sp_procoption 'StartBlackBoxTrace',
'startup', true
go



SELECT * FROM fn_trace_getinfo (2);

This returns the following:
traceid property value
----------- ----------- ---------------------------------------------------------------------------------
2 1 8
2 2 \\?\C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\blackbox_1.trc
2 3 5
2 4 NULL
2 5 0

The various properties are:
1: the trace options. 8 means its a black-box trace.
2: the filename of the trace file. This isn't configurable for a black-box trace.
3: the maximum size of the file in MB. This also isn't configurable for a black-box trace.
4: the stop time for the trace. NULL means the trace won't stop until its manually turned off.
5: the current trace status. 0 is off, 1 is on.


Notice that the trace status is 0, which means that the trace hasn't been started - so we need to start it manually using:

EXEC sp_trace_setstatus @traceid = 2, @status = 1;




SELECT * FROM fn_trace_gettable (
'H:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\blackbox.trc',
DEFAULT);
GO


http://blogs.msdn.com/sqlserverstorageengine/archive/2007/06/03/sql-server-s-black-box.aspx


A SQL Server 2000 & 2005 black box trace used to capture the following events:
1) Stored Procedure Execution (RPC:Starting),
2) T-SQL Batch Execution (SQL:BatchStarting), and
3) Errors and Warnings (Attention and Exception)

And for those events, the information that was captured was:
1) Date and Time of execution,
2) The name of the application, the name of the database, the name of the server/workstation from where the call originated and the name of the user that executed the query/procedure
3) The query and the error that occurred.