SQL Server - FOREIGN KEY - NOCHECK & CHECK CHECK

--drop table dbo.TestDisableFK_Pk

--drop table dbo.TestDisableFK_Fk

create table dbo.TestDisableFK_Pk

(

PKId int not null,

PkStr nvarchar(50) null,

CONSTRAINT [PK_Pk] PRIMARY KEY CLUSTERED 

(

PKId ASC

)

)

create table dbo.TestDisableFK_Fk

(

FKId int not null,

PKId int not null,

PkStr nvarchar(50) null

)

ALTER TABLE dbo.TestDisableFK_Fk  WITH CHECK ADD  CONSTRAINT [FK_Fk] FOREIGN KEY(PKId)

REFERENCES dbo.TestDisableFK_Pk (PKId)

insert into dbo.TestDisableFK_Pk (PKId) values (1), (2), (3)

insert into dbo.TestDisableFK_Fk (FKId, PKId) values (1, 1), (2, 2), (3, 3)

select * from dbo.TestDisableFK_Pk

select * from dbo.TestDisableFK_Fk

update dbo.TestDisableFK_Fk set PKId = 4 where FKId = 1

delete dbo.TestDisableFK_Pk  where PKId = 1

ALTER TABLE dbo.TestDisableFK_Fk NOCHECK CONSTRAINT [FK_Fk]

update dbo.TestDisableFK_Fk set PKId = 4 where FKId = 1

select * from dbo.TestDisableFK_Pk

select * from dbo.TestDisableFK_Fk

ALTER TABLE dbo.TestDisableFK_Fk WITH CHECK CHECK CONSTRAINT [FK_Fk]

update dbo.TestDisableFK_Pk set PKId = 4 where PKId = 1

ALTER TABLE dbo.TestDisableFK_Fk WITH CHECK CHECK CONSTRAINT [FK_Fk]

select * from dbo.TestDisableFK_Pk

select * from dbo.TestDisableFK_Fk

Row counts in all tables

SELECT

      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]

      , SUM(sPTN.Rows) AS [RowCount]

  , IDENT_CURRENT( sOBJ.name ) as [CurrentMaxId]

FROM 

      sys.objects (nolock) AS sOBJ

      INNER JOIN sys.partitions (nolock)  AS sPTN

            ON sOBJ.object_id = sPTN.object_id

WHERE

      sOBJ.type = 'U'

      AND sOBJ.is_ms_shipped = 0x0

      AND index_id < 2 -- 0:Heap, 1:Clustered

GROUP BY 

      sOBJ.schema_id

      , sOBJ.name

ORDER BY [CurrentMaxId] desc, [RowCount] desc

GO

SQL Sever - generate high index fragmentation level

--------------------------------------------------------
-- table creation:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IndexFrag]') AND type in (N'U'))
BEGIN
DROP TABLE [dbo].[IndexFrag]
END
GO
CREATE TABLE [dbo].[IndexFrag]
(
[IndexFragId] [int] NOT NULL identity(1,1),
[IndexFragInt] [int] NOT NULL,
[IndexFragText] [nvarchar](256) NULL,
) ON [PRIMARY]
GO

--------------------------------------------------------
-- data insertion:

INSERT INTO [dbo].[IndexFrag] (IndexFragInt, IndexFragText)
SELECT column_id, [name] from sys.columns;
GO
CREATE NONCLUSTERED INDEX IX_IndexFrag_IndexFragInt ON [dbo].[IndexFrag] ([IndexFragInt]);
GO

--------------------------------------------------------
-- make an operation on the index that will "add" it to sys.dm_db_index_usage_stats

select * from [dbo].[IndexFrag] where IndexFragInt > 5;

DELETE IndexFrag WHERE IndexFragId % 2= 0;
GO

--------------------------------------------------------
-- select:

select iu.database_id,
iu.object_id, OBJECT_NAME(iu.object_id) as TableName,
iu.index_id, x.name as IndexName, x.type_desc,
ips.avg_fragmentation_in_percent,
iu.user_scans, iu.system_scans, iu.user_updates
from sys.dm_db_index_usage_stats iu
INNER JOIN sys.partitions p ON iu.object_id = p.object_id and iu.index_id = p.index_id
INNER JOIN sys.objects o (nolock) ON iu.object_id = o.object_id and o.type='U'
INNER JOIN sys.indexes x  (nolock) ON x.object_id = iu.object_id AND x.index_id = iu.index_id 
and x.type_desc in ('CLUSTERED','NONCLUSTERED')
cross apply sys.dm_db_index_physical_stats (iu.database_id,iu.object_id,iu.index_id,p.partition_number,null) ips
WHERE iu.database_id = db_id('sales')
GO

--------------------------------------------------------
-- increase operations number

declare @MinutesFromSqlStart int, @i int = 0, @Operations int;

SELECT @MinutesFromSqlStart = DATEDIFF(minute, sqlserver_start_time, GETDATE()) FROM sys.dm_os_sys_info;

-- for MSSQL 2005 use this query to get @MinutesFromSqlStart (sys.dm_os_sys_info.sqlserver_start_time doesn't exists before SQL Server 2008)

--SELECT @MinutesFromSqlStart = DATEDIFF(minute, login_time, GETDATE()) --get the sqlserver start time

--FROM sys.dm_exec_sessions WHERE session_id = 1

-- get to operations number Per Day (since instance last restart) bigger than 1:
select @Operations = (cast( cast(1 as decimal(20, 2)) / cast(60 as decimal(20, 2)) * cast(@MinutesFromSqlStart as decimal(20, 2)) as int) + 1) * 2

-- for other operations number:
-- select @Operations = ?

WHILE @i < @Operations
BEGIN
INSERT INTO [dbo].[IndexFrag] (IndexFragInt, IndexFragText)
SELECT TOP 10 column_id, [name] from sys.columns;
SET @i = @i + 1;
END
GO

--------------------------------------------------------

-- select:

select iu.database_id,
iu.object_id, OBJECT_NAME(iu.object_id) as TableName,
iu.index_id, x.name as IndexName, x.type_desc,
ips.avg_fragmentation_in_percent,
iu.user_scans, iu.system_scans, iu.user_updates
from sys.dm_db_index_usage_stats iu
INNER JOIN sys.partitions p ON iu.object_id = p.object_id and iu.index_id = p.index_id
INNER JOIN sys.objects o (nolock) ON iu.object_id = o.object_id and o.type='U'
INNER JOIN sys.indexes x  (nolock) ON x.object_id = iu.object_id AND x.index_id = iu.index_id 
and x.type_desc in ('CLUSTERED','NONCLUSTERED')
cross apply sys.dm_db_index_physical_stats (iu.database_id,iu.object_id,iu.index_id,p.partition_number,null) ips
WHERE iu.database_id = db_id('sales')
GO

SQL Server: Simulate deadlock

For this deadlock simulation we need to have 2 tables, each one of them with at least one record:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AA]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[AA](

[A] [int] NULL

) ON [PRIMARY]

END

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BB]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[BB](

[B] [int] NULL

) ON [PRIMARY]

END

GO

INSERT INTO AA (A) VALUES (1);

GO

INSERT INTO BB (B) VALUES (2);

GO

----------

Now we will open two sessions:

Session #1	Session #2
BEGIN TRANSACTION; Update   AA Set    A = 3;   WAITFOR DELAY '00:00:03';   Update   BB Set    B = 4;   ROLLBACK TRANSACTION;	BEGIN TRANSACTION;   Update   BB Set    B = 4;   WAITFOR DELAY '00:00:03';   Update   AA Set    A = 3;   ROLLBACK TRANSACTION;

Start execute Session #1 and immediately execute Session #2.

The 2 sessions are waiting for each other: Session #1 lock AA, Session #2 lock BB.

This is a deadlock.

One of them will be chosen (By SQL Server) as a deadlock victim and will be "killed".

A script that make high CPU utilization

USE master

SELECT MyInt = CONVERT(BIGINT, o1.object_id) + CONVERT(BIGINT, o2.object_id) + CONVERT(BIGINT, o3.object_id)

INTO #temp

FROM sys.objects o1

JOIN sys.objects o2 ON o1.object_id < o2.object_id

JOIN sys.objects o3 ON o1.object_id < o3.object_id

SELECT SUM(CONVERT(BIGINT, o1.MyInt) + CONVERT(BIGINT, o2.MyInt))

FROM #temp o1

JOIN #temp o2 ON o1.MyInt < o2.MyInt

sp_monitor - statistics about SAP ASE Server

sp_monitor displays statistics about SAP ASE Server.

It can run with few options:

1. General information about how busy the Server has been:

sp_monitor

go

2. Information about connections:

sp_monitor "connection"

go

sp_monitor "connection","diskio"

go

3. Information about statements:

sp_monitor "statement"

go

4. Information about the most recently run procedures:

sp_monitor "procedure"

go

* and more.....

More details in SAP site.

SQL Server - Disable Parameter Sniffing

CREATE PROCEDURE MyProcedure(@MyParam INT)
AS
SELECT ...
FROM ...
WHERE  MyParam = @MyParam
OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))

Get plans of a Stored Procedure

Select  PS.total_logical_reads/NullIf(DateDiff(Minute,PS.cached_time,GetDate()),0) N,

        Coalesce(DB_Name(PS.database_id),DB_Name(PSh.dbid),DB_Name(PSph.dbid))

        DBName,

        Object_Schema_Name(Coalesce(PS.object_id,PSh.objectid,PSph.objectid),Coalesce(PS.database_id,PSh.dbid,PSph.dbid))

        SchemaName,

        Coalesce(Object_Name(PS.object_id,PS.database_id),Object_Name(PSh.objectid,PSh.dbid),Object_Name(PSph.objectid,PSph.dbid))

        ObjectName,

        PS.*,

        PSh.text,

        PSph.query_plan

From    (Select Object_Name(object_id), *

        From    sys.dm_exec_procedure_stats (NoLock)

        Where   database_id=DB_ID('DatabaseName')

                And Object_Schema_Name(object_id)='SchemaName'

                And Object_Name(object_id)='SpName'

        Order By total_logical_reads/NullIf(DateDiff(Minute,cached_time,GetDate()),0) Desc) PS

Outer Apply sys.dm_exec_sql_text(PS.sql_handle) PSh

Outer Apply sys.dm_exec_query_plan(PS.plan_handle) PSph;

"Automatic" extended events - system_health

After SQL Server installation, A default extended events session - system health – is created.

System health runs without any noticeable performance effects, and collect some important events:

• Deadlocks
• Sessions that have waited on locks, waits, etc.
• More….

See all in:

https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/use-the-system-health-session

I use it to catch deadlocks graph, and it was very helpful!

You can also filters events.

If you don’t remember the exact event name, you can type part of it:

After you get the results and see the event you need, you can specify the filter by it:

Actual very different than estimated - what to do in the first step!

You run a query / stored procedure, etc. and - bad performance... The you also see that the actual very different than estimated data in the execution plan.

There are 1,000,000 things to think about, 1,000,000 blogs posts about reasons, actions and so on about such a case.

BUT:

First of all, in those scenarios - before all the complicated things:

• Check the logic that behind the query, 
• Check that this logic is implemented in the query,
• Check that the tables order and joins is acording to the DB structure and logic.

See that the query is written as you would expect according to the database and/or the application logic.

After that - in most of the cases - 90% of the work will be done!

The query processor ran out of internal resources and could not produce a query plan

SELECT ........

FROM .....

JOIN .....

JOIN .....

JOIN .....

JOIN .....

JOIN .....

WHERE .....

..... (GROUP, HAVING, ETC.)

AND t.SomeColumn IN ( many thousands of values separated by commas )

Error message:

The query processor ran out of internal resources and could not produce a query plan.

This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions.

Please simplify the query.

If you believe you have received this message in error, contact Customer Support Services for more information.

Cause and solution:

the cause and the solution is written in msdn:

Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, in an IN clause can consume resources and return errors 8623 or 8632.

To work around this problem, store the items in the IN list in a table, and use a SELECT subquery within an IN clause.

https://msdn.microsoft.com/en-us/library/ms177682.aspx

So, bottom line:

1. create table #ATmpTable (SomeColumn nvarchar(16) )

2. insert into #ATmpTable (SomeColumn ) values ('204025191')

                * many thousands of values insertion...

3. Select with join to the temp table:

SELECT ........

FROM .....

JOIN .....

JOIN .....

JOIN #ATmpTable tmp on t.SomeColumn = tmp.SomeColumn

JOIN .....

JOIN .....

JOIN .....

WHERE .....

..... (GROUP, HAVING, ETC.)

Outer 2 statuses

The case:

2 tables, with a FK from one to a statuses log table.

We need to select for each row in the first table the last status with it's datetime and the description of it, and also - in the same row - the datetime of the last specific stauts (in the example - 303).

there is no one absolut answer, it depend on the data in the tables and exists indexes, constraints, etc om the table.

-- create the Demo tables and data:

CREATE TABLE TableA (ID int identity(1,1) NOT NULL, SomeFkID int)

CREATE TABLE TableFk (SomeFkID int, SomeFkStatus int, SomeFkDatetime datetime, SomeFkDesc nvarchar(50))

INSERT INTO TableA (SomeFkID) 

VALUES (1), (2), (3), (4)

INSERT INTO TableFk (SomeFkID, SomeFkStatus, SomeFkDatetime, SomeFkDesc)

VALUES 

(1, 1, '2017-01-01', N'FK 1 1 1'),

(2, 1, '2017-01-02', N'FK 2 1 1'), (2, 303, '2017-01-03', N'FK 2 303 2'), (2, 3, '2017-01-04', N'FK 2 3 3'), 

(2, 303, '2017-01-05', N'FK 2 303 4'), (2, 4, '2017-01-06', N'FK 2 4 5'),

(3, 1, '2017-01-01', N'FK 3 1 1'), (3, 2, '2017-01-03', N'FK 3 21'),

(4, 1, '2017-01-05', N'FK 4 1 1'), (4, 303, '2017-01-08', N'FK 4 303 1')

SELECT * FROM TableA

SELECT * FROM TableFk

--SELECT SomeFkID, SomeFkStatus, max(SomeFkDatetime) as maxSomeFkDatetime

--FROM TableFk f

--GROUP BY SomeFkID, SomeFkStatus

--ORDER BY SomeFkID, SomeFkStatus

--SELECT ROW_NUMBER()  OVER (PARTITION BY SomeFkID ORDER BY SomeFkDatetime DESC) as rn,

-- ROW_NUMBER()  OVER (PARTITION BY SomeFkID ORDER BY (case when SomeFkStatus = 303 then SomeFkDatetime else NULL end) DESC) as rn303,

-- SomeFkID, SomeFkStatus, SomeFkDatetime, SomeFkDesc

--FROM TableFk f

--ORDER BY SomeFkID, rn

-- option 1: CTE with 2 ROW_NUMBERs

; with rnFK as

(

SELECT ROW_NUMBER()  OVER (PARTITION BY SomeFkID ORDER BY SomeFkDatetime DESC) as rn,

ROW_NUMBER()  OVER (PARTITION BY SomeFkID ORDER BY (case when SomeFkStatus = 303 then SomeFkDatetime else NULL end) DESC) as rn303,

SomeFkID, SomeFkStatus, SomeFkDatetime, SomeFkDesc

FROM TableFk f

) 

SELECT a.ID, a.SomeFkID, 

f303.SomeFkDatetime AS YatzranStatusDate, 

f.SomeFkDesc AS ErrorDesc,

f.SomeFkStatus as FileStatusID,

f.SomeFkDatetime

FROM TableA a

JOIN rnFK f ON a.SomeFkID = f.SomeFkID

AND f.rn = 1

LEFT JOIN rnFK f303 ON a.SomeFkID = f303.SomeFkID

AND f303.rn303 = 1

AND f303.SomeFkStatus = 303

-- option 2: two OUTER APPLYs - one for the last status and one for 303 status

SELECT a.ID, a.SomeFkID, 

f303.SomeFkDatetime AS YatzranStatusDate, 

f.SomeFkDesc AS ErrorDesc,

f.SomeFkStatus as FileStatusID,

f.SomeFkDatetime

FROM TableA a

OUTER APPLY

( select top 1 SomeFkID, SomeFkStatus, SomeFkDatetime, SomeFkDesc

from TableFk where SomeFkID = a.SomeFkID

order by SomeFkDatetime desc

) f

OUTER APPLY

( select top 1 SomeFkID, SomeFkStatus, SomeFkDatetime, SomeFkDesc

from TableFk where SomeFkID = a.SomeFkID and SomeFkStatus = 303

order by SomeFkDatetime desc

) f303

-- option 3: one OUTER APPLY for the last status and Subquery (select top 1 order by) for the 303 status column

SELECT a.ID, a.SomeFkID, 

( select top 1 SomeFkDatetime

from TableFk where SomeFkID = a.SomeFkID and SomeFkStatus = 303

order by SomeFkDatetime desc

) AS YatzranStatusDate, 

f.SomeFkDesc AS ErrorDesc,

f.SomeFkStatus as FileStatusID,

f.SomeFkDatetime

FROM TableA a

OUTER APPLY

( select top 1 SomeFkID, SomeFkStatus, SomeFkDatetime, SomeFkDesc

from TableFk where SomeFkID = a.SomeFkID

order by SomeFkDatetime desc

) f

-- option 4: one OUTER APPLY for the last status and Subquery (max(SomeFkDatetime)) for the 303 status column

SELECT a.ID, a.SomeFkID, 

( select max(SomeFkDatetime)

from TableFk where SomeFkID = a.SomeFkID and SomeFkStatus = 303

) AS YatzranStatusDate, 

f.SomeFkDesc AS ErrorDesc,

f.SomeFkStatus as FileStatusID,

f.SomeFkDatetime

FROM TableA a

OUTER APPLY

( select top 1 SomeFkID, SomeFkStatus, SomeFkDatetime, SomeFkDesc

from TableFk where SomeFkID = a.SomeFkID

order by SomeFkDatetime desc

) f

-- option 4: FIRST_VALUE and MAX functions:

SELECT DISTINCT

a.ID, a.SomeFkID, 

MAX (case when f.SomeFkStatus = 303 then f.SomeFkDatetime else NULL end) OVER (PARTITION BY f.SomeFkID) AS YatzranStatusDate,

FIRST_VALUE(f.SomeFkDesc) OVER (PARTITION BY f.SomeFkID ORDER BY f.SomeFkDatetime desc) AS ErrorDesc,

FIRST_VALUE(f.SomeFkStatus) OVER (PARTITION BY f.SomeFkID ORDER BY f.SomeFkDatetime desc) as FileStatusID,

FIRST_VALUE(f.SomeFkDatetime) OVER (PARTITION BY f.SomeFkID ORDER BY f.SomeFkDatetime desc) as SomeFkDatetime

FROM TableA a

LEFT JOIN TableFk F ON A.SomeFkID  = f.SomeFkID

-- try it with and without :

CREATE NONCLUSTERED INDEX [IX_TableFk] ON TableFk

(

[SomeFkDatetime] DESC

) 

INCLUDE ([SomeFkID])

ON [PRIMARY]

DROP TABLE TableA 

DROP TABLE TableFk