question

eaglescout avatar image
eaglescout asked

Cache to Find Index Usage

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)[1]', 'system') AS SchemaName ,o.value('(IndexScan/Object/@Table)[1]', 'system') AS TableName ,o.value('(IndexScan/Object/@Index)[1]', '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
dbaetl
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlaj 1 avatar image
sqlaj 1 answered
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)[1]', **'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.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
At a guess I would try using varchar in the place of system ... but XML isnt my strong point
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
+1 for contacting the presenter. They're usually happy to help.
0 Likes 0 ·
eaglescout avatar image eaglescout commented ·
Thanks guys, I appreciate the advice!
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
`sysname` might be the right data type to use, rather than `system` (which isn't a data type). The [`sysname`][1] data type is used to represent things like object names (including index names) within SQL Server. [1]: http://msdn.microsoft.com/en-us/library/ms191240(v=sql.105).aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

eaglescout avatar image
eaglescout answered
Thanks for the help guys. Both answers were helpful.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.