Archive

Archive for May, 2011

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

Interesting view on non-relational database management systems alternatives

May 20, 2011 Leave a comment

Is the Relational Database Doomed? by Tony Bain

As a matter of fact, up untill probably couple of weeks ago I N-E-V-E-R heard anything about non-relational database management systems. Not that it’s automatically supposed to mean they never existed, – obviously not, but  definitely implies there are far, far not that popular as RDBMS. 

This notion of non-relational DBMS alternatives a bit kicked me to the head and made me decide to write this short critical post.

At first, I must admit, the author does make his point, but to a very small degree. There are quite a number of serious flows in his argument, making it totaly weak.

It starts with the most minor one right at the very beginning    😉 But that’s nothing really, of course. “Fixing” it just for fun:

–Civic is Honda
UPDATE MakeModel
SET MakeKey = 1
WHERE MakeKey = (SELECT MakeKey FROM Make WHERE Make = ‘Nissan’)
AND Model = ‘Bluebird’;

Then the author mentions that 

“new” type of database has been around for a long time and has been used for specialized applications for which the generic relational database was ill-suited

without giving a single example… Not a cool thing to do, especially attempting to question such a serious matter as  prevalence of relational DBMS over any other database systems and, what’s more to it, putting the entire future of RDBMS to a subject. One would expect some really kind of a bullet-proof, very obvious, no-flaws argument in this.

Then comes something weird. Well, I’ve modified it a bit – the way it supposed to be.

The data model is based on business requirements. Application functionality is supposed to reflect the very same business requirements and follow the data model, if this is relevant to mention at all. This entire thing doesn’t make any sense. The next one, about data model being normalized is true only for some systems, called “Operational” or OLTP in the older notation but not Data Warehouse ones. Data Warehouse systems has data de-normalization as its very base principle. And further on in his article, the author keeps building his argument like Data Warehouses never existed in relational database world – WTF?!?!

 The rest of his argument goes nowhere really. Such features as parallel execution, partitioning and data compression, which make up great performance improvements in relational databases, have never been mentioned in the article. At the bottom line, his point is this: “if you want to go with WebServices, choose key/value non-relational DB”. Well, cool, dude. Do it if you dare, – it’s “your” money at the end of a day. Well, not really yours, but the corporate ones. It’s all the question of a what you’re willing to risk.

All in all, the time and the market will show whether those “key/value” barely known around database systems have their potential or not. Meanwhile, relational databases will be there as the one and the only main player for a lifetime of computers operating on the basis of zeroes and ones.

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

How Corporations Get Out of Paying Taxes

May 13, 2011 Leave a comment

Not anyhow database-related; simply couldn’t resist re-posting.

Sad, but so true…

Taken from hereHow Corporations Get Out of Paying Taxes
Via: OnlineMBA.com

Categories: Uncategorized

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

SQL Server 2005 Backup Compression

May 12, 2011 Leave a comment

Well… not “out of the box” as in SQL Server 2008. But that could be done with a script.

The problem though is that comparable to “native” SS2008 compression, compressing database and transaction log files in SS2005 would take times more CPU, I/O and storage and, as a result, would take much more time to run. This is because SS2008 performs compression before writting to disk, whereas here, in SS2005, all files will be written to disk first, then compressed.

Nevertheless, there might be certain cituations where using archiving of database and transaction log backups might come handy.

Any archivator, which supports archiving of large-enough files could be used for this. I use 7-zip for that purpose – it’s free, fast and . Here are two other free archivators: IZArc, PeaZip

Here, you need to enable xp_cmdshell which runs OS commands from withing SS Scheduled Job, not forgetting to disable it after the execution.

Bold marks the custom directories and file names.

EXEC sp_configure ‘show advanced options’, 1
RECONFIGURE
EXEC sp_configure ‘xp_cmdshell’, 1
RECONFIGURE

DECLARE @cmd VARCHAR(1000)
DECLARE @db_bkp_files_dir varchar(100)
DECLARE @archive_destination_dir varchar(100)
DECLARE @archive_name varchar(100)
DECLARE @7z_path varchar(100)
set @archive_destination_dir = ‘D:\Backup_test’ –destination dir
set @7z_path = ‘”C:\Program Files\7-Zip
set @db_bkp_files_dir = ‘D:\Backup_test’ –db backup files origin 
set @archive_name = ‘DBFullBackup_‘ + convert(varchar, getdate(), 112) + substring(replace(convert(varchar, getdate(), 108), ‘:’, ”), 1, 4)
SET @cmd = @7z_path + ‘\7z” a -t7z -mx5 -ms=off ‘ + @archive_destination_dir + ‘\’ + @archive_name + ‘.7z ‘ +  @db_bkp_files_dir + ‘\*’ 
–print @cmd
EXEC xp_cmdshell @cmd

EXEC sp_configure ‘show advanced options’, 0
RECONFIGURE