Use DMVs to determine based on past executions which indexes would be beneficial in that database.

The following SQL should be run in Production SQL server (off hours). It basically uses DMVs to determine based on past executions which indexes would be beneficial in that database. It recommends what indexes could be created on the specified tables.

SELECT
 migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure
 ,
 'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
 + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
 + ' ON ' + mid.statement
 + ' (' + ISNULL (mid.equality_columns,'')
 + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
 + ISNULL (mid.inequality_columns, '')
 + ')'
 + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
--, migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

 

Our new TECH STACK

We have been undergoing a major upgrade to our tech stack and here is what we are implementing now…  WOOT WOOT

 

stack1

žCloud Formation – A service that allows the entire stack to be built, provisioned, provisioned, and torn down demand.

žELB – A load balancer that works with autoscaling to create and destroy servers as needed by the traffic load.

žEC2 – Cloud-based virtual machines.

žEFS – A network file system that allows multiple EC2 instances to share a file system, even across availability zones.

žS3 – A Simple Storage Service designed to hold static resources, such as images and documents.

žRDS – Relational Database Services that cross availability zones, provide high availability, provide fail-over, and provide backup.

My Neighbor Totoro parody.

totoTran

Click here to help out

Prices vary by vendor based on their pricing policies.

FYI: Sonya and I ordered shirts from TeeChip to see how their delivery and print services worked and we were very pleased with the results.

My Neighbor Totoro parody.

My Neighbor Phoenix My Neighbor Totoro T-Shirt

Eizen and Edna witness a strange sight while waiting for the bus
Art by Madeleine Williams.