Of course, I tested the queries and their accuracy by inserting/deleting a few records in one of the AdventureWorks tables. The “statistics” seems to be updated in real time, thing certified also by the SQL Server 2005 documentation. I wanted to see the definition of the respective views, though they are not browsable by using the Management Studio. Some time ago I discovered that the definition of system views, functions and stored procedures are available though the sys.all_sql_modules view, the union join between sys.sql_modules and sys.system_sql_modules views. Here’s the query I used:
-- retrieving the definition of system views used to return the number of records SELECT * FROM master.sys.all_sql_modules WHERE CHARINDEX ('sys.dm_db_partition_stats', definition)>0 OR CHARINDEX ('sys.sysindexes', definition)>0 OR CHARINDEX ('sys.partitions', definition)>0
Notes:
1. Another way to get the definition of an object is to use the Object_Definition function:
SELECT object_definition(object_id('sys.sysindexes'))
2. Following Mladen Prajdic’s post, an SQL Server MVP, I found out that all the system objects are stored in the (hidden) read-only MsSqlSystemResource database that complements the master database.
3. Diving into the three view’s definition, can be seen that all of them use the OpenRowSet function in order to make calls to INDEPROP, PARTITIONCOUNTS, ALUCOUNT internal SQL Server tables. As it seems this functionality of OpenRowSet function can’t be (mis-)used by users.
4. I found several interesting posts on sys.dm_db_partition_stats, for example Cindy Gross’ post, a Support Engineer at Microsoft, the blog containing several scripts and links to more resources.
No comments:
Post a Comment