Forum Discussion

Wilson_H's avatar
Wilson_H
Level 4
13 years ago

EV archiving report

Dear All,

 

below script failed to run again, it is showing debuging and running a very long time without and result.

Output showing

Auto-attach to process '[1868] [SQL] Server' on machine 'server' succeeded.

 

 

SELECT A.ArchiveName,
       COUNT(S.IdTransaction) "Archived Items"
FROM   EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.Root R,
       yourVaultStore.dbo.ArchivePoint AP,
       yourVaultStore.dbo.Saveset S
WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointId = R.VaultEntryId
  AND  R.RootIdentity = A.RootIdentity
  AND  S.ArchivedDate > DATEADD(d, -3, getDate())
GROUP BY A.ArchiveName
ORDER BY A.ArchiveName
 

  • Take the "ORDER BY" out, and also you may want to look at things like index fragmentation and if you're rebuilding the indexes and other SQL Maintainance.

    I do recall that EV9 Database upgrades run some queries like this extremely slowly in some circumstances, mos tlikely because of a removed or an altered index, but to be honest they shouldn't have to exist just to cater to these ad-hoc queries.

    But really if you do a SQL Profiler and show the execution plans, you'll probably find that it is the ORDER BY that is causing the most significant slow down

    Also is the Vault Store and the Directory database located on the same server, or are you using server linking and altering the query to call the secondary server?

  • ~11,000 results on mine in no time quick as hell ;)

    Whats your SQL resorces like? CPU, Memory?

  • I just ran that as well and it ran OK and quite quick too, returned about 7,500. Like Wayne said, it depends on how beefy your SQL server is.

  • It might also be helpful to know:

     

    EV Version + Service Pack

    Windows version + Service Pack

    SQL version + Service Pack

     

    Running it locally through SQL Mgmt Studio?

  • Hi, the server is high utilization as shown in Pic. will this causing the server not responding to the query? or it return enormous result ?

     
  • Take the "ORDER BY" out, and also you may want to look at things like index fragmentation and if you're rebuilding the indexes and other SQL Maintainance.

    I do recall that EV9 Database upgrades run some queries like this extremely slowly in some circumstances, mos tlikely because of a removed or an altered index, but to be honest they shouldn't have to exist just to cater to these ad-hoc queries.

    But really if you do a SQL Profiler and show the execution plans, you'll probably find that it is the ORDER BY that is causing the most significant slow down

    Also is the Vault Store and the Directory database located on the same server, or are you using server linking and altering the query to call the secondary server?