SQL queued processes to vault store databases
Howdy
We have a problem with our SQL server. This started around the same time we upgraded to EV 7 SP5. Most nights during scheduled archiving the JournalsCommit stored procedure locks an SQL table and never lets go. This causes the futures processes to be queued. If we monitor using SQL Activity monitor, I can see for that database there can be 1000+ processes. Once the total number of processes exceeds about 2500, the server refuses all connections to all databases and is effcitely offline.
The workaround is to restart the storage services for whichever EV server manages that database. This kills all process. The problem happens with different vault store databases, different EV servers.
The usual mantainance tasks are run every week.
I can't see any technotes relating to the problem. I don't want to log a case with Symnatec because we will be upgrading again next month. I don't think they would accept it anyway.
I haven't run SQL Profiler becase a) the trace will be huge, and b) I don't think it will help us much even if we did know which entry in the table was attempted to be updated.
My question is really - is there any way to tell SQL to limit the number of connections/processes per database? I don't mind so much if one of them dies, but I dont want it to bring down all of the others. From Activity Monitor I can see most database have up to 100 process on a normal day. I guess this is something to day with connection pooling on the ODBC connection. Some process have been sleeping for days (lazy). Is there anything I can configure there?
TIA
you don't have to use profiler, you would just use the Activity Monitor that SQL Provides
Once the blocking occurs, open the Activity Monitor and sort by blocks, at that point you will see a list of SQL Queries and what other SPID is blocking it, most of them will come down to a few different queries that just absolutely kill performance
You can also get in to this circular hell where SPID 17 blocks SPID112 and SPID 112 is blocking SPID 76 and SPID 76 is block SPID 17 etc so it never truly resolves itself.
Also take a look at this article
http://www.simple-talk.com/sql/sql-tools/how-to-identify-blocking-problems-with-sql-profiler/
That way you can be concise if you want to do a SQL profiler, and not be logging every single transaction that you have.
And lastly if you have SQL Server 2008 it has the option to show the last run most expensive queries which can be a real eye opener, and if you are on Windows 2008 you can use the Resorce Monitor to show if you are seeing disk thrashing.
Normally a good SQL Layout will have things completely set apart to stop disk queue lengths backing up, for instance you would have1. Master and Temp Database on one drive
2. Log files on another drive
3. Other databases on a seperate drive
The thing is EV doesn't use Simple transaction logging, therefor the the log files get written to with every single transaction, if you are doing large amounts of processing on the same drive you will have a lot of data being written to the log and the database, causing the disk queues to go up
Also remember the Temp directory will be extensively used and expensive queries will push data on to the temp db just as much.... so if you have a database, the log, and the tempdb all on the same drives then you will see a lot of disk thrashing and larger disk queues.
it is worth looking in a perfmon to see really what the disk queues are like because you may find that the locks are being produced just due to the fact that you do have resource issues on the disk, the longer the queue, the longer the query will take for its turn to run etc etc
