Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the commonly used sql sentences in sqlserver dba". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the commonly used sql sentences in sqlserver dba"?

Sqlserver views information at the instance level, using the SERVERPROPERTY function

Select SERVERPROPERTY ('propertyname')

View the configuration of a parameter XX at the instance level

Select * from sys.configurations where name='XX'

Change the value of a parameter XX at the instance level

Sp_configure 'XX','0'

RECONFIGURE WITH OVERRIDE

Sp_configure displays or changes the global configuration settings for the current server.

RECONFIGURE says SQL Server will take effect immediately without a restart.

When you use sp_configure to change settings, use the reconfigure statement to make the changes take effect immediately, otherwise the changes will take effect after SQL Server restarts. Adding WITH OVERRIDE after RECONFIGURE means that it will take effect no matter whether the value meets the requirements or not. For example, the range value of recovery interval is 10, sys.configurations.minimum is 10, and sys.configurations.maximum is 60. If sp_configure 'recovery interval', 75 is set to 75, it exceeds the 10Muth60 specification, but for 75 to take effect, WITH OVERRIDE must be added.

Sqlserver does not have a system table to query all the objects under the database

The following can only be checked under the current database

Select * from sys.all_objects-query all schema-scoped objects in the current database

Select * from sys.sysobjects-query all objects in the current database

Views such as sys.all_objects and sys.sysobjects are available under the system view of each database.

Select * from sys.databases-all database information can be queried under the current database, including whether on status

Select * from sys.sysdatabases-all database information can be queried under the current database, excluding on status. This system view will be deleted in subsequent versions.

Views such as sys.databases and sys.sysdatabases are available under the system view of each database.

Sys.processes-there is no such view

Select * from sys.sysprocesses-you can query information about all processes running on SQL Server instances under the current database, that is, threads on all databases. This system view will be deleted in subsequent versions.

Global system view, single database system view

Sys.database_files-each database file stored in the database itself occupies a row in the table. This is a view based on each database.

Sys.master_files-- one line for each file in the master database. This is a system-wide view.

Views such as sys.database_files and sys.master_files are available under the system view of each database.

Some system tables that only exist in msdb, not system views

Dbo.backupset

Dbo.log_shipping_secondary

Dbo.restorehistory

Dbo.sysjobs

Dbo.sysjobhistory

These system tables only exist in the msdb database and must be prefixed with msdb when used.

Some system stored procedures such as sp_lock, sp_who, sp_who2, sp_helptext and so on exist in each database.

Report information about locks, and display lock information and blocking information for all databases in the instance.

Sp_lock

Provide information about the current user, session, and process in the instance. You can see the status of the session running, SUSPENDED, sleeping, rollback,sp_who2. You can determine whether the corresponding transaction is very large through CPUTime and DiskIO.

Sp_who

Sp_who2

Sp_who2 active (optional parameter LoginName, or active represents active sessions)

In addition to displaying the output information of sp_who, sp_who2 also displays the following information:

CPUTime (total CPU time occupied by the process)

DiskIO (total number of disk reads by the process)

LastBatch (the last time the customer called the stored procedure or executed the query)

ProgramName (the name of the application used to initialize the connection, or hostname)

View the contents of a stored procedure

Sp_helptext pro_name

DBCC INPUTBUFFER

Displays the last statement sent to the sqlserver database by a thread number

DBCC INPUTBUFFER (249)

Assuming that the query is blocked by the lock, execute the above to find the blocked SQL statement

Check whether there is an active transaction in a database. If there is an active transaction, a log will be written, showing the ID of the earliest transaction.

DBCC OPENTRAN (dbname)

Monitor log space

DBCC SQLPERF (LOGSPACE)

Find out why the space in the log cannot be reused (the log cannot be truncated so that the log file is getting larger and larger, but the free space is too small to shrink)

Select name,log_reuse_wait_desc from sys.databases

View virtual log file information

DBCC LOGINFO

How many lines are there as a result, which represents how many virtual log files there are? the status (status) of the active virtual log file is 2

Repair msdb database, such as ssms page sql server agent missing or unable to see job view history and other functions, indicating that msdb is broken and needs to be repaired

Dbcc checkdb (msdb)

Generate a manual checkpoint in the SQL Server database you are currently connected to

CHECKPOINT [checkpoint_duration]

-- checkpoint_duration indicates that the time required for manual checkpoints to complete is specified in seconds. This parameter is generally not used and is controlled by the database itself.

View various database settings

Select name,State,user_access,is_read_only,recovery_model from sys.databases

Check to see if a session exists in a database

Select DB_NAME (dbid), * from sys.sysprocesses where dbid=db_id ('dbname')

Query all currently blocked requests

Select * from sys.sysprocesses where blocked > 0

Or

SELECT t1.resourceworthy type journal dbSecretName (t1.resource_database_id), t1.resourceworthy associatedaccounentitycorreciddd1.requestroommode

T1.request_session_id,t2.blocking_session_id,t2.wait_duration_ms

FROM sys.dm_tran_locks as t1

INNER JOIN sys.dm_os_waiting_tasks as t2

ON t1.lock_owner_address = t2.resource_address

Or

Select A.SPID as blocked process, what a.CMD AS is doing, b.spid AS blocking process number, b.cmd AS blocking what the process is doing

From master..sysprocesses a,master..sysprocesses b

Where a.blocked0 and a.uploked= b.spid

Or

SELECT session_Id,spid,ecid,DB_NAME (sp.dbid), nt_username,er.status,wait_type

[Individual Query] = SUBSTRING (qt.text,er.statement_start_offset / 2)

(CASE

WHEN er.statement_end_offset =-1

THEN

LEN (CONVERT (NVARCHAR (MAX), qt.text) * 2

ELSE

Er.statement_end_offset

END

-er.statement_start_offset)

/ 2)

Qt.text,program_name,Hostname,nt_domain,start_time

FROM sys.dm_exec_requests er

INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid

CROSS APPLY sys.dm_exec_sql_text (er.sql_handle) AS qt

WHERE session_Id > 50 / * Ignore system spids.*/

AND sp.blocked > 0 AND session_Id NOT IN (@ @ SPID)

Or

SELECT session_id, status, blocking_session_id

, wait_type, wait_time, wait_resource

, transaction_id

FROM sys.dm_exec_requests

WHERE status = Nisssuspended`

-- sys.dm_exec_requests returns information about each request being executed in the SQL Server

See which tables are locked and which process they are locked by:

Select request_session_id spid,OBJECT_NAME (resource_associated_entity_id) tableName

From sys.dm_tran_locks where resource_type='OBJECT' ORDER BY request_session_id ASC

Query whether a job is blocked

Select * from msdb.dbo.sysjobs where name='jobname'

Select a.programmatic name. * from master..sysprocesses a where a.program_name like'% 0D1CE57E8AC5%'

-- substitute the job_id queried by the first statement into the program_name of the second statement

Check whether SQL Agent is turned on

IF EXISTS (

SELECT TOP 1 1

FROM sys.sysprocesses

WHERE program_name = 'SQLAgent-Generic Refresher'

)

SELECT 'Running'

ELSE

SELECT 'Not Running'

View the sql statements executed by the active thread and generate statements that are killed in bulk

Select 'KILL' + CAST (a.spid AS NVARCHAR) AS KillCmd,REPLACE (hostname,'','') as hostname, replace (program_name,'','') as program_name

, REPLACE (loginame,'',') AS loginame, db_name (a.dbid) AS DBname,spid,blocked,waittime/1000 as waittime

, a.statusjue replace (b.text.thecontrollation) as sqlmessage,cpu

From sys.sysprocesses as a with (nolock)

Cross apply sys.dm_exec_sql_text (sql_handle) as b

Where a.statusdispensing 'AND a.spid@@SPID

View backup progress

SELECT DB_NAME (database_id) AS Exec_DB

, percent_complete

, CASE WHEN estimated_completion_time

< 36000000 THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600) + ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2) + ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining] ,b.text as tsql ,* FROM SYS.DM_EXEC_REQUESTS cross apply sys.dm_exec_sql_text(sql_handle) as b WHERE command LIKE 'Backup%' --and database_id=db_id('cardorder') --OR command LIKE 'RESTORE%' ORDER BY 2 DESC 查看恢复进度 SELECT DB_NAME(database_id) AS Exec_DB ,percent_complete ,CASE WHEN estimated_completion_time < 36000000 THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600) + ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2) + ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining] ,b.text as tsql ,* FROM SYS.DM_EXEC_REQUESTS cross apply sys.dm_exec_sql_text(sql_handle) as b WHERE command LIKE 'RESTORE%' --and database_id=db_id('cardorder') --OR command LIKE 'RESTORE%' ORDER BY 2 DESC 查看数据库的最近备份信息 SELECT database_name,type,MAX(backup_finish_date) AS backup_finish_date FROM msdb.dbo.backupset GROUP BY database_name,type ORDER BY database_name,type 备注： D 表示全备份，i 表示差异备份，L 表示日志备份 查询备份信息 SELECT bs.backup_set_id, bs.database_name, bs.backup_start_date, bs.media_set_id, bmf.physical_device_name, bmf.media_family_id, bs.* FROM msdb.dbo.backupmediafamily bmf INNER JOIN msdb.dbo.backupset bs ON bs.media_set_id = bmf.media_set_id INNER JOIN ( SELECT database_name, MAX(backup_start_date) as backup_start_date FROM msdb.dbo.backupset bs WHERE type = 'd' GROUP BY database_name ) de on bs.database_name = de.database_name and bs.backup_start_date = de.backup_start_date and bs.type = 'd' 查看数据库的历史备份记录，并生成restore语句 SELECT CONVERT(CHAR(100),SERVERPROPERTY('Servername'))AS Server, bs.database_name, bs.backup_start_date, bs.backup_finish_date, bs.expiration_date, CASE bs.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' END AS backup_type, bs.backup_size, bmf.logical_device_name, bmf.physical_device_name, bs.name AS backupset_name, bs.description, 'RESTORE DATABASE ['+bs.database_name+'] FROM DISK=N''' +bmf.physical_device_name+ '''WITH NORECOVERY;' FROM msdb.dbo.backupmediafamily bmf INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id=bs.media_set_id WHERE bs.backup_start_date>

DATEADD (DAY,-1,GETDATE ())

ORDER BY bs.backup_finish_date

Query the log backup records of the XX library from the YYYY-MM-DD date and generate the statement of restore log

SELECT TOP 1000

S.database_name [Database]

CASE [S]. [type]

WHEN 'L'

THEN N'RESTORE LOG'+ QUOTENAME (S.database_name) + N' FROM DISK =''+ F.physical_device_name + Numbai 'WITH NORECOVERY;'

END [LogRestore]

F.physical_device_name

S. [Type]

S.backup_start_date

S.backup_finish_date

FROM msdb.dbo.backupmediafamily F

INNER JOIN msdb.dbo.backupset S

ON S.media_set_id = F.media_set_id

WHERE S.database_name = 'XX' AND

S.type ='L 'AND S.backup_start_date >' YYYY-MM-DD' ORDER BY S.backup_start_date ASC

Query whether the always on status is normal

Select dc.database_name, d.synchronization_health_desc, d.synchronization_state_desc, d.database_state_desc from sys.dm_hadr_database_replica_states d join sys.availability_databases_cluster dc on d.group_database_id=dc.group_database_id and d.is_local=1

View mirror image information

SELECT

Db_name (database_id)

Mirroring_state_desc

Mirroring_role_desc

Mirroring_partner_name

Mirroring_partner_instance

FROM sys.database_mirroring

Query job related to SSRS Report Subscriptions

SELECT

B.name AS JobName

, e.name

, e.path

, d.description

, a.SubscriptionID

, laststatus

, eventtype

, LastRunTime

, date_created

, date_modified

FROM

ReportServer.dbo.ReportSchedule a

JOIN msdb.dbo.sysjobs b ON CONVERT (SYSNAME,a.ScheduleID) = b.name

JOIN ReportServer.dbo.ReportSchedule c ON b.name = CONVERT (SYSNAME,c.ScheduleID)

JOIN ReportServer.dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionID

JOIN ReportServer.dbo.Catalog e ON d.report_oid = e.itemid

WHERE

E.name = 'Report Name Goes Here'

Check the data file information of a database, even if mirror can find it from the data file of the database, and the filestream directory can also find it.

SELECT db_name (database_id), * FROM master.sys.master_files WHERE database_id = DB_ID (NumberDBA')

View a data file information

Select b.name.typewriting DTSWonda_1%' .name join sys.databases b on a.database_id=b.database_id and a.physical_name like'% DTSWonda_1%'

Query the total data file size of the instance

SELECT sum (size*8/1024/1024) FROM master.sys.master_files

Query the total size of the database used in a directory

SELECT a.size*8/1024/1024, a.* FROM master.sys.master_files a WHERE physical_name like'G:\ DEFAULT.DATA%'

Query which databases in a directory occupy more than 8G of capacity

SELECT b.name dbname,a.size*8/1024/1024 sum_GB,a.type_desc,a.name datafilename,a.physical_name FROM master.sys.master_files a join sys.sysdatabases b on a.database_id=b.dbid and a.physical_name like'G:\ DEFAULT.DATA%' and a.size*8/1024/1024 > 8

Query the size of each database on the instance

SELECT

DB_NAME (db.database_id) DatabaseName

(CAST (mfrows.RowSize AS FLOAT) * 8) / 1024 RowSizeMB

(CAST (mflog.LogSize AS FLOAT) * 8) / 1024 LogSizeMB

(CAST (mfstream.StreamSize AS FLOAT) * 8) / 1024 StreamSizeMB

(CAST (mftext.TextIndexSize AS FLOAT) * 8) / 1024 TextIndexSizeMB

FROM sys.databases db

LEFT JOIN (SELECT database_id, SUM (size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id

LEFT JOIN (SELECT database_id, SUM (size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id

LEFT JOIN (SELECT database_id, SUM (size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id

LEFT JOIN (SELECT database_id, SUM (size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id

Query the top 3 CPU consumption SQL, and have appeared in the last 5 days

SELECT TOP 3

Total_worker_time/1000 AS [total CPU time consumed (ms)], execution_count [number of runs]

Qs.total_worker_time/qs.execution_count/1000 AS [average CPU time consumed (ms)]

Last_execution_time AS [Last execution time], max_worker_time / 1000 AS [maximum execution time (ms)]

SUBSTRING (qt.text,qs.statement_start_offset/2+1

(CASE WHEN qs.statement_end_offset =-1

THEN DATALENGTH (qt.text)

ELSE qs.statement_end_offset END-qs.statement_start_offset) / 2 + 1)

AS [use CPU syntax], qt.text [full syntax]

Qt.dbid, dbname=db_name (qt.dbid)

Qt.objectid,object_name (qt.objectid,qt.dbid) ObjectName

FROM sys.dm_exec_query_stats qs WITH (nolock)

CROSS apply sys.dm_exec_sql_text (qs.sql_handle) AS qt

WHERE execution_count > 1 and last_execution_time > dateadd (dd,-5,getdate ())

ORDER BY total_worker_time DESC

Query the top 3 SQL with the highest average consumption of CPU, and have appeared in the last 5 hours

SELECT TOP 3

Total_worker_time/1000 AS [total CPU time consumed (ms)], execution_count [number of runs]

Qs.total_worker_time/qs.execution_count/1000 AS [average CPU time consumed (ms)]

Last_execution_time AS [Last execution time], min_worker_time / 1000 AS [minimum execution time (ms)]

Max_worker_time / 1000 AS [maximum execution time (ms)]

SUBSTRING (qt.text,qs.statement_start_offset/2+1

(CASE WHEN qs.statement_end_offset =-1

THEN DATALENGTH (qt.text)

ELSE qs.statement_end_offset END-qs.statement_start_offset) / 2 + 1)

AS [use CPU syntax], qt.text [full syntax]

Qt.dbid, dbname=db_name (qt.dbid)

Qt.objectid,object_name (qt.objectid,qt.dbid) ObjectName

FROM sys.dm_exec_query_stats qs WITH (nolock)

CROSS apply sys.dm_exec_sql_text (qs.sql_handle) AS qt

WHERE execution_count > 1 and last_execution_time > dateadd (hh,-5,getdate ())

ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC

Check out the 10 most resource-consuming SQL and their spid

SELECT TOP 10

Session_id,request_id,start_time AS 'start time', status AS 'status'

Command AS 'command', d_sql.text AS 'sql statement', DB_NAME (database_id) AS 'database name'

Blocking_session_id AS 'session ID' that is blocking other sessions

Wait_type AS 'waiting resource type', wait_time AS 'waiting time', wait_resource AS 'waiting resource'

Reads AS 'physical reads', writes AS 'writes', logical_reads AS 'logical reads'

Row_count AS 'returns the number of result rows'

FROM sys.dm_exec_requests AS d_request

CROSS APPLY

Sys.dm_exec_sql_text (d_request.sql_handle) AS d_sql

WHERE session_id > 50

ORDER BY cpu_time DESC

-- the first 50 session_id is usually a system background process, and the status of sys.dm_exec_requests is displayed as background.

Query which job of a stored procedure has been called

SELECT *

FROM msdb.dbo.sysjobs JOB WITH (NOLOCK)

INNER JOIN msdb. Dbo.sysjobsteps STP WITH (NOLOCK)

ON STP .job _ id = JOB .job _ id

WHERE STP .command LIKE name% spacename%'

-- to query which job has called a job, change the name of the sp_name stored procedure to the name of the job_name job.

Command to execute a job

EXECUTE msdb.dbo.sp_start_job jobless name'

Self-increasing column correlation

Query the column name of a table identity column

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME=' table name 'AND COLUMNPROPERTY (OBJECT_ID (' table name'), COLUMN_NAME,'IsIdentity') = 1

Get the seed value of the identity column

SELECT IDENT_SEED ('table name')

Gets the increment of the identity column

SELECT IDENT_INCR ('table name')

Gets the last generated identity value in the specified table

SELECT IDENT_CURRENT ('table name')

Reset the identity seed value to XX

DBCC CHECKIDENT (table name, RESEED, XX)

Before upgrading, query server name, instance name, and version number

Select SERVERPROPERTY ('machinename'), @ @ SERVERNAME,SERVERPROPERTY (' edition'), @ @ version

The permissions given to the user by grant.

Use dbname

Exec sp_helprotect @ username = 'username'

Give a user permission to execute the sp of a database

Use dbname

Grant execute to "username"

Always on views the information of each node in the cluster, including the name, type, status and number of voting arbitrations of the node members.

SELECT * FROM sys.dm_hadr_cluster_members

Always on views the information of each node in the cluster, including the name of the node member and the name of the sql instance on the node member

Select * from sys.dm_hadr_instance_node_map

Always on views WSFC (windows server failover Cluster) information, including cluster name, quorum type, quorum status

SELECT * FROM SYS.dm_hadr_cluster

Always on View AG name

Select * from sys.dm_hadr_name_id_map

Always on views the subnet information of each node in the cluster, including the name, subnet segment and subnet mask of the node members.

SELECT * FROM sys.dm_hadr_cluster_networks

Always on View and listen ip

Select * from sys.availability_group_listeners

Always on to view the status of master and slave nodes

Select d.is_local,dc.database_name, d.synchronization_health_desc

D.synchronization_state_desc, d.database_state_desc

From sys.dm_hadr_database_replica_states d

Join sys.availability_databases_cluster dc

On d.group_database_id=dc.group_database_id

Always on to see how many M logs are delayed by the secondary copy (the legendary slave library)

Select db_name (database_id), log_send_queue_size/1024 delay_M,*

From sys.dm_hadr_database_replica_states where is_primary_replica=0

Select ar.replica_server_name, db_name (drs.database_id), drs.truncation_lsn

Drs.log_send_queue_size, drs.redo_queue_size

From sys.dm_hadr_database_replica_states drs

Join sys.availability_replicas ar on drs.replica_id=ar.replica_id where drs.is_local=0

Select ar.replica_server_name, db_name (drs.database_id), drs.truncation_lsn

Drs.log_send_queue_size,drs.log_send_rate, drs.redo_queue_size,drs.redo_rate

From sys.dm_hadr_database_replica_states drs

Join sys.availability_replicas ar on drs.replica_id=ar.replica_id where drs.is_local=0

-- the number of log records in the log_send_queue_size primary database that have not been sent to the secondary database (KB)

-- the rate at which log_send_rate sent instance data at an average master copy of kilobytes (KB) during the last activity

-- the rate at which redo_queue_size sent instance data at an average master copy of kilobytes (KB) during the last activity

-- the logging rate per second for a given secondary database in redo_rate average kilobytes (KB)

Query the DIRECTORY_NAME used by the FILESTREAM of the instance

SELECT SERVERPROPERTY ('FilestreamShareName')

Query the DIRECTORY_NAME corresponding to the database of FILETABLE table

Select db_name (database_id), * from sys.database_filestream_options

When using only the filestream function, the database does not need the corresponding DIRECTORY_NAME

Query the DIRECTORY_NAME corresponding to the FILETABLE table

Select object_name (object_id), * from sys.filetables

Query the full path name of a file in filetable table testdb.dbo.table1

SELECT FileTableRootPath () + [file_stream] .GetFileNamespacePath (), name FROM testdb.dbo.table1

Query whether the status of all job is running

SELECT sj.Name

CASE

WHEN sja.start_execution_date IS NULL THEN 'Not running'

WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'

WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'

END AS 'RunStatus'

FROM msdb.dbo.sysjobs sj

JOIN msdb.dbo.sysjobactivity sja

ON sj.job_id = sja.job_id

WHERE session_id = (

SELECT MAX (session_id) FROM msdb.dbo.sysjobactivity) order by RunStatus desc

Four uses of locking table

TABLOCKX

SELECT * FROM table WITH (TABLOCKX)

During the query process, other sessions cannot query and update this table until the query process is over

TABLOCK

SELECT * FROM table WITH (TABLOCK)

During the query process, other sessions can query, but cannot update this table until the query process is over

HOLDLOCK

SELECT * FROM table WITH (HOLDLOCK)

During the query process, other sessions can query, but cannot update this table until the query process is over

NOLOCK

SELECT * FROM table WITH (NOLOCK)

During the query process, other sessions can query and update this table

Two ways to query a published XX and published database object

1. Execute on the publishing database (data source: distribution.dbo.MSpublications, distribution.dbo.MSarticles, sysarticlecolumns)

Select a.article,a.source_object,a.destination_object,b.colid from

(select article,article_id,source_object,destination_object

From [distribution]. [dbo]. MSarticles where publication_id in

(select publication_id from

[distribution]. [dbo]. MSpublications where publication='XX'

)

) a

Inner join

(select * from replicate1.dbo.sysarticlecolumns) b

On a.article_id=b.artid order by a.article

2. Execute on the subscription database

Select distinct article from MSreplication_objects where publication='XX'

Query release information, release name, release sequence number corresponding to release name

Select * from distribution.dbo.MSpublications

Query the information of the publication object in the publication name, including tables, views, stored procedures, etc.

Select * from distribution.dbo.MSarticles

To monitor whether there are any exceptions to the publication subscription, execute the following five statements

Select * from [distribution]. [DBO]. [MSlogreader _ history] WHERE error_id! = 0 AND [time] > = DATEADD (HOUR,-1, GETDATE ())

Select * from [distribution]. [DBO]. [MSdistribution _ history] WHERE error_id! = 0 AND [time] > = DATEADD (HOUR,-1, GETDATE ())

Select * from [distribution] .[ dbo] .[ MSsnapshot _ history] WHERE error_id! = 0 AND [time] > = DATEADD (HOUR,-1, GETDATE ())

Select * from [distribution] .[ DBO] .MSrepl _ errors order by 2 desc

Select * from msdb.dbo.sysreplicationalerts order by 7 desc

Query index information of XX table

SELECT a.name index_name,c.name table_name,d.name column_name

FROM sysindexes a JOIN sysindexkeys b

ON a.id=b.id AND a.indid=b.indid

JOIN sysobjects c

ON b.id=c.id

JOIN syscolumns d

ON b.idfantd.id = AND b.colid=d.colid

WHERE a.indid NOT IN (0255) AND c.name in ('XX')

Generate the execution plan of the sql statement (select XXX as an example, of course, select XXX can also be replaced by executing stored procedures such as exec pro_XXX, which only generates the execution plan, does not produce result sets, and does not execute stored procedures)

SET SHOWPLAN_ALL ON

GO

Select XXX

GO

SET SHOWPLAN_ALL OFF

GO

Or

SET SHOWPLAN_XML ON

GO

Select XXX

GO

SET SHOWPLAN_XML OFF

GO

The last time the query was run successfully for the job named XXX

SELECT TOP 1 CONVERT (DATETIME, RTRIM (run_date)) + (run_time / 10000 * 3600) + (run_time% 10000) / 100 * 60) + (run_time% 10000)% 100) / (86399.9964)

FROM msdb.dbo.sysjobhistory jobhis inner join msdb.dbo.sysjobs jobs

On jobhis.job_id = jobs.job_id AND jobhis.step_id = 0 AND jobhis.run_status = 1

And jobs.name='XXX'

ORDER BY 1 DESC

Query the total number of rows and size of a partitioned table, such as crm.EmailLog

Exec sp_spaceused 'crm.EmailLog'

Query the information of a partition table and how many rows are there in each partition, for example, the table is crm.EmailLog

Select convert (varchar (50), ps.name

) as partition_scheme

P.partition_number

Convert (varchar (10), ds2.name

) as filegroup

Convert (varchar (19), isnull (v.value,'') as range_boundary

Str (p.rows, 9) as rows

From sys.indexes i

Join sys.partition_schemes ps on i.data_space_id = ps.data_space_id

Join sys.destination_data_spaces dds

On ps.data_space_id = dds.partition_scheme_id

Join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id

Join sys.partitions p on dds.destination_id = p.partition_number

And p.object_id = i.object_id and p.index_id = i.index_id

Join sys.partition_functions pf on ps.function_id = pf.function_id

LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id

And v.boundary_id = p.partition_number-pf.boundary_value_on_right

WHERE i.object_id = object_id ('crm.EmailLog')

And i.index_id in (0,1)

Order by p.partition_number

Query partition function

Select * from sys.partition_functions

View the partition schema

Select * from sys.partition_schemes

Query ssis package information

Select * from msdb.dbo.sysssispackages

Query the size of the index in a table, such as dbo.table1 in the following example

SELECT

I.name AS IndexName

SUM (page_count * 8) AS IndexSizeKB

FROM sys.dm_db_index_physical_stats (

Db_id (), object_id ('dbo.table1'), NULL, NULL,' DETAILED') AS s

JOIN sys.indexes AS i

ON s. [object _ id] = I. [object _ id] AND s.index_id = i.index_id

GROUP BY i.name

ORDER BY i.name

Rebuild all indexes on the table

Alter index all on table_name rebuild with (

Rebuild an index on a table

Alter index index_name on table_name rebuild with (online=on)

Reorganize all indexes on the table

Alter index all on table_name reorganize

Reorganize an index on a table

Alter index index_name on table_name reorganize

View the shrinking space of the data file. For the result, see the Availabesize_MB field value.

Select name, size*8/1024 as Totalsize_MB, CAST (FILEPROPERTY (name,'SpaceUsed') AS int) * 8 Universe 1024 as Usedsize_MB

Size*8/1024-CAST (FILEPROPERTY (name, 'SpaceUsed') AS int) * 8 take 1024 AS Availabesize_MB

From sys.master_files where database_id=db_id (NumberDBNAME')

Query information about all indexes in a table

Declare @ tableName varchar (50) = 'LbaListAlertDetail'

Declare @ tableId int

Select @ tableId = object_id

From sys.objects

Where name = @ tableName

SELECT OBJECT_NAME (IX.OBJECT_ID) Table_Name

, IX.name AS Index_Name

, IX.type_desc Index_Type

, SUM (PS. [used _ page_count]) * 8 IndexSizeKB

, IXUS.user_seeks AS NumOfSeeks

, IXUS.user_scans AS NumOfScans

, IXUS.user_lookups AS NumOfLookups

, IXUS.user_updates AS NumOfUpdates

, IXUS.last_user_seek AS LastSeek

, IXUS.last_user_scan AS LastScan

, IXUS.last_user_lookup AS LastLookup

, IXUS.last_user_update AS LastUpdate

FROM sys.indexes IX

INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID

INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id

WHERE OBJECTPROPERTY (IX.OBJECT_ID,'IsUserTable') = 1

And IX.OBJECT_ID = @ tableId

GROUP BY OBJECT_NAME (IX.OBJECT_ID), IX.name, IX.type_desc, IXUS.user_seeks, IXUS.user_scans, IXUS.user_lookups,IXUS.user_updates, IXUS.last_user_seek, IXUS.last_user_scan, IXUS.last_user_lookup, IXUS.last_user_update

The view of the oracle-like dba_source in sqlserver is sys.sql_modules

Query the 10 tables with the largest disk capacity for table data under a database

Use XX

If exists (select 1 from tempdb..sysobjects where id=object_id ('tempdb..#tabName') and xtype='u')

Drop table # tabName

Go

Create table # tabName (

Table_name varchar (100)

RowsNum varchar (100)

Reserved_size varchar (100)

Data_size varchar (100)

Index_size varchar (100)

Unused_size varchar (100)

)

Declare @ name varchar

Declare cur cursor for

Select name from sysobjects where xtype='u' order by name

Open cur

Fetch next from cur into @ name

While @ @ fetch_status=0

Begin

Insert into # tabName

Exec sp_spaceused @ name

Fetch next from cur into @ name

End

Close cur

Deallocate cur

Select top 10 table_name, data_size,rowsNum, index_size,unused_size, reserved_size,convert (int,SUBSTRING (data_size,0,LEN (data_size)-2)) size

From # tabName ORDER BY size desc

Or

Select top 10 a.tablename.SCHEMANAME TotalSpaceMB,sum sum (a.TotalSpaceMB) TotalSpaceMB,sum (a.RowCounts) RowCounts

From (

SELECT

T.NAME AS TableName

S.Name AS SchemaName

P.rows AS RowCounts

SUM (a.total_pages) * 8 AS TotalSpaceKB

CAST (ROUND ((SUM (a.total_pages) * 8) / 1024.00), 2) AS NUMERIC (36,2) AS TotalSpaceMB

SUM (a.used_pages) * 8 AS UsedSpaceKB

CAST (ROUND ((SUM (a.used_pages) * 8) / 1024.00), 2) AS NUMERIC (36,2) AS UsedSpaceMB

(SUM (a.total_pages)-SUM (a.used_pages)) * 8 AS UnusedSpaceKB

CAST (ROUND ((SUM (a.total_pages)-SUM (a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC (36, 2) AS UnusedSpaceMB

FROM

Sys.tables t

INNER JOIN

Sys.indexes I ON t.OBJECT_ID = i.object_id

INNER JOIN

Sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

Sys.allocation_units an ON p.partition_id = a.container_id

LEFT OUTER JOIN

Sys.schemas s ON t.schema_id = s.schema_id

WHERE

T.NAME NOT LIKE 'dt%'

AND t.is_ms_shipped = 0

AND i.OBJECT_ID > 255i

GROUP BY

T.Name, s.Name, p.Rows) a

GROUP BY a.tablename,a.SCHEMANAME

Order by sum (a.TotalSpaceMB) desc

This is better than the last major.

Query whether there is a create index'+ name+ CHAR (10) in a database

Select 'use' + name+ CHAR (10) + 'select DB_NAME (), OBJECT_NAME (OBJECT_ID), definition from' + name+'.sys.sql_modules

WHERE objectproperty (OBJECT_ID,''IsProcedure'') = 1

AND definition like'% online%=%on%'' and definition like'% index%''' from sys.databases; queries a database name based on id number

SELECT DB_NAME (18)

Query an object name based on id number

SELECT OBJECT_NAME (1769220894)

Check the progress of contraction by 100%. This statement is to be executed under the specified database.

SELECT DB_NAME (database_id) AS Exec_DB

, percent_complete

, CASE WHEN estimated_completion_time < 36000000

THEN'0' ELSE''END + RTRIM (estimated_completion_time/1000/3600)

+':'+ RIGHT ('0' + RTRIM ((estimated_completion_time/1000)% 3600Universe 60), 2)

+':'+ RIGHT ('0' + RTRIM ((estimated_completion_time/1000)% 60), 2) AS [Time Remaining]

, b.text as tsql

, *

FROM SYS.DM_EXEC_REQUESTS

Cross apply sys.dm_exec_sql_text (sql_handle) as b

WHERE command LIKE 'DbccFilesCompact%'-- and database_id=db_id (' cardorder')

ORDER BY 2 DESC

View the 100% progress of reorganizing the index

SELECT DB_NAME (database_id) AS Exec_DB

, percent_complete

, CASE WHEN estimated_completion_time < 36000000

THEN'0' ELSE''END + RTRIM (estimated_completion_time/1000/3600)

+':'+ RIGHT ('0' + RTRIM ((estimated_completion_time/1000)% 3600Universe 60), 2)

+':'+ RIGHT ('0' + RTRIM ((estimated_completion_time/1000)% 60), 2) AS [Time Remaining]

, b.text as tsql

, *

FROM SYS.DM_EXEC_REQUESTS

Cross apply sys.dm_exec_sql_text (sql_handle) as b

WHERE command LIKE'% REORGANIZE%'-- and database_id=db_id ('cardorder')

ORDER BY 2 DESC

View the execution plan of the stored procedure

SELECT

D.object_id

DB_NAME (d.database_id) DBName

OBJECT_NAME (object_id, database_id) 'SPName'

D.cached_time

D.last_execution_time

D.total_elapsed_time/1000000 AS total_elapsed_time

D.total_elapsed_time / d.execution_count/1000000

AS [avg_elapsed_time]

D.last_elapsed_time/1000000 AS last_elapsed_time

D.execution_count

D.total_physical_reads

D.last_physical_reads

D.total_logical_writes

D.last_logical_reads

Et.text SQLText

Eqp.query_plan executionplan

FROM sys.dm_exec_procedure_stats AS d

CROSS APPLY sys.dm_exec_sql_text (d.sql_handle) et

CROSS APPLY sys.dm_exec_query_plan (d.plan_handle) eqp

WHERE OBJECT_NAME (object_id, database_id) = 'xxxx'

ORDER BY [total_worker_time] DESC

View current user

Select system_user

Query the records of ddl modification operations

1. Execute the following to find the directory and name of the trace file

Select * from Sys.traces

2. Use the sqlserver profiler tool to open the trace file, and you can find the relevant records

Thank you for your reading, these are the contents of "what are the commonly used sql sentences in sqlserver dba". After the study of this article, I believe you have a deeper understanding of what the commonly used sql sentences in sqlserver dba have, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.