MS SQL upozorenje “average wait time (ms) too high”

Posted on September 13, 2018 by

Na šta treba obratiti pažnju kada se ovo upozorenje javi :
0. Proveriti “potrošnju” za RAM i CPU, ako su u okviru granica, nastaviti dalje 🙂
1. Koliko često se javlja
2. Koja je prosečna vrednost čekanja
3. Koliki je tempdb u odnosu na veličinu baze (treba da je oko 20%)
Veličina baze : povezati se (domenski) kroz MS SQL SMS na DB server, desni klik na bazu/Properties/General
Veličina tempdb baze (ona se nalazi pod “System Databases”):

Desni klik na “tempdb”/Properties/General.
4. Broj tempdb fajlova (treba da je jednak broju procesora na DB serveru)
Desni klik na “tempdb”/Properties/Files.
5. Ensure the “optmize for ad-hoc workloads” settings is enabled also.
6. Proveriti da li su “Locks” u pitanju (dole slika sa SCOM-a). ako jesu, to je posao za programere…..

Locks are held on SQL Server resources, such as rows read or modified during a transaction, to prevent concurrent use of resources by different transactions.

Dobri linkovi : link1

Posted in Windows | Tagged , , | Leave a comment

Virtuelni WIN serveri i njihova memorija – objašnjenje

Posted on September 12, 2018 by

Deo izlaza iz WIN komande “msinfo32” :
—–
Installed Physical Memory (RAM) Not Available
U pitanju je virtielna mašina, pa nema instalirani HW RAM

Total Physical Memory 10.0 GB

Available Physical Memory 449 MB
Ovo je količina SLOBODNE memorije koja je TRENUTNO na raspolaganju.

Total Virtual Memory 19.9 GB

Available Virtual Memory 6.25 GB

Page File Space 9.86 GB
Page File C:\pagefile.sys
Ovo je obično page file za sve WIN servere.
RAM is a limited resource, and, virtual memory is (mostly) unlimited. When the memory being used by all the existing processes exceeds the available RAM, the operating system moves pages of one or more virtual address spaces to the computer’s hard disk. This frees that RAM frame for other uses. In Windows systems, these “paged out” pages are stored in one or more files (pagefile.sys files) in the root of a partition. There can be one such file in each disk partition.
For pagefile the typical recommendation of 1.5 times the installed RAM is a good starting point. On server systems, you typically want to have sufficient RAM so that there is never a shortage and so that the pagefile is basically not used.
—–

In modern OSs such as Windows, applications and many system processes always reference memory by using virtual memory addresses. Virtual memory addresses are automatically translated to real (RAM) addresses by the hardware. Only core parts of the operating system kernel bypass this address translation and use real memory addresses directly.

Virtual memory is always being used, even when the memory that is required by all running processes does not exceed the volume of RAM that is installed on the system.

Posted in Windows | Tagged , | Leave a comment

MS SQL i memorija/stanje sistema/baze

Posted on September 10, 2018 by

Neke skraćenice vezane za MS SQL i memoriju :
PLE – page life expectancy
DMV – dynamic management views (od SQL Servera 2005)
DMF – dynamic management functions.
DMV/DMF returns SQL Server runtime state information that can be used to monitor SQL Server health during runtime, troubleshoot the performance bottleneck/issues and proactively work on to minimize the downtime. The name of the DMV/DMF starts with “dm_”. They all reside in sys schema.
DMV/DMF fall into two categories:
*Server-scoped Dynamic Management Views and Functions – They reside in master database and provide SQL Server instance wide information.
*Database-scoped Dynamic Management Views and Functions – They reside in each database and provide database wide information.

sys.dm_exec_cached_plans – Cached query plans available to SQL Server
sys.dm_exec_sessions – Sessions in SQL Server
sys.dm_exec_connections – Connections to SQL Server
sys.dm_db_index_usage_stats – Seeks, scans, lookups per index
sys.dm_io_virtual_file_stats – IO statistics for databases and log files
sys.dm_tran_active_transactions – Transaction state for an instance of SQL Server
sys.dm_exec_sql_text – Returns TSQL code
sys.dm_exec_query_plan – Returns query plan
sys.dm_os_wait_stats – Returns information what resources SQL is waiting on. Every time SQL Server is forced to wait for a resource it records the wait. Odatle se vidi šta je sporo : CPU, RAM, HDD ili nešto treće. Metrics for the columns returned when querying sys.dm_os_wait_stats is cumulative. Like all Dynamic Management Object information these values accumulate over time and are wiped-clean at a SQL Server service restart.
sys.dm_os_performance_counters – Returns performance monitor counters related to SQL Server
sys.dm_os_sys_info
sys.dm_os_process_memory – prikazuje koliko memorije SQL troši u ovom trtenutku

Neki dobri linkovi : link1, link2, link3

Posted in Windows | Tagged , | Leave a comment

Memorija na WIN 2008 i sličnim serverima

Posted on September 5, 2018 by

Hardverska instalirana memorija na serveru se može videti ovako :
My Computer/desni klik/Properties, pod stavkom System :

Ali postoji i “available” memorija, koja bazično označava količinu memorije koja NIJE u tom trenutku iskorišćena.
Memorija se može videti komandom “msinfo32” :

Kako proveriti stanje stvari po pitanju memorije :
Opcija 1 (link) :
Check the system configuration settings
This problem may occur because the Maximum memory option is selected incorrectly. To fix this, follow these steps:
a) Click Start Windows icon, type msconfig in the Search programs and files box, and then click msconfig in the Programs list.
b) In the BOOT configuration window, click Advanced options on the Boot tab :

c) Click to clear the Maximum memory check box, and then click OK.
Restart the computer.
Opcija 2 (link) :
Proveriti verziju BIOS-a na serveru (vidi link).

Posted in Windows | Tagged , , | Leave a comment

SCOM i njegov agent na WIN 2008 serveru

Posted on September 3, 2018 by

Kada se javi problem sa radom SCOM agenta na WIN serveru, treba proveriti njegovo zdravlje.
Evo kako. Ide se kroz SCOM (znači ništa se ne gleda na samom WIN serveru (sem logova)!!!!
WIN server Operations Manager logovi :

SCOM :
1. Ići na stavku “Monitoring :

2. Ići skroz dole, do ove stavke :

3. Videti u kom je stanju agent.

Link1
Link2

Posted in Windows | Tagged , , , | Leave a comment

Oracle na WIN 7, problemi sa konekcijom preko Oracle SQL Developer-a (2)

Posted on August 30, 2018 by

Koristim ovaj Oracle tutorial.
##########
Sada treeba da napravim konekciju kroz “Oracle SQL Developer” na moju lokalnu mašinu, sa nalogom “hr” (videti predhodni post)
Javlja mi se greška : “ORA-01031 insufficient privileges”
Moguće rešenje 1 : setovala sam mu lozinku : ALTER USER hr IDENTIFIED BY hr; X
Moguće rešenje 2 : pokušavam da se logujem “as sysdba” X
Moguće rešenje 3 : dodeljujem sysdba prava nalogu koji koristim za kreiranje konekcije :
SQL> grant sysdba to hr;
Grant succeeded. – VV
##########
Kako videti port po kome Oracle baza radi?
Default je 1521.
Opcija 1 : pogledati u listener.ora fajl
Opcija 2 : koristiti komandnu liniju :
C:\>lsnrctl status
…..
STATUS of the LISTENER
————————
…..
Default Service XE
Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\CB-IT-010-VELDA\listener\alert\log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mojhost.mojdomen)(PORT=1521)))
…..
The command completed successfully
##########
I sada imam novu grešku : “Status : Failure -Test failed: ORA-00604: error occurred at recursive SQL level 1 ORA-12705: Cannot access NLS data files or invalid environment specified”
Link po kome sam zaključila šta da radim.
Izmenila sam postavku u Oracle SQL Developer-u ovako :

Cvrc. X
Opcija 2 : izmena ide.conf fajla koji pripada SQL Developer-u, tj samo dodati u fajl (link) :
AddVMOption -Duser.language=en
AddVMOption -Duser.region=us
Ovo radi!!!!! VV
##########
Kako videti gde su Oracle logovi :
SQL> SHOW PARAMETER AUDIT
NAME TYPE VALUE
———————- ———– ——————————
audit_file_dest string C:\ORACLEXE\APP\ORACLE\ADMIN\XE\ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE
Tu bi trebalo promeniti tako da bude audit_trail=OS (link1 i link2), što znači da bi svi audit zapisi išli u OS fajl. Da bi ovo imalo efekta DB treba restartovati :
SQL> alter system set audit_trail=os scope=spfile;
System altered.
SQL> Shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260048 bytes
Variable Size 809501616 bytes
Database Buffers 251658240 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.
SQL>
Pogledati na lokaciju gde je fajl, ali sa ovako setovanim folder opcijama :

Treba podesiti da se loguje i ono što radi sysdba : ALTER SYSTEM SET audit_sys_operations=TRUE SCOPE=SPFILE;
I opet restart baze…..
##########
I sad se sve loguje u Event Viewer/Windows Logs/Application, ali bez opisa. Greška koja se javlja u Event Log-u WIN 7 :
“The description for Event ID 34 from source Oracle.xe cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted.”
Moguće rešenje 1 (link) /ovo bi trebalo i da daje veću jasnoću logova Oracle-a pod “Event Viewer-om” :
1) Open Registry Editor.
2) Locate and then click the following registry keys :
HKEY_LOCAL_MACHINE\System\CurrentControlSet\services\eventlog\Security\Microsoft-Windows-Security-Auditing
Right click Microsoft-Windows-Security-Auditing in the left pane, and then click Permissions….
Click the Add… button in the permission dialog.
In the Enter the object names to select box, type Event Log Readers, and then click the Check Names button (paziti, pretraga treba da ide SAMO po lokalnoj mašini!!!).
Click OK to close all dialog windows.
3) Ovo zahteva restart WIN 7 mašine 🙁 X
Dobar link.
##########

Posted in Uncategorized, Windows | Tagged , , | Leave a comment

Kako videti šta ima od Oracle-a na lokalnoj mašini (WIN 7) (1)

Posted on August 30, 2018 by
  • Videti čega ima pod My Computer/Manage/Services
  • Koristiti SQL upit da se vide detalji.  

Napomena : Paziti da lokalni nalog koji se koristi bude u ove dve grupe : Administrators i ORA_DBA (Control Panel/User Accounts/Manage User Accounts/Properties/Advanced i tu željenom nalogu dodati sve potrebne grupe.

Otvoriti CLI i logovati se sa podešenim nalogom, obavezno kao sysdba :

C:\>sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Thu Aug 30 13:13:45 2018

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter user-name: moj.nalog / as sysdba
Enter password:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 – 64bit Production

  • Kako videti gde je Oracle home (Rt-Click on Computer/Properties/Advanced system settings/Advanced Tab/Environment Variables/ then check Path Option in the System variables to see Oracle client HOME dir.) :
  • Kako videti podatke o Oracle DB

SQL> select name from V$database;

DBNAME

XE

SQL>

Pošto ja imam default instalaciju, mogu postupati po ovom linku, i omogućiti nalog “hr” sa kojim ću nadalje da radim :

SQL> alter user hr identified by hr account unlock;

User altered.

Posted in Windows | Tagged , , | Leave a comment

Podaci o MS OS na remote serveru

Posted on August 30, 2018 by

Ovo može, samo ako ste u comandnoj liniji logovani kao domenski user, sa nalogom koji ima prava da vidi ove podatke (označila sam podatke koji su meni bitni) :

C:\>systeminfo /s tsaprojectm

Host Name: MOJ.SERVER
OS Name: Microsoft Windows Server 2012 R2 Standard
OS Version: 6.3.9600 N/A Build 9600
OS Manufacturer: Microsoft Corporation
…..
Original Install Date: 16.07.2018, 13:25:51
…..
System Type: x64-based PC
Processor(s): 2 Processor(s) Installed.
[01]: Intel64 Family 6 Model 63 Stepping 0 GenuineIntel ~2600 Mhz
[02]: Intel64 Family 6 Model 63 Stepping 0 GenuineIntel ~2600 Mhz
BIOS Version: Phoenix Technologies LTD 6.00, 21.09.2015
…..
Time Zone: (UTC+01:00) Belgrade, Bratislava, Budapest, Ljubljana, Prague
Total Physical Memory: 25,600 MB
Available Physical Memory: 729 MB
Virtual Memory: Max Size: 57,623 MB
Virtual Memory: Available: 7,050 MB
Virtual Memory: In Use: 50,573 MB
Page File Location(s): C:\pagefile.sys
Domain: moj.domen
Logon Server: N/A
Hotfix(s): 199 Hotfix(s) Installed.
[01]: KB2894852
…..

A na lokalnoj mašini, kao Administrator u CLI :

C:\>echo %PROCESSOR_ARCHITECTURE%
AMD64

Dobar link.

Posted in Windows | Tagged , , | Leave a comment

Heklanje 93 : pojas

Posted on August 29, 2018 by

Bilo mi je dosadno, to mi je jedino opravdanje 😉

Broš koristim da bih zakačila pojas (slika dole).
Posted in Odmor | Tagged , , | Leave a comment

MS SQL server verzija

Posted on August 28, 2018 by

Kako naći verziju MS SQL servera, bilo da je server up ili down?

Odličan link!

Remotely :

a) Korišćenjem MS SQM SMS-a i upita “select @@version“

b) Kroz PowerShell pokrenut kao MS SQL administrator, sa upitom : “

PS D:\Skriptici> invoke-sqlcmd -query “select @@version” -serverinstance “mojserver.mojdomen”

Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) – 13.0.1742.0 (X64) …

Direktno logovani na MS SQL server :

a) Pronaći fajl sqlservr.exe, i videti njegove “Properties/Details/Product version”. To je prilično dugačak broj, sa tačkama između sekvenci. Ovako se tumači :

  • 8.0 for SQL Server 2000
  • 9.0 for SQL Server 2005
  • 10.0 for SQL Server 2008
  • 10.5 for SQL Server 2008 R2
  • 11.0 for SQL Server 2012
  • 12.0 for SQL Server 2014
  • 13.0 for SQL Server 2016
  • 14.0 for SQL Server 2017

 

 

Posted in Windows | Tagged , | Leave a comment