SQL DBA Cheatcode Part 5 : Playing with indexes

0

dating site for widows ireland Hello Readers,

follow site Today, I am trying to put down few but very useful index related scripts. I have used these scripts many times with different versions of SQL Server.

opzioni digitali 1. Missing Index : Missing indexes are one reason why an SQL query takes longer (much longer) to complete. Here’s how to find out about them and fix the problem.

watch SELECT DISTINCT CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage], migs.last_user_seek, mid.[statement] AS [Database.Schema.Table], mid.equality_columns, mid.inequality_columns, mid.included_columns, migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK) INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle ORDER BY index_advantage DESC OPTION (RECOMPILE);

http://a4lions.ca/?iyted=binario-treding&2b3=1f 2. Unused Index : Once SQL Server system has been in use for a while you might find there are a number of indexes that exist on the system that aren’t being used. Fortunately, SQL Server provides a Dynamic Management View (DMV) named sys.dm_db_index_usage_stats that tracks your index usage.

http://azortin.pl/?rtysa=opcje-binarne-reklama&a3f=c8 SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], type_desc, coalesce(last_user_seek,last_user_scan,last_user_lookup,last_system_scan,last_system_seek,last_system_lookup) as LastUsed, USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES , last_user_seek,last_user_scan,last_user_lookup,last_system_scan,last_system_seek,last_system_lookup, 'drop index ['+I.[NAME]+'] on ['+OBJECT_NAME(S.[OBJECT_ID])+'];' as DropStatement FROM SYS.DM_DB_INDEX_USAGE_STATS AS S INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 order by type_desc,coalesce(last_user_seek,last_user_scan,last_user_lookup,last_system_scan,last_system_seek,last_system_lookup) desc

rencontre fille 15 16 ans 3. Index Dependencies, size and Descriptions

watch SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED set nocount on declare @db varchar(2000) declare @cmd varchar(8000) declare @TabName varchar(100) create table #T (Dbname varchar(500), TabName varchar(200), IndexName varchar(200), IndexDescr varchar(200), IndexKeys varchar(200), IndexSize int) declare cur cursor for select ltrim(upper('['+name+']')) from master .. sysdatabases where status & 512 <> 512 and dbid > 4 and name ='admin' ----------------------------------------------->> opcje binarne edukacja set database name here order by name open cur fetch next from cur into @db while @@fetch_status = 0 begin select @cmd = 'Use'+space(1)+@db select @cmd = @cmd + ' DECLARE @TabName varchar(100) DECLARE TCursor CURSOR FAST_FORWARD LOCAL FOR SELECT ist.table_schema+''.''+ist.table_name FROM sys.sysobjects sys inner join INFORMATION_SCHEMA.TABLES ist on ist.table_name = sys.name WHERE sys.xtype = ''U'' OPEN TCursor FETCH NEXT FROM TCursor INTO @TabName WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #T (IndexName, IndexDescr, IndexKeys) EXEC sp_helpindex @TabName UPDATE #T SET TabName = @TabName WHERE TabName IS NULL UPDATE #T SET DbName = db_name() where dbname is null FETCH NEXT FROM TCursor INTO @TabName END CLOSE TCursor DEALLOCATE TCursor DECLARE @ValueCoef int SELECT @ValueCoef = low FROM Master.dbo.spt_values WHERE number = 1 AND type = N''E'' UPDATE #T SET IndexSize = ((CAST(sysindexes.used AS bigint) * @ValueCoef )/1024)/1024 from sys.sysobjects INNER JOIN sys.sysindexes ON sysobjects.id = sysindexes.id INNER JOIN #T T ON substring(T.tabname, patindex(''%.%'', T.tabname)+1, len (T.tabname)) collate SQL_Latin1_General_CP1_CI_AS = sysobjects.name AND T.IndexName collate SQL_Latin1_General_CP1_CI_AS = sysindexes.name ' exec(@cmd) fetch next from cur into @db end close cur deallocate cur SELECT @@servername, dbname, Tabname, indexname, indexdescr, indexkeys, convert(varchar(20), indexSize)+space(1)+'MB' as IndexSize FROM #T group by dbname, Tabname, indexname, indexdescr, indexkeys, indexSize ORDER BY 2, len(indexsize) desc, replace(convert(varchar(20), indexsize), 'MB','') desc DROP TABLE #T

http://karenwritesromance.com/?bioeier=opzioni-binarie-deposito-minimo-50&167=4c There are a few more important scripts posted earlier

source link 1. How to create Drop and Recreate Script of all Indexes in a database?

2. How to move Indexes (Cluster and Non Cluster) from Primary File group to New File group?

 

I hope this helps…

 

Thanks

Sachin Diwakar

Sharing creates magic!!

Facebook Twitter LinkedIn 

  1. […] SQL DBA Cheatcode Part 5 : Playing with indexes […]

  2. Series : SQL DBA Cheatcode - DBA THINGS June 4, 2015 at 7:32 pm

    […] Playing with indexes […]

%d bloggers like this: