/*
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;
Author Archives: Demas Jaring
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)
Login Failed CEIP SQLTELEMETRY
We expiranced every 5 minute a login failed error in our SQL 2016 development enviroment. I found out that the CEIP services was causing this errors.
I decided to turn this service out.
For more information about this topic see stack overlfow https://stackoverflow.com/questions/43548794/how-to-turn-off-telemetry-for-sql-2016
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