How to track insert volume for each database?

I need some advice to better track the insert volume of each database on a
server.
I'd like to log that number once a month in a mock up table like
Date DbName InsertCount
4/30/04 MyDb1 2000
4/30/04 MyDb2 500
5/31/04 MyDb1 1500 (probably would be based 3500-2000
previous month records)
5/31/04 MyDb2 200 (based on total record count of
700-500)
Bottom line is to get a feel of how busy a database is. I'm not sure
tracking inserts is the best way and open to any suggestions. Thank you.
SteveA database can be very "busy" and have 0 rows added, deleted, or =
updated.
Consider a database that is used for reporting purposes. The =
connections to this database will only be reading data.
You can run Profiler and PerfMon in order to see how "busy" a server =
is...but unless you know what you are looking at (or for) the numbers =
might not mean anything.
--=20
Keith
"Steve Lin" <lins@.nospam.portptld.com> wrote in message =
news:u9qExS9IEHA.1048@.TK2MSFTNGP12.phx.gbl...
>=20
>=20
> I need some advice to better track the insert volume of each database =
on a
> server.
>=20
> I'd like to log that number once a month in a mock up table like
>=20
>=20
>=20
> Date DbName InsertCount
>=20
> 4/30/04 MyDb1 2000
>=20
> 4/30/04 MyDb2 500
>=20
> 5/31/04 MyDb1 1500 (probably would be based =
3500-2000
> previous month records)
>=20
> 5/31/04 MyDb2 200 (based on total record count =
of
> 700-500)
>=20
>=20
>=20
> Bottom line is to get a feel of how busy a database is. I'm not sure
> tracking inserts is the best way and open to any suggestions. Thank =
you.
>=20
>=20
>=20
> Steve
>=20
>|||thank you for the advice. that's very ture.
i think i still would like to track that number for the purpose of server
capacity planning.
any ideas?
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:uL7Gvf9IEHA.2720@.TK2MSFTNGP11.phx.gbl...
A database can be very "busy" and have 0 rows added, deleted, or updated.
Consider a database that is used for reporting purposes. The connections to
this database will only be reading data.
You can run Profiler and PerfMon in order to see how "busy" a server
is...but unless you know what you are looking at (or for) the numbers might
not mean anything.
Keith
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:u9qExS9IEHA.1048@.TK2MSFTNGP12.phx.gbl...
>
> I need some advice to better track the insert volume of each database on a
> server.
> I'd like to log that number once a month in a mock up table like
>
> Date DbName InsertCount
> 4/30/04 MyDb1 2000
> 4/30/04 MyDb2 500
> 5/31/04 MyDb1 1500 (probably would be based
3500-2000
> previous month records)
> 5/31/04 MyDb2 200 (based on total record count of
> 700-500)
>
> Bottom line is to get a feel of how busy a database is. I'm not sure
> tracking inserts is the best way and open to any suggestions. Thank you.
>
> Steve
>|||sp_helpdb
it returns the database name, size, and other info. You could store =
this data to a table as often as you want. =20
These examples should get you started if you want rowcounts. I have =
credited the authors=20
basic rowcount:
use pubs
go
SELECT A.name, B.rows=20
FROM sysobjects A
JOIN sysindexes B ON A.ID =3D B.ID
WHERE A.type =3D 'U'
AND B.INDID < 2=20
ORDER BY A.Name
-- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
select name, rows from sysobjects
where type =3D 'U' order by rows desc
-- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
The script below will give information about the row count and space =
used
for each table ordered by rowcount and alphabetically. You can choose to
have it run with estimates or up-to-date rowcounts, for which you have =
to
uncomment DBCC UPDATEUSAGE (0) in the beginning of the script.
I have written a script that provides a lot more information about the =
data
in your tables, including cardinality and maximum size of the data in a
column, but that will take quite a long time to run.You can find it at:
http://www.sql-server-performance.c...?TOPIC_ID=3D629
The script that is attached to this message isn't mine btw, but I can't =
find
back who the original author is.
--=20
Jacco Schalkwijk
SQL Server MVP
Script:
SET NOCOUNT ON
-- DBCC UPDATEUSAGE (0)
CREATE TABLE #TBLSize
(Tblname varchar(80),
TblRows int,
TblReserved varchar(80),
TblData varchar(80),
TblIndex_Size varchar(80),
TblUnused varchar(80))
DECLARE @.DBname varchar(80)
DECLARE @.tablename varchar(80)
SELECT @.DBname =3D DB_NAME(DB_ID())
PRINT 'User Table size Report for (Server / Database): ' + =
@.@.ServerName +
' / ' + @.DBName
PRINT ''
PRINT 'By Size Descending'
DECLARE TblName_cursor CURSOR FOR
SELECT NAME
FROM sysobjects
WHERE xType =3D 'U'
OPEN TblName_cursor
FETCH NEXT FROM TblName_cursor
INTO @.tablename
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
INSERT INTO #tblSize(Tblname, TblRows, TblReserved, TblData,
TblIndex_Size, TblUnused)
EXEC Sp_SpaceUsed @.tablename
-- Get the next author.
FETCH NEXT FROM TblName_cursor
INTO @.tablename
END
CLOSE TblName_cursor
DEALLOCATE TblName_cursor
SELECT CAST(Tblname as Varchar(30)) 'Table',
CAST(TblRows as Varchar(14)) 'Row Count',
CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total =
Space (KB)',
CAST(TblData as Varchar(14)) 'Data Space',
CAST(TblIndex_Size as Varchar(14)) 'Index Space',
CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM #tblSize
Order by 'Total Space (KB)' Desc
PRINT ''
PRINT 'By Table Name Alphabetical'
SELECT CAST(Tblname as Varchar(30)) 'Table',
CAST(TblRows as Varchar(14)) 'Row Count',
CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total =
Space (KB)',
CAST(TblData as Varchar(14)) 'Data Space',
CAST(TblIndex_Size as Varchar(14)) 'Index Space',
CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM #tblSize
Order by 'Table'
DROP TABLE #TblSize
-- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
The following was adapted from SP_SPACEUSED, and will also give the size =
in
MB.
SELECT
USER_NAME(O.UID) AS OWNER,
O.NAME AS TABLE_NAME,
ROWS =3D (SELECT I.ROWS FROM SYSINDEXES I WHERE I.INDID < 2 AND I.ID =
=3D
O.ID),
RESERVED =3D (CONVERT(DEC(15),(SELECT SUM(I.RESERVED) FROM SYSINDEXES =
I
WHERE I.INDID IN (0, 1, 255) AND I.ID =3D O.ID)) / 128),
DATA =3D (CONVERT(DEC(15),(SELECT SUM(I.DPAGES) FROM SYSINDEXES I =
WHERE
I.INDID < 2 AND I.ID =3D O.ID)) / 128),
TEXT =3D (CONVERT(DEC(15),ISNULL((SELECT SUM(I.USED) FROM SYSINDEXES =
I
WHERE I.INDID =3D 255 AND I.ID =3D O.ID),0)) / 128),
USED =3D (CONVERT(DEC(15),(SELECT SUM(I.USED) FROM SYSINDEXES I WHERE
I.INDID IN (0, 1, 255) AND I.ID =3D O.ID)) / 128)
FROM SYSOBJECTS O
WHERE O.TYPE =3D 'U'
Mike Kruchten
-- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
http://www.aspfaq.com/2428
Aaron Bertrand
-- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
--=20
Keith
"Steve Lin" <lins@.nospam.portptld.com> wrote in message =
news:%23uCwPM%23IEHA.3512@.TK2MSFTNGP10.phx.gbl...
> thank you for the advice. that's very ture.
> i think i still would like to track that number for the purpose of =
server
> capacity planning.
> any ideas?
>=20
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:uL7Gvf9IEHA.2720@.TK2MSFTNGP11.phx.gbl...
> A database can be very "busy" and have 0 rows added, deleted, or =
updated.
>=20
> Consider a database that is used for reporting purposes. The =
connections to
> this database will only be reading data.
>=20
> You can run Profiler and PerfMon in order to see how "busy" a server
> is...but unless you know what you are looking at (or for) the numbers =
might
> not mean anything.
>=20
> --
> Keith
>=20
>=20
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:u9qExS9IEHA.1048@.TK2MSFTNGP12.phx.gbl...
database on a[vbcol=seagreen]
> 3500-2000
of[vbcol=seagreen]
you.[vbcol=seagreen]
>=20
>