Hi All, I was watching a "How-To" Indexing video online and attempted to re-create the code for personal use. I re-created two separate queries (see below). The first worked without a flow and I was very pleased with the results. Now, the second query gave me a lot of trouble. For some reason I got the following error: **"Msg 9500, Level 16, State 1, Line 5 The data type 'system' used in the VALUE method is invalid."** I work with SQL Server 2008. This issue might be related to permissions, but I am not quite sure. Any help would be appreciated. Also, if someone can improve or has a better way of retrieving the same data, I would love to hear your suggestions. Thank You! FYI - I am VERY new to SQL, meaning I would like to know the "standard" way of doing this. Want to develop good habits early! /*QUERY 1*/ /*******************Generate Index performance Status*******************/ SELECT usi.database_id, usi.object_id, usi.index_id, i.name, usi.user_seeks, usi.user_scans, usi.user_lookups, usi.user_updates FROM sys.dm_db_index_usage_stats usi INNER JOIN sys.indexes i ON usi.object_id = i.object_id AND usi.index_id = i.index_id WHERE usi.database_id = DB_ID() AND usi.object_id = OBJECT_ID('TableName') /*QUERY 2*/ ;WITH XMLNAMESPACES(DEFAULT N'
http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT o.value('(IndexScan/Object/@Schema)', 'system') AS SchemaName ,o.value('(IndexScan/Object/@Table)', 'system') AS TableName ,o.value('(IndexScan/Object/@Index)', 'system') AS IndexName ,REPLACE(o.query( 'for $column in IndexScan/DefineValues/DefineValue/ColumnReference Return string($column/@Column)' ).value('.', 'varchar(max)'),' ', ', ') AS KeyLookupColumns, o.value(N'@PhysicalOp', N'varchar(50)') AS hysical_operator, o.value(N'@LogicalOp', N'varchar(50)') AS logical_operator, plan_handle, qp.query_plan FROM Sys.dm_exec_cached_plans cp CROSS APPLY Sys.dm_exec_plan_attributes(cp.plan_handle) as pa CROSS APPLY Sys.dm_exec_query_plan(cp.plan_handle) as qp CROSS APPLY qp.query_plan.nodes('//StmtSimple') as s(stmt) CROSS APPLY qp.query_plan.nodes(N'//RelOp') as r(o) WHERE query_plan.exist('.//relOp/IndexScan[@Lookup="1"]')=1 AND o.exist('./IndexScan[@Lookup="1"]')=1 AND pa.attribute = 'dbid' AND pa.value = DB_ID() GO
First, I would suggest contacting who ever the presenter was for the video you watched. Most people will provide the code used in demos. Second, as the errors said, The data type 'system' is not valid. o.value('(IndexScan/Object/@Schema)', **'system'**) AS SchemaName You need to more research to determine either the correct data type. It may be the query is written incorrectly as well.