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)