Well, I *wish* they were lying to me, it would make it easier for me to yell at someone other than myself.
OK, let’s back up a bit.
I had this *crazy* idea that I could build a policy to help me identify indexes that were not being used. Seems like a good idea, right? Find a way to quickly determine if I have indexes that I don’t need, wouldn’t that be a good thing? I know a little bit about Policy Based Management (PBM) so I dived right in. First up, of course, is that I need to select a target and build a condition.
In this case it would make sense to use the Index target because I am trying to return information about an index. I already have the script in mind that I want to use which means I also know I will be using the ExecuteSQL() function inside of the condition I create. So I go to create the condition and notice that when I select the ExecuteSQL() function there are some details displayed:
Variant ExecuteSql(string returnType, string sqlQuery)
Runs a Transact-SQL query against a target instance of SQL Server. The On Demand condition is the only supported
evaluation mode for ExecuteSql. Only one table column can be specified in a SELECT statement; more than one column
will throw an exception. A SELECT statement can use the following variables, which will be substituted at run time
by using ADO.NET parameter substitution:
@@ObjectName - corresponds to the name field in sys.objects.
The variable will be replaced with the name of the current object.
@@SchemaName - corresponds to the name field in sys.schemas.
The variable will be replaced with the schema for the current object if the current object belongs to a schema.
Arguments:
sqlQuery - String that represents the query to be run.
returnType - Specifies the return type of data that is returned by SQL Server. The valid literals for returnType are
Numeric, String, Bool, DateTime, Array, and GUID.
Return value - The first column in the first row of the result set returned by the Transact-SQL query.
Example: ExecuteSQL('Numeric','SELECT COUNT(*) FROM msdb.dbo.sysjobs')<>0
Let’s focus on @@objectname. If you read the above details you might think that you could use @@objectname in your query in order to have your query filter for the exact index you need. You might, but you would be wrong. In fact, if you build a condition on the Index target and simply use this statement:
ExecuteSQL('string', 'SELECT @@objectname')
you will get back an error:
This is not a fun thing to fight through. I wasted a good chunk of time yelling at PBM thinking that it should work based upon the details noted above. And yet it wouldn’t, so something must be different, but what?
Turns out that the @@ObjectName refers to the name being listed in sys.objects (just like it says). Guess what? Indexes are not listed in sys.objects. Apparently they aren’t objects, I guess. But they are listed in sys.indexes, which is nice, but not helpful. So if I switch to the Tables target then the @@ObjectName works just fine. Thanks, Microsoft, you really know how to tease a person.
Anyway, I thought I would post this as a reminder to others that might be heading down a similar path. I think the real frustration here is that there is an Index facet of @Name, so it’s not like PBM doesn’t know the name of the Index, but it cannot communicate that name into the ExecuteSQL() function.
If there was a way to combine these two, or expand upon the variables that can be passed, then I think PBM might be a little more robust and perhaps even more widely used. Until then I guess I will just stick with Operations Manager and have that monitor my indexes. I bet I could get OpsMgr to tell me which indexes are unused, fragmented, etc. with a lot less headache than PBM has caused me this past week.
Unfortunately there is a problem with the Index facet
that will still restrict your ability to use a technique I use to get around
this issue but I can tell you how to get the Index name into your ExecuteSql()
query. With the Index facet the @ID property is the index for the TABLE, not
for the DATABASE, as the documentation states (Microsoft needs to add another
property to indicate which TABLE the ID is referring to). However, if you have
a naming standard that every index must have a unique name (within the
database) then you’re set to go.
There is a @Name property for the Index Facet – just about all facets have one.
So you can concatenate values together to input that property into the
statement you are going to execute. Let’s pretend that we want to get the index
type description (column [type_desc] from sys.indexes) and say that all indexes
that start with PK must be CLUSTERED. We include a table filter to handle the
index names that start with PK so we are left with returning the index type
description. The SQL Statement would be:
DECLARE @IndexName VARCHAR(128);
SET @IndexName = ‘Your Index Name Here’;
SELECT [type_desc] FROM [sys].[indexes] WHERE [name] = @IndexName;
To run and execute this from the Index facet you assemble this statement,
replacing the “Your Index Name Here” with the value of the @Name property. You
do this by multiple iterations of the Concatenate function. Then you use
ExecuteSql with the new statement that you’ve created. So the left side of your
expression looks like this:
ExecuteSql(‘STRING’, Concatenate(Concatenate(‘DECLARE @IndexName
VARCHAR(128);SET @IndexName = ”’, @Name), ”’;SELECT [type_desc] FROM
[sys].[indexes] WHERE [name] = @IndexName;’))
I like to set a variable up front so that I don’t have to keep concatenating
the value into the query.
Of course in most objects you have an @ID column. In those cases I use the
STRING(@ID) function during the concatenation.
First, I’m giving you a special #datahug for leaving a comment on a blog post that is 5+ years old. That’s awesome.
Second, I need to clean this post up, the formatting looks awful. This is the issue when you have over 1,000 posts spread across many different themes. I’m starting to think we should go back to plain text and green screens.
Lastly, thanks for sharing your workaround!