Archive

Archive for the ‘Tuning and Optimization’ Category

Changing your default settings for any new database is just compulsory

June 2, 2011 2 comments

What a statement, eh? All over a sudden and out of the blue. Never bothered to alter any default settings for a to-be-created database myself and haven’t heard others doing that on a large scale. However, after hitting this article by Glenn Berry, (which references couple of other related posts One | Two by Michelle Ufford and Kimberly Tripp) I realized this is serious. And I’m very serial about this! (“South Park” is sometimes retarded and disgusting, especially that first episode of 15th season, but in 9 out of 10 – it’s brilliant!)

In short, to summarize what the guys already said: excessive number of transaction log file extents, – Virtual Log Files, will lead to lengthy restore/recovery times. As simple as that. Besides, you want to save on execution time and server workload when having multiple Transaction Log File extents following one another during “heavy” DML ops. In addition to that, having multiple small VLFs in Transaction Log File will make it unnecessary big in size, which eventually will require its truncate and shrink. Too much hassle for such a silly reason as not-really-suitable default settings, which are very easy to change.

Hence, right after the new installation of SQL Server, go and change the model db datafile and transaction log file settings from default to… whatever you feel like after reading those two articles, but the main point is – CHANGE IT!

GUI-way:

Script-way:

USE [master];
ALTER DATABASE [MyNewDB] MODIFY FILE ( NAME = N'MyNewDB_Log', FILEGROWTH = 65536KB );

Advertisements

Finding Missing Indexes

May 25, 2011 Leave a comment

Another words, those indexes SQL Server beleives it would have benefitted from having

Original Script was written by Brent Ozar, Quest Software

The scipt uses another series of DMVs, introduced in SQL Server 2005:

– sys.dm_db_missing_index_group_stat

– sys.dm_db_missing_index_groups

– sys.dm_db_missing_index_details

I’ve modified it a bit to produce self-descriptive index names, instead of static ones and ON <filegroup> clause at the very end to account for index-dedicated filegroups. (the way it should be, right? 😉  )

DECLARE @v_index_filegroup VARCHAR(30);
--set the dedicated filegroup for indexes to be built at
SET @v_index_filegroup = '[INDEX]'
SELECT  sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS [IMPACT]
,  'CREATE NONCLUSTERED INDEX IX_'
+ sys.objects.name COLLATE DATABASE_DEFAULT + '_'
+ REPLACE(SUBSTRING(REPLACE(REPLACE(REPLACE(mid.included_columns, '[', ''),']', '_'), ',', ''),
1, LEN(REPLACE(REPLACE(REPLACE(mid.included_columns, '[', ''),']', '_'), ',', ''))-1), ' ', '')
+
' ON ' + sys.objects.name + ' ( '
+ ISNULL(mid.equality_columns, '')
+
CASE
WHEN mid.inequality_columns IS NULL
THEN ''
ELSE
CASE
WHEN mid.equality_columns IS NULL
THEN ''
ELSE ','
END
+ mid.inequality_columns
END
+ ' ) '
+ CASE
WHEN mid.included_columns IS NULL
THEN ''
ELSE 'INCLUDE (' + mid.included_columns + ')'
END + 'ON ' + @v_index_filegroup + ';' AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID()
INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE migs.group_handle IN
(SELECT TOP (500) group_handle
FROM sys.dm_db_missing_index_group_stats)
AND
OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1
--"heavily needed" indexes
--and (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) > 10000000
ORDER BY [IMPACT] DESC , 3 DESC

Index Rebuild

May 16, 2011 Leave a comment

 (Following Index Reorganize post)

————————————————————

REBUILD CLUSTERED INDEXES

DECLARE @v_fbm_low smallint --fragmentation benchmark
DECLARE @v_fbm_high smallint
DECLARE @v_fillfactor smallint
SET @v_fbm_low = 30
SET @v_fbm_high = 100
SET @v_fillfactor = 80
SELECT 'ALTER INDEX '+[INDEX_NAME]+' ON ' + [SCHEMA_NAME] + '.' + [OBJECT_NAME] +
' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR,@v_fillfactor) + ', STATISTICS_NORECOMPUTE = ON, ONLINE = ON);'
FROM
(SELECT
SCHEMA_NAME(o.schema_id) AS [SCHEMA_NAME],
OBJECT_NAME(a.OBJECT_ID) AS [OBJECT_NAME],
i.name AS [INDEX_NAME],
i.type_desc AS [INDEX_TYPE],
a.index_type_desc AS [INDEX_TYPE_DESC],
a.alloc_unit_type_desc AS [ALLOC_UNIT_TYPE_DESC],
a.index_depth AS [INDEX_DEPTH],
a.avg_fragmentation_in_percent AS [AVG_FRAGMENTATION_IN_PERCENT],
a.fragment_count AS [FRAGMENT_COUNT],
a.avg_fragment_size_in_pages AS [AVG_FRAGMENT_SIZE_IN_PAGES],
a.page_count AS [PAGE_COUNT],
i.fill_factor AS [FILL_FACTOR]
FROM sys.dm_db_index_physical_stats (NULL,NULL,NULL,NULL, NULL) a
INNER JOIN sys.indexes i ON i.[OBJECT_ID] = a.[OBJECT_ID] AND i.INDEX_ID = a.INDEX_ID
INNER JOIN sys.objects o ON o.OBJECT_ID = i.OBJECT_ID
WHERE  OBJECTPROPERTY(a.[OBJECT_ID],'IsUserTable') = 1
AND a.avg_fragmentation_in_percent BETWEEN @v_fbm_low AND @v_fbm_high
AND page_count > 8 --ignore tables with less then one extent
) a
WHERE [INDEX_TYPE] = 'CLUSTERED'
ORDER BY [SCHEMA_NAME], [OBJECT_NAME]

REBUILD NON-CLUSTERED INDEXES

DECLARE @v_fbm_low smallint --fragmentation benchmark
DECLARE @v_fbm_high smallint
DECLARE @v_fillfactor smallint
SET @v_fbm_low = 30
SET @v_fbm_high = 100
SET @v_fillfactor = 80
SELECT 'ALTER INDEX '+[INDEX_NAME]+' ON ' + [SCHEMA_NAME] + '.' + [OBJECT_NAME]+
' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR, @v_fillfactor) + ', STATISTICS_NORECOMPUTE = ON, ONLINE = ON);'
FROM
(SELECT
SCHEMA_NAME(o.schema_id) AS [SCHEMA_NAME],
OBJECT_NAME(a.OBJECT_ID) AS [OBJECT_NAME],
i.name AS [INDEX_NAME],
i.type_desc AS [INDEX_TYPE],
a.index_type_desc AS [INDEX_TYPE_DESC],
a.alloc_unit_type_desc AS [ALLOC_UNIT_TYPE_DESC],
a.index_depth AS [INDEX_DEPTH],
a.avg_fragmentation_in_percent AS [AVG_FRAGMENTATION_IN_PERCENT],
a.fragment_count AS [FRAGMENT_COUNT],
a.avg_fragment_size_in_pages AS [AVG_FRAGMENT_SIZE_IN_PAGES],
a.page_count AS [PAGE_COUNT],
i.fill_factor AS [FILL_FACTOR]
FROM sys.dm_db_index_physical_stats (NULL,NULL,NULL,NULL, NULL) a
INNER JOIN sys.indexes i ON i.[OBJECT_ID] = a.[OBJECT_ID] AND i.INDEX_ID = a.INDEX_ID
INNER JOIN sys.objects o ON o.OBJECT_ID = i.OBJECT_ID
WHERE  OBJECTPROPERTY(a.[OBJECT_ID],'IsUserTable') = 1
AND a.avg_fragmentation_in_percent BETWEEN @v_fbm_low AND @v_fbm_high
AND page_count > 8 --ignore tables with less then one extent
) a
WHERE [INDEX_TYPE] = 'NONCLUSTERED';

FILLFACTOR is a tricky part. I think for any Oracle DBA it would be very tempting to think it’s a direct analogy with PCTUSED, but not quite. Long story short, in SQL Server it’s relevant only for creating or rebuilding indexes, not tables. Table pages are splitted in half whenever full, regardless of FILLFACTOR setting. Indexes behave differently. Generally, non-cluster indexes built on highly volatile tables would benefit from saving on leaf-level page splits when created or rebuilt with FILLFACTOR < 100, say b/w 70 and 90.

Clustered indexes built on IDENTITY columns should have FILLFACTOR 100 as the value of identity column doesn’t get updated, but rather is always inserted to the end of last page.

This two articles of Pinal Dave give some detailed insight on FILLFACTOR and its role in index creation/rebuild.

What is Fill factor? – Index, Fill Factor and Performance – Part 1

What is the best value for the Fill Factor? – Index, Fill Factor and Performance – Part 2

Useful TechNet articles:

Reorganizing and Rebuilding Indexes
Fill Factor
Setting Index Options
Configuring Parallel Index Operations
 if you hit any errors while running ONLINE index rebuild, it’s worthwhile to check this article:
Guidelines for Performing Online Index Operations

Index Reorganize (Defragmentation)

May 13, 2011 Leave a comment

Here are two explicit articles, nothing really to add to it.

  1. Rebuilding Clustered Indexes Efficiently with the DROP_EXISTING command in SQL Server, By: Armando Prato
  2. TechNet article

This is sophisticated Index Defrag and Rebuild SP, by Michelle Ufford, which does the job automatically; you just need to plug the values to the variables when calling it.

This is short “copy-paste-and-execute-in-SSMS” script on mine:

--===================================================--
DECLARE @v_fbm_low smallint --fragmentation benchmark
DECLARE @v_fbm_high smallint
SET @v_fbm_low = 5
SET @v_fbm_high = 30
SELECT
SCHEMA_NAME(o.schema_id) AS [SCHEMA_NAME],
OBJECT_NAME(a.OBJECT_ID) AS [OBJECT_NAME],
i.name AS [INDEX_NAME],
i.type_desc AS [INDEX_TYPE],
a.index_type_desc AS [INDEX_TYPE_DESC],
a.alloc_unit_type_desc AS [ALLOC_UNIT_TYPE_DESC],
a.index_depth AS [INDEX_DEPTH],
a.avg_fragmentation_in_percent AS [AVG_FRAGMENTATION_IN_PERCENT],
a.fragment_count AS [FRAGMENT_COUNT],
a.avg_fragment_size_in_pages AS [AVG_FRAGMENT_SIZE_IN_PAGES],
a.page_count AS [PAGE_COUNT]
FROM sys.dm_db_index_physical_stats (NULL,NULL,NULL,NULL, NULL) A
INNER JOIN SYS.INDEXES I ON I.[OBJECT_ID] = A.[OBJECT_ID] AND I.INDEX_ID = A.INDEX_ID
INNER JOIN SYS.OBJECTS O ON O.OBJECT_ID = I.OBJECT_ID
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1
AND a.avg_fragmentation_in_percent BETWEEN @v_fbm_low AND @v_fbm_high
ORDER BY [INDEX_NAME] --[OBJECT_NAME]

 –===================================================–

DECLARE @v_fbm_low smallint --fragmentation benchmark
DECLARE @v_fbm_high smallint
SET @v_fbm_low = 5
SET @v_fbm_high = 30
SELECT 'ALTER INDEX '+[INDEX_NAME]+ ' ON ' + [SCHEMA_NAME] + '.' + [OBJECT_NAME]+ ' REORGANIZE;'
FROM
(SELECT
SCHEMA_NAME(o.schema_id) AS [SCHEMA_NAME],
OBJECT_NAME(a.OBJECT_ID) AS [OBJECT_NAME],
i.name AS [INDEX_NAME],
i.type_desc AS [INDEX_TYPE],
a.index_type_desc AS [INDEX_TYPE_DESC],
a.alloc_unit_type_desc AS [ALLOC_UNIT_TYPE_DESC],
a.index_depth AS [INDEX_DEPTH],
a.avg_fragmentation_in_percent AS [AVG_FRAGMENTATION_IN_PERCENT],
a.fragment_count AS [FRAGMENT_COUNT],
a.avg_fragment_size_in_pages AS [AVG_FRAGMENT_SIZE_IN_PAGES],
a.page_count AS [PAGE_COUNT]
FROM sys.dm_db_index_physical_stats (NULL,NULL,NULL,NULL, NULL) a
INNER JOIN sys.indexes i ON i.[OBJECT_ID] = a.[OBJECT_ID] AND i.INDEX_ID = a.INDEX_ID
INNER JOIN sys.objects o ON o.OBJECT_ID = i.OBJECT_ID
WHERE  OBJECTPROPERTY(a.[OBJECT_ID],'IsUserTable') = 1
AND a.avg_fragmentation_in_percent BETWEEN @v_fbm_low AND @v_fbm_high
AND page_count > 8 --ignore tables with less then one extent
) a

View Index Usage Statistics

May 13, 2011 Leave a comment

Time to time the indexes have to be watched for. While there is quite an argument b/w many DBAs (well, Oracle for sure, but I truly believe it goes for SQL Server as well as others RDBMS too – Data Structures are more or less the same for all relational databases) whether index fragmentation notion is true as such, or not…

As to me, I don’t have (and never had, actually) any doubts about indexes going out of sync, becoming unbalanced, browning (call it whatever you want) with high number of DMLs. DELETEs result in index leafs being cleared out, UPDATEs with values greater than the old ones, generally might lead page movement inside the index, which is both expensive operation and, once again, leafs fragmented blocks “inside” the index leaf level. Hence, indexes do need to be either defragmented or rebuilt. It just depends of their fragmentation level. But here, for example, is quite a “clash of titans” about this argument. There are some high-pro DBAs to believe indexes kind of don’t need to be rebuilt or that index rebuild costs highly outwaight any performance benefits. At the end of the day – your choice. As to me, I’m on “rebuild side” – no question.

SQL Server 2005 introduced a number of Dynamic Views (in SS they are called Dynamic Management Objects) to help figuring the index stats out.

They are:

sys.dm_db_index_operational_stats

sys.dm_db_index_usage_stats

sys.dm_db_index_physical_stats

Joining each of them with sys.indexes and either sys.objects or sys.tables will give all the necessary info.

I took this code from two articles by Greg Robidoux and Greg Larsen as the starting point and created an SP which can be called with different parameters to sort the results by different columns.

--=============================================================--

IF  EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(‘dbo.sp_view_index_usage_stats’) AND TYPE IN (‘P’, ‘PC’))
DROP PROCEDURE [dbo].[sp_view_index_usage_stats]
GO

CREATE PROCEDURE sp_view_index_usage_stats (@p_sort_column smallint, @p_sort_option VARCHAR(5))
AS
SET NOCOUNT ON

DECLARE @v_sql VARCHAR(MAX);
BEGIN

SET @p_sort_option = UPPER(@p_sort_option);

SET @v_sql =
SELECT
SCHEMA_NAME(o.schema_id) as SCHEMA_NAME,
OBJECT_NAME(a.object_id) AS OBJECT_NAME,
–access
i.name AS INDEX_NAME,
i.type_desc as INDEX_TYPE,
A.LEAF_INSERT_COUNT,
A.LEAF_UPDATE_COUNT,
A.LEAF_DELETE_COUNT,
A.LEAF_GHOST_COUNT,
A.RANGE_SCAN_COUNT,
A.SINGLETON_LOOKUP_COUNT,
–latching and locking
A.PAGE_LATCH_WAIT_COUNT,
A.PAGE_IO_LATCH_WAIT_IN_MS,
A.ROW_LOCK_COUNT,
A.PAGE_LOCK_COUNT,
–physical I/O
A.PAGE_IO_LATCH_WAIT_COUNT,
A.PAGE_IO_LATCH_WAIT_IN_MS
FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = A.[OBJECT_ID] AND I.INDEX_ID = A.INDEX_ID
INNER JOIN SYS.OBJECTS O ON O.OBJECT_ID = I.OBJECT_ID
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],”IsUserTable”) = 1
ORDER BY’
;

SET @v_sql = CASE @p_sort_column
–access
WHEN 1 THEN @v_sql + ‘ A.LEAF_INSERT_COUNT’
WHEN 2 THEN @v_sql + ‘ A.LEAF_UPDATE_COUNT’
WHEN 3 THEN @v_sql + ‘ A.LEAF_DELETE_COUNT’
WHEN 4 THEN @v_sql + ‘ a.leaf_ghost_count’
WHEN 5 THEN @v_sql + ‘ a.range_scan_count’
WHEN 6 THEN @v_sql + ‘ a.singleton_lookup_count’
–latching and locking
WHEN 7 THEN @v_sql + ‘ a.page_latch_wait_count’
WHEN 8 THEN @v_sql + ‘ a.page_io_latch_wait_in_ms’
WHEN 9 THEN @v_sql + ‘ a.row_lock_count’
WHEN 10 THEN @v_sql + ‘ a.page_lock_count’
–physical I/O
WHEN 11 THEN @v_sql + ‘ a.page_io_latch_wait_count’
WHEN 12 THEN @v_sql + ‘ a.page_io_latch_wait_in_ms’ END;

SET @v_sql = CASE @p_sort_option
WHEN ‘DESC’ THEN @v_sql + ‘ DESC’
WHEN ‘ASC’ THEN @v_sql + ‘ ASC’ END;

EXECUTE(@v_sql)

END;

–=============================================================–

EXEC sp_view_index_usage_stats 1, ‘desc’ –leaf_insert_count
EXEC sp_view_index_usage_stats 2, ‘desc’ –leaf_update_count
EXEC sp_view_index_usage_stats 3, ‘desc’ –leaf_delete_count
EXEC sp_view_index_usage_stats 4, ‘desc’ –leaf_ghost_count
EXEC sp_view_index_usage_stats 5, ‘desc’ –range_scan_count
EXEC sp_view_index_usage_stats 6, ‘desc’ –singleton_lookup_count
EXEC sp_view_index_usage_stats 7, ‘desc’ –page_latch_wait_count
EXEC sp_view_index_usage_stats 8, ‘desc’ –page_io_latch_wait_in_ms
EXEC sp_view_index_usage_stats 9, ‘desc’ –row_lock_count
EXEC sp_view_index_usage_stats 10, ‘desc’ –page_lock_count
EXEC sp_view_index_usage_stats 11, ‘desc’ –page_io_latch_wait_count
EXEC sp_view_index_usage_stats 12, ‘desc’ –page_io_latch_wait_in_ms