August 1, 2008 at 9:46 am
Hi All,
I have near about 1300 tables i just want to check which tables are updated or have new rows added ....
Something like when last row inserted or any updates on existing data.
Please help me as soon as possible.
August 1, 2008 at 10:08 am
vasaharshit (8/1/2008)
Hi All,I have near about 1300 tables i just want to check which tables are updated or have new rows added ....
Something like when last row inserted or any updates on existing data.
Please help me as soon as possible.
Try this:
SELECTid
,indid
,OBJECT_NAME(id)
,[name]
,rowcnt
,rowmodctr
,STATS_DATE(id, indid) AS Date_LastUpdated
FROM sys.sysindexes WITH ( NOLOCK )
WHERE indid > 0 AND indid < 255 AND
OBJECTPROPERTY(id, 'IsUserTable') = 1 AND -- system tables excluded
INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id, name, 'IsHypothetical') = 0 AND
rowmodctr <> 0
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 1, 2008 at 10:29 am
Thanks a lot Mr or Mrs. 500 !!!
i got some result but the probleme is i am not able to analyze it.
August 1, 2008 at 10:31 am
vasaharshit (8/1/2008)
Thanks a lot Mr or Mrs. 500 !!!i got some result but the probleme is i am not able to analyze it.
where is the difficulty?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 1, 2008 at 10:42 am
You have to populate a table using the output of that query, just run the process once a day and you will end up with one row per object per day - don't forget to add a timestamp column 😀
That way, over time you would be able to check how each table behaves.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 1, 2008 at 10:47 am
PaulB (8/1/2008)
You have to populate a table using the output of that query, just run the process once a day and you will end up with one row per object per day - don't forget to add a timestamp column 😀That way, over time you would be able to check how each table behaves.
Is this better? 😉
CREATE PROCEDURE [dbo].[UpdateStatsOnlyThoseNeeded]
(
@DBNameSYSNAME
)
AS
SET NOCOUNT ON;
DECLARE @sqlVARCHAR(1000);
DECLARE @idINT;
DECLARE@indidTINYINT;
DECLARE @tblnameSYSNAME;
DECLARE@idxnameSYSNAME;
DECLARE@rowcntINT;
DECLARE@rowmodctrINT;
DECLARE @dateUpdatedSMALLDATETIME;
DECLARE @batchIDSMALLINT;
CREATE TABLE #IdxsToUpdate
(
idINT
,indidTINYINT
,tblnameSYSNAME
,idxnameSYSNAME
,rowcntINT
,rowmodctrINT
,dateUpdatedSMALLDATETIME
)
DELETE dbo.UpdateDBStatsStatus
WHERE DATEDIFF(DAY, db_recordtimestamp, GETDATE()) > 7;
SELECT @batchID = ISNULL(MAX(BatchID),0) + 1
FROM dbo.UpdateDBStatsStatus
SET @sql = 'USE ' + @DBName + '
SELECTid
,indid
,OBJECT_NAME(id)
,[name]
,rowcnt
,rowmodctr
,STATS_DATE(id, indid) AS Date_LastUpdated
FROM sys.sysindexes WITH ( NOLOCK )
WHERE indid > 0 AND indid < 255 AND
OBJECTPROPERTY(id, ''IsUserTable'') = 1 AND -- system tables excluded
INDEXPROPERTY(id, name, ''IsStatistics'') = 0 AND
INDEXPROPERTY(id, name, ''IsHypothetical'') = 0 AND
rowmodctr <> 0';
INSERT INTO #IdxsToUpdate
(
id
,indid
,tblname
,idxname
,rowcnt
,rowmodctr
,dateUpdated
)
EXEC ( @sql );
DECLAREC_IdxsToUpdate CURSOR FAST_FORWARD READ_ONLY FOR
SELECTid
,indid
,tblname
,idxname
,rowcnt
,rowmodctr
,dateUpdated
FROM #IdxsToUpdate;
OPEN C_IdxsToUpdate;
FETCH C_IdxsToUpdate
INTO
@id
,@indid
,@tblname
,@idxname
,@rowcnt
,@rowmodctr
,@dateUpdated;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'USE [' + @DBName + '] UPDATE STATISTICS [dbo].[' + @tblname + '] [' + @idxname + '] WITH FULLSCAN';
EXEC ( @sql );
INSERT INTO dbo.UpdateDBStatsStatus
(
BatchID
,DBName
,TableName
,IndexName
,SQLCmd
,PreviousDateUpdated
,DateUpdated
)
VALUES (@batchID
,@DBName
,@tblname
,@idxname
,@sql
,@dateUpdated
,STATS_DATE(@id, @indid) );
FETCH C_IdxsToUpdate
INTO
@id
,@indid
,@tblname
,@idxname
,@rowcnt
,@rowmodctr
,@dateUpdated;
END
CLOSE C_IdxsToUpdate;
DEALLOCATE C_IdxsToUpdate;
DROP TABLE #IdxsToUpdate;
---------- / dbo.UpdateStatsOnlyThoseNeeded --------------------------------------------
GO
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 1, 2008 at 10:54 am
Nice!
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 1, 2008 at 11:05 am
Thanks a lot!!!
Just one confirmation needed i will have to create this table before right UpdateDBStatsStatus?
and i will have to run it once in a day,right?
so it will add those table which are changed from its status which was on a day before.?
August 1, 2008 at 11:08 am
vasaharshit (8/1/2008)
Thanks a lot!!!
Just one confirmation needed i will have to create this table before right UpdateDBStatsStatus?
and i will have to run it once in a day,right?
so it will add those table which are changed from its status which was on a day before.?
Yes, UpdateDBStatsStatus has to be created beforehand and pruned regularly (I prune anything older than 7 days inside the script).
The table is actually not strictly needed. You can do the updatestats without it, but I keep it for analysis purposes.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply