SQL Audit performance issues
|
Logged in as: Guest
|
|
Users viewing this topic:
none
|
|
Login  | |
|
SQL Audit performance issues - 2.Sep.2009 12:55:58 PM
|
|
|
NEMHC
Posts: 1
Score: 0
Joined: 2.Sep.2009
Status: offline
|
When we turned on SQL audit with default values our SQL server performance was degraded to the point that the SQL server was not usable.
This SQL server is also acting as a Domain controller (Windows 2003). Once the SQL server audit was disabled everything returned to normal. GFI support recommended only monitoring SQL Security events. GFI should be shipped with the safe settings as default. Does anyone have the SQL running smoothly, and if so please share your settings.
RPC
|
|
|
|
|
RE: SQL Audit performance issues - 2.Sep.2009 1:03:55 PM
|
|
|
DrewE
Posts: 1058
Score: 0
Joined: 28.Apr.2008
From: Cary, NC
Status: offline
|
Did auditing only Security events help clear up the processor load? If so, we may be able to further assist you if you open a support request online at http://support.gfi.com/Support/support.aspx?lcode=en
_____________________________
Drew Easley - Technical Support Representative
GFI Software - www.gfi.com
|
|
|
|
|
RE: SQL Audit performance issues - 7.Oct.2009 4:36:28 PM
|
|
|
dpattillo@centurymar
Posts: 8
Score: 0
Joined: 7.Oct.2009
Status: offline
|
quote:
ORIGINAL: NEMHC
When we turned on SQL audit with default values our SQL server performance was degraded to the point that the SQL server was not usable.
This SQL server is also acting as a Domain controller (Windows 2003). Once the SQL server audit was disabled everything returned to normal. GFI support recommended only monitoring SQL Security events. GFI should be shipped with the safe settings as default. Does anyone have the SQL running smoothly, and if so please share your settings.
RPC
We too are having the same problem (degraded SQL performance with SQL Auditing turned on). I have changed the configuration to collect only security events, and performance improved, however, it still remains unacceptable for the end users.
I'm not sure how EventsManager collects the events from SQL, but I have to assume it does it thru a trace. Is there some way to limit the trace to the database of interest? Any help would be greatly appreciated!
|
|
|
|
|
RE: SQL Audit performance issues - 8.Oct.2009 9:27:44 AM
|
|
|
DrewE
Posts: 1058
Score: 0
Joined: 28.Apr.2008
From: Cary, NC
Status: offline
|
Would you be able to contact us at http://support.gfi.com/Support/support.aspx?lcode=en so we can best assist you with this trouble?
_____________________________
Drew Easley - Technical Support Representative
GFI Software - www.gfi.com
|
|
|
|
RE: SQL Audit performance issues - 8.Oct.2009 9:42:58 AM
|
|
|
dpattillo@centurymar
Posts: 8
Score: 0
Joined: 7.Oct.2009
Status: offline
|
Thanks for the reply Drew .. I submitted a trouble ticket yesterday and the tech that responded asked me to send a screen shot of the error! I replied with a more in-depth description of the problem and then referenced this thread .. I still haven't received a response. Is there anyway help could be provided in the forum? If not .. could you handle my case? It's case # GFI-091007-24319
|
|
|
|
|
RE: SQL Audit performance issues - 14.Oct.2009 2:26:58 AM
|
|
|
mherberg
Posts: 60
Score: 0
Joined: 19.May2009
Status: offline
|
Hi dpattillo,
My name is Matthias and I'm part of the Product Specialist Group which is dealing with Eventsmanager.
I had a look at your support case and can understand your annoyance about explaining the whole issue again to another on e of our technicians. Therefore I would ask you to provide as much information as possible in reply to Valdeli's last email so our PSG team can analyse the issue further.
Additionally I will add a note to your case so that it is escalated asap.
Thank you!
_____________________________
Regards,
Matthias Herberg - Product Specialist
GFI Software - www.gfi.com
Messaging, Content Security & Network Security Software
|
|
|
|
|
RE: SQL Audit performance issues - 15.Oct.2009 7:04:00 AM
|
|
|
mamun01
Posts: 3
Score: 0
Joined: 15.Oct.2009
Status: offline
|
Please note: the first 5 posts of all new members are manually moderated to help prevent spam, please be patient as they may not appear immediately after you post them.
|
|
|
|
|
RE: SQL Audit performance issues - 15.Oct.2009 1:21:15 PM
|
|
|
dpattillo@centurymar
Posts: 8
Score: 0
Joined: 7.Oct.2009
Status: offline
|
After learning that EventsManager does indeed create a trace to monitor the SQL events .. The interesting thing that I found was the enormous amount of entries I was getting from 2 replicated databases! After excluding the replication events from the trace and the other 5 databases of little interest to me .. things are looking better.
Long story short .. if you are using replication in your environment, you are getting far too many events in the trace. If you have more than a few well used databases, you are getting far too many events. If you have both, you will be modifying the trace if you want to use this program.
Hope this helps someone in the same boat that I was in ..
|
|
|
|
|
RE: SQL Audit performance issues - 11.Nov.2009 2:31:32 PM
|
|
|
cstump
Posts: 6
Score: 0
Joined: 10.Nov.2009
Status: offline
|
I am running into similar issueswith performance. How did you exclued a particular type of event fromthe trace? Chuck
|
|
|
|
|
RE: SQL Audit performance issues - 12.Nov.2009 8:43:28 AM
|
|
|
dpattillo@centurymar
Posts: 8
Score: 0
Joined: 7.Oct.2009
Status: offline
|
Hi Chuck,
I used the SQL Profiler to modify the trace file. (In my case, I am only capturing the securtiy events, so I modified a backed-up copy of scan_security.trc) I then set a column filter to inculde only the databases I wanted to monitor. Here are the steps I took.
1. I backed up both trace files included in the application "scan_profile.trc" and "scan_security.trc".
2. I then opened SQL Profiler, selected File .. Open .. .. and selected "scan_security.trc".
3. After the file opened, I selected, File .. Properties
4. From the Properties window, I selected the Events Selection tab .. and then clicked the Column Filters button.
5. From the Edit Filters window, left hand column, I clicked on DatabaseName .. then added under the "Like" statement, "master", "mssqlsystemresource", and the name of the database I was interested in. This screen is kinda funny, in that you have to press ";" after each database name to add a new line for additional names.
I am using SQL Profiler 2008, so if you have a different version, the screens may not be the same .. hope this helps .. Oh, and don't be affraid to add additional filters to make it run even faster. After changing and saving the trace, you will have to restart the GFI service for the change to take effect.
Dwain
|
|
|
|
|
RE: SQL Audit performance issues - 12.Nov.2009 12:40:13 PM
|
|
|
cstump
Posts: 6
Score: 0
Joined: 10.Nov.2009
Status: offline
|
This is exactly what I needed. Works great!!!!
Thanks very much.
Chuck
|
|
|
|
|
RE: SQL Audit performance issues - 12.Nov.2009 12:45:09 PM
|
|
|
dpattillo@centurymar
Posts: 8
Score: 0
Joined: 7.Oct.2009
Status: offline
|
Glad I could be of help!
I really like this product and think it makes a wonderful addition to our family of applications used in our PCI compliance. We also use GFI LANguard to help with patch maintenance and penetration testing. No, I don't work for GFI, but I do appreciate a well written piece of software. Good luck ..
|
|
|
|
|
RE: SQL Audit performance issues - 16.Nov.2009 1:34:40 PM
|
|
|
cstump
Posts: 6
Score: 0
Joined: 10.Nov.2009
Status: offline
|
Have you had any issues where the trace stops running? Three times today I noticed that I was not getting events anymore in GFI. No errors or warnings anywhere. But I did find that the trace was no longer running on the SQL server.I had to stop and restart the GFI windows service to get things going again. GFI support does not have any ideas.
Hve you seen anything like this?
Chuck
|
|
|
|
|
RE: SQL Audit performance issues - 16.Nov.2009 1:47:03 PM
|
|
|
dpattillo@centurymar
Posts: 8
Score: 0
Joined: 7.Oct.2009
Status: offline
|
I did some research for you .. read this thread .. not much help but seems to be the same problem
http://forums.gfi.com/Database_Stopped%2c_But_Isn't/m_900780777/tm.htm
I'm thinking that any database that is contained in the trace will cause the the trace to stop working if maintence is performed on it. How often do you backup the monitored DB's? Do you ever backup the Master table? Do you backup the DB that stores the events? I think it's all going to come down to EMS losing it's connection to SQL. When it does, it seems to gracefully recover everything except the SQL Audit.
< Message edited by dpattillo@centurymar -- 16.Nov.2009 2:51:57 PM >
|
|
|
|
|
RE: SQL Audit performance issues - 16.Nov.2009 3:55:37 PM
|
|
|
cstump
Posts: 6
Score: 0
Joined: 10.Nov.2009
Status: offline
|
Thanks. I saw that one too. I thought it might be maintenance related at first, but it happened again several times today even when there was no maintenance on that particular database.But there was maintenance on other databases on that server. Need to experiment more with that.
We do daily backups on all the databases (master, etc.). I have a feeling it is not related to backups. Possibly some other maintenance. Last Friday we did our nightly backups on this server and things were good. We did our weekly fulls on Sunday morning and things were good. However, today(Monday morning) and several other times throughout the day the trace stopped. No backups in that time window.
I like your thought on EMS loosing the connection to SQL. Need to investigate that some more.
Regards,
Chuck
|
|
|
|
 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 |
|
|
Printable Version
