Shulou(Shulou.com)06/01 Report--

By "finding the top ten queries consumed by CPU" on SQL03 (the script is shown below), you find that the first place is a strange statement.

SELECT TOP (10) SUBSTRING (ST.text, (QS.statement_start_offset / 2) + 1, ((CASE statement_end_offset WHEN-1 THENDATALENGTH (st.text) ELSE QS.statement_end_offset END- QS.statement_start_offset) / 2) + 1) ASstatement_text Execution_count, total_worker_time / 1000 AStotal_worker_time_ms, (total_worker_time / 1000) / execution_count ASavg_worker_time_ms, total_logical_reads, total_logical_reads / execution_count ASavg_logical_reads, total_elapsed_time / 1000 AStotal_elapsed_time_ms (total_elapsed_time / 1000) / execution_count ASavg_elapsed_time_ms, qp.query_planFROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) qp ORDER BY total_worker_time DESC

The statement expands to:

SELECT "Tbl1012". "FMSKU"Col1060", "Tbl1012". "FMID"Col1063", "Tbl1012". "ToID"Col1067" FROM "ARCHIVE". "dbo". "Log"Tbl1012" WITH (NOLOCK) WHERE "Tbl1012". "ToLotnum" = @ P1 AND "Tbl1012". "TransactionType" = 'MV' AND "Tbl1012". "ReasonCode" =' PG' AND "Tbl1012." FMLocation "like 'STAGE%' COLLATE Chinese_PRC_CI_AS AND" Tbl1012 "." ToLocation "like' STAGE%' COLLATE Chinese_PRC_CI_AS.

The table aliases and column aliases in this statement are found to be strange.

Therefore, Profiler tracking on SQL03 is turned on. On the General tab, select the Standard template. On the "Events Selection" tab, select the event class to be tracked, check "Show all columns", and be sure to select the most critical columns: HostName, SessionLoginName, and DatabaseName. Then click "Column Filters..." TextData entered% Tbl1012% in the Like filter condition. Turn on tracking.

Based on the captured output, the source host is SQL01, the session login name is ARCHIVE, and the database name is master.

Therefore, the Profiler trace is turned on on SQL01. On the General tab, select the TSQL_SPs template. On the "Events Selection" tab, be sure to select the "SP:StmtStarting" event class, check "Show all columns", and be sure to select: LineNumber, SessionLoginName and SourceDatabaseID. Click "Column Filters..." Enter% STAGE3% for the Like filter condition of TextData. Turn on tracking.

LineNumber

The line number that contains the line on which there is an error. For events that involve Transact-SQL statements, such as SP:StmtStarting,LineNumber contains the line number of the statement in the stored procedure or batch.

See: https://msdn.microsoft.com/zh-cn/library/ms190762(v=SQL.100).aspx

From the captured output, we can see that the statement came from near line 2223 of SP. Therefore, 2223 lines of the SP are found to be validated.

Then it is further verified by the corresponding relationship between "Local Login" and "Remote User" of the LinkedServer.

Summary

The alias of the query with LINKEDSERVER is internally named TBLxxxx\ COLxxxx at the remote end.

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.