Can I count the number of times a SELECT occurs on a table?
-
We are stuck with a poorly designed legacy database and the front end application that goes with it.
We need to determine what tables are actually getting used through the course of a typical day of business. I can create a series of triggers to determine if an insert, update, or delete occurs on a table but I also need to determine if a simple select has occurred.
There is a large number of stored procs, we have generated the scripts and done a search for the words "FROM" and "JOIN" and have made a hand written list of these tables, but this by no means is foolproof and is ridiciously time consuming.
What I looking to do is create a table witha structure like this:
CREATE TABLE dbo.AuditLog
(
Audit_Log_ID int IDENTITY(1,1),
Table_Name varchar(200) NOT NULL,
Insert_Count int NULL,
Select_Count int NULL,
Update_Count int NULL,
Delete_Count int NULL,
)
GO
Each time a select, insert, update or delete occurs, I need to check if the table being "used" exists in the audit table, and depending upon the T-SQL statement, increment the correct column.
Anyone have any ideas?
Thanks!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
I'm not aware of any such thing as a SELECT trigger. It seems to me as if you're best (only?) option is to wrap a layer of some sort around your SELECT statements and put the intelligence in the outer layer.
I should add that you can certainly make SELECT executions visible in the Debugger, but I would personally blanch at the thought of pulling that into my app.
-
You could also run a profiler trace and capture database activity to a table or file, then search it later.
-
I have been doing that, but its the same problem as doing a search on the code. Prone to errors, and painfully time consuming.
Thanks!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
If all the selects are done with SPS, you can create a new sp that logs the selects, and call that sp in each sp of the db.
-
I actually started to do this, working through each stored procedure starting with the first one. I then realized that there are 1050 different procedures that would need to be modified.
Thanks again!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
Thank god for copy/paste
.Don't forget to backup the original so you can revert back.
-
Here's a Microsoft article on auditing:
http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx#EGAA
Otherwise, maybe there's a third-party app you could use. This one looks intriguing:
http://www.sql-server-performance.com/entegra_spotlight.asp
Their blurb says, "Entegra allows an administrator to audit virtually any database activity on any SQL Server in an enterprise, whether there is a single SQL Server database, or thousands of them."
I've never used it, so I can't say whether it's what you need, one way or the other.
-
C2 Audit is not for such tasks as q
-
I think your way is trace with events : Stored Procedures->SP:StmtCompleted, TSQL->SP:StmtCompleted.
Good Luck.
-
He'd still have to parse the code which is error prone and slow.
-
Remi, is there a better way than going back and modifying, testing, etc. ALL of those stored procedures? I can't think of another way to do this except for the Profiler trace without A LOT of work.
-
Either is a lot of work... just creating the parsor seems more error prone to me than retesting the app. But then again that can be a $hitload of work too.
-
Couldn't you capture the trace to a SQL Server table and then search using T_SQL? You could capture the name of every Stored Procedure executed, and then look through those (generate a sql script with all of the executed stored procedures code in one file) and find any tables queried.
I've gone through a similar process during an upgrade procedure. It was a lot of manual work, but I'd rather have done that than try to modify over 1000 stored procs. Of course, If you don't have to deal with Source Control, Qualtiy Assurance, Testing, etc. it might not be such a bad deal...
-
If you have an automated test system both for the app and the procs it's not too bad... otherwise you're pretty much srewed either way.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply