07-06-2012 12:20 AM
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
Solved! Go to Solution.
07-30-2012 07:08 AM
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?
07-06-2012 01:16 AM
~11,000 results on mine in no time quick as hell ;)
Whats your SQL resorces like? CPU, Memory?
07-06-2012 01:44 AM
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.
07-06-2012 02:36 AM
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?
07-06-2012 02:37 AM
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 ?
07-30-2012 06:52 AM
I'll try the script tomorrow in my office and will let you know the result.
07-30-2012 07:08 AM
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?