profiler - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

profiler

Author

Topic

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-02-08 : 23:54:22

im trying to use the profiler to find out which tables are still being accesses as there seems to be alot of suspected tables lying around and stored objects (sps, functions etc) can the trace profiler be used in this situation?

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2007-02-09 : 00:37:24

By suspected tables do you mean test tables which are not used by you application?

Yes I think you can use it, but you can also do a search in your code to find out what all stored procedures you are using in you code.

Thanks
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

MohammedU
Posting Yak Master

145 Posts

Posted - 2007-02-09 : 00:54:37

Profiler can use profile...
In filter use OBJECT ID filter....

MohammedU

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-02-11 : 23:42:39

were trying to find which tables are no longer being accessed and which stored procedure are also not being accessed, keep in mind tables are not only accessed by SPs, is this the best way

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-11 : 23:54:14

Just to start you can do this:

declare @tbl_name varchar(100),
	@tbl_type varchar(100)

declare c1 cursor for select table_name, table_type from information_schema.tables where objectproperty(object_id(table_name),'IsMSShipped') = 0

if not(object_id('#tbl2') is not null)
	drop table #tbl2

create table #tbl2
(
	tbl_name varchar(100),
	tbl_type varchar(100),
	total_recs int
)

open c1

fetch next from c1 into @tbl_name, @tbl_type

while @@fetch_status = 0
Begin

	IF not EXISTS (SELECT * FROM information_schema.routines where routine_definition like '' + '%' + @tbl_name + '%' + '') and
		not exists(Select * from information_schema.views where view_definition like ''+ '%' + @tbl_name + '%' + '')

	begin
		insert into #tbl2 select @tbl_name, @tbl_type, 0

		update t 
		set total_recs = rows 
		from sysindexes as si join #tbl2 as t
		on si.id = object_id(t.tbl_name) 
		where si.indid < 2
	end

	fetch next from c1 into @tbl_name, @tbl_type
end	

close c1
deallocate c1

select * from #tbl2

Although it is not 100% reliable, it will give you good idea.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-02-12 : 00:39:39

thank you however most tables i am concerned about are accessed using select statements not inserts

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-02-12 : 01:39:09

but still this only shows altered items not accessed which is still the second part of this small project

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2007-02-12 : 09:08:21

Since you posted this in the SQL Server 2005 forum I'm assuming you're using SQL Server 2005. There is a view called sys.dm_db_index_usage_stats that will give you usage stats. This will tell you which indexes are being used and which indexes aren't being used. There may be tables with indexes that are being accessed without using the index. You can capture a profiler trace and then search that for table names. That will capture directly queried tables. What you're asking for isn't really that simple.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-02-12 : 18:11:49

yep uve come as close to it as i possible can... thank you once again

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-02-12 : 20:50:38

does anyone know if the modify_date in the sys.tables is changed when a record is added to the table? because mione doesnt seem to update, or is it just when the table is ALTERED

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-02-12 : 21:46:16

its okay i figured its the structure of the table that needs to be modified to change that date not the update of records

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-02-13 : 01:49:19

Im using this script but it is very inacurate, thought id share anyway for anyone who has time to add to it:

USE admin
GO
alter proc last_used
@firstparam int
AS

SET NOCOUNT ON
-- 0 : never used
-- Greater than 0: displays tables used longer then XXX days ago
-- These statistics are reset when database is rebooted

Create table #last_altered
(
database_name varchar(200),
[name] varchar(200),
created varchar(200),
last_user_seek varchar(200),
last_user_update varchar(200),
)

-- Create temp table to hold database names
CREATE TABLE #DBList
(
lngid INTEGER IDENTITY(1,1),
strDatabaseName VARCHAR(255)
)

DECLARE @lngLoop1 INTEGER,
@lngDBCount INTEGER,
@databasename VARCHAR (255),
@strSQL NVARCHAR(4000)

-- Set constants
SET @lngLoop1 = 1

-- insert database names into #dblist table
INSERT INTO #DBList (strDatabaseName)
SELECT '[' + name + ']' FROM master.dbo.sysdatabases WHERE dbid > 4
SET @lngDBCount = @@ROWCOUNT

--Loop through all databases
WHILE @lngLoop1 <= @lngDBCount
BEGIN
SET @databasename = (SELECT strDatabaseName FROM #DBList WHERE lngid = @lngLoop1)

if @firstparam=0
BEGIN
SET @strSQL = 'USE '+@databasename+'
insert into #last_altered
SELECT '''+@databasename+''',so.name,so.create_date,us.last_user_seek, us.last_user_update
FROM sys.dm_db_index_usage_stats us
INNER JOIN sys.objects so
ON us.object_id = so.object_id
INNER JOIN sys.indexes si
ON so.object_id = si.object_id
WHERE so.type = ''U''
AND us.user_seeks = 0
AND us.user_scans = 0
AND us.system_seeks = 0
AND us.system_scans = 0
AND so.is_ms_shipped=0
AND so.create_date < (CURRENT_TIMESTAMP-'+convert(varchar,@firstparam)+')
UNION
--Need indexes that do not appear in the DMV
SELECT '''+@databasename+''',so.name,so.create_date,us.last_user_seek, us.last_user_update
FROM sys.objects so
INNER JOIN sys.indexes si
ON so.object_id = si.object_id
LEFT JOIN sys.dm_db_index_usage_stats us
ON si.object_id = us.object_id
AND si.index_id = us.index_id
WHERE us.object_id IS NULL
AND so.type = ''U''
AND so.create_date < (CURRENT_TIMESTAMP-'+convert(varchar,@firstparam)+')
AND so.is_ms_shipped=0
'
exec (@strSQL)
END

if @firstparam<>0
BEGIN
SET @strSQL = 'USE '+@databasename+'
insert into #last_altered
SELECT '''+@databasename+''',so.name,so.create_date,us.last_user_seek, us.last_user_update
FROM sys.dm_db_index_usage_stats us
INNER JOIN sys.objects so
ON us.object_id = so.object_id
INNER JOIN sys.indexes si
ON so.object_id = si.object_id
WHERE so.type = ''U''
AND us.last_user_seek < (CURRENT_TIMESTAMP-'+convert(varchar,@firstparam)+')
AND us.last_user_update < (CURRENT_TIMESTAMP-'+convert(varchar,@firstparam)+')
AND so.create_date < (CURRENT_TIMESTAMP-'+convert(varchar,@firstparam)+')
AND so.is_ms_shipped=0
UNION
--Need indexes that do not appear in the DMV
SELECT '''+@databasename+''',so.name,so.create_date,us.last_user_seek, us.last_user_update
FROM sys.objects so
INNER JOIN sys.indexes si
ON so.object_id = si.object_id
LEFT JOIN sys.dm_db_index_usage_stats us
ON si.object_id = us.object_id
AND si.index_id = us.index_id
WHERE us.object_id IS NULL
AND so.type = ''U''
AND so.create_date < (CURRENT_TIMESTAMP-'+convert(varchar,@firstparam)+')
AND so.is_ms_shipped=0
'
exec (@strSQL)
END

SET @lngLoop1 = @lngLoop1 + 1
END

--update #last_altered set last_user_seek = 'NEVER' where last_user_seek IS NULL
--update #last_altered set last_user_update = 'NEVER' where last_user_update IS NULL

select * from #last_altered order by database_name

drop table #last_altered

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-02-14 : 00:32:39

by the way does anyone know how to monitor which sps are being used, i know profiler can be used but anything else, i only worry that profiler uses too many system resources

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources