November 12, 2010 at 7:28 am
Would like an easy method of monitoring sql servers.
There are a number clustered, stand alone, v2000, v2008, win 2003, win2008.
Would like to be able to implement this with the least impact on the server. The servers are managed and it's not quick (or cheap) to get changes authorised.
Main requirement I think is to keep records of statement/SP activity - look for long runnnig queries so that we can match it against performance issues. Would expect io and cpu activity to be a lot easier to get.
For the v2008 I am probably happy with the data collector - would be nice if it (or something like it could be implemented without a configuration change to the server though).
Need something similar for the 2000 servers also.
Anyone any suggestions?
Cursors never.
DTS - only when needed and never to control.
November 12, 2010 at 7:58 am
In my previous company we had HP OVO agent that were deployed on each servers and alerted us for wide ranging issues right from blockings, deadlocks, backup failure, disk space, job failure and lots of stuff.
In my current company we have a dba maintainence script installed on all servers which does the same thing.
May be you would like to search for some 3rd party monitoring tool?
November 12, 2010 at 8:10 am
Thanks - yes we expected monitoring of servers to be in place.
This is more about viewing sql queries though.
Yes 3rd party tools would be good and I'm sure we will go that way but the lead time is an issue for getting it agreed so I want something that can be implemented quickly.
Cursors never.
DTS - only when needed and never to control.
November 12, 2010 at 9:12 am
The cheapest and probably quickest way to get some monitoring inplace is to setup a combination of serverside perfmon and sql traces.
These can use the lightweight default trace if performance impact is a consideration. If you set these up correctly you can load them together in profiler and correllate the two.
November 12, 2010 at 9:14 am
default trace wont capture dml operations..
November 12, 2010 at 9:37 am
Default trace is good for object changes, but as noted, it doesn't capture DML.
You can build your own trace, but need to manage files/data carefully as if you are capturing all activity, and storing for any length of time, that can be a ton of data. I would think about scripting a standard trace that captures what you want, and you can deploy that fairly easily across 2005/2008. Powershell makes this fairly easy to do and manage as a script. For 2000, you'll likely need something separate since trace is a little different, but the same principle applies.
November 12, 2010 at 9:39 am
ps. (11/12/2010)
default trace wont capture dml operations..
Thats true but I never said it did. Simply, if performance was a consideration the default trace does provide some insight into what was going on at the time
The default trace would at least provide a basis to begin logging he could then set up a user configured server side trace to support this.
DEFAULT TRACE SQL 2005
18Audit Server Starts and StopsOccurs when the SQL Server service state is modified.
20Audit Login FailedIndicates that a login attempt to SQL Server from a client failed.
22ErrorLogIndicates that error events have been logged in the SQL Server error log.
46Object:CreatedIndicates that an object has been created, such as for CREATE INDEX, CREATE TABLE, and CREATE DATABASE statements.
47Object:DeletedIndicates that an object has been deleted, such as in DROP INDEX and DROP TABLE statements.
55Hash WarningIndicates that a hashing operation (for example, hash join, hash aggregate, hash union, and hash distinct) that is not processing on a buffer partition has reverted to an alternate plan. This can occur because of recursion depth, data skew, trace flags, or bit counting.
69Sort WarningsIndicates sort operations that do not fit into memory. Does not include sort operations involving the creating of indexes; only sort operations within a query (such as an ORDER BY clause used in a SELECT statement).
79Missing Column StatisticsColumn statistics that could have been useful for the optimizer are not available.
80Missing Join PredicateQuery that has no join predicate is being executed. This could result in a long-running query.
81Server Memory ChangeSQL Server memory usage has increased or decreased by either 1 megabyte (MB) or 5 percent of the maximum server memory, whichever is greater.
92Data File Auto GrowIndicates that a data file was extended automatically by the server.
93Log File Auto GrowIndicates that a log file was extended automatically by the server.
94Data File Auto ShrinkIndicates that a data file was shrunk automatically by the server.
95Log File Auto ShrinkIndicates that a log file was shrunk automatically by the server.
102Audit Statement GDR EventOccurs every time a GRANT, DENY, REVOKE for a statement permission is issued by any user in SQL Server.
103Audit Object GDR EventOccurs every time a GRANT, DENY, REVOKE for an object permission is issued by any user in SQL Server.
104Audit AddLogin EventOccurs when a SQL Server login is added or removed; for sp_addlogin and sp_droplogin.
105Audit Login GDR EventOccurs when a Windows login right is added or removed; for sp_grantlogin, sp_revokelogin, and sp_denylogin.
106Audit Login Change Property EventOccurs when a property of a login, except passwords, is modified; for sp_defaultdb and sp_defaultlanguage.
109Audit Add DB User EventOccurs when a login is added or removed as a database user (Windows or SQL Server) to a database; for sp_grantdbaccess, sp_revokedbaccess, sp_adduser, and sp_dropuser.
110Audit Add Member to DB Role EventOccurs when a login is added or removed as a database user (fixed or user-defined) to a database; for sp_addrolemember, sp_droprolemember, and sp_changegroup.
111Audit Add Role EventOccurs when a login is added or removed as a database user to a database; for sp_addrole and sp_droprole
115Audit Backup/Restore EventOccurs when a BACKUP or RESTORE command is issued.
116Audit DBCC EventOccurs when DBCC commands are issued.
117Audit Change Audit EventOccurs when audit trace modifications are made.
152Audit Change Database OwnerOccurs when ALTER AUTHORIZATION is used to change the owner of a database and permissions are checked to do that.
153Audit Schema Object Take Ownership EventOccurs when ALTER AUTHORIZATION is used to assign an owner to an object and permissions are checked to do that.
155FT:Crawl StartedOccurs when a full-text crawl (population) starts. Use to check if a crawl request is picked up by worker tasks.
156FT:Crawl StoppedOccurs when a full-text crawl (population) stops. Stops occur when a crawl completes successfully or when a fatal error occurs.
157FT:Crawl AbortedOccurs when an exception is encountered during a full-text crawl. Usually causes the full-text crawl to stop.
164Object:AlteredOccurs when a database object is altered.
167Database Mirroring State ChangeOccurs when the state of a mirrored database changes.
175Audit Server Alter Trace EventOccurs when a statement checks for the ALTER TRACE permission.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply