— Understand the performance of SQL server at macro level
— use sys.dm_os_performance_counters

Counters: sample counters that can be used are:
MSSQL:SQL Errors
MSSQL:SQL Statistics
MSSQL:Locks
MSSQL:Memory Manager
MSSQL:General Statistics
MSSQL:Transactions
MSSQL:Wait Statistics
etc.,

Now the limitation is that all above counters are specific to SQL Server. If you still want to capture relating to disk or network – like physical disk, network interface card, etc then use performance monitor.

DATABASEPROPERTYEX – is for database properties
INDEXPROPERTY – for index properties

– Helps in finding fill factor, clustered index column, identity column, statistics updated or not etc.,

[cc lang=”SQL”]
SELECT sysobjects.name,
sysindexes.name,
INDEXPROPERTY(OBJECT_ID(sysobjects.name),sysindexes.name,’IndexFillFactor’)
FROM sysobjects INNER JOIN
sysindexes ON sysobjects.id = sysindexes.id
WHERE xtype = ‘U’
[/cc]

System catalogs helps in getting a lot of information about the server, DBs etc.,

sys.databases – Fetch info like name, owner, creation date

sys.database_files or sys.master_files – file names, size, location, type (database or log), etc

dm_io_virtual_file_stats – SELECT * FROM sys.dm_io_virtual_file_stats(NULL, NULL)

return information on IO statistics on database files.

sp_databases – Database meta data

sp_helpdb or sp_helpdb ‘AdventureWorks’

sp_changedbowner sa – change DB owner

SELECT DB_NAME(1) – returns master, model, tempdb or resource on keep changing the names.

SELECT DB_ID(‘master’) – returns DB ID for the database

SELECT DATABASEPROPERTYEX(‘master’, ‘Status’); – returns the status id of 0 or 1 the master database indicating online or offline status

Deadlock happens when 2 processes are trying to update a resource in reverse logical order.

SQL Server then decides based on the cpu/memory etc., consumed and chooses one as deadlock victim.

HOW TO RESOLVE DEADLOCKS

— enable trace flag to identify deadlocks to avoid in future

DBCC TRACEON (1204)

It is written to SQL server error log when a deadlock does happen.

Once you get SPID information from the errorlog, find for the spid from sysprocesses table

[cc lang=”SQL”]
SELECT id, name FROM sysobjects WHERE xtype = ‘U’
[/cc]

or use
select object_name (spid number from above)

— Then we get information about the tables involved in the deadlock.
— But still struck with knowing which SQL statements caused it.

To find it, we need to do server side trace.

— Capture the events Lock Events Lock:Deadlock and Lock:Deadlock Chain
— Capture object ID data column

– SQL Profiler helps in troubleshooting SQL performance problems by collecting metrics about the statements run on SQL Server

– Since it is run on server, running from our local client machines will bring the SQL Server, so DONT EVER RUN SQL PROFILER FROM YOUR LOCAL CLIENT MACHINES but instead use server side trace as discussed below.

Server side tracing

— similar functionality as is done from client ssms

Get a list of all events you wish to track from http://msdn.microsoft.com/en-us/library/ms186265.aspx.

The events are like

SQL:StmtCompleted – TSQL completed
SQL:BatchCompleted – Occurs when a Transact-SQL batch has completed
Lock:Deadlock – in a deadlock scenario
Lock:Timeout. SP:Recompile – SP recompile
Scan:Started – table scan
Lock:Escalation – fine lock getting converted to coarse-grain lock; like page lock escalating to table level lock
Sort Warnings: sort operations not fitting in memory and probably spilled to tempDB
Deadlock Graph : when a deadlock victim is choosen and terminated

For all of these events, we need to then select the columns that can be added

TextData, BinaryData, SPID, Duration, StartTime, EndTime,
Reads, Writes, CPU

It is all done using the TSQL statement – sp_trace_setevent
But the user need to have ALTER TRACE permission.

sample TSQL trace file:
[cc lang=”SQL”]
/***********************************************/
/* Server Side Trace */
/***********************************************/
— Declare variables
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxFileSize bigint
DECLARE @fileName NVARCHAR(128)
DECLARE @on bit

— Set values
SET @maxFileSize = 5
SET @fileName = N’C:\TestTrace’
SET @on = 1

— Create trace
EXEC @rc = sp_trace_create @TraceID output, 0, @fileName, @maxFileSize, NULL

— If error end process
IF (@rc != 0) GOTO error

— Set the events and data to collect
EXEC sp_trace_setevent @TraceID, 41, 1, @on
EXEC sp_trace_setevent @TraceID, 41, 12, @on
EXEC sp_trace_setevent @TraceID, 41, 13, @on
EXEC sp_trace_setevent @TraceID, 41, 14, @on
EXEC sp_trace_setevent @TraceID, 41, 15, @on
EXEC sp_trace_setevent @TraceID, 41, 16, @on
EXEC sp_trace_setevent @TraceID, 41, 17, @on

— Set Filters
— filter1 include databaseId = 6
EXEC sp_trace_setfilter @TraceID, 3, 1, 0, 6
— filter2 exclude application SQL Profiler
EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N’SQL Profiler’

— Start the trace
EXEC sp_trace_setstatus @TraceID, 1

— display trace id for future references
SELECT TraceID=@TraceID
GOTO finish

— error trap
error:
SELECT ErrorCode=@rc

— exit
finish:
GO[/cc]

To summarize these are 4 events which will happen for setting up a new trace on SQL server
1. sp_trace_create – create the trace – file name, size and time to stop the trace etc., are given
2. sp_trace_setevent – decide event to trace and column that will need be captured
3. sp_trace_setfilter – add filters to the trace i.e include or exclude certain DBs or tables etc.,
4. sp_trace_setstatus – start/stop/close the trace

How to check trace results

[cc lang=”SQL”]
SELECT * FROM ::fn_trace_gettable(‘traceFileName’, DEFAULT)
[/cc]