All I am trying to do is select previous backups to duplicate to tape. Pretty simple, right? Nope.
I am experience long delays everytime I select the resource to duplicate. Instead of calling customer support, I decided to investigate it myself.
Turns out there is one query to SQL Server (which is called countless times) which is causing the problem:
SELECT * FROM ImageObjectView WHERE PartitionID = @P1 AND MediaFamilyGuid = @P2 AND MediaNumber >= @P3 AND ImageNumber = @P4 AND ImageGUID = @P5 ORDER BY MediaFamilyGuid , ImageNumber , MediaNumber
Each time this query is being executed, it is taking over 25 seconds. Wow........
Looking deeper, it looks like EVERY CALL to ImageObjectView is taking that period of time. (Won't bore you with all the other querie variations, but sometimes the query takes over 45 seconds to execute.)
BTW, just in case anyone is interested, it is not possible to create an index for the view "ImageObjectView" because 1. It is not bound to a schema, and 2. There are no primary keys in the view.
Just in case Symantec is reading this, SQL Profiler also pointed this out as well (not sure if it is related to the same problem...)
exec dbo.[AutoClearAlert] 0,'Aug 21 2007 12:57:45:000PM',N'DOMAIN\Account' Takes 50 seconds to execute.
The only advice I can offer to any DBA in this case is "AVOID FULL-TABLE SCANS.", and create an index or two. (Or 30).
Mark
