Find Best Clustered Index

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 ...

Creating a Rules Engine

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 […]

Kill All Connections on a Database

This seems to be the easiest way to kill all connections (sessions) on a SQL Server database: [cc lang=”sql”] ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE [/cc] After executing the above, it will put the database in single user mode. So you then need to remove single user mode using: [cc lang=”sql”] EXEC sp_dboption ‘database_name’, ‘single user’, ‘FALSE’ [/cc] Continue reading ...

Conditional Where Clause

A very powerful yet obscure feature in SQL is the ability to have parts of the where clause conditionally execute. Most often developers will make duplicate copies of an entire query to facilitate two different variable choices. Below is an example stored procedure to return employees that has an optional parameter to return employees based on GenderType. If no values is passed into the parameter, all employees will be returned. If the parameter is filled it will return the employees of that gendertype. Many developers will write the query this way: [cc lang=”sql”] CREATE PROCEDURE spGetEmployees ( @GenderType varchar(20) = NULL ) AS BEGIN IF @GenderType IS NOT NULL BEGIN SELECT * FROM dbo.Employees e WHERE e.GenderType = @GenderType END ELSE BEGIN SELECT * FROM dbo.Employees e END END [/cc] As you can see, the same query is repeated twice. This accounts for twice the maintenance and two different execution plans being generated (depending on which version of SQL Server you are using). Now we’ll write the same query with a conditional where clause [cc lang=”sql”] CREATE PROCEDURE spGetEmployees ( @GenderType varchar(20) = NULL ) AS BEGIN SELECT * FROM dbo.Employees e WHERE (@GenderType IS NULL OR e.GenderType = @GenderType) END [/cc] What we did here is utilize the power of the OR statement in the Where clause. In an or statement if the left side of the OR statement evaluates to true, then the second side is not evaluated. So when no value is entered for @GenderType, then the […]