Identify memory pressure (SQL 2008)

-- SQL Server 2008 and R2 Memory Related Queries
-- By Glenn Berry Oct 2010

http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry

Identify top 20 most expensive queries

-- Identify top 20 most expensive queries
-- By Satya

Get date when DB was last accessed

-- Database last accessed date (read and write)
-- By Aaron Bertrand

Checks hosts with NTP service stopped

#Description: This function checks hosts with NTP service stopped
#Purpose: NTP required to keep correct time for VMs and hosts
#Designed to be added as function into $profile for quick-admin tasks, run from commandline

Checks VMs with thin-provisioned disks

#Description: This function checks VMs with thin provisioned disks
#Purpose: Thin provisioned disks might over-subscribe DS usage, need to be monitored
#Designed to be added as function into $profile for quick-admin tasks, run from commandline

Checks VMs for RDMs (raw device mappings)

#Description: This function checks VMs for Raw device mappings
#Purpose: Raw device mappings limit the vmotion functionality
#Designed to be added as function into $profile for quick-admin tasks, run from commandline

Checks datastores for orphaned VMDK files

#Description: This function checks datastores for orphaned VMDK files
#Purpose: Discovering wasted storage on datastores
#Designed to be added as function into $profile for quick-admin tasks, run from commandline

Checks for VMs with out-of-date VMware tools

#Description: This function checks for VMs with out-of-date VMware tools
#Purpose: Discovering VMs that require VMTools updated
#Designed to be added as function into $profile for quick-admin tasks, run from commandline

Checks for VMs with mounted CDroms

#Description: This powershell function checks for VMs with mounted CDroms
#Purpose: If CDroms are mounted on VM, you can't VMotion the machine
#Designed to be added as function into $profile for quick-admin tasks, run from commandline

Checks for VMs with mounted floppy drives

#Description: This powershell function checks for VMs attached floppy drives
#Purpose: If floppy drives are mounted on VM, you can't VMotion the machine
#Designed to be added as function into $profile for quick-admin tasks, run from commandline

Checks for VMs with set memory limit

#Description: This powershell function checks for VMs with resource memory limit set
#Purpose: Memory limit can cause balooning and generally should not be set for the VM
#Designed to be added as function into $profile for quick-admin tasks, run from commandline

Increase number of ERRORLOGs

-- Increase number of ERRORLOGS to 20, change last value if needed
-- From http://support.microsoft.com/kb/196909

Get logcal and physical DB names

--Get logical and physical DB names for restore purposes
--Last update MsMiller 10/04/11


--SELECT LOGICAL AND PHYSICAL NAMES 2005
SELECT DB_NAME(database_id) AS DatabaseName, name AS LogicalFileName, physical_name AS PhysicalFileName
FROM sys.master_files

--SELECT LOGICAL AND PHYSICAL NAMES 2000
SELECT DB_NAME(dbid) AS DatabaseName, name AS LogicalFileName, filename AS PhysicalFileName
FROM master..sysaltfiles

Current Users, Session Counts & Authentication Type

--Current Users, Session Counts & Authentication Type
--From SQLserverCentral.com

Get index usage stats with fragmentation in spec DB

-- Run agaist DB you want to retrieve Index Usage Stats with the fragmentation level
-- Last update by : MsMiller 12-10-10

OS patching - is the patch installed and if so, does it still need reboot?

#Description: This powershell script allows you to check the list of servers for: Server OS, SP, specific patch installed and if reboot is pending
#Purpose: To ensure the patch has been applied successfully for list of computers
#Designed to be run from commandline, output written into Excel sheet

Change all DBs to FULL recover model

--Change all DBs to FULL recover model
--Last update by MsMiller 27-10-09

Get size of all DBs (MB)

-- Get size of all DBs in MB
-- Last update: MsMiller 09/08/09

Find largest folders on server

#Description: This powershell script allows you to search for folders containing largest files (over 100MB)
#Purpose: quickly find large files on remote server to resolve space issues
#Designed to be added into $profile for quick-admin tasks, run from commandline