Lock Statistics

Question

Hi All,

Is there in SQL Server to lock statistics on tables similar to Oracle LOCK Statistics ?

Answer 1

If you mean statistics about locking in SQL Server, then something like this:

select object_name(i.object_id) table_name, 
       i.name index_name, 
	   s.row_lock_count, 
	   s.row_lock_wait_count, 
	   s.row_lock_wait_in_ms,
	   s.page_lock_count, 
	   s.page_lock_wait_count, 
	   s.page_lock_wait_in_ms,
	   s.page_latch_wait_count, 
	   s.page_latch_wait_in_ms
from sys.dm_db_index_operational_stats(db_id(),0,-1,0) s
join sys.tables t
  on s.object_id = t.object_id
join sys.indexes i
  on i.index_id = s.index_id
and i.object_id = s.object_id
where t.is_ms_shipped = 0

Or if you mean "locking statistics" as with DBMS_STATS.LOCK_TABLE_STATS, then in SQL Server you would turn off automatic stats creation and maintence and manually create and update your stats. see, eg http://www.sqlskills.com/blogs/kimberly/auto-update-statistics-and-auto-create-statistics-should-you-leave-them-on-andor-turn-them-on/

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 2

Hi All,

Is there in SQL Server to lock statistics on tables similar to Oracle LOCK Statistics ?

What does Oracle Lock statistics point out or signify can you point out so that we can correlate.

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

Answer 3

LOCK_TABLE_STATS Procedure

This procedure locks the statistics on the table.

Syntax

DBMS_STATS.LOCK_TABLE_STATS (
   ownname    VARCHAR2,
   tabname    VARCHAR2);

Parameters

Table 127-66 LOCK_TABLE_STATS Procedure Parameters

Parameter	Description
ownname	The name of the schema
tabname	The name of the table

Usage Notes

• When statistics on a table are locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.

• The SET_*, DELETE_*, IMPORT_*, GATHER_* procedures that modify statistics in the dictionary of an individual table, index or column will raise an error if statistics of the object is locked.

• Procedures that operates on multiple objects (such as GATHER_SCHEMA_STATS) will skip modifying the statistics of an object if it is locked. Many procedures have force argument to override the lock.

• This procedure either freezes the current set of the statistics or keeps the statistics empty (uncollected) to use Dynamic Sampling.

• The locked or unlocked state is not exported along with the table statistics when using EXPORT_*_STATS procedures.

Answer 4

Thank you for clarifying .AFAIK there is no feature to lock Statistics of SQL Server tables .What are you trying to achieve ?

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

Answer 5

Locking serves as a control mechanism for concurrency.

Yes, sql server do have lock statistics. Its not exactly same as that of oracle. there is difference in handling of lock escalation.

Oracle as rollback segments which are used to store the changed data before it is updated in the actual tables

For more information, you can refer the lock section of the below link

http://docs.oracle.com/cd/E10405_01/doc/appdev.120/e10379/ss_oracle_compared.htm#i1038519

http://www.brentozar.com/sql/locking-and-blocking-in-sql-server/

-Prashanth

Answer 6

Prashanth I guess user is interested in knowing that does any command or feature exists in SQL Server which can stop ( for time being) sql server to update stats for table.I guess not .May be lock that table such that even if AUTO UPDATE STATS is on stats wont be updated

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

Answer 7

Yes, you are right Shanky. 

-Prashanth

Answer 8

"To turn of auto update at the index level use STATISTICS_NORECOMPUTE in the index (or statistics) definition."

http://www.sqlskills.com/blogs/kimberly/auto-update-statistics-and-auto-create-statistics-should-you-leave-them-on-andor-turn-them-on/

NORECOMPUTE               

Disable the automatic statistics update option, AUTO_STATISTICS_UPDATE, for statistics_name. If this option is specified, the query optimizer will complete any in-progress statistics updates for statistics_name and disable future updates.

To re-enable statistics updates, remove the statistics with DROP STATISTICS and then run CREATE STATISTICS without the NORECOMPUTE option.

Caution
Using this option can produce suboptimal query plans. We recommend using this option sparingly, and then only by a qualified system administrator.

For more information about the AUTO_STATISTICS_UPDATE option, see ALTER DATABASE SET Options (Transact-SQL). For more information about disabling and re-enabling statistics updates, see Statistics.

CREATE STATISTICS

David

---

David http://blogs.msdn.com/b/dbrowne/