Posts

How to Find and Update Outdated Statistics in SQL Server

We can find when the last update statistics happened and how many row modifications happened since the last update statistics using sys.dm_db_stats_properties DMV and a few joins.  We can refine this by Data Last updated and total rows and modified rows as needed. --Set the thresholds when to consider the statistics outdated DECLARE @hours int DECLARE @rows INT DECLARE @modified_rows int DECLARE @update_statement nvarchar(300); SET @hours=72 SET @modified_rows=1 SET @rows=1000000 SELECT Update_Query = 'UPDATE STATISTICS ' + QUOTENAME(sch.name) + N'.' + QUOTENAME(o.name) + N' ' + QUOTENAME(s.name)+' WITH FULLSCAN'     ,Name = QUOTENAME(sch.name) + N'.' + QUOTENAME(o.name) + N' ' + QUOTENAME(s.name)     , ddsp.stats_id , last_updated = CONVERT(datetime2(1), ddsp.last_updated)     , ddsp.rows, mod_count = ddsp.modification_counter, ddsp.unfiltered_rows FROM sys.schemas sch     INNER JOIN sys.objects ...
Post a Comment
Read more

Find a specific user across all databases in a SQL instance using SP_MSForeachdb.

The following is used to find a specific user across all databases in a SQL instance using SP_MSForeachdb.  Roles for each user are concatenated per database in one row.  A user can be listed multiple times 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 DECLARE @DB_USers TABLE (DBName sysname, UserName sysname, LoginType sysname, MemberRole varchar ( max ),create_date datetime,modify_date datetime) INSERT @DB_USers EXEC sp_MSforeachdb   ' use [?] SELECT ' '?' ' AS DB_Name, case prin.name when ' 'dbo' ' then prin.name + ' ' (' '+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =' '?' ') + ' ')' ' else prin.name end AS UserName, prin.type_desc AS LoginType, isnull(USER_NAME(mem.role_principal_id),' '' ') AS MemberRole ,create_date,modify_date FROM sys.database_princ...
Post a Comment
Read more