Server Side SQL Tracing

this is a sample script I used for capturing SQL trace on SQL 2000. higher versions have similliar statements and methods.



create procedure usp_PerfTrace @stoptime datetime = null
as
begin
declare @traceId int, @ret int
declare @tOn bit
--,@stoptime datetime,
declare @fileSize bigint,@tFile nvarchar(128)
set @tOn=1
--set @stoptime='2012-06-01 01:00'
if (@stoptime is null)
set @stoptime=dateadd(hh,1,getdate())
set @fileSize = 5
set @tFile = N'c:\shared\SQLTrace\perfTrace'+convert(varchar,getdate(),102)+'.'+convert(varchar,datepart(hh,getdate()))+convert(varchar,datepart(mm,getdate()))
--set trace file rollover after 5mb
--select N'c:\shared\SQLTrace\perfTrace'+convert(varchar,getdate(),102)+'.'+convert(varchar,datepart(hh,getdate()))+convert(varchar,datepart(mm,getdate()))

exec @ret=sp_trace_create @traceid =  @traceId OUTPUT  , @options =  2 ,
@tracefile = @tFile ,@maxfilesize=@fileSize, @stoptime=@stoptime

SELECT RET=@ret, TraceID = @traceId
exec sp_trace_setevent @traceId, 10, 1, @tOn
exec sp_trace_setevent @traceId, 10, 2, @tOn
exec sp_trace_setevent @traceId, 10, 3, @tOn
exec sp_trace_setevent @traceId, 10, 12, @tOn
exec sp_trace_setevent @traceId, 10, 13, @tOn
exec sp_trace_setevent @traceId, 10, 14, @tOn
exec sp_trace_setevent @traceId, 10, 15, @tOn
exec sp_trace_setevent @traceId, 10, 16, @tOn
exec sp_trace_setevent @traceId, 10, 17, @tOn
exec sp_trace_setevent @traceId, 10, 18, @tOn
exec sp_trace_setevent @traceId, 10, 27, @tOn
exec sp_trace_setevent @traceId, 12, 1, @tOn
exec sp_trace_setevent @traceId, 12, 2, @tOn
exec sp_trace_setevent @traceId, 12, 3, @tOn
exec sp_trace_setevent @traceId, 12, 12, @tOn
exec sp_trace_setevent @traceId, 12, 13, @tOn
exec sp_trace_setevent @traceId, 12, 14, @tOn
exec sp_trace_setevent @traceId, 12, 15, @tOn
exec sp_trace_setevent @traceId, 12, 16, @tOn
exec sp_trace_setevent @traceId, 12, 17, @tOn
exec sp_trace_setevent @traceId, 12, 18, @tOn
exec sp_trace_setevent @traceId, 12, 27, @tOn
exec sp_trace_setevent @traceId, 41, 1, @tOn
exec sp_trace_setevent @traceId, 41, 2, @tOn
exec sp_trace_setevent @traceId, 41, 3, @tOn
exec sp_trace_setevent @traceId, 41, 12, @tOn
exec sp_trace_setevent @traceId, 41, 13, @tOn
exec sp_trace_setevent @traceId, 41, 14, @tOn
exec sp_trace_setevent @traceId, 41, 15, @tOn
exec sp_trace_setevent @traceId, 41, 16, @tOn
exec sp_trace_setevent @traceId, 41, 17, @tOn
exec sp_trace_setevent @traceId, 41, 18, @tOn
exec sp_trace_setevent @traceId, 41, 27, @tOn
exec sp_trace_setevent @traceId, 43, 1, @tOn
exec sp_trace_setevent @traceId, 43, 2, @tOn
exec sp_trace_setevent @traceId, 43, 3, @tOn
exec sp_trace_setevent @traceId, 43, 12, @tOn
exec sp_trace_setevent @traceId, 43, 13, @tOn
exec sp_trace_setevent @traceId, 43, 14, @tOn
exec sp_trace_setevent @traceId, 43, 15, @tOn
exec sp_trace_setevent @traceId, 43, 16, @tOn
exec sp_trace_setevent @traceId, 43, 17, @tOn
exec sp_trace_setevent @traceId, 43, 18, @tOn
exec sp_trace_setevent @traceId, 43, 27, @tOn
exec sp_trace_setevent @traceId, 45, 1, @tOn
exec sp_trace_setevent @traceId, 45, 2, @tOn
exec sp_trace_setevent @traceId, 45, 3, @tOn
exec sp_trace_setevent @traceId, 45, 12, @tOn
exec sp_trace_setevent @traceId, 45, 13, @tOn
exec sp_trace_setevent @traceId, 45, 14, @tOn
exec sp_trace_setevent @traceId, 45, 15, @tOn
exec sp_trace_setevent @traceId, 45, 16, @tOn
exec sp_trace_setevent @traceId, 45, 17, @tOn
exec sp_trace_setevent @traceId, 45, 18, @tOn
exec sp_trace_setevent @traceId, 45, 27, @tOn

--column 13=duration, comparison 4= ">=", 1 is 1 milisecond
declare @value bigint
select @value=1
exec sp_trace_setfilter @traceid = @traceId, @columnid=13,@logical_operator= 0, @comparison_operator =4, @value=@value
--0 is equal
exec sp_trace_setfilter @traceid = @traceId, @columnid=3,@logical_operator= 0, @comparison_operator =0, @value=7
--no like  sql profiler
exec sp_trace_setfilter @traceId, 10, 0, 7, N'SQL Profiler'

--satus: 0 stop,1 start, 2 close and delete its definition from server
exec sp_trace_setstatus @traceId, 1

end
exec usp_PerfTrace

--exec sp_trace_setstatus 2, 0
--exec sp_trace_setstatus 2, 2
--go
--exec sp_trace_getdata @traceId, 0
--go

in SQL 2000, these functions can be used to return trace information.

SELECT * FROM ::fn_trace_getinfo(default)

SELECT * into xxx200 FROM ::fn_trace_gettable('c:\shared\SQLTrace\Stress20010301100.trc', default)

in SQL 2008, fn_get_tabledata, fn_trace_getinfo can be used instead.

Finding out unused indexes

Check the readings according to dm_db_index_usage_stats.

WITH indexstats ([Table],[Index],[Reads],[Writes],[Rows])
AS ( SELECT usr.[name] + '.' + obj.[name] [Table],
ixs.[name] [Index] ,
usage.user_seeks + usage.user_scans + usage.user_lookups [Reads],
usage.[user_updates] [Writes],
(SELECT SUM(sp.[rows]) FROM sys.partitions sp
WHERE usage.OBJECT_ID = sp.object_id
AND sp.index_id = usage.index_id) [Rows]
FROM sys.dm_db_index_usage_stats usage
INNER JOIN sys.indexes ixs
ON usage.[object_id] = ixs.[object_id]
AND ixs.[index_id] = usage.[index_id]
INNER JOIN sys.objects obj ON usage.[object_id] =
obj.[object_id] INNER JOIN sys.sysusers usr ON obj.[schema_id] = usr.[uid]
WHERE usage.database_id = DB_ID()
AND usage.index_id > 0
AND OBJECTPROPERTY(usage.[object_id], 'IsUserTable') = 1 )
SELECT * FROM indexstats WHERE Reads = 0 ORDER BY [Rows] DESC, [Index]

Defragment

1. determin the level of fragment
(http://www.sql-server-performance.com/2006/detect-fragmentation-sql2000-sql2005/)
1.1 sql 2000
DBCC SHOWCONTIG
The fragmentation level of an index can be determined in two ways:
--Comparing the values of Extent Switches and Extents Scanned.
The value of Extent Switches should be as close as possible to that of Extents Scanned. This ratio is calculated as the Scan Density value, which should be as high as possible.

--Understanding Logical Scan Fragmentation and Extent Scan Fragmentation values.
Logical Scan Fragmentation and, to a lesser extent, Extent Scan Fragmentation values give the best indication of a table's fragmentation level. Both these values should be as close to zero as possible (although a value from 0% through 10% may be acceptable).

1.2 2005 an dabove
sys.dm_db_index_physical_stats

--list all indexes and their fragment level
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'xxx_db'), OBJECT_ID(N'xxxtable'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id
AND a.index_id = b.index_id

2. defragment
steal content from http://www.mssqltips.com/sqlservertip/1018/index-rebuilds-in-sql-server-2000-vs-sql-server-2005/

2.1 2000

CREATE INDEX with DROP_EXISTING - Creates a new index with the same name and drops the current index while ensuring the nonclustered indexes are not rebuilt twice.
CREATE CLUSTERED INDEX au_id_clidxON Authors (au_id) WITH DROP_EXISTINGGO

DROP INDEX and CREATE INDEX - Removes the au_id_ind index on the authors table.
DROP INDEX authors.au_id_indGO
CREATE INDEX au_id_indON Authors (au_id ASC) GO

DBCC DBREINDEX - Rebuild all of the indexes on the authors table with 80% fill factor.
DBCC DBREINDEX (authors, '', 80)GO

DBCC INDEXDEFRAG - Defragments the au_id_ind index on the Authors table.
DBCC INDEXDEFRAG (Pubs, Authors, au_id_ind)GO

2.2 2005 and above

CREATE INDEX with DROP_EXISTING - Creates a new index with the same name and drops the current index while ensuring the nonclustered indexes are not rebuilt twice.
CREATE CLUSTERED INDEX au_id_clidxON dbo.Authors (au_id) WITH (DROP_EXISTING = ON);GO

DROP INDEX and CREATE INDEX - Removes the au_id_ind index on the authors table, which is the equal functionality as SQL Server 2000.
DROP INDEX authors.au_id_ind;GO
CREATE INDEX au_id_indON Authors (au_id ASC); GO

ALTER INDEX - Rebuild all of the indexes on the Authors table with 80% fill factor, sort the intermediary data in TempDB and automatic updating of the statistics are enabled.
ALTER INDEX ALL ON AuthorsREBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF);GO


ALTER INDEX - Defragment the au_id_ind index on the Authors table which is intended to be a truly online operation.
ALTER INDEX au_id_ind ON dbo.Authors REORGANIZE; GO

SQL Server System Views

they expose database metadata.

Catelog views:

Catalog views return information that is used by the SQL Server Database Engine. All user-available catalog metadata is exposed through catalog views.

some examples:

sys.databases, sys.database_files,sys.master_files

sys.linked_logins, sys.remote_logins,sys.servers

sys.objects, sys.tables, sys.indexes,sys.views, sys.procedures, sys.partitions,sys.schemas

Dynamic Management views and functions:

Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. Dynamic management views and functions in future releases may not be compatible with the dynamic management views and functions in this release.

All dynamic management views and functions exist in the sys schema and follow this naming convention dm_*. you must prefix the name of the view or function by using the sys schema.

some examples:

sys.dm_exec_connections, sys.dm_exec_sessions, sys.dm_exec_sql_text

sys.dm_tran_database_transactions

Compatibility views:

Many of the system tables from earlier releases of SQL Server are now implemented as a set of views. These views are known as compatibility views, and they are meant for backward compatibility only. The compatibility views expose the same metadata that was available in SQL Server 2000. However, the compatibility views do not expose any of the metadata related to features that are introduced in SQL Server 2005 and later. Therefore, when you use new features, such as Service Broker or partitioning, you must switch to using the catalog views.

some examples:

you can use sys. prefix in front of the sysxxx.

sysusers, sysprocesses, sysobjects,syscolumns, sysdatabases, sysservers

Replication Views:

views are available in different related databases: msdb, distribution, publication, subscription databases.

Information Schema Views:

One of several methods SQL Server provides for obtaining metadata. it comply with ISO standard definition of INFORMATION_SCHEMA. Good for generic purpose DB tools to use.

Information schema views are defined in a special schema named INFORMATION_SCHEMA. This schema is contained in each database. Each information schema view contains metadata for all data objects stored in that particular database

End a database restoration process

following all those with norecovery, issue an statement like this to put database back to operational.

restore database xxx with recovery

SQL Mirroring

comprehensive explanation:

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

Because database mirroring is so fast, it is much more suitable for keeping a hot standby of a publisher database.

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

Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record. Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions

There are two mirroring operating modes:

high-safety mode, high-performance mode

One of them, high-safety mode supports synchronous operation. Under high-safety mode, when a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible. As soon as the databases are synchronized, a transaction is committed on both partners, at the cost of increased transaction latency.

The second operating mode, high-performance mode, runs asynchronously. The mirror server tries to keep up with the log records sent by the principal server. The mirror database might lag somewhat behind the principal database. However, typically, the gap between the databases is small. However, the gap can become significant if the principal server is under a heavy work load or the system of the mirror server is overloaded.

1. make full backup, as well as a log backup

2. restore mirror database with norecovery on both database and logs

3. if sql server runs under network service, it need to create login for machine

create login [domain\machine$] from windows with default_database=[a database, maybe master]

later, after an endpoint was created, connect privilege should be granted

grant connect on endpoint::endpointname to [domain\machine$]

If windows are in domain, it'd be easier to just run under same domain user, create

login in sql server for the domain user, and grant connect on endpoint to it.

4. on principle db, you can set up mirroring with wizard. if it's successful, the status should be

fully synchronized. Extra information will also be added in database name in the management studio, such as (Principle, Synchronized)

5. it can also be configured with scripts. here are some statements for reference.

restore database xxx from disk='xxx' with norecovery

restore log xxx form disk='xxx' with norecovery

create endpoint xxx state=started as tcp(listener_port=xxxx, listener_ip=all or xxx)

for database_mirroring (role=parnter|all)

create login [xxx] xxx

grant connect on endpoint:xxx to [xxx]

alter database xxx set partner='tcp://xxx.xxx.xxx:port'

--turn off mirroring

alter database xxx set partner off

drop endpoint mirroring

--check status

select * from sys.database_mirroring_endpoints

select * from sys.database_mirroring

OR USE DATABASE MIRRORING MONITOR which requires host name instead of ip

manual fail over

On the former principal, clients are disconnected from the database and in-flight transactions are rolled back.

1. using SSMS

2. using t-sql

use master

go

ALTER DATABASE database_name SET PARTNER FAILOVER

where database_name is the mirrored database.

Client side redirect

example:

"Data Source=A;Failover Partner=B;Initial Catalog=AdventureWorks;Integrated Security=True;"

Force service

ALTER DATABASE  SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Knowledge point to prepare

1. backup recovery (syntax and scenario based) 2. High availability (log shipping, replication, mirroring, clustering) 3. Security(authentication modes, impersonating) 4. Architecture bases theoretical questions 5. transaction log based questions (u need to know how transaction log works) 6. tempdb (its purpose) 7. Installation (troubleshooting) 8. various editions 9. upgrade(troubleshooting/precautions) 10. Migration (login/job scripting/precautions)