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.
| Author |
Topic |
|
SadafKhan85
Starting Member
8 Posts |
 Posted - 2013-07-03 : 07:41:10
|
| When we perform heavy DMLs on tables updating statistics information becomes necessary for improved query performance. Below is the query which return no. of updated rows along with percent change in statistics so that the user can take decision on updating it.-- To check number of rows updated and percentage of change in order to clean statistics. select schemas.name as table_schema, tbls.name as table_name, i.name as index_name, i.id as table_id, i.indid as index_id, i.rowmodctr as modifiedRows, (select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) as rowcnt, convert(DECIMAL(18,8), convert(DECIMAL(18,8),i.rowmodctr) / convert(DECIMAL(18,8),(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2))) as ModifiedPercent, stats_date( i.id, i.indid ) as lastStatsUpdate from sysindexes i inner join sysobjects tbls on i.id = tbls.id inner join sysusers schemas on tbls.uid = schemas.uid inner join information_schema.tables tl on tbls.name = tl.table_name and schemas.name = tl.table_schema and tl.table_type='BASE TABLE' where table_schema <> 'sys' and i.rowmodctr > 1 order by ModifiedPercent desc |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-07-03 : 11:08:58
|
| Is there a question? |
 |
|
|
|
|
|
|
|
Statistics information query