• RSS
  • Twitter
  • FaceBook

Welcome to ISAserver.org

Forums | Register | Login | My Profile | Inbox | RSS RSS icon | My Subscription | My Forums | Address Book | Member List | Search | FAQ | Ticket List | Log Out

Can indexing increase performance

Users viewing this topic: none

Logged in as: Guest
  Printable Version
All Forums >> [ISA 2006 Firewall] >> Logging and Reporting >> Can indexing increase performance Page: [1]
Login
Message << Older Topic   Newer Topic >>
Can indexing increase performance - 1.Mar.2010 1:34:03 PM   
Reesritmo

 

Posts: 7
Joined: 1.Mar.2010
Status: offline
Hello,
 
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 >
Post #: 1
RE: Can indexing increase performance - 1.Mar.2010 9:03:50 PM   
paulo.oliveira

 

Posts: 3472
Joined: 3.Jan.2008
From: Amazon, Brazil
Status: offline
Hi,

I guess when you're using SQL logging, you can't query using ISA management console. Instead, you should use SQL console.

Regards,
Paulo Oliveira.

_____________________________

Microsoft Premier Field Engineer (PFE)
Blog: http://poliveirasilva.wordpress.com/
Twitter: https://twitter.com/poliveirasilva

(in reply to Reesritmo)
Post #: 2
RE: Can indexing increase performance - 2.Mar.2010 2:11:27 AM   
Reesritmo

 

Posts: 7
Joined: 1.Mar.2010
Status: offline
Hi Paolo,

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.

Thanks!
Cor

(in reply to paulo.oliveira)
Post #: 3
RE: Can indexing increase performance - 11.Mar.2010 7:35:14 AM   
Reesritmo

 

Posts: 7
Joined: 1.Mar.2010
Status: offline
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.

Thanks,
Cor

(in reply to Reesritmo)
Post #: 4

Page:   [1] << Older Topic    Newer Topic >>
All Forums >> [ISA 2006 Firewall] >> Logging and Reporting >> Can indexing increase performance Page: [1]
Jump to:

New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts