Data Science Lab

ALTER INDEX [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID]

ON [Sales].[SalesOrderDetailEnlarged] REORGANIZE;

UPDATE STATISTICS [Sales].[SalesOrderDetailEnlarged] WITH FULLSCAN, ALL;

SPID

Status

BlkBy

DBName

Command

57

RUNNABLE

.

AdventureWorks2014

DBCC

59

RUNNABLE

.

AdventureWorks2014

UPDATE STATISTIC

SELECT *

FROM [Sales].[SalesOrderDetailEnlarged]

WHERE [SalesOrderId]=1302257;

SPID

Status

BlkBy

DBName

Command

57

RUNNABLE

.

AdventureWorks2014

DBCC

58

SUSPENDED

59

dventureWorks2014

SELECT

59

RUNNABLE

57

AdventureWorks2014

UPDATE STATISTIC

SET NOCOUNT ON

GO

SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH INTO #T

FROM sys.sysprocesses R

CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T

GO

WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)

AS (SELECT

       SPID,

       BLOCKED,

       CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,

       BATCH

   FROM #T R

   WHERE (BLOCKED = 0 OR BLOCKED = SPID)

   AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)

   UNION ALL

   SELECT

      R.SPID,

      R.BLOCKED,

      CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,

      R.BATCH

   FROM #T AS R

   INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID

   WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID

   )

SELECT

   N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 1) +

   CASE WHEN (LEN(LEVEL)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END

   + CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE

FROM BLOCKERS ORDER BY LEVEL ASC

GO

DROP TABLE #T

GO

BLOCKING_TREE

HEAD - 57 alter index [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID]...

| |------ 59 UPDATE STATISTICS Sales.SalesOrderDetailEnlarged WITH FULLSCAN, ALL

| | |------ 58 SELECT * FROM [Sales].[SalesOrderDetailEnlarged] WHERE [SalesOrderId]=...

SELECT

  tl.request_session_id as spid,tl.resource_type,

  tl.resource_subtype,

  CASE

     WHEN resource_type = 'OBJECT' THEN OBJECT_NAME(tl.resource_associated_entity_id, tl.resource_database_id)

     ELSE ''

  END AS object,

  tl.resource_description,

  request_mode,

  request_type,

  request_status,

  wt.blocking_session_id as blocking_spid

FROM sys.dm_tran_locks tl

LEFT JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address

WHERE tl.request_session_id in (57,58,59);

IF NOT EXISTS (SELECT [object_id] FROM sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID(N'[CustomersTableGuid]') AND [type] IN (N'U'))

CREATE TABLE CustomersTableGuid

(

    ID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50)

)

GO

IF NOT EXISTS (SELECT [object_id] FROM sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID(N'[CustomersTableIdent]') AND [type] IN (N'U'))

CREATE TABLE CustomersTableIdent

(

    ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50)

)

GO

SET NOCOUNT ON;

DECLARE @i INT = 0

WHILE (@i <= 1000000)

BEGIN

    INSERT INTO CustomersTableGuid (FirstName, LastName)

    VALUES ('FirstName' + CAST(@i AS VARCHAR),'LastName' + CAST(@i AS VARCHAR))

    INSERT INTO CustomersTableIdent (FirstName, LastName)

    VALUES ('FirstName' + CAST(@i AS VARCHAR),'LastName' + CAST(@i AS VARCHAR))

    SET @i +=1

END

UPDATE STATISTICS CustomersTableGuid WITH FULLSCAN

GO

UPDATE STATISTICS CustomersTableIdent WITH FULLSCAN

GO

SELECT OBJECT_NAME(stat.[object_id]) AS [TableName], sp.stats_id, name,

    last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter  

FROM sys.stats AS stat  

CROSS APPLY sys.dm_db_stats_properties(stat.[object_id], stat.stats_id) AS sp 

WHERE stat.[object_id] = OBJECT_ID('CustomersTableGuid')

    OR stat.[object_id] = OBJECT_ID('CustomersTableIdent');

GO

SELECT OBJECT_NAME(stat.[object_id]) AS [TableName], stat.name, sh.stats_id,

    sh.range_high_key, sh.range_rows, sh.equal_rows

FROM sys.stats AS stat

INNER JOIN sys.stats_columns AS sc

    ON stat.stats_id = sc.stats_id AND stat.[object_id] = sc.[object_id]

INNER JOIN sys.all_columns AS ac

    ON ac.column_id = sc.column_id AND ac.[object_id] = sc.[object_id]

CROSS APPLY sys.dm_db_stats_histogram(stat.[object_id], stat.stats_id) AS sh

WHERE (stat.[object_id] = OBJECT_ID('CustomersTableGuid')

    OR stat.[object_id] = OBJECT_ID('CustomersTableIdent'))

    AND ac.name = 'ID';

GO

UPDATE STATISTICS CustomersTableGuid WITH SAMPLE 90 PERCENT

GO

UPDATE STATISTICS CustomersTableIdent WITH SAMPLE 90 PERCENT

GO