Saturday, July 25, 2020

How Find SQL Server collation

How to Find SQL Server Collation

Collations in SQL Server determine the sorting rules, case sensitivity, and accent sensitivity for your data. They play a crucial role when working with character data types like char and varchar, as they define the code page and the corresponding characters that can be represented.

Case Sensitivity

Case sensitivity refers to whether uppercase and lowercase characters are treated as distinct. For example, if the letters "A" and "a" (or "B" and "b") are considered different, the collation is case-sensitive. Computers differentiate between these letters using their ASCII values: "A" has an ASCII value of 65, while "a" has an ASCII value of 97. Similarly, "B" is 66, and "b" is 98.

Accent Sensitivity

Accent sensitivity determines whether characters with accents are considered different from their non-accented counterparts. For example, if "a" and "á" or "o" and "ó" are treated as the same, the collation is accent-insensitive. However, when treated differently, it is accent-sensitive. This distinction is based on the ASCII values of characters: "a" has a value of 97, while "á" has a value of 225; "o" is 111, and "ó" is 243.

Kana Sensitivity

Kana sensitivity occurs when Japanese kana characters, Hiragana and Katakana, are treated as distinct. When these characters are considered different in sorting, it is referred to as kana-sensitive collation.

Width Sensitivity

Width sensitivity refers to how single-byte characters (half-width) and their double-byte counterparts (full-width) are treated. When these characters are considered distinct, the collation is width-sensitive.

SQL Queries for Collation Insights

Here are a few SQL queries you can use to inspect and work with collations in SQL Server:

  1. View Columns with Collations:

    This query retrieves the schema, table, column names, and their respective collation names:

    
SELECT s.name AS 'Schema_Name', t.name AS Table_Name,
       c.name AS Column_Name, c.collation_name AS Collation
FROM sys.schemas s
INNER JOIN sys.tables t ON t.schema_id = s.schema_id
INNER JOIN sys.columns c ON c.object_id = t.object_id
WHERE collation_name IS NOT NULL
ORDER BY Column_Name;
  2. List All Supported Collations:

    You can use the following query to get a list of all supported collations:

    
SELECT name, description FROM fn_helpcollations();
  3. List All Databases with Collation Names:

    This query lists all databases along with their collation names:

    
SELECT name, collation_name FROM sys.databases;
  4. Find Server-Level Collation:

    To find the collation for the SQL Server instance, run the following query:

    
SELECT @@servername AS server_name, 
       SERVERPROPERTY('Collation') AS Collation;
  5. Comparing Different Collation Strings:

    If you need to compare columns with different collation settings, you can use the COLLATE clause as shown here:

    
SELECT *
FROM TABLE1
INNER JOIN TABLE2 
  ON TABLE1.Col1 COLLATE Latin1_General_CS_AS = 
     TABLE2.Col1 COLLATE Latin1_General_CS_AS;

These queries can help you manage and investigate collation settings, ensuring your database handles string data according to the desired rules for case sensitivity, accent sensitivity, kana sensitivity, and width sensitivity.

at
Labels:

Database property

SQL Server Database Properties and Status

In SQL Server, each row in the system catalog represents a database within the instance. Understanding the status and properties of these databases is crucial for database administrators.

Database Status and Visibility

If a database is not ONLINE, or if the AUTO_CLOSE option is enabled and the database is closed, certain column values might return as NULL. When a database is in the OFFLINE state, users with lower privileges will not see the corresponding row. To access the row of an offline database, users must have at least the ALTER ANY DATABASE permission on the server or CREATE DATABASE permissions in the master database.

Retrieving Database Information

To retrieve detailed information about the databases, you can use the following SQL query:


SELECT database_id,
       CONVERT(VARCHAR(25), DB.name) AS dbName,
       CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],
       state_desc,
       (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,
       (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],
       (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,
       (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],
       user_access_desc AS [User access],
       recovery_model_desc AS [Recovery model],
       CASE compatibility_level
         WHEN 60 THEN '60 (SQL Server 6.0)'
         WHEN 65 THEN '65 (SQL Server 6.5)'
         WHEN 70 THEN '70 (SQL Server 7.0)'
         WHEN 80 THEN '80 (SQL Server 2000)'
         WHEN 90 THEN '90 (SQL Server 2005)'
         WHEN 100 THEN '100 (SQL Server 2008)'
         WHEN 110 THEN '110 (SQL Server 2012)'
         WHEN 120 THEN '120 (SQL Server 2014)'
         WHEN 130 THEN '130 (SQL Server 2016)'
         WHEN 140 THEN '140 (SQL Server 2017)'
         WHEN 150 THEN '150 (SQL Server 2019)'
       END AS [compatibility level],
       CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],
       ISNULL((SELECT TOP 1
                 CASE TYPE WHEN 'D' THEN 'Full'
                           WHEN 'I' THEN 'Differential'
                           WHEN 'L' THEN 'Transaction log' END + ' – ' +
                 LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +
                 CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' +
                 CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +
                 CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' +
                 CONVERT(VARCHAR(20), backup_finish_date, 108) + ' (' +
                 CAST(DATEDIFF(second, BK.backup_start_date, BK.backup_finish_date) AS VARCHAR(4)) + ' seconds)'
                 FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC), '-') AS [Last backup],
       CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [fulltext],
       CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose],
       page_verify_option_desc AS [page verify option],
       CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [read only],
       CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink],
       CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto create statistics],
       CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto update statistics],
       CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [standby],
       CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE '' END AS [clean shutdown]
FROM sys.databases DB
ORDER BY DB.name;

This SQL query retrieves various details about each database, including:

  • Database name
  • Status (e.g., ONLINE, OFFLINE)
  • Number of data and log files
  • Size of data and log files
  • User access
  • Recovery model
  • Last backup time
  • Other configuration options like auto-shrink, read-only status, etc.

Recovery Model

The recovery model of a database determines how transactions are logged and whether the database can be restored to a point in time. The common recovery models are:

  • FULL: Full recovery model
  • BULK_LOGGED: Bulk-logged recovery model
  • SIMPLE: Simple recovery model

Database Status

Here are some common database statuses:

  • ONLINE: The database is available for queries.
  • OFFLINE: The database is explicitly taken offline.
  • RESTORING: The database is in the process of being restored.
  • RECOVERING: The database is recovering and not yet ready for queries.
  • SUSPECT: The database is in a state that cannot be recovered.

For more detailed information on system catalog views, refer to the official Microsoft documentation:
Sys Databases View - Microsoft Documentation

Additional Resources:

at

Re-assign the identity of all the tables.

Managing Identity Values in SQL: Script and Considerations

When the alignment of identity is crucial for your business logic, this script can be quite helpful. However, it's essential to understand the potential issues related to Lost Identity, particularly when frequently switching between clustered or replication environments.

SQL Script for Checking Identity Columns

The following SQL script helps manage identity columns by identifying when the last_value of an identity column has exceeded its seed_value, which is a common issue in replication or clustered environments.


DECLARE @CurrTable SYSNAME, @CurrCol SYSNAME, @LastValue BIGINT
DECLARE @CMD NVARCHAR(MAX), @Result NVARCHAR(MAX)
DECLARE Cur CURSOR LOCAL FAST_FORWARD FOR
SELECT QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + '.' + QUOTENAME(OBJECT_NAME(t.object_id)), c.name, CONVERT(int, c.last_value)
FROM sys.identity_columns AS c
INNER JOIN sys.tables AS t ON c.object_id = t.object_id
WHERE c.last_value > c.seed_value

OPEN Cur
FETCH NEXT FROM Cur INTO @CurrTable, @CurrCol, @LastValue

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @CMD = N'
        SELECT @pResult = N''DBCC CHECKIDENT(''''' + @CurrTable + N''''', RESEED, '' + CONVERT(nvarchar(max), MAX(' + QUOTENAME(@CurrCol) + N')) + N'') -- ' + CONVERT(nvarchar(max), @LastValue) + N''''
        FROM ' + @CurrTable + N'
        HAVING MAX(' + QUOTENAME(@CurrCol) + N') <> @LastValue'
    EXEC sp_executesql @CMD, N'@pResult NVARCHAR(MAX) OUTPUT, @LastValue BIGINT', @Result OUTPUT, @LastValue;
    IF @Result IS NOT NULL
        PRINT @Result;
    FETCH NEXT FROM Cur INTO @CurrTable, @CurrCol, @LastValue
END

CLOSE Cur
DEALLOCATE Cur

Key Considerations

  • Identity columns are often used for automatically incrementing values in tables. However, in environments where data is replicated or clustered, the identity value might get out of sync.
  • This script checks for instances where the last_value is greater than the seed_value, indicating that there might be gaps or inconsistencies in the identity values. It ensures the identity column's consistency by reseeding it to the correct value.

Additional References

  1. How to Reset Identity Value - Himanshu Patel
  2. Identity Columns - Himanshu Patel
  3. Microsoft Documentation: sys.identity_columns

This script and guidelines help you maintain the integrity of identity columns, particularly in environments prone to data replication or clustering challenges.

at
Labels:

View server roles and permissions per Login

Understanding Server Principals

The server_principals table stores a row for each server-level principal. This table holds details like the principal’s name, type, and whether the account is disabled. Additionally, it tracks the date the account was created and last modified.

Example Query to Retrieve Server Principals

Here is a sample SQL query to gather detailed information about server principals:


SELECT 
    sp.[Name] AS ServerPrincipal, 
    sp.[type_desc] AS LoginType, 
    CASE sp.is_disabled 
        WHEN 0 THEN 'No' 
        WHEN 1 THEN 'Yes' 
    END AS UserDisabled, 
    sp.create_date AS DateCreated, 
    sp.modify_date AS DateModified, 
    sp.default_database_name AS DefaultDB, 
    sp.default_language_name AS DefaultLang, 
    ISNULL(STUFF((
        SELECT ',' + 
            CASE ssp22.[Name] 
                WHEN 'sysadmin' THEN ssp22.[Name] + ' "Full privileges"' 
                ELSE ssp22.[Name] 
            END
        FROM [sys].server_principals ssp2
        INNER JOIN [sys].server_role_members ssrm2 ON ssp2.principal_id = ssrm2.member_principal_id
        INNER JOIN [sys].server_principals ssp22 ON ssrm2.role_principal_id = ssp22.principal_id
        WHERE ssp2.principal_id = sp.principal_id
        ORDER BY ssp2.[Name]
        FOR XML PATH (''), TYPE
    ).value('.[1]', 'nvarchar(max)'), 1, 1, ''), 'No Roles Held') AS ListofServerRoles,
    ISNULL(STUFF((
        SELECT ';' + ' Permission [' + sspm3.[permission_name] + '] is [' + 
            CASE 
                WHEN sspm3.[state_desc] = 'GRANT' THEN 'Granted]' 
                WHEN sspm3.[state_desc] = 'DENY' THEN 'Denied]' 
            END AS PermGrants
        FROM [sys].server_principals ssp3
        INNER JOIN [sys].server_permissions sspm3 ON ssp3.principal_id = sspm3.[grantee_principal_id]
        WHERE sspm3.[class] = 100 AND sspm3.[grantee_principal_id] = sp.principal_id
        FOR XML PATH (''), TYPE
    ).value('.[1]', 'nvarchar(max)'), 1, 1, ''), 'No Server Permissions') + ' in Server::' + @ServerName AS PermGrants
FROM [sys].server_principals sp
WHERE sp.[Type] IN ('S', 'G', 'U') AND sp.[Name] NOT LIKE '##%##'
ORDER BY ServerPrincipal;

This query retrieves the server principal name, its type, its roles, and associated permissions.

Additional Useful Queries

Built-In Permissions Hierarchy

To retrieve a list of the built-in permissions available for both servers and databases, you can use the following queries:


SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('DATABASE') ORDER BY permission_name;

Login and Server Role Mapping

To view the mapping of logins to their respective server roles, run this query:


SELECT spU.name,
       MAX(CASE WHEN srm.role_principal_id = 3 THEN 1 END) AS sysadmin,
       MAX(CASE WHEN srm.role_principal_id = 4 THEN 1 END) AS securityadmin,
       MAX(CASE WHEN srm.role_principal_id = 5 THEN 1 END) AS serveradmin,
       MAX(CASE WHEN srm.role_principal_id = 6 THEN 1 END) AS setupadmin,
       MAX(CASE WHEN srm.role_principal_id = 7 THEN 1 END) AS processadmin,
       MAX(CASE WHEN srm.role_principal_id = 8 THEN 1 END) AS diskadmin,
       MAX(CASE WHEN srm.role_principal_id = 9 THEN 1 END) AS dbcreator,
       MAX(CASE WHEN srm.role_principal_id = 10 THEN 1 END) AS bulkadmin
FROM sys.server_principals AS spR
JOIN sys.server_role_members AS srm ON spR.principal_id = srm.role_principal_id
JOIN sys.server_principals AS spU ON srm.member_principal_id = spU.principal_id
WHERE spR.[type] = 'R'
GROUP BY spU.name;

This query will list the users along with their assigned roles, such as sysadmin, securityadmin, and others.

Orphaned Database User Discovery

To check for orphaned database users and their login mappings, you can use:


exec sp_change_users_login @Action='Report';

This command helps identify orphaned users in your database.

For more details, you can refer to the official documentation here.

These SQL queries and examples provide a comprehensive way to manage and query server-level roles and permissions in SQL Server.

at

Tables dependency order

Understanding Dependency Listings in SQL Server

When managing SQL Server databases, identifying the dependencies between objects like tables, views, and stored procedures can be crucial for maintenance and troubleshooting. There are several ways to list these dependencies, but one simple method involves using a recursive query that joins tables and foreign keys.

SQL Query for Listing Dependencies

Here’s an SQL query that can help list table dependencies:

;
WITH a AS 
(
    SELECT 
        0 AS lvl, t.OBJECT_ID AS tblID 
    FROM [sys].tables t
    WHERE t.is_ms_shipped = 0
      AND t.OBJECT_ID NOT IN 
          (SELECT f.referenced_object_id FROM [sys].foreign_keys f)
    UNION ALL
    SELECT 
        a.lvl + 1 AS lvl, f.referenced_object_id AS tblId
    FROM a
    INNER JOIN [sys].foreign_keys f 
        ON a.tblId = f.parent_object_id 
        AND a.tblID != f.referenced_object_id
)
SELECT 
    OBJECT_SCHEMA_NAME(tblID) [schema_name],  
    OBJECT_NAME(tblId) [table_name], a.lvl
FROM a
GROUP BY tblId, a.lvl 
ORDER BY MAX(lvl), 1;

This query recursively tracks the relationships between tables using foreign keys, returning a hierarchical view of the dependency levels.

Using the sp_MSdependencies Stored Procedure

SQL Server provides a system stored procedure, sp_MSdependencies, that is particularly useful for checking the dependencies of specific objects, such as tables or views.

Here is how you can use it:

EXEC sp_MSdependencies N'Sales.Customer'

Parameters for sp_MSdependencies

  • name: The name of the object (e.g., a table or view).
  • type: A numeric value representing the type of object. Some common types include:
    • 0 (1 - UDF)
    • 3 (8 - User Table)
    • 4 (16 - Procedure)
    • 8 (256 - Trigger)
    • 12 (1024 - UDDT)

Additionally, you can specify flags that control the output:

  • 0x10000: Return multiple parent/child rows per object.
  • 0x20000: Descending return order.
  • 0x40000: Return children instead of parents.
  • 0x80000: Include the input object in the output.
  • 0x100000: Return only immediate parents/children.

This stored procedure is a powerful way to see which objects depend on a specific table, procedure, or other SQL Server objects.

Visualizing Dependencies

Here is a visual representation of the SQL Server object dependency hierarchy:

SQL Server Object Dependency

Additional Resources

To explore more on SQL Server topics, here are some useful articles:

This approach to listing dependencies helps in understanding the relationships between various SQL Server objects and is particularly useful when performing database analysis or maintenance.

at

Tables with Identity columns

How to Find Tables Using Identity Columns in SQL Server

If you're working with SQL Server and need to identify which tables use identity columns, you can accomplish this using T-SQL. An identity column is a column that automatically generates numeric values when new rows are inserted into the table.

Here's a T-SQL query to help you find the tables that contain identity columns:

SELECT [Table] = s.[Name] + N'.' + t.[Name]
FROM [sys].tables t
JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
    SELECT * FROM [sys].identity_columns i
    WHERE i.[object_id] = t.[object_id]
);
-- You can easily find tables without identity columns by using NOT EXISTS in the WHERE clause.

Listing All Identity Columns

To list all the identity columns in your database, you can use the following query:

SELECT COLUMN_NAME, TABLE_NAME, *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1;
-- You can add 'ORDER BY TABLE_NAME' to sort the results.

This query will return all the identity columns along with their corresponding table names.

Viewing Details About Identity Columns

If you'd like to get more detailed information about the identity columns, such as the seed value, increment value, last generated value, and replication settings, you can use this query:

SELECT OBJECT_NAME(object_id) AS [object], name,
  seed_value,
  increment_value,
  last_value,
  is_not_for_replication
FROM sys.identity_columns;

This query provides essential information like how the identity column is incremented, the last value generated, and whether the identity column is configured for replication.

For further details on identity columns, check out this link.

at

Tables with at least one LOB (max) column

 

Retrieving Tables with Maximum Column Lengths

In SQL Server, if you're looking to get a list of tables that have columns with the maximum length, you can use the following query:

SELECT [Table] = s.[Name] + N'.' + t.[Name]
FROM [sys].tables t
JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
    SELECT 1 
    FROM [sys].columns c
    WHERE c.[object_id] = t.[object_id]
    AND c.max_length = -1
    AND c.system_type_id IN
    (
        165, -- varbinary
        167, -- varchar
        231  -- nvarchar
    )
);

This SQL query returns a list of tables where the columns are defined with a maximum length. It checks the system columns for types like varbinary, varchar, and nvarchar, ensuring that the column length is set to its maximum possible value.

Additional Resources

For further details on SQL Server table and row counts, you can refer to this helpful post on Himanshu Patel's blog.

at

Tables with more than 30 columns (wide tables)

Query to Identify Tables with More Than 30 Columns in SQL Server

If you're working with a large SQL Server database, it's often helpful to analyze the structure of your tables, particularly when dealing with tables that contain a significant number of columns. This SQL query will help you find all tables that have more than 30 columns.

SQL Query:


DECLARE @threshold INT;
SET @threshold = 30;

WITH cte AS
(
    SELECT [object_id], COUNT(*) [Columns]
    FROM sys.columns
    GROUP BY [object_id]
    HAVING COUNT(*) > @threshold
)
SELECT 
    s.[name] + N'.' + t.[name] [Table],
    c.[Columns]
FROM cte c
INNER JOIN sys.tables t ON c.[object_id] = t.[object_id]
INNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
ORDER BY c.[Columns] DESC;

This query performs the following tasks:

  1. It defines a threshold (@threshold) set to 30, which filters tables based on the number of columns they contain.
  2. It then uses a common table expression (CTE) to count the number of columns per table and returns the tables that exceed this threshold.
  3. Finally, it joins the relevant system tables (sys.tables, sys.columns, sys.schemas) to list the tables and their respective column counts, ordered by the column count in descending order.

Additional Resources:

These articles offer additional insights and queries that can help you manage and optimize your SQL Server databases effectively.

at

Query statistics for a single table

SQL Server Statistics Overview

sys.stats: This contains a row for each statistics object related to tables, indexes, and indexed views in a SQL Server database.

sys.dm_db_stats_properties: This returns details about statistics for a given database object (either a table or an indexed view) in the current SQL Server database.

Query to Check Statistics Information

Use the following query to retrieve statistics details for a specific table or indexed view:

-- Execute the query below to inspect statistics
SELECT sp.stats_id, 
       name, 
       filter_definition, 
       last_updated, 
       rows, 
       rows_sampled, 
       steps, 
       unfiltered_rows, 
       modification_counter
FROM sys.stats AS stat
     CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = OBJECT_ID('HumanResources.Employee');

Explanation of Columns:

  • Stats_ID: The unique ID for the statistics object.
  • Name: The name of the statistics object.
  • Last_updated: The timestamp of when the statistics were last updated.
  • Rows: The total number of rows at the time of the last update.
  • Rows_sampled: The number of rows sampled for generating the statistics.
  • Unfiltered_rows: The count of rows in the table without any filters (it may be the same as rows_sampled if no filter is applied).
  • Modification_counter: This counts the number of changes made to the table since the last update to the statistics.
Table Statistics

Identify Auto-Created Statistics by SQL Server

To find statistics that were automatically generated by SQL Server, run this query:

-- Query to find auto-created statistics by SQL Server
SELECT sp.stats_id, 
       name, 
       filter_definition, 
       last_updated, 
       rows, 
       rows_sampled, 
       steps, 
       unfiltered_rows, 
       modification_counter
FROM sys.stats AS stat
     CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = OBJECT_ID('HumanResources.Employee')
AND name LIKE '_WA%';

Helpful Link

For more information on the sys.dm_db_stats_properties dynamic management view, check the official documentation:

sys.dm_db_stats_properties - Microsoft Docs

Example: Identify Tables with Modified Statistics

The query below retrieves a list of all tables, indexed views, and statistics in the current database where the leading column has been modified more than 1000 times since the last statistics update:

SELECT obj.name, obj.object_id, stat.name, stat.stats_id, last_updated, modification_counter  
FROM sys.objects AS obj   
INNER JOIN sys.stats AS stat ON stat.object_id = obj.object_id  
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp  
WHERE modification_counter > 1000;
at
Labels:

IO Statistics in SQL Server using Query

TSQL Latency and I/O Statistics Queries

1. Average Latency

To calculate the average read and write latency, you can use the following SQL query. It breaks down the latency by reads, writes, and transfers:

    -- Calculate Avg Latency
    SELECT 
        [ReadLatency] = CASE WHEN [num_of_reads] = 0 THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
        [WriteLatency] = CASE WHEN [num_of_writes] = 0 THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
        [Latency] = CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0) THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
        [AvgBPerRead] = CASE WHEN [num_of_reads] = 0 THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
        [AvgBPerWrite] = CASE WHEN [num_of_writes] = 0 THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
        [AvgBPerTransfer] = CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0) THEN 0 ELSE
            (([num_of_bytes_read] + [num_of_bytes_written]) / ([num_of_reads] + [num_of_writes])) END,
        LEFT([mf].[physical_name], 2) AS [Drive], 
        DB_NAME([vfs].[database_id]) AS [DB], 
        [mf].[physical_name]
    FROM 
        sys.dm_io_virtual_file_stats(NULL, NULL) AS [vfs]
    JOIN 
        sys.master_files AS [mf] ON [vfs].[database_id] = [mf].[database_id] AND [vfs].[file_id] = [mf].[file_id]
    ORDER BY 
        [WriteLatency] DESC;
Avg Latency

2. I/O Statistics by File for Current Database

This query helps you track I/O statistics by file for the current database, including read and write percentages and I/O stall breakdowns.

    -- Check I/O Statistics by file for the current database
    SELECT 
        DB_NAME(DB_ID()) AS [DB_Name], 
        DFS.name AS [Logical_Name], 
        DIVFS.[file_id], 
        DFS.physical_name AS [PH_Name], 
        DIVFS.num_of_reads, 
        DIVFS.num_of_writes, 
        DIVFS.io_stall_read_ms, 
        DIVFS.io_stall_write_ms,
        CAST(100. * DIVFS.io_stall_read_ms / (DIVFS.io_stall_read_ms + DIVFS.io_stall_write_ms) AS DECIMAL(10, 1)) AS [IO_Stall_Reads_Pct],
        CAST(100. * DIVFS.io_stall_write_ms / (DIVFS.io_stall_write_ms + DIVFS.io_stall_read_ms) AS DECIMAL(10, 1)) AS [IO_Stall_Writes_Pct],
        (DIVFS.num_of_reads + DIVFS.num_of_writes) AS [Writes + Reads], 
        CAST(DIVFS.num_of_bytes_read / 1048576.0 AS DECIMAL(10, 2)) AS [MB Read], 
        CAST(DIVFS.num_of_bytes_written / 1048576.0 AS DECIMAL(10, 2)) AS [MB Written],
        CAST(100. * DIVFS.num_of_reads / (DIVFS.num_of_reads + DIVFS.num_of_writes) AS DECIMAL(10, 1)) AS [# Reads Pct],
        CAST(100. * DIVFS.num_of_writes / (DIVFS.num_of_reads + DIVFS.num_of_writes) AS DECIMAL(10, 1)) AS [# Write Pct],
        CAST(100. * DIVFS.num_of_bytes_read / (DIVFS.num_of_bytes_read + DIVFS.num_of_bytes_written) AS DECIMAL(10, 1)) AS [Read Bytes Pct],
        CAST(100. * DIVFS.num_of_bytes_written / (DIVFS.num_of_bytes_read + DIVFS.num_of_bytes_written) AS DECIMAL(10, 1)) AS [Written Bytes Pct]
    FROM 
        sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS DIVFS
    INNER JOIN 
        sys.database_files AS DFS WITH (NOLOCK) ON DIVFS.[file_id] = DFS.[file_id];
I/O Statistics by File

3. I/O Utilization by Database

The following query shows the I/O statistics for each database in your system, helping you determine the total I/O in megabytes and its percentage of the system's total I/O.

    -- Check I/O utilization by database
    WITH AggregateIOStatistics AS
    (
        SELECT 
            DB_NAME(database_id) AS [DB Name],
            CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1048576 AS DECIMAL(12, 2)) AS io_in_mb
        FROM 
            sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
        GROUP BY 
            database_id
    )
    SELECT 
        ROW_NUMBER() OVER (ORDER BY io_in_mb DESC) AS [I/O Rank], 
        [DB Name], 
        io_in_mb AS [Total I/O (MB)],
        CAST(io_in_mb / SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5, 2)) AS [I/O Percent]
    FROM 
        AggregateIOStatistics
    ORDER BY 
        [I/O Rank];
I/O Statistics by DB

4. Drive Level Latency Analysis

Use this query to check the latency per disk drive. The levels of latency are categorized as follows:

  • Brilliant: < 1ms
  • Great: < 5ms
  • Good Quality: 5 – 10ms
  • Poor: 10 – 20ms
  • Horrific: 20 – 100ms
  • Disgracefully Bad: 100 – 500ms
  • WOW!: > 500ms
    -- Check Drive level latency
    SELECT 
        DISK_Drive,
        CASE WHEN DISK_num_of_reads = 0 THEN 0 ELSE (DISK_io_stall_read_ms / DISK_num_of_reads) END AS [Read Latency],
        CASE WHEN DISK_io_stall_write_ms = 0 THEN 0 ELSE (DISK_io_stall_write_ms / DISK_num_of_writes) END AS [Write Latency],
        CASE WHEN (DISK_num_of_reads = 0 AND DISK_num_of_writes = 0) THEN 0 ELSE (DISK_io_stall / (DISK_num_of_reads + DISK_num_of_writes)) END AS [Overall Latency],
        CASE WHEN DISK_num_of_reads = 0 THEN 0 ELSE (DISK_num_of_bytes_read / DISK_num_of_reads) END AS [Avg Bytes/Read],
        CASE WHEN DISK_io_stall_write_ms = 0 THEN 0 ELSE (DISK_num_of_bytes_written / DISK_num_of_writes) END AS [Avg Bytes/Write],
        CASE WHEN (DISK_num_of_reads = 0 AND DISK_num_of_writes = 0) THEN 0 ELSE ((DISK_num_of_bytes_read + DISK_num_of_bytes_written) / (DISK_num_of_reads + DISK_num_of_writes)) END AS [Avg Bytes/Transfer]
    FROM 
        (SELECT 
            LEFT(UPPER(mf.physical_name), 2) AS DISK_Drive, 
            SUM(num_of_reads) AS DISK_num_of_reads,
            SUM(io_stall_read_ms) AS DISK_io_st
at
Labels:

Database files information using query

Understanding Database Files in SQL Server

In SQL Server, the `database_files` view provides information about the files associated with a specific database. Each row represents a different file, including details like file type, size, and location. Below is a sample SQL query to retrieve information about database files:

                --- Query to retrieve database file details
                SELECT 
                    f.type_desc AS [File Type], 
                    f.name AS [File Name],
                    fg.name AS [File Group],
                    f.physical_name AS [File Path], 
                    f.size / 128.0 AS [Current Size (MB)], 
                    f.size / 128.0 - CONVERT(INT, FILEPROPERTY(f.name,'SpaceUsed')) / 128.0 AS [Free Space (MB)]
                FROM 
                    sys.database_files f WITH (NOLOCK) 
                LEFT OUTER JOIN 
                    sys.filegroups fg WITH (NOLOCK) ON f.data_space_id = fg.data_space_id
                OPTION (RECOMPILE);

SQL Server System Procedures

Another useful system procedure for viewing database file details is `sp_helpfile`. This can provide additional information about the files in a database.

                -- Display information about database files
                sp_helpfile;

Useful References

For more detailed information, check out the following Microsoft documentation:

Visual Overview

Here’s a visual representation of the SQL Server database files:

at

Backup History using TSQL

Using SQL to Retrieve Backup History Details

By querying system objects in SQL, you can retrieve detailed information about your backup history. This includes tables like Backupmediafamily and Backupset, which provide insight into backup sets and media families.

Understanding Key Tables:

  • Backupmediafamily: Stores details for each media family. If a media family exists in a mirrored media set, there will be a separate row for each mirror. This data is stored in the msdb database.
  • Backupset: Contains one row for each backup set. A backup set represents a successful backup operation, and statements like RESTORE, RESTORE FILELISTONLY, and RESTORE HEADERONLY act on a single backup set within the media set.

Find Backup History from the Last Day

If you're looking to retrieve backup history from the past 24 hours, use the following SQL query:

SELECT 
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
    bs.server_name, 
    BS.database_name, 
    BS.backup_start_date, 
    BS.backup_finish_date, 
    DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS 'Duration (sec)', 
    CASE BS.type  
        WHEN 'D' THEN 'Database'  
        WHEN 'L' THEN 'Log'  
        WHEN 'I' THEN 'Differential database'
        WHEN 'F' THEN 'File/Filegroup'   
        WHEN 'G' THEN 'Differential File'  
        WHEN 'P' THEN 'Partial'  
        WHEN 'Q' THEN 'Differential partial'   
    END AS backup_type, 
    ROUND(((bs.backup_size/1024)/1024),2) AS 'Backup Size (MB)',
    ROUND(((bs.compressed_backup_size/1024)/1024),2) AS 'Compressed Backup Size (MB)',
    BS.backup_size, 
    BMF.physical_device_name, 
    BS.name AS backupset_name, 
    bs.is_copy_only, 
    bs.compressed_backup_size, 
    bs.recovery_model, 
    bs.is_password_protected, 
    bs.last_lsn, 
    bs.compatibility_level, 
    bs.database_version
FROM msdb.dbo.backupmediafamily BMF
INNER JOIN msdb.dbo.backupset BS ON BMF.media_set_id = BS.media_set_id 
WHERE CONVERT(datetime, BS.backup_start_date, 102) >= GETDATE()-1 
ORDER BY BS.backup_finish_date DESC

Alternative Query with Different Columns

Here's a similar query that returns the backup history with alternative column names:

SELECT 
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
    bs.server_name, 
    BS.database_name, 
    BS.backup_start_date, 
    BS.backup_finish_date, 
    DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS 'Duration (min)', 
    CASE BS.type  
        WHEN 'D' THEN 'Database'  
        WHEN 'L' THEN 'Log'  
        WHEN 'I' THEN 'Differential database'
        WHEN 'F' THEN 'File/Filegroup'   
        WHEN 'G' THEN 'Differential File'  
        WHEN 'P' THEN 'Partial'  
        WHEN 'Q' THEN 'Differential partial'   
    END AS backup_type, 
    ROUND(((bs.backup_size/1024)/1024),2) AS 'Backup Size (MB)', 
    ROUND(((bs.compressed_backup_size/1024)/1024),2) AS 'Compressed Backup Size (MB)', 
    BS.backup_size, 
    BMF.physical_device_name, 
    BS.name AS backupset_name, 
    bs.is_copy_only, 
    bs.compressed_backup_size, 
    bs.recovery_model, 
    bs.is_password_protected, 
    bs.last_lsn, 
    bs.compatibility_level, 
    bs.database_version
FROM msdb.dbo.backupmediafamily BMF
INNER JOIN msdb.dbo.backupset BS ON BMF.media_set_id = BS.media_set_id 
WHERE CONVERT(datetime, BS.backup_start_date, 102) >= GETDATE()-1 
ORDER BY BS.backup_finish_date DESC

Backup History for Databases Without Backups

This query helps identify databases that haven't had any backups:

-- Database backup not taken
SELECT 
    S.NAME AS database_name,  
    'Nobackups' AS [Backup Age (Hours)]  
FROM master.dbo.sysdatabases S 
LEFT JOIN msdb.dbo.backupset B
    ON S.name = B.database_name 
WHERE B.database_name IS NULL 
    AND S.name <> 'tempdb'
ORDER BY B.database_name

Find Most Recent Full Backup for Each Database

To get a list of the most recent full backups for all databases, use this query:

-- To get a list of the most recent full backup of all the databases:
SELECT 
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
    msdb.dbo.backupset.database_name,  
    MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date 
FROM msdb.dbo.backupmediafamily  
INNER JOIN msdb.dbo.backupset 
    ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE msdb.dbo.backupset.type = 'D' 
GROUP BY msdb.dbo.backupset.database_name

Track Backup Progress Percentage

If you're monitoring a backup, this query shows the percentage completion:

-- Backup percentage complete
SELECT 
    query = a.text, 
    start_time, 
    percent_complete,
    eta = dateadd(second, estimated_completion_time/1000, getdate())
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command LIKE 'BACKUP%'

References:

For further details, refer to the official documentation:

at
Labels:

SSIS catalog database read

Understanding the SSISDB Catalog in SQL Server Integration Services (SSIS)

The SSISDB catalog serves as the central repository for managing and interacting with SSIS projects deployed to an Integration Services server. In this catalog, users can configure environments, manage parameters, execute and troubleshoot packages, and oversee server operations. The catalog is essential for running and managing Integration Services (SSIS) projects.

Key Components of the SSISDB Catalog

The SSISDB catalog contains various objects necessary for integration services management:

  • Projects: Contains all the SSIS projects that are deployed.
  • Packages: Includes details of all SSIS packages stored in the catalog.
  • Parameters: Defines project and package parameters.
  • Environments: Specifies runtime values for packages via environment configurations.
  • Operational History: Tracks the execution history of packages and projects.

Important SSISDB Catalog Views

1. Executions

The executions view displays details about the instances where packages have been executed within the catalog. When a package is executed using the Execute Package task, it runs within the same execution instance as the parent package.

2. Folders

The folders view lists all folders within the SSISDB catalog, which help organize projects and packages.

3. Packages

The packages view shows all the packages registered in the catalog.

4. Projects

The projects view contains details about each project in the catalog, including project names and metadata.

Common SQL Queries to Manage and Troubleshoot SSISDB

1. View Executions Details

This SQL query retrieves execution details for packages within the SSISDB catalog:

SELECT E.execution_id, E.folder_name, E.project_name, E.package_name, E.reference_id, 
       E.reference_type, E.environment_folder_name, E.environment_name, E.project_lsn, 
       E.executed_as_sid, E.executed_as_name, E.use32bitruntime, E.operation_type, 
       E.created_time, E.object_type, E.object_id, E.status, E.start_time, E.end_time, 
       E.caller_sid, E.caller_name, E.process_id, E.stopped_by_sid, E.stopped_by_name, 
       E.dump_id, E.server_name, E.machine_name, E.total_physical_memory_kb, 
       E.available_physical_memory_kb, E.total_page_file_kb, E.available_page_file_kb, 
       E.cpu_count
FROM SSISDB.catalog.executions AS E
INNER JOIN ssisdb.catalog.folders AS F ON F.name = E.folder_name
INNER JOIN SSISDB.catalog.projects AS P ON P.folder_id = F.folder_id AND P.name = E.project_name
INNER JOIN SSISDB.catalog.packages AS PKG ON PKG.project_id = P.project_id AND PKG.name = E.package_name;

2. Find Error Messages by Package

This query allows you to locate error messages associated with a specific package:

SELECT q.*
FROM (SELECT em.* 
      FROM SSISDB.catalog.event_messages em
      WHERE em.operation_id = (SELECT MAX(execution_id) FROM SSISDB.catalog.executions)
      AND event_name NOT LIKE '%Validate%') q
WHERE package_name = 'MainPackage.dtsx'
ORDER BY message_time DESC;

3. Find All Error Messages

This query fetches all error messages related to the operations:

SELECT OM.operation_message_id, OM.operation_id, OM.message_time, OM.message_type, 
       OM.message_source_type, OM.message, OM.extended_info_id
FROM catalog.operation_messages AS OM
WHERE OM.message_type = 120;

4. Find Messages Associated with Failing Operations

This query retrieves messages linked to failing operations:

SELECT OM.operation_message_id, OM.operation_id, OM.message_time, OM.message_type, 
       OM.message_source_type, OM.message, OM.extended_info_id
FROM catalog.operation_messages AS OM
INNER JOIN (
    SELECT DISTINCT OM.operation_id 
    FROM catalog.operation_messages AS OM
    WHERE OM.message_type = 120
) D ON D.operation_id = OM.operation_id;

5. Find Messages Associated with the Last Failing Run

This query retrieves messages related to the last failing operation:

SELECT OM.operation_message_id, OM.operation_id, OM.message_time, OM.message_type, 
       OM.message_source_type, OM.message, OM.extended_info_id
FROM catalog.operation_messages AS OM
WHERE OM.operation_id = 
    (SELECT MAX(OM.operation_id) FROM SSISDB.catalog.operation_messages AS OM
    WHERE OM.message_type = 120);

6. Send Email with Query Results

If you want to send the query result via email, you can use the following query:

DECLARE @profile_name sysname = 'SQLProfile',
        @recipients varchar(max) = 'recipient@example.com',
        @subject nvarchar(255) = 'Failed Package Alert',
        @body nvarchar(max) = 'An SSIS package has failed. Please check the logs.',
        @query nvarchar(max) = 'SELECT O.object_name AS FailingPackageName, ...'
EXECUTE msdb.dbo.sp_send_dbmail @profile_name, @recipients, @subject, @body, @query;

References

at
Labels:

How to calculate Easter date

In this example, we'll explore the usage of Date and Integer data types in SQL Server. The code snippets demonstrate how to manipulate and extract date-related information.

1. Sample Code: Calculating Easter Date for a Given Year

Below is a SQL script to calculate the Easter date for a given year:

DECLARE @Date DATE, @c INT, @n INT, @i INT, @k INT, @j INT, @l INT, @m INT, @d INT, @Year INT = 2025
SET @n = @Year - 19 * (@Year / 19)
SET @c = @Year / 100
SET @k = (@c - 17) / 25
SET @i = @c - @c / 4 - (@c - @k) / 3 + 19 * @n + 15
SET @i = @i - 30 * (@i / 30)
SET @i = @i - (@i / 28) * (1 - (@i / 28) * (29 / (@i + 1)) * ((21 - @n) / 11))
SET @j = @Year + @Year / 4 + @i + 2 - @c + @c / 4
SET @j = @j - 7 * (@j / 7)
SET @l = @i - @j
SET @m = 3 + (@l + 40) / 44
SET @d = @l + 28 - 31 * (@m / 4)
SELECT EasterDate = CAST(@Year AS VARCHAR) + '-' + CAST(@m AS VARCHAR) + '-' + CAST(@d AS VARCHAR)

2. Retrieve Date Information Using SQL Server Functions

The following SQL queries return various date components such as the year, week, month, and the current date and time:

-- Retrieve various components of the current date
SELECT DATENAME(year, GETDATE()) AS Year,
       DATENAME(week, GETDATE()) AS Week,
       DATENAME(dayofyear, GETDATE()) AS DayOfYear,
       DATENAME(month, GETDATE()) AS Month,
       DATENAME(day, GETDATE()) AS Day,
       DATENAME(weekday, GETDATE()) AS Weekday;

-- Higher precision datetime functions
SELECT SYSDATETIME() AS 'DateAndTime',       -- returns datetime2(7)
       SYSDATETIMEOFFSET() AS 'DateAndTime+Offset', -- datetimeoffset(7)
       SYSUTCDATETIME() AS 'DateAndTimeInUtc';  -- returns datetime2(7)

-- Lesser precision datetime functions
SELECT CURRENT_TIMESTAMP AS 'DateAndTime',  -- note: no parentheses
       GETDATE() AS 'DateAndTime',
       GETUTCDATE() AS 'DateAndTimeUtc';

3. Example of Date Output

Here’s an example of a date output in SQL Server:

Use of date function in SQL Server

References

For further details and in-depth exploration, check out the original article on the Simple Talk Website.

at No comments:
Labels:

SQL Server Q/A (high availability)

 

SQL Server Interview Questions for SQL DBAs

What is a Listener?

A Listener (sometimes referred to as the Virtual Network Name [VNN]) functions similarly to the network name and IP addresses created when setting up a Failover Cluster Instance (FCI). Instead of directly connecting to the nodes, you connect to the Listener’s name or IP. This simplifies connectivity, allowing applications and end-users to only focus on a single entry point.

Is there one Listener per Windows Server Failover Cluster (WSFC)?

No, a Listener is dedicated to a single Availability Group (AG). Different AGs cannot share a Listener. However, a single WSFC can have multiple Listeners, each one tied to a different AG.

Do Availability Groups (AGs) require a WSFC?

Yes, AGs are dependent on a Windows Server Failover Cluster (WSFC) for their operation.

Why do I need a WSFC when Database Mirroring (DBM) didn’t?

The WSFC serves two main purposes: quorum and the Listener. With DBM, the Witness instance handled quorum manually, while AGs use the built-in, reliable quorum mechanism of the operating system. More on the Listener in the next questions.

What edition of Windows do I need for WSFC?

To deploy a WSFC, you’ll need the Enterprise Edition of Windows Server. While the cost of the Enterprise Edition might be higher than other editions (like Standard), SQL Server deployment costs tend to outweigh Windows licensing costs.

Do all my WSFC nodes need to be part of the same domain?

Yes, all nodes in a WSFC must be in the same domain. This can be a major change if you are migrating from setups like log shipping or DBM where instances may not be in the same domain.

Does a WSFC require shared storage?

No. Unlike FCIs, AGs do not require shared storage. You can deploy AGs on standalone SQL Server installations without the need for shared storage. FCIs do require shared storage, though, and combining FCIs with AGs would require shared storage as well.

Does the WSFC have to be on physical hardware?

No. You can run the entire WSFC on virtual machines (VMs), as long as the setup meets the supportability requirements outlined in official SQL Server documentation.

Do I need Active Directory (AD) for a WSFC?

Yes. AD is necessary for a WSFC, as it manages cluster objects and ensures integration with DNS. Not all organizations use AD, especially those that primarily run non-Microsoft software, but it is crucial for the functionality of a WSFC.

Do I need physical hardware to implement AGs?

No. AGs can be implemented on virtual machines as long as the WSFC setup is supported by SQL Server and meets all the requirements.

Can I upgrade from DBM or Log Shipping to an AG?

Yes, it’s possible to migrate from DBM or log shipping to an AG. You can create the WSFC after SQL Server is installed, even during an upgrade (e.g., from SQL Server 2005/2008 to 2012). Just ensure your underlying configuration is compatible with WSFC requirements.

Can I put AD on one of my cluster nodes if I don’t have AD elsewhere?

No. A cluster node cannot also be a domain controller. SQL Server flags this during setup, and it is unsupported.

What edition of SQL Server is required to deploy an AG?

You need SQL Server 2012 Enterprise Edition to implement an AG. Unlike DBM, there’s no “limited” version of AGs available in the Standard Edition.

Can I deploy an AG using Windows Server Core?

Yes, AGs are supported on Windows Server Core as well as full UI setups. However, with Windows Server 2008 R2 SP1, you must choose between Server Core and full UI; mixing them is not supported.

Is planning for and deploying an AG easy?

It depends. Some setups are straightforward, while others can be more complex. There may be challenges to consider when planning your architecture, but having a solid understanding of both SQL Server and Windows Failover Clusters will help.

How important is quorum in an AG setup?

Quorum is essential for AGs. As a DBA, it’s important to understand the concept of quorum and how it affects failover and cluster health. If quorum is lost, your entire cluster will be down, regardless of how many instances of SQL Server are running.

Can I use secondary replicas for read-only queries or backups?

Yes. You can configure your replicas for read-only queries and backups without extra configuration. Backups made from replicas must use the COPY_ONLY option.

How do readable replicas work?

Readable replicas use snapshot isolation to minimize blocking for write transactions. It’s crucial to have sufficient resources and disk I/O to handle read-only queries effectively on these replicas.

Will AGs fix performance issues related to synchronous mirroring from DBM?

If you’ve faced performance issues with DBM, especially around synchronous mirroring, AGs may not solve the problem. Proper architecture is essential to handle high I/O and network throughput in both DBM and AG setups.

Can I combine FCIs and AGs in a synchronous setup?

Yes, but automatic failover isn’t possible with this combination. You can have FCIs paired with AGs, though there are additional considerations regarding storage and quorum.

Can an AG have multiple databases?

Yes, a single AG can include multiple databases, just like a folder containing files. This allows better management and failover of related databases.

Can databases in an AG span multiple instances?

No. All databases within a single AG must reside in the same instance. You cannot place a database from Instance_1 and Instance_2 into the same AG.

Are distributed transactions supported in an AG?

No. Distributed transactions, like in DBM and log shipping, are not supported in AGs.

Are databases in an AG kept in sync?

Not exactly. While the databases in an AG will fail over together, each database's replication or mirroring process operates independently. You’ll need to monitor their replication status individually.

Can a database participate in multiple AGs?

No. A database can only belong to one AG at a time.

Do I need to worry about logins and SQL Server Agent jobs with AGs?

Yes. AGs only protect databases, so you still need to plan for logins, SQL Server Agent jobs, and other external configurations. Using contained databases in SQL Server 2012 can help address some login concerns.

How will AGs affect my SQL Server licensing?

Licensing will depend on your specific configuration. It's important to consult with your SQL Server licensing representative or Microsoft directly for guidance on how AGs will impact your costs.

Is HADRON the same as an AG?

HADRON was an early codename for Availability Groups. It may also be referred to as HADR (High Availability Disaster Recovery), especially in DMVs.

Is AlwaysOn the same as an AG?

No, AlwaysOn is a term that covers both FCIs and AGs in SQL Server 2012. It’s not exclusive to AGs, although it was initially used for that purpose during development.

Do AGs replace FCIs?

No, AGs and FCIs serve different purposes, and one does not replace the other. For more details, check out our earlier discussions on this topic.

Is Database Mirroring (DBM) going away?

Yes, DBM has been deprecated in SQL Server 2012. However, it’s not disappearing immediately—existing DBM setups will still function, but new implementations should focus on AGs.

at

Server OS information

Finding Information About SQL Server Installation Using T-SQL

If you need to gather information about where SQL Server is installed, you can use the following T-SQL query. This script will provide details such as the server instance name, version, authentication mode, and more.


-- Declare a variable to get the SQL Server version
-- declare @version varchar(4)
-- select @version = substring(@@version,22,4)
-- select @@version
-- If SQL version is greater than or equal to 2012, continue

SELECT 
    SERVERPROPERTY('ServerName') AS [Instance Name],
    CASE LEFT(CONVERT(VARCHAR, SERVERPROPERTY('ProductVersion')), 4) 
        WHEN '11.0' THEN 'SQL Server 2012'
        WHEN '12.0' THEN 'SQL Server 2014'
        ELSE 'Newer than SQL Server 2014'
    END AS [Version Build],
    SERVERPROPERTY('Edition') AS [Edition],
    SERVERPROPERTY('ProductLevel') AS [Service Pack],
    CASE SERVERPROPERTY('IsIntegratedSecurityOnly') 
        WHEN 0 THEN 'SQL Server and Windows Authentication mode'
        WHEN 1 THEN 'Windows Authentication mode'
    END AS [Server Authentication],
    CASE SERVERPROPERTY('IsClustered') 
        WHEN 0 THEN 'False'
        WHEN 1 THEN 'True'
    END AS [Is Clustered?],
    SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [Current Node Name],
    SERVERPROPERTY('Collation') AS [SQL Collation],
    [cpu_count] AS [CPUs],
    [physical_memory_kb] / 1024 AS [RAM (MB)]
FROM 
    [sys].[dm_os_sys_info];

Example Output:


Version Build            Edition           Instance Name      Service Pack  Server Authentication  Is Clustered?  Current Node Name   SQL Collation                CPUs  RAM (MB)
Newer than SQL Server 2014  Express Edition (64-bit)  23-IT\SQLEXPRESS   RTM        SQL Server and Windows Authentication mode  False          23-IT           SQL_Latin1_General_CP1_CI_AS  8     8012

SQL Server Version Greater Than 2005

If your SQL Server version is greater than 2005, you can use the following script to find detailed system properties such as the version, edition, authentication mode, and server information.


SELECT 
    SERVERPROPERTY('ServerName') AS [Instance Name],
    CASE LEFT(CONVERT(VARCHAR, SERVERPROPERTY('ProductVersion')), 4) 
        WHEN '9.00' THEN 'SQL Server 2005'
        WHEN '10.0' THEN 'SQL Server 2008'
        WHEN '10.5' THEN 'SQL Server 2008 R2'
    END AS [Version Build],
    SERVERPROPERTY('Edition') AS [Edition],
    SERVERPROPERTY('ProductLevel') AS [Service Pack],
    CASE SERVERPROPERTY('IsIntegratedSecurityOnly') 
        WHEN 0 THEN 'SQL Server and Windows Authentication mode'
        WHEN 1 THEN 'Windows Authentication mode'
    END AS [Server Authentication],
    CASE SERVERPROPERTY('IsClustered') 
        WHEN 0 THEN 'False'
        WHEN 1 THEN 'True'
    END AS [Is Clustered?],
    SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [Current Node Name],
    SERVERPROPERTY('Collation') AS [SQL Collation],
    [cpu_count] AS [CPUs],
    [physical_memory_in_bytes] / 1048576 AS [RAM (MB)]
FROM 
    [sys].[dm_os_sys_info];

For further details on the SQL functions used in these queries, you can refer to the official Microsoft documentation:

Related Articles:

at
Labels:

Saturday, July 18, 2020

How to Find all constraints.


How to find all constraints.

SQL Command for Enabling Constraints

In SQL Server, sometimes you need to enable constraints that are not trusted. You can accomplish this using the following SQL command, which identifies the constraints that need to be re-enabled and generates the necessary commands to fix them.

The SQL query below helps you find foreign key and check constraints that are not trusted, and it provides a script to re-enable them.

SELECT 
 SCHEMA_NAME(s.[schema_id]) AS [Schema], 
 OBJECT_NAME(fk.parent_object_id) AS [Table], 
 fk.[name] AS [Constraint],
 CASE is_not_trusted WHEN 1 THEN 'No' ELSE 'Yes' END AS Trusted, 
 fk.[Type_desc], 
 ('ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(s.[schema_id])) + '.' + 
 QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + 
 ' WITH CHECK CHECK CONSTRAINT ' + fk.name) AS SQLcmdConstraint
FROM sys.foreign_keys fk
   INNER JOIN sys.objects o ON fk.parent_object_id = o.OBJECT_ID
   INNER JOIN sys.schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE fk.is_not_trusted = 1 AND fk.is_not_for_replication = 0
UNION ALL
SELECT 
 SCHEMA_NAME(s.[schema_id]) AS [Schema], 
 OBJECT_NAME(cc.parent_object_id) AS [Table], 
 cc.[name] AS [Constraint],
 CASE is_not_trusted WHEN 1 THEN 'No' ELSE 'Yes' END AS Trusted, 
 cc.[type_desc], 
 ('ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(s.[schema_id])) + '.' + 
 QUOTENAME(OBJECT_NAME(cc.parent_object_id)) + 
 ' WITH CHECK CHECK CONSTRAINT ' + cc.name) AS col1
FROM sys.check_constraints cc
   INNER JOIN sys.objects o ON cc.parent_object_id = o.OBJECT_ID
   INNER JOIN sys.schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE cc.is_not_trusted = 1 AND cc.is_not_for_replication = 0 AND cc.is_disabled = 0;

Automating the Process with a Cursor

If you want to automatically enable all the constraints, you can use a cursor. This script will loop through each constraint and execute the necessary SQL to trust it.

Here’s the SQL script for automating the process:

DECLARE @entrust_constraint NVARCHAR(1000);
DECLARE Cursor1 CURSOR FOR
 SELECT 
  ('ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(s.[schema_id])) + '.' + 
  QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + 
  ' WITH CHECK CHECK CONSTRAINT ' + fk.[Name]) AS [EntrustTheConstraint]
 FROM [sys].foreign_keys fk
 INNER JOIN [sys].objects o ON fk.parent_object_id = o.OBJECT_ID
 INNER JOIN [sys].schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
 WHERE fk.is_not_trusted = 1 AND fk.is_not_for_replication = 0
UNION ALL
 SELECT 
  ('ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(s.[schema_id])) + '.' + 
  QUOTENAME(OBJECT_NAME(cc.parent_object_id)) + 
  ' WITH CHECK CHECK CONSTRAINT ' + cc.[Name]) AS [EntrustTheConstraint]
 FROM [sys].check_constraints cc
 INNER JOIN [sys].objects o ON cc.parent_object_id = o.OBJECT_ID
 INNER JOIN [sys].schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
 WHERE cc.is_not_trusted = 1 AND cc.is_not_for_replication = 0 AND cc.is_disabled = 0;

OPEN Cursor1;
FETCH NEXT FROM Cursor1 INTO @entrust_constraint;
WHILE (@FETCH_STATUS = 0)
BEGIN
  BEGIN TRY
   EXECUTE sp_executesql @entrust_constraint;
   PRINT 'Successed: ' + @entrust_constraint;
  END TRY
  BEGIN CATCH
   PRINT 'Failed: ' + @entrust_constraint;
  END CATCH;
  FETCH NEXT FROM Cursor1 INTO @entrust_constraint;
END;
CLOSE Cursor1;
DEALLOCATE Cursor1;

This cursor script will automatically run through the constraints that are not trusted and attempt to enable them. If it’s successful, you’ll see a success message for each constraint, or if it fails, an error message will be printed.

Conclusion

If you're managing databases with many foreign key and check constraints, using these SQL scripts will save you a lot of time and effort. You can run them periodically to ensure that all your constraints are properly trusted and functional.

at

Saturday, July 11, 2020

Get fragmentation info for the tables

 

Analyzing Index Fragmentation in SQL Server

In SQL Server, understanding how your indexes and data are physically stored can help improve performance. The dm_db_index_physical_stats dynamic management function is a powerful tool that provides detailed information about index fragmentation and storage usage.

This function returns statistics about the size and fragmentation of indexes and data within a specified table or view. For each index, it outputs a row for every level of the B-tree structure in each partition.

Sample Query to Retrieve Fragmentation Details

The following SQL query provides fragmentation data for all user-defined tables and their indexes at the leaf level (excluding heaps). It uses the 'LIMITED' mode to reduce CPU usage, which is sufficient for general maintenance purposes.

-- Get fragmentation info for the tables
SELECT 
  ss.[Name] AS [Schema], 
  OBJECT_NAME(ddips.OBJECT_ID) AS [Table_name], 
  ISNULL(si.[Name], '') AS [Index_name],
  si.Index_id,
  si.[Type_desc],
  ISNULL(ddips.avg_fragmentation_in_percent, 0) AS [Ext_fragmentation], 
  ddips.page_count AS [Pages],
  si.Fill_factor,
  ISNULL(ddips.avg_page_space_used_in_percent, 0) AS [Page_fullness_pct]
FROM [sys].dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ddips
  JOIN [sys].indexes si 
    ON ddips.index_id = si.index_id 
    AND ddips.OBJECT_ID = si.OBJECT_ID
  JOIN [sys].tables st 
    ON ddips.OBJECT_ID = st.OBJECT_ID
  JOIN [sys].schemas ss 
    ON st.SCHEMA_ID = ss.SCHEMA_ID
WHERE ddips.index_level = 0 
  AND si.index_id > 0 
  AND st.[Type] = N'U' -- Only user-defined tables
GROUP BY 
  ss.[Name], ddips.OBJECT_ID, si.[Name], si.index_id, 
  si.type_desc, avg_fragmentation_in_percent, ddips.page_count, 
  avg_page_space_used_in_percent, si.fill_factor
ORDER BY ddips.page_count DESC;

This query helps database administrators (DBAs) identify indexes that may benefit from maintenance actions such as reorganizing or rebuilding based on fragmentation and page usage.

Further Reading

For more details on the sys.dm_db_index_physical_stats function and its parameters, visit the official Microsoft documentation:

Microsoft Docs - sys.dm_db_index_physical_stats

at

How to read Deadlock from system health of SQL server

 How to read the Deadlock from the system health of the SQL server

This query can be used to analyze deadlock events from the system health extended events in SQL Server.

SELECT 
    xed.value('@timestamp', 'datetime') AS Creation_Date,
    xed.query('.') AS Extended_Event,
    xed.value('(//deadlock/process-list/process[1]/@clientapp)[1]', 'NVarChar(50)') AS VictimClientApp,
    xed.value('(//deadlock/process-list/process[1]/@spid)[1]', 'NVarChar(15)') AS VictimProcessID,
    CAST(REPLACE(xed.value('(//deadlock/process-list/process[1]/@lastbatchstarted)[1]', 'NChar(23)'), N'T', N' ') AS DATETIME) AS VictimLastBatchStarted,
    xed.value('(//deadlock/process-list/process[1]/@lockMode)[1]', 'NVarChar(15)') AS VictimLockMode,
    xed.value('(//deadlock/process-list/process[1]/@xactid)[1]', 'NVarChar(15)') AS VictimXActID,
    xed.value('(//deadlock/process-list/process[1]/@clientapp)[1]', 'NVarChar(50)') AS VictimClientApp,
    -- Live Process
    xed.value('(//deadlock/process-list/process[2]/@spid)[1]', 'NVarChar(15)') AS LiveProcessID,
    CAST(REPLACE(xed.value('(//deadlock/process-list/process[2]/@lastbatchstarted)[1]', 'NChar(23)'), N'T', N' ') AS DATETIME) AS LiveLastBatchStarted,
    xed.value('(//deadlock/process-list/process[2]/@lockMode)[1]', 'NVarChar(15)') AS LiveLockMode,
    xed.value('(//deadlock/process-list/process[2]/@xactid)[1]', 'NVarChar(15)') AS LiveXActID,
    xed.value('(//deadlock/process-list/process[2]/@clientapp)[1]', 'NVarChar(50)') AS LiveClientApp,
    -- Live Resource
    xed.value('(//deadlock/resource-list/pagelock[1]/@fileid)[1]', 'NVarChar(15)') AS LiveFileID,
    xed.value('(//deadlock/resource-list/pagelock[1]/@pageid)[1]', 'NVarChar(15)') AS LivePageID,
    xed.value('(//deadlock/resource-list/pagelock[1]/@objectname)[1]', 'NVarChar(50)') AS LiveObjectName,
    xed.value('(//deadlock/resource-list/pagelock[1]/@mode)[1]', 'NVarChar(50)') AS LiveLockModeHeld,
    xed.value('(//deadlock/resource-list/pagelock[1]/waiter-list/waiter/@mode)[1]', 'NVarChar(50)') AS VictimLockModeRequest,
    -- Victim Resource
    xed.value('(//deadlock/resource-list/pagelock[2]/@fileid)[1]', 'NVarChar(15)') AS VictimFileID,
    xed.value('(//deadlock/resource-list/pagelock[2]/@pageid)[1]', 'NVarChar(15)') AS VictimPageID,
    xed.value('(//deadlock/resource-list/pagelock[2]/@objectname)[1]', 'NVarChar(50)') AS VictimObjectName,
    xed.value('(//deadlock/resource-list/pagelock[2]/@mode)[1]', 'NVarChar(50)') AS VictimLockModeHeld,
    xed.value('(//deadlock/resource-list/pagelock[2]/waiter-list/waiter/@mode)[1]', 'NVarChar(50)') AS LiveLockModeRequest,
    -- Input Buffers
    xed.value('(//deadlock/process-list/process[1]/executionStack/frame/@procname)[1]', 'NVarChar(100)') AS VictimProcName,
    xed.value('(//deadlock/process-list/process[1]/executionStack/frame)[1]', 'VarChar(max)') AS VictimExecStack,
    xed.value('(//deadlock/process-list/process[2]/executionStack/frame/@procname)[1]', 'NVarChar(max)') AS LiveProcName,
    xed.value('(//deadlock/process-list/process[2]/executionStack/frame)[1]', 'VarChar(max)') AS LiveExecStack,
    RTRIM(LTRIM(REPLACE(xed.value('(//deadlock/process-list/process[1]/inputbuf)[1]', 'NVarChar(2048)'), NCHAR(10), N''))) AS VictimInputBuffer,
    RTRIM(LTRIM(REPLACE(xed.value('(//deadlock/process-list/process[2]/inputbuf)[1]', 'NVARCHAR(2048)'), NChar(10), N''))) AS LiveInputBuffer
FROM 
    (SELECT CAST([target_data] AS XML) AS Target_Data
     FROM sys.dm_xe_session_targets AS xt
     INNER JOIN sys.dm_xe_sessions AS xs ON xs.address = xt.event_session_address
     WHERE xs.name = N'system_health'
     AND xt.target_name = N'ring_buffer') AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)
ORDER BY Creation_Date DESC;
at
Labels:

Tuesday, July 7, 2020

SQL Server Tables and row counts

 

Exploring SQL Server System Objects for Table Insights

In SQL Server, system objects can provide valuable information about database tables, such as the number of rows, indexes, key columns, and more. Below are some important system views that can help you retrieve such details.

  • sys.tables: This system view returns a row for each user-defined table within the database.
  • sys.partitions: Contains a row for each partition associated with all tables and most index types in the database.
  • sys.dm_db_partition_stats: Provides the page and row count information for each partition in the current database.
  • sys.indexes: This view holds a row for each index or heap of a table, view, or table-valued function.
  • sys.objects: This contains a row for every user-defined, schema-scoped object created within the database. It includes natively compiled scalar user-defined functions.

Sample Queries

You can use the following SQL queries to retrieve useful information about your database tables and indexes:

Query to Get Table Names and Row Counts

SELECT s.[Name] + N'.' + t.[Name] AS [Table], p.[Rows]
FROM [sys].tables t
JOIN [sys].schemas s ON s.SCHEMA_ID = t.SCHEMA_ID
JOIN [sys].partitions p ON p.OBJECT_ID = t.OBJECT_ID AND p.index_id IN (0,1); --heap or clustered index

Query to Get Indexes and Row Counts

SELECT o.name, ddps.row_count
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE i.index_id < 2 AND o.is_ms_shipped = 0;

References:

  1. sys.tables - SQL Server Documentation
  2. sys.dm_db_partition_stats - SQL Server Documentation
  3. sys.indexes - SQL Server Documentation
at

Sunday, July 5, 2020

Find the average size of rows in tables

 

How to Find the Average Row Size in SQL Server Tables

When managing large databases, it’s often helpful to understand how efficiently your tables are storing data. One useful metric is the average row size, which can help identify storage inefficiencies or opportunities for optimization.

Below is a SQL Server query that provides detailed insights into the average row size, total size, and fragmentation of your tables and indexes.

SQL Query to Analyze Table Storage Details


-- Retrieve detailed statistics about table and index storage

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT 
   CAST(OBJECT_NAME(ps.OBJECT_ID) + '.' + ISNULL(i.[Name], 'heap') AS VARCHAR(60)) AS Table_Index_Name,
   SUM(ps.record_count) AS Total_Record_Count,
   CAST(((SUM(ps.page_count) * 8192) / 1000000.00) AS NUMERIC(9,2)) AS Table_Size_MB,
   AVG(ps.max_record_size_in_bytes) AS Average_Record_Size_Bytes,
   MAX(ps.max_record_size_in_bytes) AS Max_Record_Size_Bytes,
   CAST(AVG(avg_fragmentation_in_percent) AS NUMERIC(6,1)) AS Avg_Fragmentation_Percent,
   CAST(AVG(ps.avg_page_space_used_in_percent) AS NUMERIC(6,1)) AS Avg_Page_Space_Used_Percent
FROM [sys].dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ps
LEFT JOIN [sys].indexes AS i 
    ON i.OBJECT_ID = ps.OBJECT_ID 
    AND i.index_id = ps.index_id
-- Optional: Uncomment the line below to filter results by specific table(s)
-- WHERE OBJECT_NAME(ps.OBJECT_ID) IN ('YourTableName') 
GROUP BY OBJECT_NAME(ps.OBJECT_ID), i.[Name]
ORDER BY OBJECT_NAME(ps.OBJECT_ID), i.[Name];

Key Points:

  • The query uses dm_db_index_physical_stats in DETAILED mode to ensure comprehensive metrics.
  • It calculates the average and maximum row sizes, total number of records, and storage size in megabytes.
  • Fragmentation percentage and page space usage are also included, which can be useful for performance tuning.
  • You can filter the results for specific tables by uncommenting and editing the WHERE clause.

This script is ideal for DBAs and developers who want to monitor and optimize data storage within SQL Server databases.

at
Labels:
Subscribe to: Comments (Atom)

Popular Posts