MS SQL: Expensive queries

Use SQL Server Profiler (on the tools menu in SSMS) to create a trace that logs these events: RPC:Completed SP:Completed SP:StmtCompleted SQL:BatchCompleted SQL:StmtCompleted Also SELECT TOP 20 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), qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.min_logical_reads, qs.max_logical_reads, qs.total_elapsed_time, qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time, qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE qt.encrypted=0 ORDER BY qs.total_logical_reads DESC 2. You can start with the standard trace template and prune it. You didn't specify whether this was for a specific database or the whole server, if it is for specific Db's, include the DatabaseID column and set a filter to your DB (SELECT DB_ID('dbname')). Make sure the logical Reads data column is included for each event. Set the trace to log to a file. If you are leaving this trace to run unattended in the background, it is a good idea to set a maximum trace file size say 500MB or 1GB if you have plenty of room (it all depends on how much activity there is on the server, so you will have to suck it and see). Briefly start the trace and then pause it. Goto File->Export->Script Trace Definition and pick your DB version, and save to a file. You now have a sql script that creates a trace that has much less overhead than running through the profiler GUI. When you run this script it will output the Trace ID (usually @ID=2); note this down. Once you have a trace file (.trc) (either the trace completed due to reaching the max file size or you stopped the running trace using EXEC sp_trace_setstatus @ID, 0 EXEC sp_trace_setstatus @ID, 2 You can load the trace into profiler, or use ClearTrace (very handy) or load it into a table like so: <code><span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> INTO </span><span class="typ">TraceTable</span><span class="pln"><br />FROM </span><span class="pun">::</span><span class="pln">fn_trace_gettable</span><span class="pun">(</span><span class="str">'C:\location of your trace output.trc'</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">default</span><span class="pun">)</span><span class="pln"><br /></span></code> Then you can run a query to aggregate the data such as this one: <code><span class="pln">SELECT COUNT</span><span class="pun">(*)</span><span class="pln"> AS </span><span class="typ">TotalExecutions</span><span class="pun">,</span><span class="pln"> <br />    </span><span class="typ">EventClass</span><span class="pun">,</span><span class="pln"> CAST</span><span class="pun">(</span><span class="typ">TextData</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">2000</span><span class="pun">))</span><span class="pln"><br /> </span><span class="pun">,</span><span class="pln">SUM</span><span class="pun">(</span><span class="typ">Duration</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">DurationTotal</span><span class="pln"><br /> </span><span class="pun">,</span><span class="pln">SUM</span><span class="pun">(</span><span class="pln">CPU</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">CPUTotal</span><span class="pln"><br /> </span><span class="pun">,</span><span class="pln">SUM</span><span class="pun">(</span><span class="typ">Reads</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">ReadsTotal</span><span class="pln"><br /> </span><span class="pun">,</span><span class="pln">SUM</span><span class="pun">(</span><span class="typ">Writes</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">WritesTotal</span><span class="pln"><br />FROM </span><span class="typ">TraceTable</span><span class="pln"><br />GROUP BY </span><span class="typ">EventClass</span><span class="pun">,</span><span class="pln"> CAST</span><span class="pun">(</span><span class="typ">TextData</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">2000</span><span class="pun">))</span><span class="pln"><br />ORDER BY </span><span class="typ">ReadsTotal</span><span class="pln"> DESC<br /></span></code> Once you have identified the costly queries, you can generate and examine the actual execution plans. From http://stackoverflow.com/questions/257906/how-can-i-log-and-find-the-most-expensive-queries

Windows : Trace recent rdp sessions

Install log parser Create a bat file like (change server accordingly): echo off cls c: cd "c:\Program Files\Log Parser 2.2\" logparser.exe file:TSLoginsDetails_More.sql?source=\\server\Security -o:DATAGRID Create a sql file like : SELECT       timegenerated,       EXTRACT_TOKEN(Strings,0,'|') AS User,       EXTRACT_TOKEN(Strings,4,'|') AS ClientName,       EXTRACT_TOKEN(Strings,5,'|') AS ClientAddress FROM %Source% WHERE EventID=682 ORDER BY timegenerated desc Run the bat file as admin

Windows : Uptime

net stats srv

MS Sql : Ctas = SELECT INTO

In Oracle create table T1 as select * from T1 The equivalent of that in Sql Server is SELECT INTO select * into T2 from T1

Windows : Audit recent RDP logins

If the login/logoff is being audited in group policy (default?) Event log id 682 in the security log shows all log on events including client name and ip Event log id 683 in the security log shows all log off events including client name and ip

Excel: Worksheet splitter vb

Sub SalesmanToSheet() Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long Dim ws As Worksheet Application.ScreenUpdating = False With ActiveSheet lastrow = .Cells(Rows.Count, "A").End(xlUp).Row LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column .Range(.Cells(2, 1), Cells(lastrow, LastCol)).Sort Key1:=Range("A2"), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom iStart = 2 For i = 2 To lastrow If .Range("A" & i).Value <> .Range("A" & i + 1).Value Then iEnd = i Sheets.Add after:=Sheets(Sheets.Count) Set ws = ActiveSheet On Error Resume Next ws.Name = .Range("A" & iStart).Value On Error GoTo 0 ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value With ws.Rows(1) .HorizontalAlignment = xlCenter With .Font .ColorIndex = 5 .Bold = True End With End With .Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy Destination:=ws.Range("A2") iStart = iEnd + 1 End If Next i End With Application.CutCopyMode = False Application.ScreenUpdating = True End Sub

WIndows : Stop Robocopy trying forever

robocopy srcDir destDir /R:2 /W:10 /MIR' retry and wait between attempts - default is 1million and 30 seconds

SQL Server: Listing sessions in 2000

SELECT DB_NAME(dbid) as 'Database Name', COUNT(dbid) as 'Total Connections' FROM master.dbo.sysprocesses WITH (nolock) WHERE dbid > 0 GROUP BY dbid SELECT DB_NAME(dbid) as 'Database Name', a.* FROM master.dbo.sysprocesses a WITH (nolock) WHERE dbid > 0