Mashed Articles

I have a client who has viewes that contain UNION ALL command for number of very large tables. The logic is to apply a WHERE condition to that view to get the data. Even having indexes on those tables SQL Server applies a WHERE condition to each SELECT statement within a view that may lead to performance proble. To demonstate it please considetr AdwentureWork data and two tables Sales.SalerDetails and SalesOrderHeader

CREATE VIEW v1
AS
SELECT S.SalesOrderID
FROM
Sales.SalesOrderDetail S
UNION ALL
SELECT S.SalesOrderID
,S.CreditCardID FROM
Sales.SalesOrderHeader S

SET STATISTICS IO ON
SELECT TOP 100 * FROM v1 WHERE SalesOrderDetailID >40000 AND SalesOrderDetailID<45000

Table ‘SalesOrderDetail’. Scan count 3, logical reads 1359, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘SalesOrderHeader’. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

You can see that SQL Server ‘touched’ those tables by using Clustered Index Scan on
Sales.SalerDetails and Clustered Index Seek on Sales.SalesOrderHeader. Indeed I realy simplified the logic as in reality it has horrible performance. How to improve?

I create a Multi-Statement Table-Valued UDF that accepts a parameter
CTREATE FUNCTION dbo.udf1
(
@SalesOrderID INT
)
RETURNS @t TABLE (c1 INT,c2 INT)
AS
BEGIN
INSERT INTO @t
SELECT S.SalesOrderID
FROM
Sales.SalesOrderDetail S
WHERE SalesOrderID > @SalesOrderID AND SalesOrderID<@SalesOrderID+5000
UNION ALL
SELECT S.SalesOrderID
FROM
Sales.SalesOrderHeader S
WHERE SalesOrderID > @SalesOrderID AND SalesOrderID<@SalesOrderID+5000
RETURN
END
Table ‘#74AE54BC’. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

See , I applied a WHERE condition within an UDF for every statement as UDF must get parameters.This is another argument to use Table-Valued UDF …

This question had been asked in NG yesterday.I think it is a good exercise so I take a script too get the column usage from both sides (parent/child) written by Aaron Bertrand and modified a little bit to return the table/column that does not have an index on.

WITH fk_no_indexes
AS
(
SELECT
[constraint_name] = f.[name],
[child_table] = OBJECT_NAME(f.parent_object_id),
[child_table_id]=f.parent_object_id,
[child_column] = cc.name,
[child_column_id]=cc.[column_id],
[parent_table] = OBJECT_NAME(f.referenced_object_id),
[parent_column] = pc.name
FROM
sys.foreign_keys f
INNER JOIN
(
SELECTc.[object_id],c.name,c.column_id,ic.index_id
FROM sys.columns c INNER JOIN sys.index_columns ic
ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
) AS pc
ON
f.key_index_id = pc.index_id
INNER JOIN sys.foreign_key_columns fkc
ON f.[object_id] = fkc.constraint_object_id
AND pc.[object_id] = fkc.referenced_object_id
AND fkc.referenced_column_id = pc.column_id
INNER JOIN sys.columns cc
ON fkc.parent_object_id = cc.[object_id]
AND fkc.parent_column_id = cc.column_id
)
SELECT [constraint_name],[child_table],[child_column],[parent_table] FROM
fk_no_indexes WHERE NOT EXISTS
(
SELECT * FROM sys.index_columns i
WHERE i.[object_id]=[child_table_id]
AND [child_column_id]=column_id
)
ORDER BY
constraint_name,
child_table;

Hi everybody.
It seems like I am seeing more and more inquires from our clients asking for help solving performance related issues with rebuilding indexes. All of them (or almost all of them) have been using Maintanace Plan Rebuild/Reorganize Index Task. We have lots of clients who have pretty big databases(>200GB) and have not hired yeat a DBA:-).They used to use this task and specify all tables as well as all databases, moreover, one client used to run such tasks in the middle of work day. It leads to locks on tables and performance decreasing. I would also notice you to not cancelling the task as SQL Server will rolback the whole transactions and you are about to wait a lot of time. Just let the task to complete. I suggested instead of running the task, first, identify fragmented indexes on tables that have more than 1000 pages.

DECLARE @RebuildStatement nvarchar(4000)
DECLARE RebuildStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT ‘ALTER INDEX ‘+i.name+ ‘ ON ‘+
OBJECT_NAME(i.object_id)+’ REORGANIZE;’
FROM
sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, ‘DETAILED’) phystat inner JOIN sys.indexes i
ON i.object_id = phystat.object_id
AND i.index_id = phystat.index_id WHERE phystat.avg_fragmentation_in_percent > 40
and page_count>=1000

OPEN RebuildStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM RebuildStatements INTO @RebuildStatement
IF @@FETCH_STATUS 0 BREAK
EXEC(@RebuildStatement)
END
CLOSE RebuildStatements
DEALLOCATE RebuildStatements

The above SELECT generates a simple script to REORGANIZE (change to REBUILD) indexes and EXECUTES the dynamic sql. As you probaly know this script has to be run on SQL Server 2005/2008 and do not forget about really great feature such rebuilding indexes ONLINE. For more details please see BOL.