From IT Skills
Revision as of 14:43, 16 March 2016 by Waiter1 (talk | contribs) (special parallelism)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

connections quantity[edit | edit source]

select db_name(dbid), COUNT(dbid) as NumberOfConnections, loginame from sys.sysprocesses
group by dbid, loginame

how to see exclusive locks and deadlocks[edit | edit source]

src/Examples/tsql/management/exclusiveLocks.sql

Deadlocks and trace flag 1204... DBCC TRACEON (1204, 3605, -1) Flag 1204 records deadlock information. Flag 3605 sends it to the error log. And flag -1 turns the trace on for all sessions, not just the current session starting the trace.

how to see currently executing sql commands[edit | edit source]

src/Examples/tsql/management/Find%20Currently%20Running%20Query.sql

src/Examples/tsql/management/monitor.sql that creates sp_radhe in master db

query execution plan[edit | edit source]

  • measure with
SET STATISTICS IO ON 
SET STATISTICS TIME ON
DECLARE @StartTime datetime
DBCC DROPCLEANBUFFERS
SET @StartTime = GETDATE()  
---- sql to test
SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())
create table #vars (start datetime)
insert #vars values (getdate())

SELECT ExecutionTimeInMS = DATEDIFF(millisecond, (select start from #vars), getdate())
drop table #vars
  • or with Sql Profiler

parallelism[edit | edit source]

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 2;
GO
RECONFIGURE WITH OVERRIDE;