I came to work this morning to find out that the execution was supposed to be completed over the weekend.script executionBy now, relying on the self-built etl log table found that the query of a certain large table was particularly slow to modify .
Later restarted the database (restarted it in the control panel's services)sqlserver), just fine.
Guess the reason: may be because the system's temporary database tempdb full, or is blocked or so on, in the event piece of matter inside to see my process has been in the report RESOURCE_SEMAPHORE wait state , blocking his process is tempdb database, and then guess is not this reason. sqlserver every startup will re-create the tempdb table. The tempdb table is recreated every time sqlserver starts.
--20190305 Update
It's the temporary tables that are the problem, seeDo temporary tables in SQL Server need to be explicitly deleted? _sql server temporary tables do they need to be cleanly deleted - CSDN Blogs
reconsiderStarting the databaseAfter that, the disk space where the data is stored is nearly 100 g more. because I often don't write drop statement inside the query of ssms, may be this cause, before the connection pool is disconnected, the database won't help us to delete the temporary table, and we need to show the deletion. So it is better to show delete temporary table in future.
--
Detailed Description:
It was found that the job took a long time to complete, normally it can be completed in less than 2 minutes, but it took him almost 2 days.
Action at that time: stop the job, re-execute the job, and then find theetlThe logs indicate that it's not executing or that it's stuck. Checking disk io and memory, etc. shows that none of them are moving (this is important to show which statements are not being executed at all)
Here's how the troubleshooting went
1. Check production data for recent high volume data. (No)
2. Is the disk space is not enough. (Check the remaining space of the disk, there are 200G, not this reason)
2. Is there a deadlock (no)
consultationQuery whether a deadlock exists in sqlserver_sql server deadlock check-CSDNblog (loanword)
3. View the execution plan of the table
Since these queries are pretty much fixed queries and nothing was wrong with them before, you can only check with hope.
Here I found a strange thing, there is an index it doesn't go to the index, it favors a full table scan, and I read his suggestion that he wants me to see an index. But I didn't think it was necessary. Later it turned out that this wasn't the reason.
Then record some common check statements
-
DBCC showcontig('[R_sy_jfrs_year_report]')
-
--Concerns: Scan Density [Best Count:Actual Count], Logical Scan Fragmentation
-
--When you find that, scanning density rows, the ratio of optimal counts to actual counts has become grossly out of whack, with a very large percentage of logical scanning debris, per page flat
-
- - When the average number of available bytes is very large, it's a sign that your index needs to be reorganized
-
-
-- then execute, -- rebuild the index
-
DBCC DBREINDEX('Table_name'')
-
-
-View the table that is currently being used, this is a good way to guess where the execution is going in the absence of real-time logs.
-
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
-
from sys.dm_tran_locks where resource_type='OBJECT'
-
ORDER BY TABLENAME
-
--View the indexing of the table
-
SELECT
-
,
-
,
-
,
-
FROM sysindexes a
-
JOIN sysindexkeys b ON = AND =
-
JOIN sysobjects c ON =
-
JOIN syscolumns d ON = AND =
-
WHERE NOT IN (0,255)
-
-- and = 'U' and >0 -- check all users table
-
AND ='Fact_Trade_log' --Check the specified table
-
-
--View database load
-
SELECT
-
substring (,0,20) as [database name].
-
[Number of connections]= (SELECT COUNT(*)
-
FROM master..sysprocesses b
-
WHERE
-
= ),
-
[blocking process]= (SELECT COUNT(*)
-
FROM master..sysprocesses b
-
WHERE
-
= AND
-
blocked <> 0),
-
[Total memory]= ISNULL((SELECT SUM(memusage)
-
FROM
-
master..sysprocesses b
-
WHERE
-
= ),0),
-
[General IO]= ISNULL((SELECT SUM(physical_io)
-
FROM
-
master..sysprocesses b
-
WHERE
-
= ),0),
-
[Total CPU]= ISNULL((SELECT SUM(cpu)
-
FROM
-
master..sysprocesses b
-
WHERE
-
= ),0),
-
[Total waiting time]= ISNULL((SELECT SUM(waittime)
-
FROM
-
master..sysprocesses b
-
WHERE
-
= ),0)
-
FROM a WITH (nolock)
-
WHERE
-
DatabasePropertyEx(,'Status') = 'ONLINE'
-
ORDER BY [database name]
-
--query statement being executed
-
SELECT spid,
-
blocked,
-
DB_NAME() AS DBName,
-
program_name,
-
waitresource,
-
lastwaittype,
-
,
-
,
-
a.[Text] AS [TextData],
-
SUBSTRING(, sp.stmt_start / 2,
-
(CASE WHEN sp.stmt_end = -1 THEN DATALENGTH() ELSE sp.stmt_end
-
END - sp.stmt_start) / 2) AS [current_cmd]
-
FROM AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A
-
WHERE spid > 50
---20240611----------------------------------------------------------------------------------------------------------------
Feel free to follow my subscription to discuss
On how PMs grow
