Data file: Datafiles are physical files of the operating system that store the data of all logical structures in the database. They must be explicitly created for each tablespace. Oracle assigns each datafile two associated file numbers, an absolute file number and a relative file number, that are used to uniquely identify it. In Oracle, you can…

Read More

–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

Read More

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…

Read More

— 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…

Read More

A brief description of SQL Server’s CDC- Change Data Capture, or CDC for short, was initially introduced in the SQL Server 2008 edition as a useful feature to track and record changes made to SQL Server database tables without requiring extra programming work. Prior to SQL Server 2016, a SQL Server database could only have…

Read More

Introduction- In order to connect using SSMS or client apps, SQL Server offers Windows and SQL authentication methods. For DBAs, security is always the first priority, particularly in the production setting. We can manage database access by giving users the minimal and suitable permissions for their task. SQL Server’s provide and DENY statements can be…

Read More

Introduction- Normalization in SQL Server is a crucial concept in database design that aims to organize data efficiently, reduce redundancy, and maintain data integrity. It involves structuring relational databases to ensure that they meet certain criteria regarding the relationships between tables and the dependencies among their attributes. Normalization’s primary objectives are- Eliminating Redundancy- Redundant data…

Read More

Introduction- In SQL Server, the OFFSET command is used in conjunction with the ORDER BY clause to specify the number of rows to skip before starting to return rows from a query. This is particularly useful for implementing pagination in applications or when you need to skip a certain number of rows before fetching results.…

Read More

Introduction- A correlated subquery is a SQL query in which the values of the inner query (subquery) are derived from the outer query. Essentially, the subquery is conducted many times, one for each row processed by the outer query. This makes correlated subqueries very useful for complex data retrieval scenarios, but they can also be…

Read More