Sql-server – Use a condition to limit the databases a policy checks

sql serversql-server-2008-r2

I'd like to create a Policy that checks, on demand, one database in an instance for tables that have no index. I can see how to create a Condition, linked to the 'Database' Facet and checks for my database's GUID. I can also see how to create a Condition, linked to the 'Table' Facet that checks for the presence of an index. When I create a Policy, however, I seem to be able to only check one Condition. I know you can add clauses to Conditions, which would be fine if I didn't have to specify a Facet and so limit my options. Can you not have multiple Conditions in a Policy?

Best Answer

You can certainly limit the set of databases that are checked, using a condition, and have that condition be separate from the actual condition the policy is using to check. Pull down the "Every" drop-down in the UI:

enter image description here

Create a new condition:

enter image description here

Then you should be able to use that condition:

enter image description here

Note that this is using the SQL Server 2012 version of Management Studio (which is now completely free, as of SP1). If the UI does not behave this way in the 2008 R2 version of the tools, upgrade your tools.

Related Solutions

Sql-server – Sql server full text search performance with additional conditions

Kin made a couple of suggestions which should be helpful.

First: Make sure that your statistics for the CallTime index are up to date.

Your plan shows that the time filter is being made by seeks to the clustered index. So, for some reason the CallTime index is not being used. What is the definition of that index? If it is a multicolumn index, be sure that the most specific column is first. Example:

IDX_Calls_CallTime
  NOT: CallID, CallTime
  USE: CallTime, CallID

Second: Consider using CONTAINSTABLE.

I doubt that you will ever want to return 1.200.000 rows for "efendim". By using CONTAINSTABLE and ranking you can set the top number of rows that you want, thus reducing the number of FullText results to process to 10, 100, 1000, or whatever fits your needs.

SQL Server Update – How to Avoid Repeating the Same Condition in a WHEN MATCHED THEN UPDATE Clause

MERGE is a handy statement, but limited in situations such as this. I'd suggest simply breaking apart your query so that you can completely cover your logic. If you were expecting or required the fact that the MERGE statement will perform it's operations in a single transaction, you can declare an explicit transaction around your logic:

BEGIN TRAN
INSERT
WHERE

UPDATE
WHERE

UPDATE
WHERE

COMMIT TRAN

Best Answer

Related Solutions

Sql-server – Sql server full text search performance with additional conditions

SQL Server Update – How to Avoid Repeating the Same Condition in a WHEN MATCHED THEN UPDATE Clause

Related Question