jdaigle

select
    qs.creation_time,
    qs.last_execution_time
    ,qs.execution_count
    ,(CAST(qs.execution_count as decimal(18,4)) / (SUM(qs.execution_count) OVER (PARTITION BY 1))) AS perc_execution_count
 
    ,qs.total_worker_time -- microseconds
    ,CAST(qs.total_worker_time as decimal(18,4)) / CAST(qs.execution_count AS float) as avg_total_worker_time
    --,SUM(qs.total_worker_time) OVER (PARTITION BY 1) AS total_total_worker_time
    ,(CAST(qs.total_worker_time as decimal(18,4)) / (SUM(qs.total_worker_time) OVER (PARTITION BY 1))) AS perc_total_worker_time

jdaigle

• We can implement something like https://github.com/etsy/statsd. This will collect and aggregate metrics.
• At pre-defined intervals (say... every 10 seconds), these metrics are flushed to a backend store
• We could use SQL Server to store the metrics:

#SQL Schema

• Each series is a seperate table containing a timestamp epoch (bigint) and a value (float)
• Rows are inserted, timestamp is the PK

#Example Query

jdaigle

find . -type f -name *.csproj -exec sed -b -i "s/\(ProjectReference.*\)\(\\\\Shared\\\\\)/\1\\\\src\\\\/ig" {} \;
find . -type f -name *.sln -exec sed -b -i "s/\(Project.*\)\(\"Shared\\\\\)/\1\"src\\\\/ig" {} \;
find src -type f -name *.csproj -exec sed -b -i "s/\(ProjectReference.*\)\(..\\\\..\\\\src\\\\\)/\1..\\\\/ig" {} \;

jdaigle

SELECT [text], cp.size_in_bytes, plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype = N'Adhoc'
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;


DBCC FREEPROCCACHE(plan_handle)

jdaigle

SELECT TableName = object_name(i.object_id)
    , IndexName = i.name
    , i.type_desc
    , p.data_compression_desc
    , [Partitions] = Max(p.partition_number)
    , [Rows] = Sum(p.rows)
    , [Pages] = Sum(au.data_pages)
    , [RowsPerPage] = Sum(p.rows) / Sum(au.data_pages)
FROM sys.indexes AS i
INNER JOIN sys.partitions AS p

jdaigle

#Example 1: Local Forwarding

ssh -L *:8081:host:3389 root@gateway -N

This command will listen locally on port 8081 which will forward all connections to host:3389 from the gateway server. This basically lets us access a host or port that only "gateway" can access.

#Example 2: Remote Forwarding

ssh -R *:8081:host:3389 root@gateway -N

jdaigle

declare @n char(1)
set @n = char(10)

declare @stmt nvarchar(max)

-- procedures 
select @stmt = isnull( @stmt + @n, '' ) +
    'drop procedure [' + name + ']' from sys.procedures

-- check constraints 

jdaigle

SELECT
    q.name AS Queue_Name
  , s.name AS Service_Name
  , p.rows AS Row_Count
  , (p.rows / 2) AS Estimated_Message_Count -- divide by two because our framework always sends an "END CONVERSATION" message with each real message sent
  , COALESCE(f.Failed_Message_Count,0) AS Failed_Message_Count
  -- other interesting things
  --, q.is_enqueue_enabled
  --, q.is_receive_enabled

jdaigle

SET NOCOUNT ON 

DBCC UPDATEUSAGE(0) 

-- DB size.
EXEC sp_spaceused

-- Table row counts and sizes.
CREATE TABLE #t 
( 

jdaigle

SELECT 
o.name
, indexname=i.name
, i.index_id   
, reads=user_seeks + user_scans + user_lookups 
, user_seeks
, last_user_seek
, last_system_seek
, user_scans
, last_user_scan