几个精彩的DMV

--统计表的增删改次数，反映表的使用程度　　

SELECT DB_NAME([database_id]) AS [Database]
,iops.[object_id] AS [ObjectID]
,QUOTENAME(OBJECT_SCHEMA_NAME(iops.[object_id], [database_id])) + N'.' + QUOTENAME(OBJECT_NAME(iops.[object_id], [database_id])) AS [ObjectName]
,i.[index_id] AS [IndexID]
,i.[name] AS [IndexName]
,i.[fill_factor] AS [IndexFillFactor]
,iops.[partition_number] AS [PartitionNumber]
,CASE
WHEN i.[is_unique] = 1
THEN 'UNIQUE '
ELSE ''
END + i.[type_desc] AS [IndexType]
,iops.[leaf_insert_count] AS [LeafInsertCount]
,iops.[leaf_delete_count] AS [LeafDeleteCount]
,iops.[leaf_update_count] AS [LeafUpdateCount]
FROM [sys].[dm_db_index_operational_stats](null, NULL, NULL, NULL) iops
INNER JOIN [sys].[indexes] i
ON i.[object_id] = iops.[object_id]
AND i.[index_id] = iops.[index_id]
WHERE OBJECTPROPERTY(iops.[OBJECT_ID],'IsUserTable') = 1
ORDER BY iops.[page_latch_wait_count] + iops.[page_io_latch_wait_count] DESC

 

 

--SP最后一次执行时间
SELECT o.name,
ps.last_execution_time
FROM sys.dm_exec_procedure_stats ps
INNER JOIN
sys.objects o
ON ps.object_id = o.object_id
WHERE DB_NAME(ps.database_id) = 'Your DBname'
ORDER BY
ps.last_execution_time DESC

 

--varchar 复制到 nvarchar乱码了，查询时通过如下进行转码

select cast (cast (remarks as varbinary(1000)) as varchar(2000)) as remarks
from t1 with (nolock)
where id =22685940