The NAV/SQL Performance Field Guide

STRYK System ImprovementPerformance Checklists

Performance Optimization & TroubleshootingVersion 1.12

Performance Checklists

Part A - SQL Server Configuration

Company Name:______

Name: ______

Date: ______

Place: ______

Database Server General
Description
UNC Name
IP Address
Place
Installation Date
Administrator (E-Mail)
Operating System
Comment
Hardware Specifications
Architecture / O x86 O x64 O i64
Number of CPU
CPU Specification
RAM
Network LAN
Network Cluster
Network Server-Server
PAE / O Yes O No (required with 32bit and > 4GB RAM)
3GB / O Yes O No (required with 32bit and < 16GB RAM)
Comment
BIOS Setting
Hyper-Threading / O Yes O No
Power Management / O Yes O No
Comment
Disk Subsystem
Description
Disk 1
Drive Letter
Controller Type
RAID Configuration
Capacity
RPM
Comment/Usage
Disk 2
Drive Letter
Controller Type
RAID Configuration
Capacity
RPM
Comment/Usage
Disk 3
Drive Letter
Controller Type
RAID Configuration
Capacity
RPM
Comment/Usage
Disk 4
Drive Letter
Controller Type
RAID Configuration
Capacity
RPM
Comment/Usage
Disk 5
Drive Letter
Controller Type
RAID Configuration
Capacity
RPM
Comment/Usage
SQL Server Settings
SQL Instance Name
SQL Server Build No.
Cluster / O Yes O No
Collation
Affinity Mask
CPU used
Degree of Parallelism
Threshold Parallelism
AWE Enabled / O Yes O No (required with 32bit and > 4GB RAM)
Max. Worker-Threads
Memory Dynamic / O Yes O No
Memory Min.
Memory Max.
Authentication / O Mixed O Windows
SQL Service Settings
SQL Service Account
Local Administrator / O Yes O No
Lock Pages in Memory / O Yes O No
Perform Volume Maintenance Tasks / O Yes O No
Trace Flags

Part B.1 – Database Configuration

NAV Database Settings
NAV Database Name
Current Database Size / GB:
NAV Build No.
NAV DB Version No.
Recovery Model / O Full O Simple O Bulk
Collation
Auto. Close / O Yes O No
Auto. Shrink / O Yes O No
Auto. Create Stats / O Yes O No
Auto. Update Stats / O Yes O No O Asynchronously
Torn Page Detection / O Yes O No
Page Verify / O Torn Page Detection O Checksum
Maintain Defaults / O Yes O No
Maintain Views / O Yes O No
Maintain Relations / O Yes O No
Always Rowlock / O Yes O No
Lock Timeout / O Yes O No Milliseconds:
Find As You Type / O Yes O No
Caching Record Set
Security Model / O Standard O Enhanced
File-Group(s) System / PRIMARY
File-Group(s) Data
Other File-Group(s)
Auto. Growth Data / O MB O % Amount:
Auto. Growth Log / O MB O % Amount:

Part B.2 – NAV Application

NAV Application Settings
No. of Concurrent Users
No. of App. Servers
No. of Companies
No. of Dimensions
No. of Analysis Views
(updated on Posting)
Comment

Part C – Database Maintenance

Maintenance Jobs
Cycle Error Logs / O Yes O No O Name:
Keep Logs
Maintain Stats / O Yes O No O Name:
Maintain Indexes / O Yes O No O Name:
SIFT Maintenance / O Yes O No O Name:
Clean Up History / O Yes O No O Name:
Deadlock Detection / O Yes O No O Name:
Block Detection / O Yes O No O Name:
Integrity Check / O Yes O No O Name:

Remarks:

______

Part D – Performance Monitor

Performance Monitor
Object / Counter / Instance / Value
Best / Avg.
Memory / Available MBytes / n/a / 100
Memory / Pages/sec / n/a / < 25
Physical Disk / Avg. Read Queue Length / NAV Database file / < 2
Physical Disk / Avg. Read Queue Length / NAV Transaction Log file / < 2
Physical Disk / Avg. Write Queue Length / NAV Database file / < 2
Physical Disk / Avg. Write Queue Length / NAV Transaction Log file / < 2
Physical Disk / Time % / NAV Database file / < 50
Physical Disk / Time % / NAV Transaction Log file / < 50
Physical Disk / Avg. sec/Transfer / NAV Database file / < 0,015
Physical Disk / Avg. sec/Transfer / NAV Transaction Log file / < 0,015
Physical Disk / Transfers/sec / NAV Database file / < 120
Physical Disk / Transfers/sec / NAV Transaction Log file / < 120
Processor / % Processor Time / Total / < 80
Processor / % Privileged Time / Total / < 10
Process / % Processor Time / SQL Server Instance / n/a
Process / Page Faults/sec / SQL Server Instance / 0
Process / Working Set / SQL Server Instance / n/a
System / Processor Queue Length / < 2
System / Context Switches/sec / < 8000
Per CPU
Network Interface / Current Bandwidth / >= 1GB
Network Interface / Output Queue Length / < 2
SQL Server Access Methods / Full Scans/sec / NAV database / n/a
SQL Server Access Methods / Page Splits/sec / NAV database / n/a
SQL Server Access Methods / Lock Escalations/sec / n/a
SQL Server Access Methods / Worktables created/sec / n/a
SQL Server Buffer Manager / Buffer Cache Hit Ratio / > 95
SQL Server Buffer Manager / Free Pages / > 640
SQL Server Buffer Manager / Page Life Expectancy / > 300
SQL Server Buffer Manager / Total Pages / n/a
SQL Server Locks / Lock Request/sec / Total / n/a
SQL Server Locks / Lock Waits/sec / Total / 0
SQL Server Locks / Number of Deadlocks/sec / Total / 0
SQL Server Databases / Data File(s) Size (KB) / NAV database / n/a
SQL Server Databases / Log file(s) Size (KB) / NAV database / n/a
SQL Server Databases / Percent Log Used / NAV database / n/a
SQL Server General Statistics / User Connections / n/a
SQL Server General Statistics / Processes blocked / 0
SQL Server Memory Manager / Total Server Memory (KB) / n/a
SQL Server Memory Manager / Target Server Memory (KB) / n/a
SQL Server Memory Manager / Memory Grants Pending / 0
SQL Server SQL Statistics / Batch Requests/sec / n/a
SQL Server SQL Statistics / Re-Compilations/sec / < 100

Part E – SQL Profiler

Measurement Start Date/Time:______

Measurement End Date/Time:______

Description:______

SQL Profiler
Events / Columns / Filters
Stored Procedures
RPC: Completed
SP: Completed
SP: StmtCompleted
TSQL
SQL: BatchCompleted
SQL: StmtCompleted / Groups
<none>
Columns
SPID
TextData
Reads
Writes
CPU
Duration
Start Time
End Time
LoginName
HostName
Application Name
EventClass / Application Name
Not Like %SQL%
Reads
Greater or Equal than 1000
Duration
Greater or Equal than 30

Remarks:

______

Part F – System Report

Open embedded TSQL scriptSQL Management Studio and Execute (F5) within context of the NAV database. Save output to RPT file (CTRL + SHIFT + F) and add report to this document.

1. Set correct Database Context:

2. Set Result Output “To File”:

3. “Execute” Script:

4. Assign Filename and “Save”: