SQL Server common statements

Original text: https://www.cnblogs.com/jearay/p/7715658.html

1.sql server permission to open clr:

exec sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
ALTER DATABASE HWMESTC SET TRUSTWORTHY ON
ALTER AUTHORIZATION ON Database::HWMESTC TO sa;

2. Query database size

Exec sp_spaceused
select name, convert(float,size) * (8192.0/1024.0)/1024. from dbo.sysfiles

3. Database log compression

--Select the database you want to use
USE PIMS
--Set the database mode to SIMPLE
ALTER DATABASE PIMS SET RECOVERY SIMPLE
-- Shrink log file to 1 M 
DBCC SHRINKFILE ('PIMS_log', 1)
-- Restore database
ALTER DATABASE PIMS SET RECOVERY FULL

4. View database connection users

Select * From sys.dm_exec_connections

5. View the session that currently occupies the most cpu resources and the statements executed in it (timely CPU)

select spid,cmd,cpu,physical_io,memusage,
(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master..sysprocesses order by cpu desc,physical_io desc

6. View the query statements that are reused less and occupy more memory in the cache (not released in the current cache) -- global

SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text] 
FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql 
ORDER BY usecounts,p.size_in_bytes  desc

7. See which tables (current database) are cached in BUFFER POOL

select OBJECT_NAME(object_id) Table name,COUNT(*) the number of pages,COUNT(*)*8/1024.0 Mb                              
from   sys.dm_os_buffer_descriptors a,sys.allocation_units b,sys.partitions c                              
where  a.allocation_unit_id=b.allocation_unit_id   
       and b.container_id=c.hobt_id             
       and database_id=DB_ID()                              
group by OBJECT_NAME(object_id)                           
order by 2 desc  

8. Query SQLSERVER memory usage

select * from sys.dm_os_process_memory

9. Query the overall memory usage of SqlServer

select      type,
sum(virtual_memory_reserved_kb)*0.1*10/1024/1024 as vm_Reserved_gb,--Reserved memory  
sum(virtual_memory_committed_kb)*0.1*10/1024/1024 as vm_Committed_gb,--Committed memory  
sum(awe_allocated_kb)*0.1*10/1024/1024 as awe_Allocated_gb,--open AWE Memory used after  
sum(shared_memory_reserved_kb)*0.1*10/1024/1024 as sm_Reserved_gb,--Shared reserved memory  
sum(shared_memory_committed_kb)*0.1*10/1024/1024 as sm_Committed_gb--Shared commit memory  
from    sys.dm_os_memory_clerks
group by type
order by type

10. Query all data pages cached in the current database, which data tables and the number of cached data pages

-- Query all data pages cached in the current database, which data tables and the number of cached data pages
-- From this information, we can see which tables the system often needs to access and how large are they?
select p.object_id, object_name=object_name(p.object_id), p.index_id, buffer_pages=count(*) 
from sys.allocation_units a, 
    sys.dm_os_buffer_descriptors b, 
    sys.partitions p 
where a.allocation_unit_id=b.allocation_unit_id 
    and a.container_id=p.hobt_id 
    and b.database_id=db_id()
group by p.object_id,p.index_id 
order by buffer_pages desc 

11. Query various execution plans of the cache and how much memory they occupy

-- Query the various execution plans of the cache and how much memory they occupy
-- Dynamic query and parameterization can be compared SQL(Amount of cache for predefined statements)
select    cacheobjtype
        , objtype
        , sum(cast(size_in_bytes as bigint))/1024 as size_in_kb
        , count(bucketid) as cache_count
from    sys.dm_exec_cached_plans
group by cacheobjtype, objtype
order by cacheobjtype, objtype

12. Query the specific execution plan in the cache and the corresponding SQL

-- Query the specific execution plan in the cache and the corresponding SQL
-- According to the data sheet or SQL Statistics can be used as a baseline and considered when adjusting the index
-- The query result will be very large. Pay attention to outputting the result set to a table or file
SELECT  usecounts ,
        refcounts ,
        size_in_bytes ,
        cacheobjtype ,
        objtype ,
        TEXT
FROM    sys.dm_exec_cached_plans cp
        CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY objtype DESC ;
GO

13. Query the overall memory usage of sql server

--query sql server Overall memory usage
  SELECT object_name, cntr_value*0.1*10/1024/1024 ,cntr_value,cntr_type,t.counter_name,t.instance_name
  FROM sys.dm_os_performance_counters t
  WHERE counter_name = 'Total Server Memory (KB)';

14. Clear the data of all tables in the database at one time

CREATE PROCEDURE sp_DeleteAllData  
AS  
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'  
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'  
EXEC sp_MSForEachTable 'DELETE FROM ?'  
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'  
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'  
EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'  
GO  

15.SQL optimization and execution time

SELECT creation_time  N'Statement compilation time'  
        ,last_execution_time  N'Last execution time'  
        ,total_physical_reads N'Total physical reads'  
        ,total_logical_reads/execution_count N'Logical reads per time'  
        ,total_logical_reads  N'Total logical reads'  
        ,total_logical_writes N'Total number of logical writes'  
        ,execution_count  N'Number of executions'  
        ,total_worker_time/1000 N'Used CPU Total time ms'  
        ,total_elapsed_time/1000  N'Total time spent ms'  
        ,(total_elapsed_time / execution_count)/1000  N'average time ms'  
        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,  
         ((CASE statement_end_offset   
          WHEN -1 THEN DATALENGTH(st.text)  
          ELSE qs.statement_end_offset END   
            - qs.statement_start_offset)/2) + 1) N'Execute statement'  
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st  
WHERE SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,  
         ((CASE statement_end_offset   
          WHEN -1 THEN DATALENGTH(st.text)  
          ELSE qs.statement_end_offset END   
            - qs.statement_start_offset)/2) + 1) NOT LIKE '%fetch%'  
ORDER BY  total_elapsed_time / execution_count DESC;  

16.truncate foreign key table stored procedure

USE PIMS
GO

CREATE PROCEDURE [dbo].[usp_Truncate_Table]
  @TableToTruncate VARCHAR(64)
AS 

BEGIN

SET NOCOUNT ON

--==Variable definition
DECLARE @i int
DECLARE @Debug bit
DECLARE @Recycle bit
DECLARE @Verbose bit
DECLARE @TableName varchar(80)
DECLARE @ColumnName varchar(80)
DECLARE @ReferencedTableName varchar(80)
DECLARE @ReferencedColumnName varchar(80)
DECLARE @ConstraintName varchar(250)

DECLARE @CreateStatement varchar(max)
DECLARE @DropStatement varchar(max)   
DECLARE @TruncateStatement varchar(max)
DECLARE @CreateStatementTemp varchar(max)
DECLARE @DropStatementTemp varchar(max)
DECLARE @TruncateStatementTemp varchar(max)
DECLARE @Statement varchar(max)

 SET @Debug = 0--(0:Related statements will be executed|1:Do not execute statement)
 SET @Recycle = 0--(0:Do not create/Do not clear storage tables|1:Will create/Clean up storage tables)
 set @Verbose = 1--(1:Print messages at each step|0:Do not print messages)

 SET @i = 1
    SET @CreateStatement = 'ALTER TABLE [dbo].[<tablename>]  WITH NOCHECK ADD  CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])'
    SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]'
    SET @TruncateStatement = 'TRUNCATE TABLE [<tablename>]'

-- Create foreign key temporary table
IF OBJECT_ID('tempdb..#FKs') IS NOT NULL
    DROP TABLE #FKs

-- Get foreign key
SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID,
       OBJECT_NAME(constraint_object_id) as ConstraintName,
       OBJECT_NAME(parent_object_id) as TableName,
       clm1.name as ColumnName, 
       OBJECT_NAME(referenced_object_id) as ReferencedTableName,
       clm2.name as ReferencedColumnName
  INTO #FKs
  FROM sys.foreign_key_columns fk
       JOIN sys.columns clm1 ON fk.parent_column_id = clm1.column_id AND fk.parent_object_id = clm1.object_id
       JOIN sys.columns clm2 ON fk.referenced_column_id = clm2.column_id AND fk.referenced_object_id= clm2.object_id
 --WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated')
 WHERE OBJECT_NAME(referenced_object_id) = @TableToTruncate
 ORDER BY OBJECT_NAME(parent_object_id)

-- Foreign key operation(delete|reconstruction)surface
IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage')
BEGIN
    IF @Verbose = 1
        PRINT '1. Creating table(Internal_FK_Definition_Storage)...'
    CREATE TABLE [Internal_FK_Definition_Storage] 
    (
        ID int not null identity(1,1) primary key,
        FK_Name varchar(250) not null,
        FK_CreationStatement varchar(max) not null,
        FK_DestructionStatement varchar(max) not null,
        Table_TruncationStatement varchar(max) not null
    ) 
END 
ELSE
BEGIN
    IF @Recycle = 0
    BEGIN
        IF @Verbose = 1
        PRINT '1. Cleaning up tables(Internal_FK_Definition_Storage)...'
        TRUNCATE TABLE [Internal_FK_Definition_Storage]    
    END
    ELSE
        PRINT '1. Cleaning up tables(Internal_FK_Definition_Storage)...'
END

IF @Recycle = 0
BEGIN
    IF @Verbose = 1
        PRINT '2. Backing up foreign key definitions...'           
    WHILE (@i <= (SELECT MAX(ID) FROM #FKs))
    BEGIN
        SET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i)
        SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i)
        SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i)
        SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i)
        SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i)

        SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'<tablename>',@TableName),'<constraintname>',@ConstraintName)
        SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName)
        SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'<tablename>',@TableName) 

        INSERT INTO [Internal_FK_Definition_Storage]
        SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp
        
        SET @i = @i + 1
        
        IF @Verbose = 1
            PRINT '  > Foreign keys backed up:[' + @ConstraintName + '] Belonging table: [' + @TableName + ']'
    END   
END   
ELSE 
    PRINT '2. Backing up foreign key definitions...'

IF @Verbose = 1
    PRINT '3. Removing foreign keys...'
BEGIN TRAN    
BEGIN TRY
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
    SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
    SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i)
    IF @Debug = 1 
        PRINT @Statement
    ELSE
        EXEC(@Statement)
    SET @i = @i + 1
    IF @Verbose = 1
        PRINT '  > Foreign key deleted:[' + @ConstraintName + ']'
END     

IF @Verbose = 1
    PRINT '4. Cleaning up data table...'
--Clear the table where the foreign key is located first(Because the table where the foreign key is located may still be referenced by other foreign keys, circular recursive processing is required)(Note: this processing is not realized)
--Please do not use the following comment code
/*    
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
    SET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
    IF @Debug = 1 
        PRINT @Statement
    ELSE
        EXEC(@Statement)
    SET @i = @i + 1
    IF @Verbose = 1
        PRINT '  > ' + @Statement
END
*/

IF @Debug = 1 
    PRINT 'TRUNCATE TABLE [' + @TableToTruncate + ']'
ELSE
    EXEC('TRUNCATE TABLE [' + @TableToTruncate + ']')
IF @Verbose = 1
    PRINT '  > Cleaned data table[' + @TableToTruncate + ']'
    
IF @Verbose = 1
    PRINT '5. Rebuilding foreign keys...'
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
    SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
    SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
    IF @Debug = 1 
        PRINT @Statement
    ELSE
        EXEC(@Statement)
    SET @i = @i + 1
    IF @Verbose = 1
    PRINT '  > Foreign key rebuilt:[' + @ConstraintName + ']'
END
    COMMIT
END TRY
BEGIN CATCH
    ROLLBACK 
    PRINT 'Error message:'+ERROR_MESSAGE()
END CATCH
IF @Verbose = 1
    PRINT '6. Processing completed!'
END

 17. View job run duration

SELECT 
     [T1].[job_id]
    ,[T1].[name] AS [job_name] 
    ,[T2].[run_status]
    ,[T2].[run_date]
    ,[T2].[run_time]
    ,[dbo].[agent_datetime]([T2].[run_date], [T2].[run_time]) AS [run_datetime]
    ,[T2].[run_duration]
    ,DATEDIFF(SECOND, '1900-01-01', DATEADD(SECOND, 31, [dbo].[agent_datetime](19000101, [run_duration]))) AS [run_duration_s]
FROM 
    [dbo].[sysjobs] AS T1
    INNER JOIN [dbo].[sysjobhistory] AS T2
        ON [T2].[job_id] = [T1].[job_id]
WHERE 
    [T1].[enabled] = 1
    AND [T2].[step_id] = 0
    AND [T2].[run_duration] >= 1
    and [T1].[name]='PIMS_CreatePaperCraftParameterAnalysisData'
ORDER BY
     [T2].[job_id] ASC
    ,[T2].[run_date] ASC
GO

 18. Releases all unused cache entries from all caches

DBCC FREESYSTEMCACHE('ALL');

 19. Query and release deadlock

--Query table deadlock information
select object_name(resource_associated_entity_id) as tableName, request_session_id as pid from sys.dm_tran_locks
where resource_type = 'OBJECT'

dbcc opentran

--View deadlock details and execution sql sentence
exec sp_who2 53
--exec sp_who 53 
DBCC inputbuffer (53)

--Deadlock Relieving 
kill 53

 20. Query SQL Server lists the top 5 worst performance queries according to CPU consumption

-- Worst performing CPU bound queries
SELECT TOP 5
    st.text,
    qp.query_plan,
    qs.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC
GO

 21. Shows how to find your worst performing queries based on I/O consumption

-- Worst performing I/O bound queries
SELECT TOP 5
    st.text,
    qp.query_plan,
    qs.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_logical_reads DESC
GO

22. Query some special information of the server

select SERVERPROPERTY(N'edition') as Edition     --Data version, such as enterprise version, development version, etc
    ,SERVERPROPERTY(N'collation') as Collation   --Database character set
    ,SERVERPROPERTY(N'servername') as ServerName --service name
    ,@@VERSION as Version   --Database version number
    ,@@LANGUAGE AS Language  --The language used by the database, such as us_english etc.

 23. Query the size of each data table in the database

-- =============================================
-- Description: update the size of each table in the query database, and store the results in the data table
-- =============================================

    --Query whether there is a result storage table
    IF NOT EXISTS (SELECT * FROM sysobjects where id = OBJECT_ID(N'temp_tableSpaceInfo') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    BEGIN
        --Create if not present
        CREATE TABLE temp_tableSpaceInfo
        (name NVARCHAR(128), 
        rows char(11), 
        reserved VARCHAR(18),
        data VARCHAR(18),
        index_size VARCHAR(18),
        unused VARCHAR(18))
    END
    --Empty data table
    DELETE FROM temp_tableSpaceInfo

    --Defines the name of the table where temporary variables are stored during traversal
    DECLARE @tablename VARCHAR(255)

    --Use the cursor to read all table names in the database
    DECLARE table_list_cursor CURSOR FOR 
    SELECT name FROM sysobjects 
    WHERE OBJECTPROPERTY(id, N'IsTable') = 1 AND name NOT LIKE N'#%%' ORDER BY name

    --Open cursor
    OPEN table_list_cursor
    --Read the first data
    FETCH NEXT FROM table_list_cursor INTO @tablename 

    --Traverse the queried table name
    WHILE @@FETCH_STATUS = 0
    BEGIN
        --Check whether the current table is a user table
        IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(@tablename) AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
        BEGIN
            --The current table reads its information and inserts it into the table
            EXECUTE sp_executesql N'INSERT INTO temp_tableSpaceInfo EXEC sp_spaceused @tbname', N'@tbname varchar(255)', @tbname = @tablename
        END
        --Read next data
        FETCH NEXT FROM table_list_cursor INTO @tablename 
    END

    --Release cursor
    CLOSE table_list_cursor
    DEALLOCATE table_list_cursor

    SELECT *,replace(reserved,'KB','')/1024 Data table size M FROM temp_tableSpaceInfo order by replace(reserved,'KB','')/1024 desc
    drop table temp_tableSpaceInfo

24. Compress database, files and logs

DBCC ShrinkFile('Database name',  targetsize);            /* Shrink database file */
DBCC ShrinkFile('Database name_log',  targetsize);        /* Shrink log file */
Targetsize: The unit is megabytes, which must be an integer, DBCC SHRINKFILE Attempt to shrink the file to the specified size.

DBCC SHRINKFILE The file will not shrink to less than the size of "space actually used", such as "allocated space" of 10 M,""Actual space used" is 6 M,When formulated targetsize When it is 1, the file is shrunk to 6 M,The file will not shrink to 1 M. 

--Shrink database
DBCC SHRINKDATABASE(Database name, percentage)
Percentage: refers to the maximum available space in the shrunk file. The value range is greater than or equal to 0 and less than 100%",It can be set to 0 in actual use.

 25. Capture past deadlock information with extended time

DECLARE @SessionName SysName 

SELECT @SessionName = 'system_health'


IF OBJECT_ID('tempdb..#Events') IS NOT NULL BEGIN
    DROP TABLE #Events
END

DECLARE @Target_File NVarChar(1000)
    , @Target_Dir NVarChar(1000)
    , @Target_File_WildCard NVarChar(1000)

SELECT @Target_File = CAST(t.target_data as XML).value('EventFileTarget[1]/File[1]/@name', 'NVARCHAR(256)')
FROM sys.dm_xe_session_targets t
    INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
WHERE s.name = @SessionName
    AND t.target_name = 'event_file'

SELECT @Target_Dir = LEFT(@Target_File, Len(@Target_File) - CHARINDEX('\', REVERSE(@Target_File))) 

SELECT @Target_File_WildCard = @Target_Dir + '\'  + @SessionName + '_*.xel'

--Keep this as a separate table because it's called twice in the next query.  You don't want this running twice.
SELECT DeadlockGraph = CAST(event_data AS XML)
    , DeadlockID = Row_Number() OVER(ORDER BY file_name, file_offset)
INTO #Events
FROM sys.fn_xe_file_target_read_file(@Target_File_WildCard, null, null, null) AS F
WHERE event_data like '<event name="xml_deadlock_report%'

;WITH Victims AS
(
    SELECT VictimID = Deadlock.Victims.value('@id', 'varchar(50)')
        , e.DeadlockID 
    FROM #Events e
        CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/victim-list/victimProcess') as Deadlock(Victims)
)
, DeadlockObjects AS
(
    SELECT DISTINCT e.DeadlockID
        , ObjectName = Deadlock.Resources.value('@objectname', 'nvarchar(256)')
    FROM #Events e
        CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/resource-list/*') as Deadlock(Resources)
)
SELECT *
FROM
(
    SELECT e.DeadlockID
        , TransactionTime = Deadlock.Process.value('@lasttranstarted', 'datetime')
        , DeadlockGraph
        , DeadlockObjects = substring((SELECT (', ' + o.ObjectName)
                            FROM DeadlockObjects o
                            WHERE o.DeadlockID = e.DeadlockID
                            ORDER BY o.ObjectName
                            FOR XML PATH ('')
                            ), 3, 4000)
        , Victim = CASE WHEN v.VictimID IS NOT NULL 
                            THEN 1 
                        ELSE 0 
                        END
        , SPID = Deadlock.Process.value('@spid', 'int')
        , ProcedureName = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)')
        , LockMode = Deadlock.Process.value('@lockMode', 'char(1)')
        , Code = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)')
        , ClientApp = CASE LEFT(Deadlock.Process.value('@clientapp', 'varchar(100)'), 29)
                        WHEN 'SQLAgent - TSQL JobStep (Job '
                            THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj WHERE substring(Deadlock.Process.value('@clientapp', 'varchar(100)'),32,32)=(substring(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(Deadlock.Process.value('@clientapp', 'varchar(100)'), 67, len(Deadlock.Process.value('@clientapp', 'varchar(100)'))-67)
                        ELSE Deadlock.Process.value('@clientapp', 'varchar(100)')
                        END 
        , HostName = Deadlock.Process.value('@hostname', 'varchar(20)')
        , LoginName = Deadlock.Process.value('@loginname', 'varchar(20)')
        , InputBuffer = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
    FROM #Events e
        CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/process-list/process') as Deadlock(Process)
        LEFT JOIN Victims v ON v.DeadlockID = e.DeadlockID AND v.VictimID = Deadlock.Process.value('@id', 'varchar(50)')
) X
ORDER BY DeadlockID DESC

 26. Database object information retrieval

--View the description of the object
exec sp_help 'T_papermachine'
--Displays the definition script of views, stored procedures, functions and triggers. 
exec sp_helptext 'proc_report_getmeasuredata' 
--Displays the number of rows and space occupied by the table.  
exec sp_spaceused  'T_papermachine'
--Display the first 100 rows of a table or view, select“ tablename,1000"Press Ctrl+F1 The first 1000 rows of the table can be displayed.
exec sp_executesql N'IF OBJECT_ID(@tablename) IS NOT NULL EXEC(N''SELECT TOP(''+@n+N'') * FROM ''+@tablename)',N'@tablename nvarchar(100)=''t_papermachine'',@n int=100' 
--Displays the space occupied by each index in the table.  
exec sp_executesql N'SELECT index_name = ind.name, ddps.used_page_count, ddps.reserved_page_count, ddps.row_count FROM sys.indexes ind INNER JOIN sys.dm_db_partition_stats ddps ON ind.object_id = ddps.object_id AND ind.index_id = ddps.index_id WHERE ind.object_id = OBJECT_ID(@tablename)',N'@tablename nvarchar(100)=''t_papermachine''' 
--Displays the field names of the table or view, separated by commas.  
exec sp_executesql N'SELECT columns = STUFF((SELECT '', ''+name FROM sys.columns WHERE object_id = OBJECT_ID(@tablename) FOR XML PATH('''')),1,2,'''')',N'@tablename nvarchar(100)=''T_Papermachine''' 
--Look up tables, views, stored procedures and functions in the current database according to the selected keywords  
exec sp_executesql N'SELECT * FROM sys.objects WHERE type IN (''U'',''V'',''P'',''FN'') AND name LIKE ''%''+@keyword+''%'' ORDER BY type,name',N'@keyword nvarchar(50)=''machine'''  
--Query the tables, views, stored procedures and functions containing the specified keywords in the database
select routine_name,routine_definition,routine_type
from information_schema.routines
where routine_definition like '%AssessmentSpeed%'
order by routine_type
--Fuzzy query stored procedure sql Contains a text
SELECT obj.Name Stored procedure name, sc.TEXT Stored procedure content
FROM syscomments sc
INNER JOIN sysobjects obj ON sc.Id = obj.ID
WHERE sc.TEXT LIKE '%Stored procedure content%'

27. Database user and permission Operation

USE [master]
GO
--Account and password to be confirmed
CREATE LOGIN [NDIT] WITH PASSWORD=N'1', DEFAULT_DATABASE=[PIMS], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE PIMS
go
CREATE USER [NDIT] FOR LOGIN [NDIT]
GO
--Large permission. If it is a specified part of the table, do not execute this. If all contents can be read, use this script
--EXEC sp_addrolemember N'db_datareader', N'NDIT'
--GO
--Specify a specific table name to add/to update/query
DECLARE @Sql NVARCHAR(max)
SET @Sql=''
--table
--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.tables AS a WHERE name IN ('Tab1','Tab2');
--view
--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.views AS a WHERE name IN ('view1','view2');
--procedure
--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.procedures AS a WHERE name IN ('proc1','proc2');

PRINT @Sql

EXEC(@Sql)
go

--Disable login account
alter login NDIT disable
--Enable login account
alter login NDIT enable

--Login account name change
alter login NDIT with name=dba_tom

--Login account password change: 
alter login NDIT with password='aabb@ccdd'

--Database user name change: 
alter user NDIT with name=dba_tom

--Change database user defult_schema:  
alter user NDIT with default_schema=sales

--Delete database user: 
drop user NDIT

--delete SQL Server Login account: 
drop login NDIT

 

 28. Use Checksum combined with NewID to obtain random numbers

Create FUNCTION Scalar_CheckSumNEWID  
(  
    @From int,  
    @To int,  
    @Keep int,  
    @newid varchar(50)  
)  
RETURNS float  
BEGIN  
    DECLARE @ResultVar float  
    SELECT @ResultVar=CONVERT(BIGINT,RIGHT(ABS(CHECKSUM(@newid)),9))*0.1/100000000  
    RETURN @From+round((@To-@From)*@ResultVar,@Keep)  
END  
GO  

 29. Query the attribute information of database table fields to facilitate direct copying and export of excel tables

 

 30. Judge whether there are databases, tables, columns and views

 

 31. The execution time of stored procedure of CTE query is significantly longer than that of T-Sql query. You can add the "WITH RECOMPILE" parameter to force the stored procedure to recompile each time it is executed, so as to realize fast query.

Great God's post: Parameter Sniffing, Embedding, and the RECOMPILE Options 

 32. To solve the problem of insert exec nesting, the solution is to establish a database pointing to itself and add a link server.

--1. First, add a linked server:

   exec sp_addlinkedserver 'srv1','','SQLOLEDB','(local)'   
   exec sp_addlinkedsrvlogin 'srv1','false',null,'sa','sa'

--2. Secondly, find the linked server, right-click properties and open RPC: 

   Server object->Link server->Right click->attribute->Server Options->RPC,RPC Out All set to True

--3. start-up MSDTC Services:

The service name is: MSDTC(Display name is Distributed Transaction Coordinator)
  If it is not started, an error will be reported as follows: MSDTC on server 'servername' is unavailable 

--4. Adjust stored procedure access, using srv1 Call stored procedure

insert #Temp exec srv1.DBName.dbo.Proc_Test @param

--5. success! end!

 33. Number of queries, database connections, etc

--View connection to database"DB"Connection of
SELECT * from master.dbo.sysprocesses WHERE dbid = DB_ID('DB')
--Query the connection of a database user
sp_who 'sa'
--View the maximum connections allowed in the database
select @@MAX_CONNECTIONS
--View the number of connections to the database since it was last started
SELECT @@CONNECTIONS
--Close the connection and the above query can get spid,according to spid,Just close the process.
kill 54

 34. Database cache cleanup

CREATE PROCEDURE [dbo].ClearMemory   
AS   
BEGIN 
    --Clear all caches 
    DBCC DROPCLEANBUFFERS 
    --Open advanced configuration 
    exec sp_configure 'show advanced options', 1 
    --Set the maximum memory value to clear the existing cache space 
    exec sp_configure 'max server memory', 25600 
    EXEC ('RECONFIGURE') 
    --Set waiting time 
    WAITFOR DELAY '00:00:01' 
    --Reset maximum memory 
    EXEC  sp_configure 'max server memory',40960 
    EXEC ('RECONFIGURE') 
    --Turn off advanced configuration 
    exec sp_configure 'show advanced options',0 
END
GO 

  35. Modify memory with sqlcmd command

net start MSSQLServer /mSQLCMD /f

SQLCMD

EXEC sp_configure 'show advanced option', '1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max server memory', '40960'
GO
RECONFIGURE WITH OVERRIDE
GO
go

 36. Database restore in progress...

RESTORE DATABASE Database name WITH RECOVERY

 37. SQL server queries all child nodes

use CTE recursion
;with f as 
(
select * from tab where id=1
union all
select a.* from tab as a inner join f as b on a.pid=b.id
)
select * from f

 38. Perform row Association calculation with the dataset by offset

Lag (accessing data in the first row of the same result set), lead (accessing data in subsequent rows of the same result set)

https://docs.microsoft.com/zh-cn/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15

 39. Column splice string

select stuff((
select ';'+name
from Student for xml path('')),1,1,'') as name

 40. Update function

EXECUTE sp_refreshsqlmodule N'[dbo].[Proc_Chart]';

 41. Row to column

Declare @tanks nvarchar(200);
Declare @sql nvarchar(4000)
set  @tanks=(SELECT STUFF((SELECT ','+tl.UnitCode FROM dbo.V_CP_Tank tl   FOR XML PATH('')),1,1,''))
SELECT @tanks

SET @sql='
    SELECT DataDate,'+@tanks+'
    FROM CP_TankMass
    PIVOT(sum(RealQuantity) FOR [UnitCode] IN('+@tanks+')) AS T 
    '
Exec(@sql)

 

Tags: SQL Server

Posted by jllydgnt on Sun, 15 May 2022 08:19:45 +0300