Netsh int tcp set global autotuninglevel=disabled

To see the current configuration for the autoTuningLevel parameter: netsh interface tcp show global
The value for the autoTuningLevel parameter is listed as Receive Window Auto-Tuning Level. The following list includes all possible values for the autoTuningLevel parameter:

disabled - Sets the receive window at the default value.
highlyrestricted - Lets the receive window grow beyond the default value, but does so very conservatively.
restricted - Lets the receive window grow beyond the default value, but limits such growth in some scenarios.
normal - Lets the receive window grow to accommodate most scenarios.
experimental - Lets the receive window grow to accommodate extreme scenarios.

Note The experimental value may decrease performance in common scenarios. This value should be used only for research purposes. The TCP receive window size is the maximum amount of received data in bytes that can be buffered at the same time on the receiving side of a connection. The sending host can send only that amount of data before waiting for an acknowledgement and for a "receive window" update from the receiving host.

The TCP/IP stack in Windows Vista tunes itself in most environments by using larger default window sizes than in earlier versions of Windows. Instead of using a hard-coded default receive window size, TCP automatically adjusts the window size to increase the percentage of full-sized TCP segments that are used during bulk data transmission. This helps limit the number of segments that are sent when large sets of data are transmitted.

When auto-tuning is enabled in Windows Vista, Receive the Window Auto-Tuning feature continually determines the optimal true receive window size by measuring the bandwidth delay product and the application retrieve rate. Then, Receive Window Auto-Tuning adjusts the maximum receive window size based on the changing network conditions.

-- Get IO Stats for each DB
SELECT dbs.name, 
	  [WriteLatency ms] = CASE WHEN [num_of_writes] = 0 
			 THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END, 
	[ReadLatency ms] = 
		 CASE WHEN [num_of_reads] = 0 
			 THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END, 
	[Latency] = 
		 CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0) 
			 THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END, 
--avg bytes per IOP
	 [AvgBPerRead] = 
		 CASE WHEN [num_of_reads] = 0 
			 THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END, 
	 [AvgBPerWrite] = 
		 CASE WHEN [io_stall_write_ms] = 0 
			 THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END, 
	 [AvgBPerTransfer] = 
		 CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0) 
			 THEN 0 ELSE 
				 (([num_of_bytes_read] + [num_of_bytes_written]) / ([num_of_reads] + [num_of_writes])) END, 
	  files.physical_name, files.name, stats.* 
FROM sys.dm_io_virtual_file_stats(NULL,NULL) stats
INNER JOIN master.sys.master_files AS files  
	ON stats.database_id = files.database_id 
	AND stats.file_id = files.file_id 
INNER JOIN master.sys.databases AS dbs  
	ON stats.database_id = dbs.database_id 

CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
      Login  VARCHAR(255),HostName  VARCHAR(255), 
      BlkBy  VARCHAR(255),DBName  VARCHAR(255), 
      Command VARCHAR(255),CPUTime INT, 
      DiskIO INT,LastBatch VARCHAR(255), 
      ProgramName VARCHAR(255),SPID2 INT, 
      REQUESTID INT) 
INSERT INTO #sp_who2 EXEC sp_who2
SELECT      * 
FROM        #sp_who2
-- Add any filtering of the results here :
--WHERE       DBName <> 'master'
-- Add any sorting of the results here :
ORDER BY    login,ProgramName ASC
 
DROP TABLE #sp_who2

Get the top 50 commands and change the Order by to select the sorting to be by reads, writes or length of time.

      SELECT TOP 50 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.total_logical_writes, qs.last_logical_writes,
      qs.total_worker_time,
      qs.last_worker_time,
      qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
      qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
      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
      ORDER BY qs.total_logical_reads DESC -- logical reads
      -- ORDER BY qs.total_logical_writes DESC -- logical writes
      -- ORDER BY qs.total_worker_time DESC -- CPU time

-- tempdb layout
use tempdb
go
exec sp_helpfile
go
          

-- cores and hyperthreading
select cpu_count, hyperthread_ratio 
from sys.dm_os_sys_info
          

SQL server is reporting msdb is in suspect mode and you are unable to log into server. You have a backup copy of the msdb, here are the steps I used to restore the msdb from the backup copy.

 1. Stop SQL servers and SQL agent
 2. Put SQL into single user mode:	
  a. Click -> START -> Microsoft SQL Server 2005 -> Configuration Tools – > SQL Server Configuration Manager
  b. Select SQL Services on the left, then Right click on SQL Server instance on the right and choose Properties
  c. Click on the Advanced tab. Under Startup Parameters you will be adding the 
    following parameters to the beginning of the string: -m;-c;-T3608;
  d. Start SQL Server
 3. Run sqlcmd and enter the following commands (not sure you really need to do the detach):
  use master
  GO
  sp_detach_db 'msdb'
  GO
  RESTORE DATABASE [msdb]
  FROM DISK = N'E:\SQL\Backup\msdb\msdb_backup_2013_10_07_220003_5074315.bak'
  WITH REPLACE
  GO
 4. Stop SQL
 5. Remove Start up options you added in Step 2a.
 6. Start SQL

Have you ever been looking for a triger that is firing off and running xp_sendmail? Here is a command to find all triggers with xp_sendmail in them

      SELECT      Tables.Name TableName,
             Triggers.name TriggerName,
             Triggers.crdate TriggerCreatedDate,
             Comments.Text TriggerText
      FROM      sysobjects Triggers
             Inner Join sysobjects Tables On Triggers.parent_obj = Tables.id
             Inner Join syscomments Comments On Triggers.id = Comments.id
      WHERE      Triggers.xtype = 'TR'
             and Tables.xtype = 'U'
             and Comments.Text like '%xp_sendmail%'
      ORDER BY Tables.Name, Triggers.name
      
 

Here is a TSQL command that will create some data, does some writes/reads and give you info on how fast it ran. Adjust Loops for the number of loops you want to run. This will also affect the size of the test tables. Results are displayed and logged to SQLTESTLog

    -- Use with Caution, be careful with log files if continually running this process it might generate very large logs 
    BEGIN TRANSACTION
    SET QUOTED_IDENTIFIER ON
    SET ARITHABORT ON
    SET NUMERIC_ROUNDABORT OFF
    SET CONCAT_NULL_YIELDS_NULL ON
    SET ANSI_NULLS ON
    SET ANSI_PADDING ON
    SET ANSI_WARNINGS ON
    COMMIT
    
    -- Remove any existing objects
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SQLTESTScratchPad]') AND type in (N'U'))
          DROP TABLE [dbo].[SQLTESTScratchPad];
    GO
    
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SQLTESTTemp1]') AND type in (N'U'))
          DROP TABLE [dbo].[SQLTESTTemp1];
    GO
    
    -- Log results into SQLTESTLog
    IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SQLTESTLog]') AND type in (N'U'))
          CREATE TABLE dbo.SQLTESTLog
          (
          startTime datetime NULL,
          endtime datetime  NULL,
                      exectime datetime  NULL,
          scriptTime varchar(10)  NULL,
                      loops int NULL,
                      loopsPerSec int NULL
          )  ON [PRIMARY]
    GO
    
    
    
    
    BEGIN TRANSACTION
    GO
    CREATE TABLE dbo.SQLTESTScratchPad
          (
          AutoAssignBigInt bigint NOT NULL IDENTITY (1, 1),
          guid uniqueidentifier NULL,
          datetime datetime NULL,
          varchar50 varchar(50) NULL,
          nvarchar50 nvarchar(50) NULL,
          char50 char(50) NULL
          )  ON [PRIMARY]
    GO
    ALTER TABLE dbo.SQLTESTScratchPad ADD CONSTRAINT
          DF_ScratchPad_guid DEFAULT newid() FOR guid
    GO
    ALTER TABLE dbo.SQLTESTScratchPad ADD CONSTRAINT
          PK_ScratchPad PRIMARY KEY CLUSTERED 
          (
          AutoAssignBigInt
          ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    
    CREATE UNIQUE NONCLUSTERED INDEX IX_ScratchPad_Guid ON dbo.SQLTESTScratchPad
          (
          guid
          ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    
    CREATE NONCLUSTERED INDEX IX_ScratchPad_VarChar50 ON dbo.SQLTESTScratchPad
          (
          varchar50
          ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    
    CREATE NONCLUSTERED INDEX IX_ScratchPad_NVarChar50 ON dbo.SQLTESTScratchPad
          (
          nvarchar50
          ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    
    --ALTER TABLE dbo.SQLTESTScratchPad SET (LOCK_ESCALATION = TABLE) -- This only works with SQL 2008 or higher, so I have 
    
    disabled it.
    GO
    COMMIT
    
    Set NOCOUNT ON;
    
    -- Start of loop
    Declare @Counter int;
    Set @Counter = 1;
    
    Declare @Loops int;
    Set @Loops = 1000000;
    Set @Loops = 200000;
    
    
    -- Record the start time.
    Declare @StartTime datetime;
    Set @StartTime = getdate();
    
    
    
    
    While (@Counter <= @Loops)
    Begin
          Insert Into SQLTESTScratchPad
                (
                      guid,
                      [datetime],
                      varchar50,
                      nvarchar50,
                      char50
                )
          Values
                (
                      newid(),
                      getdate(),
                      Cast(Rand() As varchar(50)),
                      Cast(Rand() As nvarchar(50)),
                      Cast(Rand() As char(50))
                )
          Set @Counter = (@Counter + 1);
          
    End
    
    -- Issue a simple select
    Select
          *
    Into SQLTESTTemp1
    From SQLTESTScratchPad
    Order By guid desc;
    
    -- Record the end time.
    Declare @EndTime datetime;
    Set @EndTime = getdate();
    
    
    -- LogResults
    insert into SQLTESTLog
          VALUES ( @StartTime, @EndTime,(@EndTime - @StartTime), Cast(DatePart(minute, (@EndTime - @StartTime)) As varchar(10)) 
    
    + ':' + Cast(DatePart(second, (@EndTime - @StartTime)) As varchar(10)), @Loops, (@Loops*1000/DATEDIFF(millisecond, @StartTime, 
    
    @EndTime))  );
    
    
    -- Display the results
    Select
          @StartTime As StartTime,
          @EndTime As EndTime,
          (@EndTime - @StartTime) As ExecTime,
          Cast(DatePart(minute, (@EndTime - @StartTime)) As varchar(10)) + ':' + Cast(DatePart(second, (@EndTime - @StartTime)) 
    
    As varchar(10)) As ScriptTime,  
                      @Loops as Loops, 
                      (@Loops*1000/DATEDIFF(millisecond, @StartTime, @EndTime)) As LoopsPerSec,
                      DATEDIFF(millisecond, @StartTime, @EndTime) As MSec;;

    DROP TABLE [dbo].[SQLTESTScratchPad];
    DROP TABLE [dbo].[SQLTESTTemp1];

------------------------------------------------------------------------------------------------------------ -- Configuration Declare @SourceDB sysname = 'SourceDB' -- Enter in the Source database to copy from Declare @DestinationDB sysname = 'DestDB' --Enter in the Destination database to restore to Declare @BackupFile nvarchar(2000) = N'C:\Temp\test.bak' -- Enter in the location and name of backup file -- Options Declare @CompressBackup varchar(1) = 'Y' -- Y or N (Only Supported on 2008R2 and later) Declare @KillActiveConnections varchar(1) = 'Y' -- Y or N to kill active connections on Destination DB before Restore ------------------------------------------------------------------------------------------------------------ Use Master Declare @CompressOption nvarchar(50) = ',compression' IF @CompressBackup = 'N' set @CompressOption = '' declare @query varchar(max); print 'Backing up DB ' + @SourceDB + ' to ' + @BackupFile set @query = 'BACKUP DATABASE ' + @SourceDB + ' TO DISK = ''' + @BackupFile + ''' with Format ' + @CompressOption print @query exec(@query) IF ( @KillActiveConnections = 'Y' ) BEGIN print'' print 'Checking for any active connections on ' + @DestinationDB Declare @spid int Select @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@DestinationDB) While @spid Is Not Null Begin print 'Killing spid ' + cast(@spid as varchar) + ' on Database ' + @DestinationDB Execute ('Kill ' + @spid) Select @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@DestinationDB) and spid > @spid End END print '' print 'Restoring DB ' + @DestinationDB RESTORE DATABASE @DestinationDB FROM DISK = @BackupFile WITH REPLACE