.::: Test Performance SQL Server, MSSQL, MySQL, MariaDB, Oracle using Apache JMeter :::.

1. Open Jmeter.bat
2. Creating a Thread Group Element

3. Fill Threat Group and save
Name = MSSQL

4. Creating JDBC Connection
Right Click -> Add -> Config Element -> JDBC Connection Configuration
 

.::: How to Flush Clear RAM Memory Cache, Buffer and Swap Space on Linux :::.

1. Clear PageCache only.

# sync; echo 1 > /proc/sys/vm/drop_caches

2. Clear dentries and inodes.

# sync; echo 2 > /proc/sys/vm/drop_caches

3. Clear pagecache, dentries, and inodes.

# sync; echo 3 > /proc/sys/vm/drop_caches

.::: How to enable CDC (Change Data Capture) in SQL Server in Table & Databases SQL Server :::.

1. check table from database
USE teguhth
GO
select @@servername as ServerName, DB_NAME() as DBName,* from pembelian;
select @@servername as ServerName, DB_NAME() as DBName,* from teguhth.dbo.pembelian;

//** command for data cdc become

select @@servername as ServerName, DB_NAME() as DBName,* from cdc.change_tables
select @@servername as ServerName, DB_NAME() as DBName,* from cdc.dbo_pembelian_CT
select @@servername as ServerName, DB_NAME() as DBName,* from cdc.dbo_pembelian_CT ORDER BY __$start_lsn DESC
**//

.::: Backup & Restore database IBM-DB2 DB2 :::.

 1. Introduction
Backup and recovery methods are designed to keep our information safe. In Command Line Interface (CLI) or Graphical User Interface (GUI) using backup and recovery utilities you can take backup or restore the data of databases in DB2 UDB.

Logging
Log files consist of error logs, which are used to recover from application errors. The logs keep the record of changes in the database. There are two types of logging as described below:

Circular logging
It is a method where the old transaction logs are overwritten when there is a need to allocate a new transaction log file, thus erasing the sequences of log files and reusing them. You are permitted to take only full back-up in offline mode. i.e., the database must be offline to take the full backup.

Archive logging
This mode supports for Online Backup and database recovery using log files called roll forward recovery. The mode of backup can be changed from circular to archive by setting logretain or userexit to ON. For archive logging, backup setting database require a directory that is writable for DB2 process.
 

.::: insert Query to test Replica SQL Server from Publisher to Subscription :::.

 continue from http://teguhth.blogspot.com/2021/10/how-to-install-replica-sql-server-mssql.html

1. run query
-- run in primary/publisher
select @@SERVERNAME as ServerName,* from raju.dbo.pembelian;

-- run in subscription
select @@SERVERNAME as ServerName,* from raju_replica.dbo.pembelian

.::: Create user, change password, change role & drop user , create user with specification limited access PostgreSQL EDB :::.

1. Create user

su - postgres

create user admin;
ALTER ROLE admin SUPERUSER CREATEDB CREATEROLE REPLICATION;
ALTER ROLE admin PASSWORD 'admin';
ALTER USER admin WITH PASSWORD 'admin';

DROP user admin;

[root@teguhth ~]# su - postgres
Last login: Thu Oct 13 15:13:33 +07 2022 on pts/0
-bash-4.2$ psql
psql (9.2.24, server 14.5)
WARNING: psql version 9.2, server version 14.0.
         Some psql features might not work.
Type "help" for help.

postgres=#

postgres=# create user testuser;
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 admin     | Superuser, Create role, Create DB, Replication             | {}
 myuser    |                                                            | {}
 pgb       | Superuser                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 teguh     | Superuser, Create role, Create DB, Replication             | {}
 testuser  |                                                            | {}

.::: Query Check SQL Server Uptime MSSQL :::.

 1. Check

SELECT @@servername as servername,getdate() as TimeCollect,sqlserver_start_time, DATEDIFF(Day, sqlserver_start_time,getdate()) as sql_uptime_day ,
DATEDIFF(hour, sqlserver_start_time,getdate()) as sql_uptime_hours,DATEDIFF(minute, sqlserver_start_time,getdate()) as sql_uptime_minutes,
DATEDIFF(second, sqlserver_start_time,getdate()) as sql_uptime_second,DATEDIFF(millisecond, sqlserver_start_time,getdate()) as sql_uptime_milisecond
FROM sys.dm_os_sys_info;

.::: Install IBM DB2 In Windows :::.

 

.::: Install IBM DB2 In Linux Centos 7 / Redhat 7 & check version Db2 :::.

 
1. Extract * install DB2

[db2inst1@teguhth-grid ~]$ cd /data/
[db2inst1@teguhth-grid data]$ ls
db2v11.5.7_linuxx64_server_dec.tar  server_dec
[db2inst1@teguhth-grid data]$
[db2inst1@teguhth-grid data]$ ls server_dec/
db2  db2checkCOL_readme.txt  db2checkCOL.tar.gz  db2ckupgrade  db2_deinstall  db2_install  db2ls  db2prereqcheck  db2setup  installFixPack
[db2inst1@teguhth-grid data]$

[root@teguhth-grid server_dec]# ./db2setup

password root root

 

 

.::: Study Kasus Praktis belajar Query create & insert table IBM DB2 (Create Data Warehouse) :::.

1. create user db2 in linux

[root@teguhth-grid ~]# useradd teguh
[root@teguhth-grid ~]# passwd teguh
Changing password for user teguh.
New password:
BAD PASSWORD: The password is shorter than 8 characters
Retype new password:
passwd: all authentication tokens updated successfully.
[root@teguhth-grid ~]#

2. login to user db2 linux

[root@teguhth-grid ~]# su - db2inst1
Last login: Sun Oct  2 10:29:47 WIB 2022 on pts/0
[db2inst1@teguhth-grid ~]$ db2 grant connect on database to user teguh
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003
[db2inst1@teguhth-grid ~]$ 

[db2inst1@teguhth-grid ~]$ db2 connect to teguhth
[db2inst1@teguhth-grid ~]$ db2 grant connect on database to user teguh
[db2inst1@teguhth-grid ~]$ db2 grant dbadm on database to user teguh
 

.::: How to identify DB2 port number Service & connect IBM Data Studio include error :::.

1. check for windows
db2 get database manager configuration | findstr /i svce

C:\Program Files\IBM\SQLLIB\BIN>db2 get database manager configuration | findstr
 /i svce
 TCP/IP Service name                          (SVCENAME) = db2c_DB2
 SSL service name                         (SSL_SVCENAME) =

C:\Program Files\IBM\SQLLIB\BIN>


C:\Program Files\IBM\SQLLIB\BIN>db2 get dbm cfg | findstr "SVCENAME"
 TCP/IP Service name                          (SVCENAME) = db2c_DB2
 SSL service name                         (SSL_SVCENAME) =

C:\Program Files\IBM\SQLLIB\BIN>
C:\Program Files\IBM\SQLLIB\BIN>

C:\Program Files\IBM\SQLLIB\BIN>findstr "db2c_DB2" %systemroot%\system32\drivers
\etc\services
db2c_DB2        25000/tcp

C:\Program Files\IBM\SQLLIB\BIN>
 

.::: Archive table 6 months and 24 months SQL Server Using Copy import export write a query to specify the data to transfer :::.

A. Import using ssms
1. create table source

create table pembelian_source(
KODE_PEMBELIAN char(10),
KODE_BARANG char(6),KODE_CUSTOMER char(6),
TANGGAL_PEMBELIAN date,
JUMLAH_PEMBELIAN decimal(4),
primary key(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER),
foreign key(KODE_BARANG)references barang(KODE_BARANG),
foreign key(KODE_CUSTOMER)references customer(KODE_CUSTOMER));

2. create table destination
create table pembelian_dest(
KODE_PEMBELIAN char(10),
KODE_BARANG char(6),KODE_CUSTOMER char(6),
TANGGAL_PEMBELIAN date,
JUMLAH_PEMBELIAN decimal(4),
primary key(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER),
foreign key(KODE_BARANG)references barang(KODE_BARANG),
foreign key(KODE_CUSTOMER)references customer(KODE_CUSTOMER));
 

::: Enable Automate Startup/Shutdown of Oracle Database and Listener on Linux :::.

continue from
https://teguhth.blogspot.com/2021/11/install-database-oracle-19c-in-linux.html

1. check /etc/oratab

The Y on the end of the string signifies that the database can be started and stopped by the ORACLE_HOME/bin/dbstart and ORACLE_HOME/bin/dbshut scripts.

[oracle@teguhth bin]$ cat /etc/oratab
#


# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third field indicates
# to the dbstart utility that the database should , 'Y', or should not,
# 'N', be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
#tgh:/u01/app/oracle/product/19.0.0/dbhome_1:N
tgh:/u01/app/oracle/product/19.0.0/dbhome_1:Y
[oracle@teguhth bin]$
 

.::: List Index, size Index, unusage index, duplicate index & drop Index in SQL Server :::.

1. list index all

use [AdventureWorks2019]
go

select @@servername as ServerName, db_name() as [DBName],object_id, name from sys.indexes
where object_id in (select object_id from sys.objects )

 

2. size index all
 

.::: Load log file & insert into table in MariaDB MySQL (sample mariadb audit) :::.

continue from http://teguhth.blogspot.com/2022/08/enable-server-audit-in-mariadb-mysql.html

1.    Create table tbl_mariadb_audit
create database teguhthtools;
use teguhthtools;

CREATE TABLE `tbl_mariadb_audit`
(   `date` varchar(64) DEFAULT NULL,
    `host` varchar(64) DEFAULT NULL,
    `userdb` varchar(64) DEFAULT NULL,
    `client` varchar(64) DEFAULT NULL,
    `id` int,
    `connection_id` int NULL,
    `type` varchar(64) DEFAULT NULL,
    `db` varchar(64) DEFAULT NULL,
    `sqltext` varchar(64) DEFAULT NULL,
    `status` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

MariaDB [teguhthtools]> select * from teguhthtools.tbl_mariadb_audit;
Empty set (0.00 sec)

MariaDB [teguhthtools]>

.::: Enable server audit in MariaDB MySQL :::.

1.    Check plugin log
[root@teguhth data]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.44-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>
 

.::: Enable Slow Query Log in MariaDB MySQL :::.

1.    Configure using CLI. For testing . long_query_time using 0.0000000001 from default 10
direct command in mysql

MariaDB [(none)]> set global slow_query_log = 'ON'
MariaDB [(none)]> set global log_queries_not_using_indexes = 'ON';
MariaDB [(none)]> set global slow_query_log_file = 'teguh-slow.log';
MariaDB [(none)]> set global long_query_time = 0.000000000001;
MariaDB [(none)]> show variables like '%slow%';
MariaDB [(none)]> show variables like '%long%';
 

.::: Create Event Scheduler to run Query & List Store Procedure in MySQL, MariaDB :::.

 

1. refer link

https://teguhth.blogspot.com/2020/12/database-size-table-capacity-in-mysql.html
http://teguhth.blogspot.com/2022/08/create-store-procedure-for-database.html
https://teguhth.blogspot.com/2022/08/create-event-scheduler-to-run-query.htm

2. create event scheduler using existing store procedure

CREATE EVENT event_sp_database_size_info
    ON SCHEDULE EVERY '1' day
    STARTS '2022-08-01 02:00:00'
   do call sp_database_size_info ;

CREATE EVENT event_sp_table_size_info
   ON SCHEDULE EVERY '1' day
   STARTS '2022-08-01 02:00:00'
  do call sp_table_size_info;
 

 

.::: Create Store procedure for database & size capacity in MariaDB, MySQL :::.

 1. referlink

https://teguhth.blogspot.com/2020/12/database-size-table-capacity-in-mysql.html
http://teguhth.blogspot.com/2022/08/create-store-procedure-for-database.html
https://teguhth.blogspot.com/2022/08/create-event-scheduler-to-run-query.htm

 

A. Database size info
2. create store procedure for Database size capacity

 
use teguhthtools;

DELIMITER //
Create Procedure sp_database_size_info()

begin

insert into teguhthtools.tbl_dbsize
SELECT @@hostname as ServerName, current_timestamp() as DateTime,s.schema_name as SchemaName,
CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))/1024/1024,2),0.00)) TotalSizeMB,
CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00)) DataUsedMB,
CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00)) DataFreeMB  
FROM INFORMATION_SCHEMA.SCHEMATA s, INFORMATION_SCHEMA.TABLES t
WHERE s.schema_name = t.table_schema GROUP BY s.schema_name ORDER BY SchemaName ;


end //

DELIMITER ;
 

.::: Backup & restore Single Table, Database & All Database MariaDB, MySQL include single-transaction & skip-lock-tables include using Date Time file Name :::.

 database/table non encripted can restore to database encripted

but database/table encripted cannot restore to database non encripted
A. Backup Database
1. list database
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| dbatools           |
| information_schema |
| mysql              |
| performance_schema |
| teguhth            |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]>
 

.::: Create user admin, readonly, show priviledge database MySQL MariaDB & change password with all hostname & specific hostname :::.

1. Create user & database;

 

-- for access local
CREATE USER 'root'@'localhost' IDENTIFIED BY 'root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

-- for access remote
CREATE USER 'root'@'%' IDENTIFIED BY 'root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

ALTER USER 'root'@'%' IDENTIFIED BY 'root';
FLUSH PRIVILEGES;

-- for access local
CREATE USER 'teguh'@'%' IDENTIFIED BY 'teguhteguh';
GRANT ALL PRIVILEGES ON *.* TO 'teguh'@'%' WITH GRANT OPTION;

ALTER USER 'teguh'@'%' IDENTIFIED BY 'teguhteguh';
FLUSH PRIVILEGES;

-- for access remote
CREATE USER 'teguh'@'localhost' IDENTIFIED BY 'teguhteguh';
GRANT ALL PRIVILEGES ON *.* TO 'teguh'@'localhost' WITH GRANT OPTION;

ALTER USER 'teguh'@'localhost' IDENTIFIED BY 'teguhteguh';
FLUSH PRIVILEGES;

create database tgh;
create user 'teguh' identified by 'teguhteguh';
grant all privileges on *.* to 'teguh'@'%' identified by 'teguhteguh' with grant option;
FLUSH PRIVILEGES;
 
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

 
ALTER USER 'root'@'%' IDENTIFIED BY 'root';

grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;

UPDATE mysql.user SET host='%' WHERE user='root'; 

FLUSH PRIVILEGES;

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
ALTER USER 'admin'@'localhost' IDENTIFIED BY 'admin';
FLUSH PRIVILEGES;

CREATE USER 'admin'@'%' IDENTIFIED BY 'admin';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;
ALTER USER 'admin'@'%' IDENTIFIED BY 'admin';
FLUSH PRIVILEGES;

sample log

.::: Compare date and time and if statement in MySQL MariaDB, SQL Server :::.

A. Date and Time
-- MariaDB MySQL
select curdate() as tanggalskrng;
SELECT DAYNAME(curdate()) as haritanggal;
select date(NOW()) - interval 1 day as onedayagotanggal;
SELECT DAYNAME(date(NOW()) - interval 1 day) as harionedayagotanggal;
select current_timestamp() as dateandtimelast;
select date(current_timestamp()) as onlydatetimelast;
select DAYNAME(current_timestamp()) as haridateandtimelast;
 

.::: Install MySQL Dashboard and Collector in Grafana :::.

 B. Install MySQL Dashboard and Collector in Grafana

1. visit to collect Dasboard template (2mysql-simple-dashboard_rev6.json)
https://grafana.com/grafana/dashboards/?dataSource=mysql

2. Downlaod script
Before installing the dashboard, we need to set up a collector on our MySQL server. The collector script is downloaded from https://github.com/meob/my2Collector

wget https://codeload.github.com/meob/my2Collector/zip/refs/heads/master

.::: install and configure Grafana on CentOS 7 :::.

A. Install Grafana
1. Disable SELinux

2. Installing Grafana via YUM Repository

[root@nmslinux ~]# cat  /etc/yum.repos.d/grafana.repo
[grafana]
name=grafana
baseurl=https://packages.grafana.com/oss/rpm
repo_gpgcheck=1
enabled=1
gpgcheck=1
gpgkey=https://packages.grafana.com/gpg.key
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt
[root@nmslinux ~]#
 

.::: Install MySQL Router load balancer for Galera MariaDB MySQL :::.

1. Disable selinux & firewalld
[root@mysqlrouter data]# sestatus
SELinux status:                 disabled
[root@mysqlrouter data]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:firewalld(1)
[root@mysqlrouter data]#

 

.::: How to Install and Configure MaxScale for MariaDB :::.

1. Add into the MaxScale server MaxScale server the MariaDB repositories

[root@maxscale data]# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
# [info] Checking for script prerequisites.
# [info] MariaDB Server version 10.8 is valid
# [info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo
# [info] Adding trusted package signing keys...
/etc/pki/rpm-gpg /data
/data
# [info] Successfully added trusted package signing keys
# [info] Cleaning package cache...
Loaded plugins: fastestmirror
Cleaning repos: base extras mariadb-main mariadb-maxscale mariadb-tools updates
Cleaning up list of fastest mirrors
[root@maxscale data]#
 

.::: Install ProxySQL load balancer for Galera MariaDB MySQL :::.

 1. Disable selinux & firewalld
[root@proxysql ~]# sestatus
SELinux status:                 disabled
[root@proxysql ~]#
[root@proxysql ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:firewalld(1)
[root@proxysql ~]#

2. additional hosts
[root@proxysql ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.10.10.51 mariadb01
10.10.10.52 mariadb02
10.10.10.100 severnines
10.10.10.140 proxysql
[root@proxysql ~]#
 

.::: How to Enable Performance report performance-schema MySQL, MariaDB :::.

1. Open MySQL WorkBench Server -> Performance report
mysqld --verbose --help | grep performance-schema

2. enable performance_schema in /etc/my.cnf
[mysqld]
performance_schema
#performance_schema_events_waits_history_size=20
#performance_schema_events_waits_history_long_size=15000

.::: Install HAProxy Load Balancer to MariaDB MySQL :::.

 1. Preparation
https://teguhth.blogspot.com/2022/03/how-to-create-ha-high-availability.html
wsrep_cluster_name="cluster1" , 0.0.0.0:3306 -> detect ip loopback & statistic
mariadb01 mariadb02 haproxy
[root@mariadb01 ~]# cat /etc/my.cnf.d/server.cnf
#
......................
# * Galera-related settings
#
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.10.10.51,10.10.10.52"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name="cluster1"
wsrep_sst_method=rsync
wsrep_node_address= "10.10.10.51"
wsrep_node_name="mariadb01"

#wsrep_sst_auth=teguh:triharto
 

.::: SQL Server OFFSET FETCH :::.

select * from pembelian order by KODE_PEMBELIAN; select * from pembelian order by KODE_PEMBELIAN offset 5 rows;
select * from pembelian order by KODE_PEMBELIAN offset 5 rows FETCH NEXT 2 ROWS ONLY; select * from pembelian order by KODE_PEMBELIAN offset 0 rows FETCH NEXT 2 ROWS ONLY;

 

 

https://www.sqlservertutorial.net/sql-server-basics/sql-server-offset-fetch/ 

.::: Simple Archive data table in SQL :::.

 1. Main table will be archive  
USE [teguhth]
GO
select @@servername as ServerName, db_name() as DBName,* from pembelian -- original data from table
select @@servername as ServerName, db_name() as DBName,* from pembelian where TANGGAL_PEMBELIAN < '2020-07-02 00:00:00' -- original data will archive from table
 

.::: Simulation Execution Plan SQl Server & how to read :::.

1. query 1
USE [teguhth]
GO

Create Procedure [dbo].[SpBarang_pasok]

as
begin

select @@SERVERNAME as ServerName,
       @@servicename as ServiceName,
       DB_NAME() AS DBName,
       p.TANGGAL_PASOK,p.JUMLAH_PASOK
from  barang b,suplier s,pasok p
where
      b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER
order by TANGGAL_PASOK

end
GO
 

.::: Object ID, ObJect Name, Physical Index, database size, index size, drive size in MSSQL SQL Server :::.

274100017 = Person
2101582525 = EmployeeDepartmentHistory

1. List Object ID, Object Name, Table Name
SELECT  @@servername as ServerName,
        db_name() as [DBName],
        name as TableName,
        object_id,
        OBJECT_ID(name) AS [OBJECT_ID(name)]
FROM sys.objects
-- where  name = 'barang' or name = 'customer' or name = 'suplier' or
-- name = 'pasok' or name = 'pembelian' or name = 'sysdiagrams'
where  name in ('barang','customer','suplier','pasok','pembelian','sysdiagrams')
order by object_id

.::: How to list all objects of a particular database in SQL Server (List Object Name, Object ID) :::.

   SELECT @@servername as ServerName,db_name() as DbName, o.type_desc AS Object_Type
       ,  s.name AS Schema_Name
       ,  o.name AS Object_Name,object_id
       ,  o.name AS ObjectName
    FROM  sys.objects o
    JOIN  sys.schemas s
      ON  s.schema_id = o.schema_id
   WHERE  o.type NOT IN ('S'  --SYSTEM_TABLE
                        ,'PK' --PRIMARY_KEY_CONSTRAINT
                        ,'D'  --DEFAULT_CONSTRAINT
                        ,'C'  --CHECK_CONSTRAINT
                        ,'F'  --FOREIGN_KEY_CONSTRAINT
                        ,'IT' --INTERNAL_TABLE
                        ,'SQ' --SERVICE_QUEUE
                        ,'TR' --SQL_TRIGGER
                        ,'UQ' --UNIQUE_CONSTRAINT
                        )
ORDER BY  Object_Type
       ,  SCHEMA_NAME
       ,  Object_Name

 

.::: Install YugabyteDB in Linux (PostgreSQL Family) :::.

 1. Download the YugabyteDB package using the following wget command.

[root@yugabyte-teguhth data]# wget https://downloads.yugabyte.com/releases/2.11.2.0/yugabyte-2.11.2.0-b89-linux-x86_64.tar.gz

2. Extract the package and then change directories to the YugabyteDB home.

[root@yugabyte-teguhth data]# tar xvfz yugabyte-2.11.2.0-b89-linux-x86_64.tar.gz && cd yugabyte-2.11.2.0/

[root@yugabyte-teguhth yugabyte-2.11.2.0]# ls
bin  lib  linuxbrew  postgres  pylib  share  tools  version_metadata.json  www
[root@yugabyte-teguhth yugabyte-2.11.2.0]# ./bin/post_install.sh

.::: How to Create HA (High Availability) MariaDB/MySQL using Galera Cluster on Linux from Course (without password) Udemy :::.

A. Installation Galera Cluster (example on 2 node = mariadb01, mariadb02)
1. Disable selinux
[root@mariadb01 ~]# sestatus
SELinux status:                 disabled
[root@mariadb01 ~]# cat /etc/selinux/config

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected.
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

[root@mariadb01 ~]#
 

.::: Top 50 CPU, Disk IO Consuming Queries, Find Slow SQL Queries & History Query :::.

  -- >> query 11 sdh di coba . Top 50 CPU Consuming Queries in SQL Server

Select
     @@SERVERNAME as ServerName,
     @@servicename as ServiceName,
     DB_NAME(st.dbid) AS [Database Name],
     st.[text] AS [Query Text],          
     wt.last_execution_time AS [Last Execution Time],
     wt.execution_count AS [Execution Count],
     wt.total_worker_time/1000000 AS [Total CPU Time(second)],
     wt.total_worker_time/wt.execution_count/1000 AS [Average CPU Time(milisecond)],
     qp.query_plan

 

.::: Get CPU Utilization History for last 60 minutes,Get I/O Usage,Memory usage by Database,Memory Usage Query SQL Server :::.

-- Query Get CPU Utilization History for last 60 minutes SQL Server 

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

SELECT TOP(60) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
               SystemIdle AS [System Idle Process],
               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] , @@SERVERNAME as ServerName,@@servicename as ServiceName
FROM (
      SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
            AS [SystemIdle],
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
            'int')
            AS [SQLProcessUtilization], [timestamp]
      FROM (
            SELECT [timestamp], CONVERT(xml, record) AS [record]
            FROM sys.dm_os_ring_buffers
            WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE '%<SystemHealth>%') AS x
      ) AS y
ORDER BY record_id DESC;

.::: Top 10 Query SQL Server base on Top 10 Total CPU, Average CPU, Expensive Query, Execution count :::.

TOTAL_WORKER_TIME = in mocrosecond https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql?view=sql-server-ver16
-- Query 1 : Top 10 total CPU consuming queries >> sdh dicoba

SELECT TOP 10

       @@SERVERNAME as ServerName,

       @@servicename as ServiceName,

       DB_NAME(qp.dbid) AS database_name,

       QT.OBJECTID AS OBJECT_ID, 

       object_name(QT.OBJECTID) as ObjectName,

       QS.TOTAL_WORKER_TIME AS CPU_TIME,

       QT.TEXT AS STATEMENT_TEXT,

       QP.QUERY_PLAN

FROM SYS.DM_EXEC_QUERY_STATS QS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT (QS.SQL_HANDLE) AS QT

CROSS APPLY SYS.DM_EXEC_QUERY_PLAN (QS.PLAN_HANDLE) AS QP

-- where not DB_NAME(qp.dbid) = 'msdb' and not DB_NAME(qp.dbid) = 'master' and not DB_NAME(qp.dbid) = 'model' and not DB_NAME(qp.dbid) = 'tempdb'

-- where  OBJECT_ID is not  = 'null'

-- where DB_NAME(qp.dbid) = 'teguhth' and QT.OBJECTID is not null

-- where  QT.TEXT ='select * from pembelian'

where  QT.OBJECTID is not null

ORDER BY TOTAL_WORKER_TIME DESC

.::: How to identify and monitor unused indexes in SQL Server from sqlshack :::.

-- A. Finding unused indexes
-- 1. Query 1
-- A simple query that can be used to get the list of unused indexes in SQL Server (updated indexes not used in any seeks, scan or lookup operations) is as follows:

SELECT @@servername as ServerName,@@Servicename as ServiceName,db_name() as DBName,
    objects.name AS Table_name,
    indexes.name AS Index_name,
    dm_db_index_usage_stats.user_seeks,
    dm_db_index_usage_stats.user_scans,
    dm_db_index_usage_stats.user_updates
FROM

    sys.dm_db_index_usage_stats
    INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
    INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID
WHERE
    dm_db_index_usage_stats.user_lookups = 0
    AND
    dm_db_index_usage_stats.user_seeks = 0
    AND
    dm_db_index_usage_stats.user_scans = 0
ORDER BY
    dm_db_index_usage_stats.user_updates DESC

.::: Check Querying SQL Server Agent Job Information :::.

-- 1. SQL Server Agent Job Setup and Configuration Information
SELECT @@SERVICENAME as ServiceName,
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , [sDBP].[name] AS [JobOwner]
    , [sCAT].[name] AS [JobCategory]
    , [sJOB].[description] AS [JobDescription]
    , CASE [sJOB].[enabled]
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
      END AS [IsEnabled]
    , [sJOB].[date_created] AS [JobCreatedOn]
    , [sJOB].[date_modified] AS [JobLastModifiedOn]
    , [sSVR].[name] AS [OriginatingServerName]
    , [sJSTP].[step_id] AS [JobStartStepNo]
    , [sJSTP].[step_name] AS [JobStartStepName]
    , CASE
        WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
        ELSE 'Yes'
      END AS [IsScheduled]
    , [sSCH].[schedule_uid] AS [JobScheduleID]
    , [sSCH].[name] AS [JobScheduleName]
    , CASE [sJOB].[delete_level]
        WHEN 0 THEN 'Never'
        WHEN 1 THEN 'On Success'
        WHEN 2 THEN 'On Failure'
        WHEN 3 THEN 'On Completion'
      END AS [JobDeletionCriterion]

.::: Sample Query T-SQL Select Statements from AdventureWorks2019 :::.

SELECT ROW_NUMBER() OVER (ORDER BY T.Name) AS No,@@SERVERNAME as ServerName, @@servicename as ServiceName

use AdventureWorks2019
go

--- sample 1
SELECT ROW_NUMBER() OVER (ORDER BY [TotalDue]) AS No,@@SERVERNAME as ServerName, @@servicename as ServiceName,
CustomerID, SalesOrderID, [SubTotal], [TotalDue]
FROM Sales.SalesOrderHeader

--- sample 2
SELECT ROW_NUMBER() OVER (ORDER BY SalesOrderID) AS No,@@SERVERNAME as ServerName, @@servicename as ServiceName,
* FROM Sales.SalesOrderHeader
WHERE [OrderDate] BETWEEN '1/1/2000' AND '1/31/2023'
 

.::: Procedure Indexing SQL Server Using DBCC DBREINDEX Sample SalesOrderHeaderEnlarged in AdventureWorks2019 :::.

 
A. Precheck before indexing
1. Check Index fragmentation
SELECT @@servername as Server, DB_NAME(ips.database_id) AS DatabaseName,@@servicename as ServiceName,
       SCHEMA_NAME(ob.[schema_id]) SchemaNames,
       ob.[name] AS ObjectName,
       ix.[name] AS IndexName,
       ob.type_desc AS ObjectType,
       ix.type_desc AS IndexType,
       -- ips.partition_number AS PartitionNumber,
       ips.page_count AS [PageCount], -- Only Available in DETAILED Mode
       ips.record_count AS [RecordCount],
       ips.avg_fragmentation_in_percent AS AvgFragmentationInPercent
-- FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'DETAILED') ips
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'SAMPLED') ips -- QuickResult
INNER JOIN sys.indexes ix ON ips.[object_id] = ix.[object_id]
                AND ips.index_id = ix.index_id
INNER JOIN sys.objects ob ON ix.[object_id] = ob.[object_id]
WHERE ob.[type] IN('U','V')
AND ob.is_ms_shipped = 0
AND ix.[type] IN(1,2,3,4)
AND ix.is_disabled = 0
AND ix.is_hypothetical = 0
AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
AND ips.index_level = 0
and ob.[name] ='SalesOrderHeaderEnlarged'
-- AND ips.page_count >= 1000 -- Filter to check only table with over 1000 pages
-- AND ips.record_count >= 100 -- Filter to check only table with over 1000 rows
-- AND ips.database_id = DB_ID() -- Filter to check only current database
-- AND ips.avg_fragmentation_in_percent > 50 -- Filter to check over 50% indexes
-- ORDER BY DatabaseName
order by ips.avg_fragmentation_in_percent desc, DatabaseName asc

.::: Query index fragmentation level SQL Server with detail, specify Table, Database :::.

1. index fragmentation level

SELECT @@servername as Server, DB_NAME(ips.database_id) AS DatabaseName,
       SCHEMA_NAME(ob.[schema_id]) SchemaNames,
       ob.[name] AS ObjectName,
       ix.[name] AS IndexName,
       ob.type_desc AS ObjectType,
       ix.type_desc AS IndexType,
       -- ips.partition_number AS PartitionNumber,
       ips.page_count AS [PageCount], -- Only Available in DETAILED Mode
       ips.record_count AS [RecordCount],
       ips.avg_fragmentation_in_percent AS AvgFragmentationInPercent
-- FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'DETAILED') ips
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'SAMPLED') ips -- QuickResult
INNER JOIN sys.indexes ix ON ips.[object_id] = ix.[object_id]
                AND ips.index_id = ix.index_id
INNER JOIN sys.objects ob ON ix.[object_id] = ob.[object_id]
WHERE ob.[type] IN('U','V')
AND ob.is_ms_shipped = 0
AND ix.[type] IN(1,2,3,4)
AND ix.is_disabled = 0
AND ix.is_hypothetical = 0
AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
AND ips.index_level = 0
-- AND ips.page_count >= 1000 -- Filter to check only table with over 1000 pages
-- AND ips.record_count >= 100 -- Filter to check only table with over 1000 rows
-- AND ips.database_id = DB_ID() -- Filter to check only current database
-- AND ips.avg_fragmentation_in_percent > 50 -- Filter to check over 50% indexes
-- ORDER BY DatabaseName
order by ips.avg_fragmentation_in_percent desc, DatabaseName asc

.::: Query Check ServerName, Database Name, Database ID, Table ID, Object ID,Object Name, Size Table, master_files, size Database :::.

1. Info ServerName, Version, db_name
select @@SERVERNAME as ServerName,
       @@SERVICENAME as ServiceName,
       db_name() as DBName,
       @@VERSION as VersionDB,
       @@MICROSOFTVERSION as MICROSOFTVERSION,
       @@LANGUAGE as Language
 

.::: Create Store Procedure & Check Dependencies :::.

A. Sample Query 1 & Store Procedure 1
1. Sample 1
-- Query 1
select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang b,suplier s,pasok p where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;

2. Store Procedure 1

Create Procedure Barang_pasok

as
 begin
 select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from   barang b,suplier s,pasok p where b.KODE_BARANG=p.KODE_BARANG and  s.KODE_SUPLIER=p.KODE_SUPLIER;

end

.::: Insert Into table from Linkedserver to Linkedserver with Open Query & dbname(database_id) :::.

A. Linked Server (MSSQL)

1. Check Database ID on Linked Server
select @@Servername as LocalServer,@@servername as ServerName_Link, name as dbname,database_id  from sys.databases

2. Check Result Query on Linked Server
SELECT @@Servername as LocalServer, @@Servername as ServerName_Link,db_name('5')  as [DBName], * from teguhth.dbo.barang