kr153

MsMpEng.exe - add to exclution - folder and process

Open admin PowerShell and run:

	New-MpPerformanceRecording -recordto C:\Defender.ETL

Stop the trace after 60 seconds.
Then run:

	Get-MpPerformanceReport -Path:C:\Defender.ETL -TopFiles:100 -TopExtensions:10 -TopProcesses:10 -TopScans:100 | out-file c:\scans.txt

kr153

-- First PASS
DECLARE @First INT
DECLARE @Second INT
SELECT @First = cntr_value
FROM sys.dm_os_performance_counters
WHERE
OBJECT_NAME = 'SQLServer:SQL Statistics' AND -- Change name of your server ' AND -- Change name of your server
counter_name = 'Batch Requests/sec'-- AND
--instance_name = 'XC21801'; -- Change name of your database
-- Following is the delay

kr153

with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)
select 
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB

kr153

select   percent_complete
 from sys.dm_exec_requests 

where  session_id=<spid>

order by start_time desc

kr153

--kr153

SET NOCOUNT ON
DECLARE @BATCHCOUNT INT


---------------------------------------
/* BATCH COUNT - CHANGE AS NEEDED */
---------------------------------------
SET @BATCHCOUNT = 1000                       

kr153

SELECT sc.NAME + '.' + ta.NAME TableName
	,SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0
	AND pa.index_id IN (
		1
		,0
		)

kr153

DECLARE @Database VARCHAR(255)   
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @fillfactor INT 

SET @fillfactor = 90 

DECLARE DatabaseCursor CURSOR FOR  
SELECT name FROM master.dbo.sysdatabases   
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')   

kr153

--------------------------------------------------------------------------------- 
----------------     Check Fragmentation in SQL Server     ----------------------
--------------------------------------------------------------------------------- 

-- For Specific Darabase, uncomment the USE USE [Database Name] just below
-- USE [Database Name]

SELECT Db_id()                    AS DbId, 
       Object_name(ind.object_id) AS TableName, 
       ind.name                   AS IndexName, 

kr153

--ReOrg and RunStat for IBM DB2 
--All Programs > IBM DB2 > DB2COPY1 (Default) > Command Line Tools > Command Window - Administrator

-- Connect to Database
db2 connect to <DATABASE NAME>

-- Generate output for RunStat on Tables 
db2 -x "SELECT 'RUNSTATS ON TABLE ' || TRIM(TABSCHEMA) || '.' || TRIM(TABNAME) || ' AND INDEXES ALL;' FROM SYSCAT.TABLES WHERE TYPE = 'T' AND TABSCHEMA NOT LIKE 'SYS%' ORDER BY TABSCHEMA, TABNAME" > db2_runstats.sql.out
-- Use the genereated file for RunStat on Tables.
db2 -tvf db2_runstats.sql.out

kr153

-- If avg_fragmentation_in_percent  > 5% and < = 30%  ALTER INDEX REORGANIZE 
-- If avg_fragmentation_in_percent  > 30%  ALTER INDEX REBUILD WITH (ONLINE = ON)   

--Check Fragmentation Level  
SELECT object_id                    AS objectid, 
       index_id                     AS indexid, 
       partition_number             AS partitionnum, 
       avg_fragmentation_in_percent AS frag 
FROM   sys.Dm_db_index_physical_stats (Db_id(N'<Database Name>'), 
              Object_id(N'<Table Name>'), NULL, NULL, 'LIMITED')