Tuesday, May 29, 2012

Index Information - Fragmentation level, etc...

The following queries when executed together provide the following:

tablename, objectID, indexName, rows in table, index reads, indexwrites, user seeks, user scans, user lookups, Fragmentation level and LastStatsUpdate...

create table #Frag (TableName varchar(300), IndexName varchar(700), FragPercent float)

insert into #Frag(TableName, IndexName, FragPercent)SELECT c.name, b.name,ps.avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS psINNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_IDINNER JOIN sys.objects as C on B.object_ID = C.object_IDAND ps.index_id = b.index_idWHERE ps.database_id = DB_ID()ORDER BY c.name

create table #iu(tablename varchar(200), [Object_ID] bigint, Index_name varchar(700), UserSeeks bigint, UserScans bigint, UserLookups bigint, UserUpdates bigint, RowsInTable bigint,IndexReads bigint, IndexWrites bigint)

insert into #iu(tablename, Object_ID, Index_name, UserSeeks,UserScans, UserLookups,UserUpdates, RowsInTable,IndexReads, IndexWrites)SELECT
I
USER_SEEKS
USER_SCANS
USER_LOOKUPS
USER_UPDATES
OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], s.object_ID, .[NAME] AS [INDEX NAME], , , , ,
reads
writes
rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id), =user_seeks + user_scans + user_lookups, = user_updates
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 order by OBJECT_NAME(S.[OBJECT_ID])

SELECT name AS Stats, STATS_DATE(object_id, stats_id) AS LastStatsUpdate into #a
FROM sys.stats where left(name,4)!='_WA_';

select i.tablename, i.OBJECT_ID,i.index_name, i.RowsinTable, i.indexReads, i.IndexWrites, i.UserSeeks, i.UserScans,i.UserLookups,FragPercent,LastStatsUpdate
from #iu i
inner join #frag f on i.Index_name = f.indexname and i.tablename = f.tablename
inner join #a a on a.stats = i.index_name--order by i.tablename
order by laststatsupdate

Tuesday, January 10, 2012

Linked Server - Distributed Transaction Issue

--Error Message

Executed as user: domainName\user. The operation could not be performed because OLE DB provider "SQLNCLI10"
for linked server "LSName" was unable to begin a distributed transaction. [SQLSTATE 42000] (Error 7391)
OLE DB provider "SQLNCLI10" for linked server "LSName" returned message "The partner transaction manager
has disabled its support for remote/network transactions.". [SQLSTATE 01000] (Error 7412). The step failed.

--Fix
EXEC sp_serveroption @server = 'LSName',@optname = 'remote proc transaction promotion', @optvalue = 'False' ;