We have a setup of several ISA servers handling a couple of thousand users and 1 dedicated SQL loggingserver following the default SQL setup for ISA. The problem is that if we have more than approximately 5 days of logging in the database, we have issues when we want to use this logging from the ISA console. When we select a small timestamp (up till 20 minutes to search inside), the query will function, but if we select e.g. a whole day or multiple items the query stops after a couple of minutes with the errorcode 'SQL query stopped because of an error'. We expect that this issue is related to the size of the database (15 Gb). I was wondering if more indexing on the database could give a performance boost. At this moment only DateTime is indexed by default, but if e.g. the clientIP or username is indexed searching should be faster. But only if the ISA console is using these indexes. Therefore my question: can indexing multiple tables increase performance of searching through the ISA (SQL) database?
Thanks for your help! Regards, Cor
< Message edited by Reesritmo -- 1.Mar.2010 1:35:45 PM >
Yes we use SQL logging, and you can perfectly use this from within the ISA console. But only with a very limited timestamp, as described above. Also on smaller environments I haven't seen this issue. Logging to SQL didn't give issues when you needed to get the data out with the ISA console.
That's why I was wondering if it is possible to optimize the SQL database with more indexes.
I made a typo in the size of the databases, this is 350 Gb for 7 days of logging, so it's a big environment. When we try to search the data directly via SQL server, we notice Log Write Time Excessive messages in the log of ISA. That's why we want to try to optimize the search via the ISA management console.
I hope that someone can explain if additional indexing on the logging server can increase the performance, or decrease the error messages.