April 2017 – Yvonne Knows Databases

Troubleshooting SQL Server Performance

If your concerned your SQL server database is the cause of a performance issue then these commands might help

IF you think postgres performance is the cause of an application performing badly these sql statements may help

Vcloud kill idle sessions

This mainly shows how to kill sessions and look at locking in postgres

This runs as a service account

NT SERVICE\vPostgres

You can start it using

C:\Program Files\VMware\vCenter Server\bin>service-control –start vPostgres

C:\Program Files\VMware\vCenter Server\bin>service-control –stop vPostgres

You cannot start it using pg_ctl as administrator

If there are errors starting you can see the postgresql*.logs

normally in C:\ProgramData\VMware\vCenterServer\logs\vpostgres

If nothing here check the windows events log

Go check the Application log: right
click on Computer -> Manage -> Events -> Application

You can increase logging editing the postgresql.conf file

normally found in C:\ProgramData\VMware\vCenterServer\data\vpostgres

track_activities = on
track_activity_query_size = 6000
log_statement_stats = on
log_statement = ‘all’

In a case I had the reverse proxy server had to be restarted to allow the vpostgres service to start.

If you need to work around unknown passwords you can lower security so a password isn’t required

Search for the C:\ProgramData\VMware\vCenterServer\data\vpostgres\pg_hba.conf

change md5 to trust

To run pg_ctl you’#ll have to be postgres user. Run command prompt at postgres user

C:\Program Files\VMware\vCenter Server\vPostgres\bin>psql.exe -U vc -d VCDB
psql (9.4.9 (VMware Postgres 9.4.9.1-4536683 release))