Lập Trình .NET

select tb.name as table_name, cl.name as column_name
, type.name as type_name
, cl.max_length
from sys.columns cl
join sys.tables tb on cl.object_id = tb.object_id
join sys.types type on cl.user_type_id = type.user_type_id
where type.name in ('date', 'datetime', 'datetime2')
order by tb.name, cl.name

-- batch size: 20,000
-- total records: 10,000,000

DELETE TOP (20000)
FROM TableName
WHERE Condition

GO 500

EXEC sp_estimate_data_compression_savings 
    @schema_name = 'dbo', 
    @object_name = 'TableName', 
    @index_id = NULL, 
    @partition_number = NULL, 
    @data_compression = 'ROW'
GO

EXEC sp_estimate_data_compression_savings 
    @schema_name = 'dbo', 
    @object_name = 'TableName', 
    @index_id = NULL, 
    @partition_number = NULL, 
    @data_compression = 'PAGE'
GO

SELECT [database_id]
      ,i.[object_id]
      ,i.[index_id]
      ,DB_NAME(i.database_id) as DatabaseName
      ,OBJECT_SCHEMA_NAME (i.object_id, i.database_id) + '.'
      + OBJECT_NAME(i.object_id, i.database_id) as ObjectName
      ,idx.name as IndexName
      ,[partition_number]
      ,[index_type_desc]
      ,[alloc_unit_type_desc]
      ,[index_depth]
      ,[index_level]
      ,[avg_fragmentation_in_percent]
      ,[fragment_count]
      ,[avg_fragment_size_in_pages]
      ,[page_count]
      ,[avg_page_space_used_in_percent]
      ,[record_count]
      ,[ghost_record_count]
      ,[version_ghost_record_count]
      ,[min_record_size_in_bytes]
      ,[max_record_size_in_bytes]
      ,[avg_record_size_in_bytes]
      ,[forwarded_record_count]
      ,[compressed_page_count]
INTO #dm_db_index_physical_stats_logs
FROM sys.dm_db_index_physical_stats  (DB_ID(), NULL, NULL, NULL , 'DETAILED') i
JOIN sys.indexes idx on idx.object_id = i.object_id and idx.index_id = i.index_id;  
GO 


SELECT * 
FROM #dm_db_index_physical_stats_logs
--WHERE object_id = 1477580302
ORDER BY DatabaseName, ObjectName, index_id, index_level
GO

SELECT 
 i.database_id,
 i.object_id,
 i.index_id,
 DB_NAME(i.database_id) as DatabaseName,
 OBJECT_SCHEMA_NAME (i.object_id, i.database_id) + '.' 
 + OBJECT_NAME(i.object_id, i.database_id) as ObjectName,
 idx.name as IndexName,
 i.user_seeks,
 i.user_scans,
 i.user_lookups,
 i.user_updates,
 (user_seeks + user_scans + user_lookups) as TotaledSeekScanLookUp,
 i.last_user_seek,
 i.last_user_scan,
 i.last_user_lookup,
 i.last_user_update,
 i.system_seeks,
 i.system_scans,
 i.system_lookups,
 i.system_updates,
 i.last_system_seek,
 i.last_system_scan,
 i.last_system_lookup,
 i.last_system_update
INTO #dm_db_index_usage_stats_logs
FROM sys.dm_db_index_usage_stats i
JOIN sys.indexes idx on idx.object_id = i.object_id and idx.index_id = i.index_id
GO

SELECT *
FROM #dm_db_index_usage_stats_logs
--WHERE database_id = 9 and object_id = 1477580302
--and user_updates > (user_seeks + user_scans + user_lookups)
ORDER BY DatabaseName, ObjectName, user_seeks
GO

IF OBJECT_ID(N'[dbo].[SpaceUsedLogs]', N'U') IS NULL
BEGIN
 CREATE TABLE SpaceUsedLogs (
  name VARCHAR(200)
  ,rows BIGINT
  ,reserved VARCHAR(100)
  ,data VARCHAR(100)
  ,index_size VARCHAR(100)
  ,unused VARCHAR(100)
  ,created_date DATETIME DEFAULT getdate()
  )
END
GO

DECLARE @spaceUsed TABLE (
 name VARCHAR(200)
 ,rows BIGINT
 ,reserved VARCHAR(100)
 ,data VARCHAR(100)
 ,index_size VARCHAR(100)
 ,unused VARCHAR(100)
 )

INSERT INTO @spaceUsed
EXEC sp_spaceused [Table Name 1]

INSERT INTO @spaceUsed
EXEC sp_spaceused [Table Name 2]

-- ...

INSERT INTO SpaceUsedLogs (name, rows, reserved, data, index_size, unused)
SELECT * FROM @spaceUsed

SELECT 
    XEvent.query('data[@name="xml_report"]/value/deadlock') AS deadlock_graph
FROM    (SELECT CAST([target_data] AS XML) AS TargetData
         FROM sys.dm_xe_session_targets AS st
         INNER JOIN sys.dm_xe_sessions AS s 
            ON [s].[address] = [st].[event_session_address]
         WHERE [s].[name] = N'system_health'
           AND [st].[target_name] = N'ring_buffer') AS Data
CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent);