t-sql
How to find the last good known checkDB date?
Starting with SQL Server 2016 SP2, we have DATABASEPROPERTYEX (‘dbname’ , ‘LastGoodCheckDbTime’ )
In SQL Server 2017, CU14 ( I tested it) , this is also working.
Like so:
SELECT DATABASEPROPERTYEX (‘db_workspace’ , ‘LastGoodCheckDbTime’ )
or
SELECT DATABASEPROPERTYEX (d.name , ‘LastGoodCheckDbTime’ )
FROM sys.databases as d
Value of `1900-01-01 00:00:00.000` means – no check db was run.
Please, see here more info
S
New , in sql server 2017, with CU10, we have new hints related to CE&Compatibility level:
USE HINT(QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_XXX)
where XXX can be :
SELECT TOP(10) *
FROM TableAny
OPTION(USE HINT(QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_120));
For more info, please, read here.
S
It’s important to monitor the file grow. Due to database activity, your files will accumulate records and this will trigger a grow. Now disk space is not expensive , never the less, you need to know when you are close to the limits of your disk space.
For this , you can use sys.master_files and/or sys.database_files (this is for the current database).
You need some additional objects :
DROP TABLE IF EXISTS dbo.FileSize;
GO
CREATE TABLE dbo.FileSize
(
DBName VarChar(128)
, FileLogicalName VarChar(128)
, FilePhysicalName VarChar(512)
, Size_MB DECIMAL(15,2)
, InsertedDate DATE
, CONSTRAINT pk_FileSize__DBName_FileLogicalName
PRIMARY KEY CLUSTERED (DBName,FileLogicalName,InsertedDate)
);
GO
CREATE OR ALTER PROCEDURE dbo.fileSize_add
(
@i_InsertedDate DATE
)
AS
BEGIN
INSERT INTO dbo.FileSize( DBName, FileLogicalName, FilePhysicalName, Size_MB, InsertedDate)
SELECT
DB_NAME(mf.database_id) as dbName
,mf.name as fileLogicalName
,mf.physical_name as physicalName
,(mf.size * 8 )/1024.0 as fileSize_MB /* why * 8 ? because 8kb is for a page */
,@i_InsertedDate as InsertedDate
FROM sys.master_files as mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id,mf.file_id) as vs
WHERE
mf.database_id IN( DB_ID(‘db_workspace’) , DB_ID(‘WideWorldImporters’));
END
GO
In a next version, you can also monitor the size of your volume :
SELECT DISTINCT
vs.volume_mount_point as volumeName
,CONVERT(INT, vs.available_bytes / 1048576.0) as freeSpace_MB
,CONVERT(INT, vs.total_bytes / 1048576.0) as TotalSize_MB
FROM sys.master_files as mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id,mf.file_id) as vs
ORDER BY volumeName
Next, let’s start the monitoring by calling the sp and see what it’s in the target table:
EXEC dbo.FileSize_add @i_InsertedDate = ‘20180816’;
SELECT * FROM dbo.FileSize
DBName FileLogicalName FilePhysicalName Size_MB InsertedDate
db_workspace db_workspace D:\SQL\Databases\DATA\db_workspace.mdf 8.00 2018-08-16
db_workspace db_workspace_log D:\SQL\Databases\DATA\db_workspace_log.ldf 24.00 2018-08-16
WideWorldImporters WWI_InMemory_Data_1 D:\SQL\Databases\DATA\WWI\WideWorldImporters_InMemory_Data_1 0.00 2018-08-16
WideWorldImporters WWI_Log D:\SQL\Databases\DATA\WWI\WideWorldImporters.ldf 100.00 2018-08-16
WideWorldImporters WWI_Primary D:\SQL\Databases\DATA\WWI\WideWorldImporters.mdf 1024.00 2018-08-16
WideWorldImporters WWI_UserData D:\SQL\Databases\DATA\WWI\WideWorldImporters_UserData.ndf 2048.00 2018-08-16
step2: Simulate a database activity:
use db_workspace
go
drop table if exists dbo.test;
create table dbo.test
( idTest int identity(1,1) not null
,cTest char(2000) not null
)
insert into dbo.Test(ctest)
select top (100000) s.name
from sys.all_columns as c
cross join sys.all_columns as s
/*(100000 rows affected)*/
Step3 : next day, monitor again the size:
EXEC dbo.FileSize_add @i_InsertedDate = ‘20180817’;
SELECT DbName,FileLogicalName,Size_MB,InsertedDate FROM dbo.FileSize WHERE InsertedDate = ‘20180817’
DbName FileLogicalName Size_MB InsertedDate
db_workspace db_workspace 200.00 2018-08-17
db_workspace db_workspace_log 344.00 2018-08-17
WideWorldImporters WWI_InMemory_Data_1 0.00 2018-08-17
WideWorldImporters WWI_Log 100.00 2018-08-17
WideWorldImporters WWI_Primary 1024.00 2018-08-17
WideWorldImporters WWI_UserData 2048.00 2018-08-17
Step 4: additional activity:
use db_workspace
go
drop table if exists dbo.test2;
create table dbo.test2
( idTest int identity(1,1) not null
,cTest char(2000) not null
)
insert into dbo.Test2(ctest)
select top (1000) s.name
from sys.all_columns as c
cross join sys.all_columns as s
/*(1000 rows affected)*/
Step5 : next day, monitor again the size:
EXEC dbo.FileSize_add @i_InsertedDate = ‘20180817’;
SELECT DbName,FileLogicalName,Size_MB,InsertedDate FROM dbo.FileSize WHERE InsertedDate = ‘20180817’
DbName FileLogicalName Size_MB InsertedDate
db_workspace db_workspace 264.00 2018-08-18
db_workspace db_workspace_log 344.00 2018-08-18
WideWorldImporters WWI_InMemory_Data_1 0.00 2018-08-18
WideWorldImporters WWI_Log 100.00 2018-08-18
WideWorldImporters WWI_Primary 1024.00 2018-08-18
WideWorldImporters WWI_UserData 2048.00 2018-08-18
Step6: Conclusions:
;WITH Src AS
(
SELECT FileLogicalName
,Size_MB as CurrentSize
,LAG(Size_MB,1,0) OVER(PARTITION BY DbName,FileLogicalName ORDER BY InsertedDate) AS PrevSize
,LAG(InsertedDate,1,NULL) OVER(PARTITION BY DbName,FileLogicalName ORDER BY InsertedDate) AS PrevDate
,Size_MB – LAG(Size_MB,1,0) OVER(PARTITION BY DbName,FileLogicalName ORDER BY InsertedDate) AS DeltaSize
,DATEDIFF(DAY,LAG(InsertedDate,1,NULL) OVER(PARTITION BY DbName,FileLogicalName ORDER BY InsertedDate),InsertedDate) AS DeltaDays
,DBName
,InsertedDate
,FIRST_VALUE(Size_MB) OVER(PARTITION BY DbName,FileLogicalName ORDER BY InsertedDate DESC) AS LastValue_Size
,FIRST_VALUE(InsertedDate) OVER(PARTITION BY DbName,FileLogicalName ORDER BY InsertedDate DESC) AS LastValue_Date
FROM dbo.FileSize as FS
WHERE
RIGHT(FilePhysicalName,3) <> ‘ldf’
AND FileLogicalName = ‘db_workspace’
)
SELECT FileLogicalName
,MAX(LastValue_Size) AS CurrentSize
,MAX(LastValue_Date) AS LastReportedDate
,CAST(SYSDATETIME() AS DATE) AS CurrentDate
,AVG(DeltaSize/NULLIF(DATEDIFF(DAY,PrevDate,InsertedDate),0)) AS AVG_DeltaSize
,MIN(DeltaSize/NULLIF(DATEDIFF(DAY,PrevDate,InsertedDate),0)) AS minDeltaPerDay
,MAX(DeltaSize/NULLIF(DATEDIFF(DAY,PrevDate,InsertedDate),0)) AS maxDeltaPerDay
,MAX(LastValue_Size) + 30* MAX(DeltaSize/NULLIF(DATEDIFF(DAY,PrevDate,InsertedDate),0)) AS Size30Days_Max
,MAX(LastValue_Size) + 30* AVG(DeltaSize/NULLIF(DATEDIFF(DAY,PrevDate,InsertedDate),0)) AS Size30Days_Avg
FROM Src
GROUP BY
FileLogicalName
FileLogicalName CurrentSize LastReportedDate CurrentDate AVG_DeltaSize minDeltaPerDay maxDeltaPerDay Size30Days_Max Size30Days_Avg
db_workspace 264.00 2018-08-18 2018-08-18 128.00 64.00 192.00 6024.00 4104.00
So , in 30 days , keeping the maxim grow in a day, will have size = 6024.00, or by calculating with average grow, the size will be = 4104.00.
And now, you can collaborate with the available space in the volume … and trigger some response, some mail…
Thanks,
S
#t-sql #fileGrow #sqlserver #mssqlserver #sql #databaseGrow
How about a home made script that is checking the statistics modification counter, and base on this value, check it with a company / object threshold and if this value is above that threshold, trigger an update of that particular statistics.
For this we must have a utility table that hold the threshold for which will trigger the update. This is kept on object_id and stat_id ( for a more flexibility) but could be only one per database or one per object_id …
ex: I have a statistics : _WA_Sys_0000000E_44CA3770 , if this has a value = 4563 and the threshold it’s like 3000. In this case we should trigger , for this statistics, an update.
Here are the steps:
I will start with step 3 , the utility table.This should be save in a “utility” database.
use db_workspace
go
DROP TABLE IF EXISTS dbo.utility_stats;
GO
CREATE TABLE dbo.utility_stats
(
uts_id INT IDENTITY(1,1) NOT NULL
,uts_object_id INT NOT NULL
,uts_stats_id INT NOT NULL
,uts_threshold INT NULL
);
GO
ALTER TABLE dbo.utility_stats
ADD CONSTRAINT pk_utility_stats__uts_object_id_uts_stats_id
PRIMARY KEY CLUSTERED(uts_object_id,uts_stats_id);
GO
Step 1: List the statistics (for indexes and for columns) of an object.
In this case for Sales.Orders
SELECT
OBJECT_SCHEMA_NAME(s.object_id)
+ ‘.’ + OBJECT_NAME(s.object_id) as objectName
, s.name as statisticsName
,s.object_id as objectID
FROM sys.stats as s
WHERE s.object_id = OBJECT_ID(‘Sales.Orders’)
objectName statisticsName objectID
Sales.Orders PK_Sales_Orders 1154103152
Sales.Orders FK_Sales_Orders_CustomerID 1154103152
Sales.Orders FK_Sales_Orders_SalespersonPersonID 1154103152
Sales.Orders FK_Sales_Orders_PickedByPersonID 1154103152
Sales.Orders FK_Sales_Orders_ContactPersonID 1154103152
Sales.Orders _WA_Sys_0000000E_44CA3770 1154103152
Sales.Orders _WA_Sys_0000000A_44CA3770 1154103152
Step 2: Show the fields that holds the modification counter associated
with this statistics
SELECT
OBJECT_SCHEMA_NAME(s.object_id)
+ ‘.’ + OBJECT_NAME(s.object_id) as objectName
, s.name as statisticsName
,s.object_id as objectID
,s.stats_id as statsID
,sp.modification_counter
,sp.last_updated
,sc.colNames
FROM sys.stats as s
CROSS APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) as sp
CROSS APPLY(SELECT STRING_AGG(c.name,’,’) as colNames
FROM sys.stats_columns as sc
INNER JOIN sys.columns as c
ON sc.column_id = c.column_id
and sc.object_id = c.object_id
WHERE sc.object_id = s.object_id
and sc.stats_id = s.stats_id
) as sc
WHERE s.object_id = OBJECT_ID(‘Sales.Orders’)
objectName statisticsName objectID statsID modification_counter last_updated colNames
Sales.Orders PK_Sales_Orders 1154103152 1 3966 2016-06-02 10:39:54.2533333 OrderID
Sales.Orders FK_Sales_Orders_CustomerID 1154103152 2 0 2018-07-25 10:52:18.0733333 CustomerID
Sales.Orders FK_Sales_Orders_SalespersonPersonID 1154103152 3 0 2018-07-25 10:52:18.4233333 SalespersonPersonID
Sales.Orders FK_Sales_Orders_PickedByPersonID 1154103152 4 4751 2016-06-02 10:43:25.6400000 PickedByPersonID
Sales.Orders FK_Sales_Orders_ContactPersonID 1154103152 5 2012 2016-06-02 10:45:05.4033333 ContactPersonID
Sales.Orders _WA_Sys_0000000E_44CA3770 1154103152 6 0 2018-07-25 10:53:22.7400000 PickingCompletedWhen
Sales.Orders _WA_Sys_0000000A_44CA3770 1154103152 7 3495 2016-06-02 10:41:14.3700000 IsUndersupplyBackordered
Step 3 : Do an update/delete in a transaction with rollback – for field PickingCompletedWhen
DECLARE @i_modification_counter_before int = 0
,@i_modification_counter_after int = 0;
SELECT
@i_modification_counter_before = sp.modification_counter
FROM sys.stats as s
CROSS APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) as sp
WHERE s.object_id = OBJECT_ID(‘Sales.Orders’)
AND s.stats_id = 6
BEGIN TRAN
UPDATE TOP(1000) Sales.Orders
SET PickingCompletedWhen = SYSDATETIME()
SELECT
@i_modification_counter_after = sp.modification_counter
FROM sys.stats as s
CROSS APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) as sp
WHERE s.object_id = OBJECT_ID(‘Sales.Orders’)
AND s.stats_id = 6
ROLLBACK TRAN
SELECT @i_modification_counter_before as beforeCounter
,@i_modification_counter_after as afterCounter
,@i_modification_counter_after-@i_modification_counter_before as differ
beforeCounter afterCounter differ
7480 8480 1000
Step 4 : utility table and populate with thresholds:
INSERT INTO db_workspace.dbo.utility_stats(uts_object_id,uts_stats_id,uts_threshold)
SELECT
s.object_id as objectID
,s.stats_id as statsID
,5000
FROM WideWorldImporters.sys.stats as s
WHERE s.object_id = OBJECT_ID(‘WideWorldImporters.Sales.Orders’);
GO
SELECT * FROM db_workspace.dbo.utility_stats
/*
uts_id uts_object_id uts_stats_id uts_threshold
1 1154103152 1 5000
2 1154103152 2 5000
3 1154103152 3 5000
4 1154103152 4 5000
5 1154103152 5 5000
6 1154103152 6 5000
7 1154103152 7 5000
*/
Step 5: Create dynamic string to run the update statistics , for the statistics with modification counter above the threshold.
DECLARE @nvc_SQLText AS nvarchar(4000) = N”;
SELECT
@nvc_SQLText = STRING_AGG(N’ UPDATE STATISTICS ‘
+ OBJECT_SCHEMA_NAME(s.object_id)
+ N’.’ + OBJECT_NAME(s.object_id)
+N'(‘ + s.name +N’)’
+ N’ WITH FULLSCAN ‘ + char(10) + char(13) ,N’;’)
FROM sys.stats as s
CROSS APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) as sp
LEFT JOIN db_workspace.dbo.utility_stats as us
ON us.uts_object_id = s.object_id
AND us.uts_stats_id = s.stats_id
WHERE s.object_id = OBJECT_ID(‘Sales.Orders’)
AND sp.modification_counter >= us.uts_threshold
SELECT @nvc_SQLText
/* output :
UPDATE STATISTICS Sales.Orders(_WA_Sys_0000000E_44CA3770) WITH FULLSCAN
*/
EXEC sp_executeSQL @nvc_SQLText
GO
Step 6: Check the value after update statistics, for that particullary statistics:
_WA_Sys_0000000E_44CA3770
use WideWorldImporters
go
SELECT
OBJECT_SCHEMA_NAME(s.object_id)
+ ‘.’ + OBJECT_NAME(s.object_id) as objectName
, s.name as statisticsName
,s.object_id as objectID
,s.stats_id as statsID
,sp.modification_counter
,sp.last_updated
,sc.colNames
,us.uts_threshold
FROM sys.stats as s
CROSS APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) as sp
CROSS APPLY(SELECT STRING_AGG(c.name,’,’) as colNames
FROM sys.stats_columns as sc
INNER JOIN sys.columns as c
ON sc.column_id = c.column_id
and sc.object_id = c.object_id
WHERE sc.object_id = s.object_id
and sc.stats_id = s.stats_id
) as sc
LEFT JOIN db_workspace.dbo.utility_stats as us
ON us.uts_object_id = s.object_id
AND us.uts_stats_id = s.stats_id
WHERE s.object_id = OBJECT_ID(‘Sales.Orders’)
/*
objectName statisticsName objectID statsID modification_counter last_updated colNames uts_threshold
Sales.Orders _WA_Sys_0000000E_44CA3770 1154103152 6 0 2018-07-25 10:53:22.7400000 PickingCompletedWhen 5000
*/
That’s it fox!
S
To get information about transaction log, usually we use :
DBCC SQLPerf(LogSpace) = how much log space it’s used
DBCC LogInfo = information about vlf virtual log files
But there are new dmvs ,that can do the same things maybe more:
for DBCC SQLPerf , we can use sys.dm_db_log_space_usage
for DBCC LogInfo , we can use sys.dm_db_log_info
also can be used sys.dm_db_log_stats
S
New in sql server 2017:
Graph data processing – this give us , the ability to create two new types of tables
– node – holds the entities and could have properties
– edge – holds the relations between nodes/entities.
There are some hidden columns
$node_id …
$edge_id
$from_id
$to_id
Is adding a new clause = MATCH =
Here is an example from microsoft blog:
SELECT Person2.Name
FROM Person Person1, Friends, Person Person2
WHERE MATCH(Person1-(Friends)->Person2)
AND Person1.Name = ‘John’;
see it here
Good stuff , microsoft!
S
New things are getting from sql server 2017.
Now , I founded : Automatic tuning.See here more : automatic-tuning.
The engine will automatically resolve problems that it identifies
This is based on query store.
sys.dm_db_tuning_recommendations
To enable automatically tuning :
ALTER DATABASE current SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON )
S
I wrote some stored procedures , like 1 month ago.
During this month, these stored procedures suffered from different modification, new fields, new joins, new where conditions , so they end up in an “ugly” form of writing and the performance was very poor. Initial form were run in a few second, and the yesterday form were running in like 2 hours :O . To much.
But testing was started , so was hard to rewrite , because this is generating a new start of testing. But after a new negotiation and some coffees “bribes” , they let me rewrite the queries and now I am back , with the run in under 1 minutes.
Thanks sql server, because you can write the same queries in different format.
S
Sql Server 