Posts
Thursday, November 1, 2012
SQL - EDMPASS Nov. 20th, 2012
Key Performance Indicators that PerformancePoint can obtain from the SQL BI Data Wharehouse using a Dashboard http://lnkd.in/igQM4m
Thursday, October 25, 2012
TSQL - Partial Duplicate Indexes
Paul Nielsen has another T-SQL code which helps to find partial duplicate indexes that share leading key columns, e.g. Ix1(col1, col2, col3) and Ix2(col1, col2) which would be considered duplicate indexes. This query only examines key columns and does not consider included columns.
WITH indexcols AS
(
SELECT
object_id AS id
, index_id AS indid
, name
, (SELECT CASE keyno
WHEN 0 THEN NULL
ELSE colid END AS [data()]
FROM sys.sysindexkeys AS k
WHERE
k.id = i.object_id
AND k.indid = i.index_id
ORDER BY keyno, colid
FOR XML PATH('') ) AS cols
FROM sys.indexes AS i
)
SELECT
object_schema_name(c1.id) +
'.' + object_name(c1.id) AS 'Table Name'
, c1.name AS 'Index'
, c2.name AS 'Partial Duplicate'
FROM indexcols AS c1
JOIN indexcols AS c2
ON c1.id = c2.id
AND c1.indid < c2.indid
AND c1.cols <> c2.cols -- remove exact duplicates
AND ((c1.cols like c2.cols + '%'
AND SUBSTRING(c1.cols,LEN(c2.cols)+1,1) = ' ')
OR (c2.cols like c1.cols + '%'
AND SUBSTRING(c2.cols,LEN(c1.cols)+1,1) = ' '))
ORDER BY [Table Name]
Wednesday, October 24, 2012
TSQL - Exact Duplicate Indexes
Paul Nielsen provided T-SQL code which helps to find exact duplicate indexes. The indexes must have the same key columns in the same order, and the same included columns but in any order. These indexes are sure targets for elimination. The only caution would be to check for index hints.
with indexcols as
(
select
object_id as id
, index_id as indid
, name
, (select case keyno
when 0 then NULL
else colid end as [data()]
from sys.sysindexkeys as k
where
k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path('')) as cols
, (select case keyno
when 0 then colid
else NULL end as [data()]
from sys.sysindexkeys as k
where
k.id = i.object_id
and k.indid = i.index_id
order by colid
for xml path('')) as inc
from sys.indexes as i
)
select
object_schema_name(c1.id) +
'.' + object_name(c1.id) as 'Table Name'
, c1.name as 'index'
, c2.name as 'exactduplicate'
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and c1.cols = c2.cols
and c1.inc = c2.inc
ORDER BY [Table Name]
Subscribe to:
Posts (Atom)
