How to get the execution plan of sqlserver

tracker

Hello everyone, I have encountered a problem using sqlserver. Please help me. The problem is this. In oracle, when we want to view the execution plan, generally we first create the plan_table table, then use the "excute plan + sql statement" command, and then query the plan_table table to get the execution plan results, but In sqlserver, there is only the function of displaying the execution plan. I want to know how to get the execution plan in sqlserver through sql statement query. Please help, thank you in advance.

hiqwolfzzh

I don't know if this is right. . .

How to get the maintenance plan log on the client
How to determine the status of the maintenance plan
How to start SQLServer Agent on the client
-------------------------------------------------- -------------

--How to start the SQLServer agent on the client
exec master..xp_cmdshell'net start SQLSERVERAGENT'
-------------------------------------------------- -------------

Query the system table, you can get the status of the maintenance plan:
select * from msdb..sysdbmaintplan_history


Explanation of the information returned by this system table:
sysdbmaintplan_history
Each maintenance plan operation performed occupies one row in the table. This table is stored in the msdb database.

Column name Data type Description
sequence_id int Sequence of historical records for maintenance plan execution.
plan_id uniqueidentifier Maintenance plan ID.
plan_name sysname The maintenance plan name.
database_name sysname The database name associated with the maintenance plan.
server_name sysname system name.
activity nvarchar(128) Maintain planned activities (such as backing up transaction logs, etc.).
succeeded bit 0 = succeeded bit 0
1 = failed
end_time datetime The time when the operation was completed.
duration int The time required to complete the maintenance plan operation.
start_time datetime The time when the operation started.
error_number int The error number reported on failure.
message nvarchar(512) The message generated by sqlmaint.

tracker

Thank you so much upstairs, I still have a question, what is the command to generate a maintenance plan for a sql statement?

tracker

Can you give me an example?

exec master..xp_cmdshell'net start SQLSERVERAGENT'

Can't execute it, I don't quite understand

silverdoom

Oracle previously required users to create plan_table by themselves in order to view the execution plan functions such as set autotrace on. Then the execution plan cache is displayed to the user through plan_table. It can be said that the way before Oracle10g requires users to create plan_table is more troublesome. The plan_table only serves as a cache. It will be refreshed every time a new SQL statement is viewed. Does it make sense to query it? !

SQL Server and Oracle have different ideas. Because the UI of SQL Server is well done, the execution plan is generally displayed graphically. If SQL is more complicated, generally use:
set showplan_all {on | off}

set showplan_text {on | off}

To view the text execution plan (most similar to Oracle, which is basically the same as Oracle's set autotrace on). If you need to save the execution plan, you need to do it manually. If you want to automate this process, you can consider using SQL Server Profiler (SQL Server's tracking function) to automatically record execution plan events.


hiqwolfzzhis talking about the maintenance plan of the database. Some database maintenance functions that are performed automatically with jobs. And your problem is not a concept.