Check List

Through detailed checklists on configurations, operations and best practices, SQL Server LifeStyle allows to get precise and detailed analysis of the health status of installations to produce accurate documentation and a list of considerations for the highest level of stability and performance.

 

Instance level

Setup and general assessment
•   GENERAL 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
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

What do we use

T-SQL, powershell, DMVs, DMFs, system views, system stored procedures
NO Setup, NO Restart, NO Components

Service access

The analysis can be done from any computer.
The tool simply need a connection with the SQL Server instance.

Confidentiality of informations

During the phases of analysis any information will be treated as "confidential information", in whatever form it is (paper or electronic), so will not be revealed, disclosed or communicated to third parties in any way even within the same organization.

Microsoft, SQL Server, Windows, and Windows Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.