/*
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;Category Archives: Geen categorie
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
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);
GOQuerying 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
` 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
Convert DATALENGTH to other units
Calculate Avg BLOB size
select *, gigabytes / 1024.0 as terabytes
from (
select *, megabytes / 1024.0 as gigabytes
from (
select *, kilobytes / 1024.0 as megabytes
from (
select *, bytes / 1024.0 as kilobytes
from (
select avg(datalength([Value])) as bytes
from <schema>.<yourtable>
) a
) b
) c
) dSQL Always-On Alerts
EXEC msdb.dbo.sp_add_alert @name=N'1480 - AG Role Change',
@message_id=1480,
@severity=0,
@enabled=1,
@delay_between_responses=1,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'35264 - AG Data Movement - Suspended',
@message_id=35264,
@severity=0,
@enabled=1,
@delay_between_responses=1,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'35265 - AG Data Movement - Resumed',
@message_id=35265,
@severity=0,
@enabled=1,
@delay_between_responses=1,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'41404 - AG is offline',
@message_id=41404,
@severity=0,
@enabled=1,
@delay_between_responses=1,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'41405 - AG is not ready for automatic failover',
@message_id=41405,
@severity=0,
@enabled=1,
@delay_between_responses=1,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GOSSIS status last package execution
Get the status of the last package execution
USE SSISDB
GO
;WITH CTE_STATUS (s_id, s_created, s_active)
AS
(SELECT s_id, s_created, s_active
FROM (VALUES
(1, 'Created', 'Active')
,(2, 'Running', 'Active')
,(3, 'Canceled', 'Failed')
,(4, 'Failed', 'Failed')
,(5, 'Pending', 'Active')
,(6, 'Ended unexpectedly', 'Failed')
,(7, 'Succeeded', 'Succeeded')
,(8, 'Stopping', 'Active')
,(9, 'Completed', 'Failed')) AS S(s_id, s_created, s_active)
)
SELECT TOP (1)
E.execution_id
, E.folder_name
, E.project_name
, E.package_name
, E.executed_as_name
, E.created_time
, E.status
, s.s_active
, cast(E.start_time as datetime) as start_time
, cast(E.end_time as datetime) as end_time
, DATEDIFF(minute,cast(E.start_time as datetime), isnull(cast(e.end_time as datetime), GETDATE())) as duration_minutes
, E.caller_name
, E.process_id
, E.stopped_by_name
, 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
, F.folder_id
, F.name
, F.description
, F.created_by_name
, F.created_time
, P.project_id
, P.folder_id
FROM 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
LEFT JOIN CTE_STATUS AS S on E.status = s.s_id
WHERE E.package_name = '<your pacakage>'
ORDER BY E.execution_id DESC
GO