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)