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 : –
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
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!)
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
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 :-
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.total_elapsed_time / 1000000 as totalseconds
, qt.text as query
, o.name as objectname
, DB_NAME(qt.dbid) as databasename
sys.dm_exec_query_stats as qs
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