Optimizing Indexes – Data Management Views


Introduction

As you have probably gathered from this blog, we have several personalisations on our Infor Syteline 8 system and it is a very big database. as such it is important to do everthing we can to keep it running as quickly as possible.

Here’s some useful code out of The Microsoft Training Kit 70-461 that I have been using to work out the state of my indexes and identify slow running queries :-

Getting the SQL server statistics

It’s only worth running the subsequent queries if your server has been up long enough so you have sufficient useage information. You can find out how long the server has been up, how many processors and how much ram it has by using this query : –

select

cpu_count as locgical_cpu_count

, cpu_count / hyperthread_ratio as physical_cpu_count

,CAST(physical_memory_in_bytes / 1024 / 1024 asint)as physical_memory_mb

, sqlserver_start_time

from sys.dm_os_sys_info

What indexes are required on the SQL server?

The following is a very useful bit of code, it lists missing indexes, user seeks, and average percentage benefit ……. it’s  sped up a couple of processes already  by adding our own indexes – (note we prefix our indexes “PBTI_IDX” so they stand out!)

select

mid.statement as [Database.Schema.Table]

,mic.column_id as column_id

, mic.column_name as columnName

, mic.column_usage as ColumnUseage

, migs.user_seeks as userseeks

, migs.user_scans as userscans

, migs.last_user_seek as LastUserSeek

, MIGS.avg_total_user_cost as AvgQueryCostReduction

, migs.avg_user_impact as AvgPctBenefit

from sys.dm_db_missing_index_details as mid

cross apply sys.dm_db_missing_index_columns(mid.index_handle)as mic

inner join sys.dm_db_missing_index_groups as mig on mig.index_handle = mid.index_handle

inner join sys.dm_db_missing_index_group_stats as migs on mig.index_group_handle = migs.group_handle

where

mid.statementnotlike‘%wb_tmp%’

and mid.statementnotlike‘%devl_app%’

orderby

user_seeks desc;

 

What indexes are not required  on the sql server?

Because SQL server has to maintain indexes even if they are not being read, there is an overhead to them, so if you have added indexes that are not in use, you may wish to delete them – I am sure this query can be enhanced but this was enough for me at the moment :-

select

i.name

, us.*

fromsys.dm_db_index_usage_statsas us

leftjoinsys.indexesas i on i.object_id= us.object_id

where i.name like‘pbti%’

orderby us.user_seeks desc

What are my slowest running t-sql queries?

Not strictly index related – as it could be an issue with the way the query has been written that needs optimisation, but this little script is really useful – it can take a while to return the results though so be warned!

select top 10

qs.total_elapsed_time / qs.execution_count / 1000000 as AverageSeconds

, qs.execution_count

, qs.total_elapsed_time / 1000000 as totalseconds

, qt.text as query

, o.name as objectname

, DB_NAME(qt.dbid) as databasename

from

sys.dm_exec_query_stats as qs

cross apply

sys.dm_exec_sql_text(qs.sql_handle) as qt

left outer join

sys.objects as o on qt.objectid = o.object_id

where DB_NAME(qt.dbid) = ‘live_app’

order by averageseconds desc

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.