Category Archives: SQL PASS

T-SQL performance tuning – no differences between virtual and physical

Check host and GPU numbers – look outside of the VM at the host.

Remote Perfmon users – group you can be added to for viewing performance monitor information for the server; you can be added to this group on the server where your VM is hosted.

ESX Top – task manager for UNIX; command line utility. Check for CPU thrashing – looking for percent used.

Check host and guest for disk IO latency.

Balloon memory driver – should be enabled; used by host os to request memory back from the guest os; if this is turned off – prevents host os from paging physical memory to the hosts swap file; paging will occur and you will have random slowness that will be difficult to diagnose.

Reserved memory setting – How much memory you want and how much memory you really need to have (reserved).

Memory deduplication – looking for and stripping out duplicate instances of processes in memory; great for OS memory. Doesn’t work for all SQL Server – unless multiple SQL Servers have the same pages in cache.

Storage configuration options – IO is the same if the disks are physical or virtual; automatic tier adjusting technology if possible. Storage slow? Get faster storage ($$$). Keep OS, data, logs tempdb on separate disks if possible.

Storage deduplication – can greatly improve overall performance; deduplicating the OS virtual disks = save much less data to the array.

VMWare Paravirtualization Driver – optional driver for vSphere Virtual Machines; recommended for high IO workloads; config via VM Properties – select SCSI controller, change type.

Monitoring – look at more levels of the environment; SQL Server, guest OS, hypervisor, etc.

Counters –
reads/writes per sec – correlated counter at the host level
seconds / reads and writes
disk queue
page life expectancy
system processor queue
VM Disk
VM Memory

Timer inside the VM lies – ugly rumor and nothing more.