Author: Abhishek Sharma
-
SQL Loader
Hi guys, in this blog I am going to explain you about SQL Loader. SQL Loader- Oracle has a powerful application called SQL Loader, which is often referred to as SQL*Loader, that makes it easy to load data from external files into Oracle database tables. When dealing with enormous volumes of data that must be…
//
-
Performance Monitoring Related
Performance Monitoring Related- Session Related –Returns information about all the waits encountered by threads that executed for each session. select * from sys.dm_exec_session_wait_stats where session_id in (select session_id from sys.dm_exec_sessions where status not like ‘sleeping’ and session_id <> @@SPID –and session_id <> *** ) order by 3 desc –Returns information about running session. select * from sys.dm_exec_sessions where status not like ‘sleeping’ and session_id <> @@SPID –Find all queries waiting in the memory queue. SELECT * FROM sys.dm_exec_query_memory_grants WHERE grant_time IS NULL; — Retrieve every query plan from the plan cache SELECT * FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle); –sp_whoisactive –Active requests with memory grants SELECT…
//
-
Startup In Oracle Database
Summary: in this tutorial, you will learn how to use the Oracle STARTUP command to start an Oracle Database instance. To start up a database instance, you use the STARTUP command: When the Oracle Database starts an instance, it goes through the following stages: NOMOUNT, MOUNT, and OPEN The STARTUP command allows you to control the stage of the database instance. NOMOUNT stage In the NOMOUNT stage, Oracle…
//
-
Missing Indexes in a DB
–It runs at DB Instance Level. SELECT CONVERT (varchar(30), getdate(), 126) AS runtime, mig.index_group_handle, mid.index_handle, CONVERT (decimal (28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) ) AS improvement_measure, ‘CREATE INDEX missing_index_’ + CONVERT (varchar, mig.index_group_handle) + ‘_’ + CONVERT (varchar, mid.index_handle) + ‘ ON ‘ + mid.statement + ‘ (‘ + ISNULL (mid.equality_columns, ”) + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ‘,’ ELSE ” END + ISNULL (mid.inequality_columns, ”) + ‘)’ + ISNULL (‘ INCLUDE (‘ + mid.included_columns + ‘)’, ”) AS create_index_statement, migs.*, mid.database_id, mid.[object_id] FROM sys.dm_db_missing_index_groups mig INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle WHERE CONVERT (decimal (28, 1),migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10 ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
//
-
Transaction Space Utilization Info
use DB_Name select db_name(database_id) ‘db_name’, total_log_size_in_bytes/(1024*1024) ‘total_log_size_in_mb’,used_log_space_in_bytes/(1024*1024) ‘used_log_space_in_mb’,used_log_space_in_percent,log_space_in_bytes_since_last_backup/(1024*1024) ‘log_space_in_mb_since_last_backup’ from sys.dm_db_log_space_usage DBCC SQLPERF(LOGSPACE); –VLF Count info.. SELECT [name], COUNT(l.database_id) AS ‘vlf_count’ FROM sys.databases AS s CROSS APPLY sys.dm_db_log_info(s.database_id) AS l GROUP BY name –VLF Status Info use DB_Name ;WITH cte_vlf AS ( SELECT ROW_NUMBER() OVER(ORDER BY vlf_begin_offset) AS vlfid, DB_NAME(database_id) AS [Database Name], vlf_sequence_number, vlf_active, vlf_begin_offset, vlf_size_mb FROM sys.dm_db_log_info(DEFAULT)), cte_vlf_cnt AS (SELECT [Database Name], COUNT(vlf_sequence_number) AS vlf_count, (SELECT COUNT(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 0) AS vlf_count_inactive, (SELECT COUNT(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 1) AS vlf_count_active, (SELECT MIN(vlfid) FROM cte_vlf WHERE vlf_active = 1) AS ordinal_min_vlf_active, (SELECT MIN(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 1) AS min_vlf_active, (SELECT MAX(vlfid) FROM cte_vlf WHERE vlf_active = 1) AS ordinal_max_vlf_active, (SELECT MAX(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 1) AS max_vlf_active FROM cte_vlf GROUP BY [Database Name]) SELECT [Database Name], vlf_count, min_vlf_active, ordinal_min_vlf_active, max_vlf_active, ordinal_max_vlf_active, ((ordinal_min_vlf_active-1)*100.00/vlf_count) AS free_log_pct_before_active_log, ((ordinal_max_vlf_active-(ordinal_min_vlf_active-1))*100.00/vlf_count) AS active_log_pct, ((vlf_count-ordinal_max_vlf_active)*100.00/vlf_count) AS free_log_pct_after_active_log FROM cte_vlf_cnt; GO dbcc loginfo –VLF Info of T-Log declare @database_id int set @database_id = DB_ID(); select * from sys.dm_db_log_info ( @database_id ) DBCC OPENTRAN; SELECT name, log_reuse_wait, log_reuse_wait_desc, is_cdc_enabled FROM sys.databases WHERE name = ‘DB_Name’; –To find Big Tables List in SQL Server WITH TableSizes AS (SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS…
//
-
SQL Server Statistics
— Turn on/off statistics IO SET STATISTICS IO ON SET STATISTICS IO OFF — Turn on/off statistics TIMESET STATISTICS TIME ON SET STATISTICS TIME OFF — Turn on/off profile information. Note: It displays the profile information for a statement. STATISTICS PROFILE works for ad hoc queries, views, and stored procedures. SET STATISTICS PROFILE ON SET…
//
-
Top 10 performance Counters in Oracle Database19c
Monitoring performance counters is critical for optimizing Oracle Database performance. Here are ten significant performance counters (also called as performance metrics or statistics) to consider while analyzing an Oracle 19c database’s performance: Buffer Cache Hit Ratio- Indicates the efficiency of the buffer cache by measuring the ratio of cache hits to total requests. Query- SELECT…
//
-
DB Backup and Restore History
–DB Backup HistorySELECT CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,msdb.dbo.backupset.database_name,msdb.dbo.backupset.backup_start_date,msdb.dbo.backupset.backup_finish_date,msdb.dbo.backupset.expiration_date,CASE msdb..backupset.type WHEN ‘D’ THEN ‘Database’ WHEN ‘L’ THEN ‘Log’ WHEN ‘I’ THEN ‘Diff’END AS backup_type, msdb.dbo.backupset.backup_size, msdb.dbo.backupmediafamily.logical_device_name, msdb.dbo.backupmediafamily.physical_device_name, msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() – 30) and msdb.dbo.backupset.database_name like ‘DB_Name’ order by 3 desc —DB Restore History SELECT [rh].[destination_database_name], [rh].[restore_date], [b].[backup_start_date], [b].[backup_finish_date], [b].[type], [b].[first_lsn], [b].[last_lsn], [b].[checkpoint_lsn], [b].[database_backup_lsn] FROM [msdb].[dbo].[restorehistory] AS [rh] JOIN [msdb].[dbo].[backupset] AS [b] ON [rh].[backup_set_id] = [b].[backup_set_id] WHERE [rh].[destination_database_name] = ‘DB_Name’ ORDER BY [rh].[restore_date] DESC; ====================================================================================================================== —DB Backup –Full backup of DB_Name BACKUP DATABASE [DB_Name] TO DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_1.bak’, DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_2.bak’, DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_3.bak’, DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_4.bak’, DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_5.bak’, DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_6.bak’, DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_7.bak’, DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_8.bak’, DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_9.bak’, DISK…
//
-
AAOG Log Sync Analysis
SELECT r.replica_server_name AS Replica, DB_name(rs.database_id) ‘database_name’, rs.is_primary_replica IsPrimary, rs.last_received_lsn, rs.last_hardened_lsn, rs.last_redone_lsn, rs.end_of_log_lsn, rs.last_commit_lsn FROM sys.availability_replicas r INNER JOIN sys.dm_hadr_database_replica_states rs ON r.replica_id = rs.replica_id ORDER BY replica; select log_reuse_wait_desc from sys.databases where name like ‘DB_Name’ SELECT ar.replica_server_name as Replica, adc.database_name, drs.is_local, drs.is_primary_replica, drs.synchronization_state_desc, drs.is_commit_participant, drs.synchronization_health_desc, drs.recovery_lsn FROM sys.dm_hadr_database_replica_states AS drs INNER JOIN sys.availability_databases_cluster AS adc ON drs.group_id = adc.group_id AND drs.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id INNER JOIN sys.availability_replicas AS ar ON drs.group_id = ar.group_id AND drs.replica_id = ar.replica_id ORDER BY Replica; Secondary replica is lagging behind the Primary replica SELECT ag.name AS ag_name, ar.replica_server_name AS ag_replica_server, db_name(dr_state.database_id) AS database_name, is_ag_replica_local = CASE WHEN ar_state.is_local = 1 THEN N’LOCAL’ ELSE ‘REMOTE’…
//
Search
Categories
- Azure (7)
- About Us (1)
- Azure SQL Database (6)
- My SQL (53)
- About Us (1)
- DBMS Concepts (20)
- Handbook (1)
- MySQL Architecture (19)
- MySQL Basics (29)
- MySQL L1 (14)
- Oracle (34)
- PostgreSQL (37)
- About Us (1)
- DBMS Concepts (21)
- PG Hand Book (1)
- pg_ High Availability (4)
- pg_Backup (4)
- pg_basic (11)
- SQL Server (132)
- About Us (1)
- Azure SQL Database (4)
- Backup and Recovery (17)
- Backup Tools for SQL Server (3)
- DBA Worries Continued (27)
- DBMS Concepts (28)
- High Availability (12)
- Installation and Configuration (23)
- L1 DBA (59)
- Monitoring (17)
- Performance Tuning (29)
- Power Shell Script (6)
- Security (12)
- SQL DBA Hand Book (2)
- SQL Scripting (16)
- SQL Server Reporting Services (2)
- Uncategorized (3)