Category Archives: Tuning

DBCC TRACEON

Posted on by
Reply

There are 2 types of DBCC TRACEON (session or global). Session trace is turned on that session only and global trace is visible for all connections on the server.

Trace#: 3502 – This trace writes information to SQL Server log every time a check point occurs
SQLServer 2008 R2 trace flags
SQLServer 2005 trace flags

Example:
-- Turn on trace so SQLServer writes message to SQL Server log everytime check point occurs
DBCC TRACEON(3502, -1)

-- Display which traces are turned on and is it global or session based
DBCC TRACESTATUS(-1)

-- Turn off trace 3502
DBCC TRACEOFF(3502, -1)

Messages recorded in SQLServer log:
2011-03-26 17:28:24.840 spid52 DBCC TRACEON 3502, server process ID (SPID) 52. This is an informational message only; no user action is required.
2011-03-26 17:30:43.300 spid11s Ckpt dbid 5 started (8)
2011-03-26 17:30:43.300 spid11s About to log Checkpoint begin.
2011-03-26 17:30:44.320 spid11s Ckpt dbid 5 phase 1 ended (8)
2011-03-26 17:30:44.320 spid11s About to log Checkpoint end.
2011-03-26 17:30:44.330 spid11s Ckpt dbid 5 complete

Advertisements

How to execution plan on SQLServer

Posted on by
Reply

In SQLServer Managment Studio to the execution plan one can use one of the following SET commands

“SET SHOWPLAN_XML ON” – SQLServer will not execute the SQL and it will return execution plan and has estimated row counts and stats in form of a XML document. The output displayed in SQL Server management studio can be stored with .sqlplan extension using “Save Results As..” which will allow show you view execution plan of the SQL in SQL Server Managment Studio. To turn off this option by “”SET SHOWPLAN_XML OFF”
“SET SHOWPLAN_TEXT ON” – SQLServer will not execute the query, not include estimated row counts and statistics.
“SET SHOWPLAN_ALL ON” – SQLServer will not execute the query, it will include estimated row counts and statistics
“SET STATISTICS PROFILE ON” – SQLServer will execute the query and include estimated rows counts and statistics and actual row counts and statistics.
“SET STATISTICS XML ON” – SQLSever will execute the query and include estimated rows counts and statistics and actual row counts and statistics.

SQL Statement in cache, it’s execution time

Posted on by
Reply

— Display SQL statement (SQLServer 2005 and SQLServer 2008), it displays creation time, last exeuction time, max time it ran for in microseconds, average execution time, # of times sql statement executed

SELECT j.plan_handle
, sqltext
, deqs.creation_time
, deqs.last_execution_time
, deqs.
, deqs.total_worker_time/deqs.execution_count avg_exec_time
, deqs.execution_count
FROM (SELECT decp.plan_handle, st.text sqltext
FROM sys.dm_exec_cached_plans decp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st) j,
sys.dm_exec_query_stats deqs
WHERE deqs.plan_handle = j.plan_handle

Output:
Plan handle sqltext creation_time last_execution_time max_worker_time avg_exec_time execution_count
—————— ———— ——————— ————————– ———————— ——————— ———————-
0x060001006F639935B8C1E480000000000000000000000000 select * from sys.dm_exec_cached_plans 2010-11-15 21:30:28.183 2010-11-15 21:35:16.700 35 32 4
…………

List locks held by a session

Posted on by
Reply

To list locks held by a current session

select convert (smallint, req_spid) As spid,
rsc_dbid As dbid,
rsc_objid As ObjId,
object_name(rsc_objid) tablename,
rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 16) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type ='LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
and rsc_dbid = db_id()
and req_spid = @@spid
order by spid

Example
BEGIN TRANSACTION
INSERT INTO TEST VALUES ( 1 )

Output
spid dbid ObjId tablename IndId Type Resource Mode Status
—— —— ———– ——————————— —— —- ————– ——– ——
52 5 0 NULL 0 DB S GRANT
52 5 2105058535 TEST 0 RID 1:143:0 X GRANT
52 5 2105058535 TEST 0 PAG 1:143 IX GRANT
52 5 2105058535 TEST 0 TAB IX GRANT

List blocked sessions in SQLServer

Posted on by
Reply

List sessions that are being blocked

select spid
, blocked being_blocked
, db_name(dbid)
, login_time
, last_batch
, program_name
from master..sysprocesses where blocked <> 0

Output:
spid being_blocked login_time last_batch program_name
—— ————- ——————————————————————————————————————————– ———————– ———————– ——————————————————————————————————————————–
53 52 test 2010-10-04 20:40:41.780 2010-10-04 20:45:50.357 Microsoft SQL Server Management Studio – Query

SET STATISTICS

Posted on by
Reply

SET STATISTICS TIME [ON|OFF] – Tells how much CPU time and time taken to parse and compile and also the CPU time and elapsed time taken for execution of the SQL statement.

Sample Output:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SET STATISTICS IO [ON|OFF] – Shows IO statistics when the SQL statement.
scan_count – shows number of times the table is referenced
logical reads – number of pages SQL Server had to read to generate the results.
physical reads – This value would change if the data cache
read-ahead reads – Number of pages placed in cache
lob logical reads – number of varchar(max), nvarchar(max) logical reads from cache
lob physical reads – number of varchar(max), nvarchar(max) physical reads

Sample Output:
Table ‘test_table’. Scan count 1, logical reads 230, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.