Friday, February 19, 2010

Tempdb in SQL Server 2005

Working with tempdb in SQL Server 2005 :

Optimizing tempdb Performance:

Capacity Planning for tempdb:

Below are five of the most common tempdb

Physical Database Storage Design

Update Statistics Before or After an Index Rebuild

Show Statistics:


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
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);

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

Thursday, February 18, 2010


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]
,physical_memory_in_bytes / 1048576 AS 'mem_MB'
,virtual_memory_in_bytes / 1048576 AS 'virtual_mem_MB'
FROM sys.dm_os_sys_info

Monday, February 15, 2010

Black box Trace in 2005

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

exec StartBlackBoxTrace

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

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',

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.