Check List

Tramite dettagliate checklist su configurazioni, operazioni e best practices, SQL Server LifeStyle permette di ottenere un’analisi precisa e dettagliata dello stato di salute delle installazioni per produrre un’accurata documentazione ed una lista di attenzioni e considerazioni per il massimo livello di stabilità e di performance.

 

Instance level

Setup and general assessment
•   INSTANCE INFO
•   INSTANCE PROPERTIES
•   NON DEFAULT CONFIGURATION OPTIONS
•   PORT NUMBER
•   SQL SERVER SERVICE CHECK UTILITY
•   LIST DATABASES
•   TRANSACTION LOG STATISTICS
•   NUMBER OF ERRORS
•   GET AVAILABLE AND FREE DISK SPACE
•   OPERATING SYSTEM INFO
•   VERIFY SERVICE PACK
•   INSTANCE REGISTRY INFO
•   TABLES IN MSDB DATABASE
•   TABLES IN MASTER DATABASE
•   LIST USER DATABASE ON C DRIVE
•   LIST SYSTEM DATABASE ON C DRIVE
•   STORED PROCEDURE AT STARTUP
Performance
•   WAIT ANALYSIS
•   PERFORMANCE COUNTERS
•   PERFORMANCE COUNTERS INFO LOCKS
TempDB
•   TEMPDB OBJECTS
•   TEMPDB TIPS
•   USER TABLES IN TEMPDB DATABASE
Memory
•   MEMORY UTILIZATION BY DATABASE
•   MEMORY CLEAN AND DIRTY BY DATABASE
•   CACHE AND MEMORY UTILIZATION
•   MEMORY STATE
•   CACHE SPACE
•   AGE OF ITEMS IN PROCEDURE CACHE
•   CACHE SINGLE USE PLANS
IO
•   IO STALL
•   IO BOTTLENECKS
•   IO STATISTICS
•   IO STATISTICS MOST DB READS
•   IO SLOW STORAGE
•   LOGICAL IO PERFORMED
CPU
•   CPU BOTTLENECKS
•   CPU USAGE
•   CPU UTILIZATION
•   CPU UTILIZATION BY DATABASE
•   SIGNAL WAITS
•   TOTAL WORKER TIME
Instance workload
•   TOP 15 SLOW QUERIES
•   TOP 15 MOST EXPENSIVE STORED PROCEDURES
•   TOP 30 USE COUNT STORED PROCEDURES
Security
•   BACKUP INFORMATION
•   SYSADMIN LIST
•   DISABLE GUEST ACCOUNT FROM EACH DB USER
•   SAMPLE DATABASES
•   VERIFY EXISTS BUILTIN ADMINISTRATORS
•   SEARCH GRANT PERMISSIONS TO THE PUBLIC DATABASE ROLE
Maintenance
•   MAINTENANCE PLANS
Jobs
•   Jobs list
•   Jobs with errors

Database level

Properties and space
•   DATABASE PROPERTIES
•   TABLE SPACE
•   ROWS IN TABLES
Consistency
•   DATABASE CONSISTENCY HISTORY
Files and allocation
•   DATABASE EXTENTS
•   DATABASE FILES
•   DATABASE GROWTH
•   DATABASE GROWTH COUNTER
•   DATABASE FILES DRIVE
•   ANALYZE VIRTUAL LOGS
Memory
•   MEMORY UTILIZATION
INDEXES
•   INDEX ANALYSIS
•   INDEX DUPLICATED
•   INDEX MISSING TOP 15
•   INDEX Possible Bad Indexes (writes on reads)
•   INDEX Read Write stats for a single table
•   INDEX UNUSED
•   REBUILD DATABASES INDEXES
•   REBUILD INDEX ANALYSIS
•   FOREIGN KEY MISSING INDEX
•   INDEX XML MISSING TOP 15
•   INDEX DISABLED
•   NONCLUSTERED INDEX COUNTER
•   INDEX REDUNDANT
Statistics
•   ANALYSIS OLD STATISTICS
•   ANALYSIS STATISTICS TO UPDATE
Modeling
•   TABLES CLUSTERED INDEX VERIFICATION
•   TABLES PRIMARY KEY VERIFICATION
•   CLUSTERING KEYS LENGTH
•   UNICODE COLUMNS
•   FOREIGN KEY DISABLED
•   TABLES WITHOUT ANY INDEX
•   VERIFY FIXED LENGTH COLUMNS
•   OBJECTS CHECK DEPENDENCIES
•   TABLES WITH DIFFERENT COLLATION FROM DB
Used Tables
•   TABLES with the most reads TOP 10
•   TABLES with the most writes TOP 10
•   TABLES LIST UNUSED
Workload
•   SLOW QUERIES by consume large amount of log space
•   SLOW QUERIES by CPU TOP 10
•   SLOW QUERIES by duration TOP 10
•   SLOW QUERIES by excessive compiles recompiles
•   SLOW QUERIES by execution count TOP 10
•   SLOW QUERIES by reads consumption
•   SLOW QUERIES by writes consumption
•   PLAN CACHE WITH INEFFICIENT OPERATORS
Contention
•   MAIN OBJECTS OF BLOCKING CONTENTION
SQLCLR
•   SQLCLR Not Used Assembly
Dynamic SQL
•   ROUTINES WITH DYNAMIC SQL WITHOUT SP EXECUTESQL
Programming
•   OBJECTS CODE ANALYSIS
•   OBJECTS_LINES_OF_CODE

Table level

Modeling
•   CHANGE COLUMN TYPE TO SAVE BYTES
•   CHANGE COLUMN LEN TO SAVE BYTES
•   CHANGE COMPRESSION TO SAVE IO
•   HIGH NULLs PERCENTAGE

Cosa utilizziamo

Strumenti del sistema operativo
T-SQL, powershell, DMVs, DMFs, viste di sistema, stored procedure di sistema
NO Setup, NO Restart, NO Componenti

Modalità di analisi

L’analisi può essere eseguita da qualsiasi macchina in grado di raggiungere l'istanza da analizzare.

Riservatezza delle informazioni

Durante le fasi di analisi ogni informazione sarà trattata come “informazione riservata”, in qualunque forma essa sia (cartacea o elettronica), quindi non verrà rivelata, divulgata o comunicata in alcun modo a terzi anche all’interno della stessa organizzazione.

Microsoft, SQL Server, Windows e Windows Server sono marchi registrati o marchi commerciali di Microsoft Corporation negli Stati Uniti e/o in altri paesi.