Skip to main content

Performance Troubleshooting - Advanced

Michael Dirne
  • Updated

Review this article before proceeding with the advanced techniques: Performance Troubleshooting

These steps focus on slowness originating from the FeneVision databases, and involve the use of SQL queries run in SQL Server Management Studio.

Check for active deadlocks

  • In SSMS, use this query to identify active deadlocks. Look for a value in the BlkBy column of the results. That value is the SPID of the process that is causing the deadlock. 
EXEC sp_who2
  • This query can give you more details once you have the SPID:
DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
Where spid = 27-- 27 is an example (you will need the spid that shows under BlkBy
SELECT TEXT 
FROM sys.dm_exec_sql_text(@sqltext)

See the "higher risk" section at the bottom of this article for options once you have the blocking SPID.

Get a trace file

Check the weekly and nightly database maintenance jobs

  • On the FeneVision database server, in SQL Server Management Studio (SSMS), verify that the weekly and nightly database maintenance jobs have been running successfully. The weekly indexing job is especially important to maintain performance of the system. If no history is visible for the weekly job, use this query (modify to your timeframe):
USE master
GO
SELECT StartTime, DatabaseName, ObjectName, IndexName, DATEDIFF(MILLISECOND, StartTime, EndTime) As Duration, Command
FROM CommandLog
WHERE Command like '%REBUILD%' AND StartTime >= '2026-09-16 01:00:00.000' AND StartTime <= '2026-09-16 05:00:00.000'
ORDER BY Duration

 

Check for long-running reports

  • In SSMS, use this query to identify long-running reports:
SELECT TOP 100 * FROM ReportServer..ExecutionLog2 ORDER BY TimeDataRetrieval DESC

 

Analyze historical query performance in BlitzWhoStatsHistory

  • In SSMS, the BlitzWhoStatsHistory table can provide a lot of historical performance data that can be helpful. It is either in the FVPerformance or FVMaster database, depending on the version. You can write queries similar to this example, which shows queries that are likely to have caused deadlocks (change the dates in the query):
SELECT sessionid, RunDate, LoginName, DatabaseName, ProgramName, CPU, sqltext, SQLCommand
FROM BlitzWhoStatsHistory
WHERE rundate > '2026-11-23 10:00:00' 
AND SessionID in 
	(
	SELECT BlockingSessionID from BlitzWhoStatsHistory
	WHERE RunDate > '2026-11-23 10:00:00' and BlockingSessionID is not null
	)
order by ElapsedTime desc

 

Advanced Troubleshooting - Higher Risk

The below information is intended for database administrators. Generally, these SQL queries should only be used in an emergency, as an alternative to rebooting the whole database server, or if a support agent tells you to run these queries.

Killing a process

  • If you have identified a deadlock using the queries in the previous section, this command can kill the deadlocking process (replace 123 with the SPID you found):
KILL 123

 

Recompiling stored procedure execution plans

  • In SSMS, use this query to clear all cached stored procedure execution plans. If this solves the problem, it tells us that a bad stored procedure execution plan was the root cause of the performance issue.
    • Do not do this repeatedly or create a job to automatically do this. It is only meant as a temporary workaround, and to prove that the problem is caused by a bad execution plan to enable further investigation.  You can read more about what exactly this command does HERE. 
DBCC FREEPROCCACHE

Related articles