Proactive performance tuning

So I was bored the other day... at work... yeah... not so much bored, but rather tired of doing the same tasks and so I decided to do something new. I put myself in learning mode and decided to figure out what I could do to help the performance on my Reporting Cluster.

See, it is a subscriber in a transactional replication scheme, and when it is under heavy load it sometimes lags by 30 minutes in committing transactions. 30 minutes is a bit too much for this database (yes I know, it is reporting, near real time should do)

So I dug around and instead of trying to fix one report I kept failing in narrowing down the right one, I went for the 'big hammer' approach.

I found some ways of using some system management views to show me what indexes aren't being used. I did this in hopes of deleting some indexes and saving some IO and making the transactions commit faster.

In messing with that, I found another dynamic management view (complete with joins) that shows system recommended indexes... as well as some stats behind them... how many times they would have been used, time saved, along with other things to help rank.

NOW remember that indexing is as much art as science, and that simply indexing everything is not such a good idea... and to make the indexes as wide as they need to be, and try to avoid single purpose indexes.... unless they have HUGE impact.

So on one table I got rid of 5 unused indexes (legacy) and added 3. Net loss of 2.... and on the next day during peak time I had to ask the other developers if they tweaked anything, or deactivated reports... because we never had any spikes in IO or CPU usage (was normal) and we never had a single replication alert... which means we never dipped above 4 min lag time in transactions committed.

Awesome.

I proceeded to tweak some more the next day... heck I spent 8 full hours tweaking.. I tweaked Reporting, I tweaked OLTP, I tweaked Dev.... I played and tweaked and tweaked and played...

The upside is that we are MUCH faster... the down side is that our database has grown a bit... but thats ok. I have more indexes to delete when I need the space and performance, and I have yet to compress some key tables. That will help our IO even more, gain some more performance...

Lesson, in going off the beaten path I was able to make a huge impact, get some behind the sceens hero mojo working, make things faster and better.

Not that anybody noticed. I even got a ticket to look into some of the symptoms I had fixed 4 days earlier!

As for the code. I've got some other queries but here are the basics.. and yes I know I could format them better... but hey, this is what I have now

--Find Missing Index
SELECT
--statement AS [database.scheme.table],
--column_id , column_name, column_usage,
sb.name,id.statement,id.equality_columns,id.inequality_columns,id.included_columns,
migs.user_seeks * avg_total_user_cost as AdditiveCost,
migs.user_seeks, migs.user_scans,
migs.last_user_seek, migs.avg_total_user_cost,
migs.avg_user_impact--,
--,*
--,mid.*
--FROM sys.dm_db_missing_index_details AS mid
--CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
--INNER JOIN
from 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
inner join sys.dm_db_missing_index_details id
on id.index_handle = mig.index_handle
inner join sys.sysdatabases sb
on id.database_id = sb.dbid
where sb.name <> 'msdb' --and user_seeks >1000
--and last_user_seek > '2010-09-16 08:00:17.820'
and avg_total_user_cost > 1
and user_seeks > 10
order by 6 desc
go
--Conversly Unused index
DECLARE @TABLENAME SYSNAME
SET @TABLENAME= 'dbo.card'

SELECT DB_NAME(DATABASE_ID) AS [DATABASE NAME]
, OBJECT_NAME(SS.OBJECT_ID) AS [OBJECT NAME]
, I.NAME AS [INDEX NAME]
, I.INDEX_ID AS [INDEX ID]
, USER_SEEKS AS [NUMBER OF SEEKS]
, USER_SCANS AS [NUMBER OF SCANS]
, USER_LOOKUPS AS [NUMBER OF BOOKMARK LOOKUPS]
, USER_UPDATES AS [NUMBER OF UPDATES]
FROM
SYS.DM_DB_INDEX_USAGE_STATS SS
INNER JOIN SYS.INDEXES I
ON I.OBJECT_ID = SS.OBJECT_ID
AND I.INDEX_ID = SS.INDEX_ID
WHERE DATABASE_ID = DB_ID()
AND OBJECTPROPERTY (SS.OBJECT_ID,'IsUserTable') = 1
AND SS.OBJECT_ID = OBJECT_ID(@TABLENAME)
ORDER BY [OBJECT NAME]
,USER_SEEKS desc
, USER_SCANS desc
, USER_LOOKUPS desc
, i.index_id
GO