Project Description

A collection of scripts that simplify the usage of SQL Server 2005 and over DMVs, avoiding the user to have to write queries with tons of joins and allowing him to have directly the results he needs.

SQL Server Versions

Scripts are compatibile with SQL Server 2005, 2008 and 2008R2
Update for SQL Server 2012 are being developed.

Downloads

Scripts can be downloaded via the "Source Code" tab, or through SCC Client.

Content

Newly added scripts are underlined.

VIEWS/FUNCTIONS
sys2.buffer_cache_usage
Show how much memory is being used for Plan Cache by each Database

sys2.database_backup_info
Returns information of last backup of each database

sys2.database_files
Shows the space available and the spaces used for eah file in a database.

sys2.indexes
Returns a row per index in table or view. It's a wrapper around sys.indexes.

sys2.indexes_operational_stats
Returns a row per index in table or view, displaying operational data. It's a wrapper around sys.dm_db_index_operational_stats().

sys2.indexes_per_table
Display a list of all table along with information regarding presence of clustered and nonclustered indexes.

sys2.indexes_physical_stats
Returns a row per index in table or view, displaying physical informations. It's a wrapper around sys.dm_db_index_physical_stats().

sys2.indexes_size
Returns a row per index in table or view, displaying its size in MB and KB.

sys2.indexes_usage_stats
Returns a row per index in table or view, displaying usage informations. It's a wrapper around sys.dm_db_index_usage_stats().

sys2.logs_usage
Display Transaction Log usage data for all databases.

sys2.missing_indexes
Returns a row per detected missing index in the whole instance. It's a wrapper around sys.dm_db_missing_index_* DMVs.

sys2.objects_data_spaces
Returns a row per index/heap per table, showing in which Filegroup the object is located. Also shows in which filegroup LOB data are stored and the space used in any Filegroup.

sys2.objects_dependencies
Returns a list of all the objects upon which a table or view is dependent.

sys2.objects_partition_ranges
Returns a row per partition per table, showing in which Filegroup the partition is located, how many rows are there, and the partition's range values.

sys2.plan_cache_size
Shows how much memory is being used by different object types in plan cache, reporting also how much memory assigned to reused plan and to plans used only one time

sys2.query_memory_grants
Wrapper around sys.dm_exec_query_memory_grants.

sys2.query_stats
Wrapper around sys.query_stats.

sys2.stats
Wrapper around sys.stats.

sys2.tables_columns
Return tables and their columns and types. Also tell if a column is a LOB column or not and, if yes, in which filegroup is stored.

STORED PROCEDURES

stp_get_databases_space_used_info
Returns a list of all databases along with the relativ space used, space available, max space and growth.

Last edited Dec 12, 2011 at 11:11 AM by manowar, version 20