Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 How to determine the fastets growing object (table)  [new]
mike_mike
Member

Откуда:
Сообщений: 14
Ne podskajet li kto, kak lutshe vsego opredelit samie bistrorostushie obekti v DB?
Takje samie dolgobegushie i chastovipolniaemie SP.

Zaranee spasibo!
16 янв 04, 23:02    [495533]     Ответить | Цитировать Сообщить модератору
 Re: How to determine the fastets growing object (table)  [new]
Ilana
Member

Откуда: LA
Сообщений: 8
---//Ne podskajet li kto, kak lutshe vsego opredelit samie bistrorostushie obekti v DB?
sp_spaceused (executed daily or weekly for reqiested objects)
select into table and run report against.

---//Takje samie dolgobegushie
SQL Profiler , check duration and analyze Data.


--// i chastovipolniaemie SP.
This could be useful or use SQL Profiler again.


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetProceduresFoundInCache]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetProceduresFoundInCache]
GO





CREATE PROCEDURE [dbo].[GetProceduresFoundInCache]
AS
/*******************************************************************************************************
* dbo.GetProceduresFoundInCache
* Creator: Bill Wunder
* bwunder@yahoo.com
*
* Description: periodically sample syscacheobjects to track which procedures are being used
* Notes: Set up a SQL Agent job that runs once a minute to call this job.
* Table size is limited to one row per distinct stored procedure called on server
*
* Usage:
EXECUTE GetProceduresFoundInCache
*
*
* Modifications:
* Developer Name Date Brief Description
* ------------------ -------- ------------------------------------------------------------
*
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------
declare @findstr varchar(20)

---------------------------------------------
-- create temp tables
---------------------------------------------
declare @cacheobjects table (id int identity(1,1) primary key,
sql varchar(100),
dbname varchar(30))
declare @procedures table (id int identity(1,1) primary key,
procname varchar(100),
dbname varchar(30))

-- create the history table if it doesn't already exist
if object_id('dbo.ProceduresFoundInCache','U') is null
CREATE TABLE dbo.ProceduresFoundInCache
(ProcedureName varchar(100) not null,
DatabaseName varchar(30),
LastFoundDt datetime null, -- if null later you'll know the proc was only used that one time
RecCreatedDt datetime
CONSTRAINT dft_ProceduresFoundInCache__LastFoundDt default getdate(),
FoundCount int not null
CONSTRAINT dft_ProceduresFoundInCache__FoundCount default 1,
CONSTRAINT pk_ProceduresFoundInCache__ProcedureName__DatabaseName
primary key (ProcedureName, DatabaseName))

if object_id('dbo.CacheSeed','P') is null
exec sp_executesql N'create proc dbo.CacheSeed as select @@spid'


---------------------------------------------
-- set session
---------------------------------------------
SET NOCOUNT ON


---------------------------------------------
-- body of stored procedure
---------------------------------------------

if not exists (select 1 from master.dbo.syscacheobjects with(nolock)
where objid = object_id('dbo.CacheSeed') and dbid = db_id('scratchpad'))
begin
-- first get a snapshot of interesting syscacheobjects to minimize system impact while updating history table
-- get rid of tabs, line feeds and carriage returns found in the sql column of syscacheobjects
-- make a bit of an attempt to eliminate two white adjunnct spaces
insert @cacheobjects
select replace(replace(replace(replace(substring(ltrim(rtrim(sql)),1,100), char(9), char(32)), char(10) , char(32)), char(13), char(32)), char(32) + char(32), char(32)),
db_name(dbid)
from master.dbo.syscacheobjects with(nolock)
where cacheobjtype = 'Compiled Plan'

-- remove database and owner qualification syntax (help avoid duplicates and misqueries later)
-- may as well standardize the exec now too for parsing in next steps
update @cacheobjects
set sql = replace(replace(replace(replace(sql,'..','.'),dbname + '.',''),'dbo.',''),'execute' + char(32),'exec' + char(32))

-- filter as many cache items as possible that are not stored procedures
set @findstr = 'exec' + space(1)

insert @procedures
(procname,
dbname)
select distinct
case when charindex(@findstr,sql) > 0
then cast(substring(sql,
charindex(@findstr, sql) + datalength(@findstr),
charindex(char(32), substring(sql,
charindex(@findstr, sql) + datalength(@findstr),
datalength(sql)))) as varchar(100))
when charindex(char(32), sql) = 0
then sql
else null
end,
dbname
from @cacheobjects
where charindex('sp_MS',sql) = 0
and charindex('sp_help',sql) = 0
and charindex('dbo.sys',sql) = 0
and charindex('information_schema',sql) = 0
and charindex ('select ', sql) = 0
and charindex ('insert ', sql) = 0
and charindex ('update ', sql) = 0
and charindex ('delete ', sql) = 0
and charindex ('CREATE ', sql) = 0
and charindex ('ALTER ', sql) = 0
and charindex ('GRANT ', sql) = 0
and charindex ('REVOKE ', sql) = 0
and charindex ('DENY ', sql) = 0

-- at this point the procedure will stil be prefixed with "<dbname>." in the case where the connectin db
-- is not the db where the procedure lives so move the procedures db out to the dbname column
update @procedures
set dbname = case when charindex('.', procname) > 0
then substring(procname,1,charindex('.', procname) - 1)
else dbname
end,
procname = case when charindex('.', procname) > 0
then substring(procname,charindex('.', procname) + 1, datalength(procname))
else procname
end

update p
set FoundCount = FoundCount + 1,
LastFoundDt = getdate()
from dbo.ProceduresFoundInCache p
inner join @procedures p1
on p.ProcedureName = p1.procname
and p.DatabaseName = p1.dbname

insert dbo.ProceduresFoundInCache
(ProcedureName,
DatabaseName)
select distinct procname,
dbname
from @procedures p
where p.procname is not null
and rtrim(ltrim(p.procname)) <> ''
and not exists (select 1
from dbo.ProceduresFoundInCache
where ProcedureName = p.procname
and DatabaseName = p.dbName)
and p.dbname not in ('master','model','msdb','tempdb', 'pubs', 'Northwind')

-- exec the seed proc to get it back in cache
exec dbo.CacheSeed

end



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
17 янв 04, 00:48    [495566]     Ответить | Цитировать Сообщить модератору
 Re: How to determine the fastets growing object (table)  [new]
mike_mike
Member

Откуда:
Сообщений: 14
Bolshoe spasibo!
Very helpfull
17 янв 04, 01:24    [495579]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить