Linux – Memory
Linux free command info Play with Linux memory watch -d=cumulative vmstat -d # disk watch -d=cumulative vmstat -a # free active and inactive memory
Oracle – Sessions using TEMP tablespace
SELECT b.TABLESPACE , b.segfile# , b.segblk# , ROUND ( ( ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb , a.SID , a.serial# , a.username , a.osuser , a.program , a.status…
SQL Server – display Users and Roles
select * from ( SELECT [UserName] = CASE princ.[type] WHEN ‘S’ THEN princ.[name] WHEN ‘U’ THEN ulogin.[name] COLLATE Latin1_General_CI_AI END, [UserType] = CASE princ.[type] WHEN ‘S’ THEN ‘SQL User’ WHEN ‘U’ THEN ‘Windows User’ END,…
SQL Server – Display Executed SQL
SELECT * FROM ( SELECT TOP 10000 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END – qs.statement_start_offset)/2)+1) AS [Text], qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.total_logical_writes, qs.last_logical_writes, qs.total_worker_time/1000 total_worker_time_in_ms, qs.last_worker_time/1000 last_worker_time_in_ms, qs.total_elapsed_time/1000 total_elapsed_time_in_ms, qs.last_elapsed_time/1000…
Oracle – Diagnostics and Tuning Pack
Thanks to Mike Smithers for the following post in his blog: What you are not licensed for
Oracle – Feature Usage
select * from DBA_FEATURE_USAGE_STATISTICS order by detected_usages desc;
SQL Server – Find occurrences of text in a string
select (LEN(@string)-LEN(REPLACE(@string,@strToFind,”)))/LEN(@strToFind)
SQL Server – SplitString Function
— 100 recursions maximum create function dbo.SplitString ( @str nvarchar(max), @separator char(1) ) returns table AS return ( with tokens(p, a, b) AS ( select cast(1 as bigint), cast(1 as bigint), charindex(@separator, @str) union all…
SQL Server – Display Enterprise Edition Feature Usage
select * from sys.dm_db_persisted_sku_features;
SQL Server – Connection Port
— Read the errorlog with a stored procedure EXEC xp_ReadErrorLog 0, 1, N’Server is listening on’, N’any’, NULL, NULL, ‘DESC’ GO Or look in the Configuration Manager SQL Server Network Configuration/Protocols/IP Addresses/TCP Port