Determining the database owner is important if you want to take advantage of cross-database-ownership-chaining. If databases have different owners, then you have issues with accessing objects between databases. To find the database owners: [cc lang=”sql”] SELECT SUSER_SNAME(owner_sid) FROM sys.databases [/cc] To change the owner of a database: [cc lang=”sql”] USE database EXEC sp_changedbowner ‘sa’ [/cc] The standard owner for databases is usually sa.
Continue reading ...
The following query will compare the nonclustered indexes vs the clustered index and determine which index would qualify as the best clustered index based upon the DMV statistics. If a clustered index does not exist on the table, it will also suggest one of the existing nonclustered indexes as the clustered index. [cc lang=”sql”] DECLARE @NonClusteredSeekPct float DECLARE @ClusteredLookupFromNCPct float — Define percentage of usage the non clustered should — receive over the clustered index SET @NonClusteredSeekPct = 1.50 — 150% — Define the percentage of all lookups on the clustered index — should be executed by this non clustered index SET @ClusteredLookupFromNCPct = .75 — 75% SELECT TableName = object_name(idx.object_id) ,NonUsefulClusteredIndex = idx.NAME ,ShouldBeClustered = nc.NonClusteredName ,Clustered_User_Seeks = c.user_seeks ,NonClustered_User_Seeks = nc.user_seeks ,Clustered_User_Lookups = c.user_lookups ,DatabaseName = db_name(c.database_id) FROM sys.indexes idx LEFT JOIN sys.dm_db_index_usage_stats c ON idx.object_id = c.object_id AND idx.index_id = c.index_id –AND c.database_id = @DBID JOIN ( SELECT idx.object_id ,nonclusteredname = idx.NAME ,ius.user_seeks FROM sys.indexes idx JOIN sys.dm_db_index_usage_stats ius ON idx.object_id = ius.object_id AND idx.index_id = ius.index_id WHERE idx.type_desc = ‘nonclustered’ AND ius.user_seeks = ( SELECT MAX(user_seeks) FROM sys.dm_db_index_usage_stats WHERE object_id = ius.object_id AND type_desc = ‘nonclustered’ ) GROUP BY idx.object_id ,idx.NAME ,ius.user_seeks ) nc ON nc.object_id = idx.object_id WHERE idx.type_desc IN (‘clustered’,’heap’) — non clustered user seeks outweigh clustered by 150% AND nc.user_seeks > (c.user_seeks * @NonClusteredSeekPct) — nc index usage is primary cause of clustered lookups 80% AND nc.user_seeks >= (c.user_lookups * @ClusteredLookupFromNCPct) ORDER BY nc.user_seeks DESC [/cc] The way it performs this determination is […]
Continue reading ...
A rules engine is a schedule based data validity application that typically runs as a meta-layer on top of an OLTP application. It fires a set of queries (defined as rules) which determine whether the underlying data elements comply with a specific rule’s definition. The compliance to the rule is then recorded indicating a pass or fail. Determining the object to report on The first step is to understand the main object of data you are monitoring. This could range from monitoring customers, loans, employees.. Basically any object that has data surrounding it can be monitored. This object will then be the focus of the rules engine report. Sometimes the data being monitored may be composed of more than one object. You may want to monitor the data surrounding customers at certain store locations. In which case, the granularity of your application will be customer-location combinations. In our example we will monitor customers for our retail store AlphaBeta. The rule data we want to collect falls within 3 categories: Failure to Pay Significant Purchases Returns These 3 categories are parent categories for a series of “Rules”. These rules are actually stored procedures that check for specific conditions surrounding our customers. Here are the rules in each category: Failure to pay: Rule 1 – Has failed credit card authorizations within 30 days Rule 2 – Insufficient funds outstanding Significant purchases: Rule 1 – Order amount over $500 within 30 days Rule 2 – Alcohol purchase over $200 […]
Continue reading ...
