Archive

Archive for December, 2011

Find Index Fragmentation in SQL Server

December 20, 2011 Leave a comment

Below is a script using the DMV’s to find fragmented tables in a SQL Server database instead of the old way using DBCC SHOWCONTIG.  Most DBA’s want to start at using these DMV’s and this script can be customized to your environment

SELECT
    DB_NAME(database_id) as DatabaseName,
    OBJECT_SCHEMA_NAME(ps.object_id) + '.' + OBJECT_NAME(ps.object_id) as TableName,
    i.name as IndexName,
    ps.page_count as IndexPageCount,
    ps.avg_fragmentation_in_percent as IndexFragmentation,
    ps.avg_fragment_size_in_pages as IndexFragmentationPages,
    ps.partition_number as PartitionNumber,
    ps.index_type_desc as IndexDesc,
    ps.alloc_unit_type_desc as AllocUnitDesc,
    ps.index_depth as IndexDepth,
    ps.index_level as IndexLevel,
    i.[fill_factor] as [FillFactor],
    CASE i.index_id
        WHEN 1 THEN 1
        ELSE 0
        END as IsClustered
FROM 
sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') ps
INNER JOIN sys.indexes i
    ON ps.object_id = i.object_id 
    AND ps.index_id = i.index_id
WHERE ps.index_type_desc <> 'HEAP'          --Ignore HEAP tables
AND ps.page_count > 30                      --Ingore less than 30 pages
AND ps.avg_fragmentation_in_percent > 30    --Fragmentation Threshold
ORDER BY TableName, IsClustered DESC, IndexName
Categories: indexes

TEMPDB Temp tables vs Permanent tables

December 8, 2011 Leave a comment

I was testing some code on a client site this past week where we were populating temp tables and when I unit tested it, it ran in 15 minutes. Then we decided to make them permanent tables within TEMPDB and the process ran for over an hour. I posted some questions to the SQL Server Community and was able to get a resolution thanks to Paul White (http://sqlblog.com/blogs/paul_white) and Jorge Segarra (www.sqlchicken.com)and below was the original script

--15 minutes
CREATE TABLE #TWPA
(
	AccountEntity  VARCHAR(3) NOT NULL,
	AccountProductCD  VARCHAR(3) NOT NULL,
	AccountNum  VARCHAR(21) NOT NULL,
	PartyID VARCHAR(11) NOT NULL,
	CONSTRAINT PK_#TWPA PRIMARY KEY CLUSTERED
	(
		AccountEntity,
		AccountProductCD,
		AccountNum,
		PartyID
	)
)
GO
INSERT INTO #TWPA

	SELECT AccountEntity, AccountProductCD, AccountNum, PartyID 
	FROM Table1		--9,109,553 rows
	UNION     
	SELECT AccountEntity, AccountProductCD, AccountNum, PartyID 
	FROM Table2		--137,696,784 rows

GO


--Over an hour
CREATE TABLE tempdb.dbo.TWPA
(
	AccountEntity  VARCHAR(3) NOT NULL,
	AccountProductCD  VARCHAR(3) NOT NULL,
	AccountNum  VARCHAR(21) NOT NULL,
	PartyID VARCHAR(11) NOT NULL,
	CONSTRAINT PK_#TWPA PRIMARY KEY CLUSTERED
	(
		AccountEntity,
		AccountProductCD,
		AccountNum,
		PartyID
	)
)
GO
INSERT INTO tempdb.dbo.TWPA

	SELECT AccountEntity, AccountProductCD, AccountNum, PartyID 
	FROM Table1		--9,109,553 rows
	UNION     
	SELECT AccountEntity, AccountProductCD, AccountNum, PartyID 
	FROM Table2		--137,696,784 rows


GO
</code>

The resolution is the permanent table in TEMPDB was not using minimal logging like the temp table did.  To resolve this I had to put WITH (TABLOCKX) into the insert statement and it ran in 15 minutes just like the temp table version

<code>
INSERT INTO tempdb.dbo.TWPA WITH (TABLOCKX)

	SELECT AccountEntity, AccountProductCD, AccountNum, PartyID 
	FROM Table1		--9,109,553 rows
	UNION     
	SELECT AccountEntity, AccountProductCD, AccountNum, PartyID 
	FROM Table2		--137,696,784 rows


GO
Categories: Uncategorized
Design a site like this with WordPress.com
Get started