Author Archives: Demas Jaring

About Demas Jaring

I'm working as senior Microsoft Database Administrator at VWE in the Netherlands. I love to tune SQL Server and to write T-SQL.

CompressionSavingsEstimate

/*
This script estimates compression savings for all objects, indexes, and partitions in the current database.

See http://blogs.msdn.com/b/dfurman/archive/2011/02/25/estimating-data-compression-savings-for-entire-database.aspx for details.
*/

DECLARE @CompressionSavingsEstimate table
(
SchemaName sysname NOT NULL,
ObjectName sysname NOT NULL,
IndexName sysname NOT NULL,
IndexType nvarchar(60) NOT NULL,
PartitionNum int NOT NULL,
CompressionType nvarchar(10) NOT NULL,
[size_with_current_compression_setting (KB)] bigint NOT NULL,
[size_with_requested_compression_setting (KB)] bigint NOT NULL,
[sample_size_with_current_compression_setting (KB)] bigint NOT NULL,
[sample_size_with_requested_compression_setting (KB)] bigint NOT NULL,
PRIMARY KEY (SchemaName, ObjectName, IndexName, IndexType, PartitionNum, CompressionType)
);
DECLARE @ProcResult table
(
[object_name] sysname NOT NULL,
[schema_name] sysname NOT NULL,
[index_id] int NOT NULL,
[partition_number] int NOT NULL,
[size_with_current_compression_setting (KB)] bigint NOT NULL,
[size_with_requested_compression_setting (KB)] bigint NOT NULL,
[sample_size_with_current_compression_setting (KB)] bigint NOT NULL,
[sample_size_with_requested_compression_setting (KB)] bigint NOT NULL
);
DECLARE @SchemaName sysname;
DECLARE @ObjectName sysname;
DECLARE @IndexID int;
DECLARE @IndexName sysname;
DECLARE @IndexType nvarchar(60);
DECLARE @PartitionNum int;
DECLARE @CompTypeNum tinyint;
DECLARE @CompressionType nvarchar(60);

SET NOCOUNT ON;

DECLARE CompressedIndex INSENSITIVE CURSOR FOR
SELECT s.name AS SchemaName,
       o.name AS ObjectName,
       i.index_id AS IndexID,
       COALESCE(i.name, '<HEAP>') AS IndexName,
       i.type_desc AS IndexType,
       p.partition_number AS PartitionNum
FROM sys.schemas AS s
INNER JOIN sys.objects AS o
ON s.schema_id = o.schema_id
INNER JOIN sys.indexes AS i
ON o.object_id = i.object_id
INNER JOIN sys.partitions AS p
ON o.object_id = p.object_id
   AND
   i.index_id = p.index_id
WHERE o.type_desc IN ('USER_TABLE','VIEW');

OPEN CompressedIndex;

WHILE 1 = 1
BEGIN
    FETCH NEXT FROM CompressedIndex 
    INTO @SchemaName, @ObjectName, @IndexID, @IndexName, @IndexType, @PartitionNum;

    IF @@FETCH_STATUS <> 0
        BREAK;

    SELECT @CompTypeNum = 0;
    WHILE @CompTypeNum <= 2
    BEGIN
        SELECT @CompressionType = CASE @CompTypeNum 
                                  WHEN 0 THEN 'NONE' 
                                  WHEN 1 THEN 'ROW'
                                  WHEN 2 THEN 'PAGE' 
                                  END;

        DELETE FROM @ProcResult;

        RAISERROR('Estimating compression savings using "%s" compression for object "%s.%s", index "%s", partition %d...', 10, 1, @CompressionType, @SchemaName, @ObjectName, @IndexName, @PartitionNum);

        INSERT INTO @ProcResult
        EXEC sp_estimate_data_compression_savings @schema_name = @SchemaName, 
                                                  @object_name = @ObjectName, 
                                                  @index_id = @IndexID, 
                                                  @partition_number = @PartitionNum, 
                                                  @data_compression = @CompressionType;

        INSERT INTO @CompressionSavingsEstimate
        (
        SchemaName,
        ObjectName,
        IndexName,
        IndexType,
        PartitionNum,
        CompressionType,
        [size_with_current_compression_setting (KB)],
        [size_with_requested_compression_setting (KB)],
        [sample_size_with_current_compression_setting (KB)],
        [sample_size_with_requested_compression_setting (KB)]
        )
        SELECT [schema_name],
               [object_name],
               @IndexName,
               @IndexType,
               [partition_number],
               @CompressionType,
               [size_with_current_compression_setting (KB)],
               [size_with_requested_compression_setting (KB)],
               [sample_size_with_current_compression_setting (KB)],
               [sample_size_with_requested_compression_setting (KB)]
        FROM @ProcResult;

        SELECT @CompTypeNum += 1;
    END;
END;

CLOSE CompressedIndex;
DEALLOCATE CompressedIndex;

SELECT SchemaName,
       ObjectName,
       IndexName,
       IndexType,
       PartitionNum,
       CompressionType,
       AVG([size_with_current_compression_setting (KB)]) AS [size_with_current_compression_setting (KB)],
       AVG([size_with_requested_compression_setting (KB)]) AS [size_with_requested_compression_setting (KB)],
       AVG([sample_size_with_current_compression_setting (KB)]) AS [sample_size_with_current_compression_setting (KB)],
       AVG([sample_size_with_requested_compression_setting (KB)]) AS [sample_size_with_requested_compression_setting (KB)]
FROM @CompressionSavingsEstimate
GROUP BY GROUPING SETS (
                       (CompressionType),
                       (SchemaName, ObjectName, IndexName, IndexType, PartitionNum, CompressionType)
                       )
ORDER BY SchemaName, ObjectName, IndexName, IndexType, PartitionNum, CompressionType DESC;

SET NOCOUNT OFF;

Generate Dummy Data

Generate dummy data and insert it into table dbo.dummydata


-- // create table to store data

drop table if exists dbo.dummydata
GO
create table dbo.dummydata
(
    Id int,
    randomdata uniqueidentifier
)

declare @cnt int = 100

while @cnt > 0 

begin 
-- // Generate 50000 random rows

    declare @datetime datetime = getdate()
    ;with randowvalues
        as(
           select 1 id, NEWID() randomnumber
            union  all
            select id + 2, NEWID() randomnumber
            from randowvalues
            where 
              id < 100000-1
          )

        insert into dbo.dummydata(Id, randomdata)
        select *
        from randowvalues
        OPTION(MAXRECURSION 0)

    set @cnt = @cnt-1

    declare @msg varchar(255)
    set     @msg = concat('duration batch: ',datediff(ms, @datetime, getdate()), ' (ms)')

    RAISERROR(@msg, 0, 1) WITH NOWAIT

end

Generate script – enable query store for each database

Use the print query result for a pretty SQL script

SET NOCOUNT ON
select 
CONCAT('ALTER DATABASE [',name,'] SET QUERY_STORE = ON',char(10),'GO',char(10),
'ALTER DATABASE [',name,'] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)',char(10),'GO',char(10))
from sys.databases
where is_query_store_on = 0
and database_id > 4 

Capture last login datetime per database

-- create table to store the results
drop table if exists dbo.DatabaseUsages
create table dbo.DatabaseUsages
(
    DbName nvarchar(256) not null,
    LoginName nvarchar(256) not null,
    LastLoginTime datetime not null
)

-- capture the data
MERGE dbo.DatabaseUsages AS tgt
USING 
(
    SELECT 
      db_name(database_id) as [DbName]
    , original_login_name as [LoginName] 
    , MAX(login_time) AS [LastLoginTime]
    FROM sys.dm_exec_sessions as src
    where db_name(database_id) is not null
    GROUP BY db_name(database_id), original_login_name, original_login_name
) as src ON (tgt.DbName = src.DbName AND tgt.LoginName = src.LoginName)
WHEN MATCHED THEN 
    UPDATE SET tgt.LastLoginTime = src.LastLoginTime

WHEN NOT MATCHED THEN
    INSERT VALUES (DbName, LoginName, LastLoginTime);
GO

Querying the Query Store DMVs Directly to find missing indices


SELECT
    SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_reads) as est_logical_reads,
    SUM(qrs.count_executions) AS sum_executions,
    AVG(qrs.avg_logical_io_reads) AS avg_avg_logical_io_reads,
    SUM(qsq.count_compiles) AS sum_compiles,
    (SELECT TOP 1 qsqt.query_sql_text FROM sys.query_store_query_text qsqt
        WHERE qsqt.query_text_id = MAX(qsq.query_text_id)) AS query_text,    
    TRY_CONVERT(XML, (SELECT TOP 1 qsp2.query_plan from sys.query_store_plan qsp2
        WHERE qsp2.query_id=qsq.query_id
        ORDER BY qsp2.plan_id DESC)) AS query_plan,
    qsq.query_id,
    qsq.query_hash
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp on qsq.query_id=qsp.query_id
CROSS APPLY (SELECT TRY_CONVERT(XML, qsp.query_plan) AS query_plan_xml) AS qpx
JOIN sys.query_store_runtime_stats qrs on qsp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qsrsi on qrs.runtime_stats_interval_id=qsrsi.runtime_stats_interval_id
WHERE    
    qsp.query_plan like N'%%'
    and qsrsi.start_time >= DATEADD(HH, -24, SYSDATETIME())
GROUP BY qsq.query_id, qsq.query_hash
ORDER BY est_logical_reads DESC
GO
`

Query machine and instance information

Query SQL server machine information

-- machine  info
select
    SERVERPROPERTY('MachineName') AS ComputerName,
    SERVERPROPERTY('ServerName') AS InstanceName,  
    CPUCount                = cast(cpu_count as varchar(300)),
    HyperThreadRatio        = cast(hyperthread_ratio as varchar(300)),
    SocketCount             = cast(socket_count as varchar(300)),
    CoresPerSocket          = cast(cores_per_socket as varchar(300)),
    PhysicalMemoryGB        = cast((physical_memory_kb / 1048576) as varchar(300)),
    IsClustered             = SERVERPROPERTY('IsClustered'),
    SERVERPROPERTY('IsHadrEnabled') IsAlwaysOn
from sys.dm_os_sys_info

The maximum values for an integer

The maximum values for an integer in SQL Server are:

-2147483648 through 2147483647

And the byte size is 4 bytes.

Other maximum values:

BigInt: -9223372036854775808 through 9223372036854775807 (8 bytes)
SmallInt: -32768 through 32767 (2 bytes)
TinyInt: 0 through 255 (1 byte)

Get First and Last day of a week

Get first and last day of a week and set monday as te first day of the week

SET DATEFIRST 1

declare @Week int  set @Week = 40
declare @Year int  set @Year = 2021

select dateadd (week, @Week, dateadd (year, @Year-1900, 0)) - 4 -
         datepart(dw, dateadd (week, @Week, dateadd (year, @Year-1900, 0)) - 4) + 1

select dateadd (week, @Week, dateadd (year, @Year-1900, 0)) - 4 -
         datepart(dw, dateadd (week, @Week, dateadd (year, @Year-1900, 0)) - 4) + 6

SCOM query – get DISK and CPU info


use [OperationsManagerDW]
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
--=================
-- Set time period
--=================
declare @DateFrom datetime = EOMONTH(GETDATE(), -2) 
declare @DateTo datetime = EOMONTH(GETDATE(), -1)
--=====================
-- Daily disk info
--=====================
; WITH CTE_DISK
AS
(
    SELECT
          vph.DateTime
        , vPerformanceRuleInstance.InstanceName
        , vManagedEntity.Path
        , vPerformanceRule.ObjectName
        , vPerformanceRule.CounterName 
        , vph.MinValue
        , vph.MaxValue
        , vph.AverageValue
        , EventType = 
            CASE  
                WHEN ObjectName = 'LogicalDisk' AND  CounterName = '% Free Space' AND AverageValue < 5 then 'Critical' 
                WHEN ObjectName = 'LogicalDisk' AND  CounterName = '% Free Space' AND AverageValue < 10 then 'Warning' 
                ELSE NULL 
            END 
        , Event = 
            CASE 
                WHEN ObjectName = 'LogicalDisk' AND  CounterName = '% Free Space' AND AverageValue < 10 
                THEN '% Free Space < 10'  
                ELSE NULL 
            END
    FROM Perf.vPerfDaily AS vph 
    INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vph.PerformanceRuleInstanceRowId 
    INNER JOIN vManagedEntity ON vph.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId 
    INNER JOIN vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
    WHERE 
        vPerformanceRule.ObjectName = 'LogicalDisk' AND
        vPerformanceRule.CounterName = '% Free Space' AND
        vph.DateTime > @DateFrom AND vph.DateTime <= @DateTo
)
--=====================
-- Hourly CPU info
--=====================
,CTE_CPU AS 
(
    SELECT
          vph.DateTime
        , vPerformanceRuleInstance.InstanceName
        , vManagedEntity.Path
        , vPerformanceRule.ObjectName
        , vPerformanceRule.CounterName 
        , vph.MinValue
        , vph.MaxValue
        , vph.AverageValue
        , EventType= 
            case  
                when ObjectName = 'Processor Information' and  CounterName = '% Processor Time' and AverageValue > 90 then 'Critical' 
                when ObjectName = 'Processor Information' and  CounterName = '% Processor Time' and AverageValue > 85 then 'Warning' 
                else null 
            end 
        , Event = 
            CASE 
                WHEN
                    ObjectName = 'Processor Information' 
                AND CounterName = '% Processor Time' 
                AND AverageValue > 90 THEN '% Processor Time > 90' 
                ELSE NULL
            END
    FROM  Perf.vPerfHourly AS vph 
    INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vph.PerformanceRuleInstanceRowId 
    INNER JOIN vManagedEntity ON vph.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId 
    INNER JOIN vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
    WHERE 
        vPerformanceRule.ObjectName = 'Processor Information' AND
        vPerformanceRule.CounterName = '% Processor Time' AND
        vph.DateTime > @DateFrom AND vph.DateTime <= @DateTo
)
SELECT * FROM CTE_DISK WHERE Event IS NOT NULL
UNION ALL
SELECT * FROM CTE_CPU WHERE Event IS NOT NULL
GO