SQL Saturday 151 in Orlando

I'm speaking this Saturday, September 29, 2012, please be sure to come over & say "Hi" in our booth... register at www.sqlsaturday.com

Speaking at PASS DBA Virtual chapter on Wednesday!

                Registration: https://www.livemeeting.com/lrs/8000181573/Registration.aspx?pageName=mqpxrl7f7x8cxgvm

                Meeting: https://www.livemeeting.com/cc/8000181573/join?id=FBT42K&role=attend

 

Registration is not required to attend the meeting but if people want to be included in the drawing for a $50 Amazon Gift Card, they must register no later than 5:00 PM Eastern on Tuesday, September 25^th.

Dynamically create missing indexes frm DMVs

Here's the code. Note: DO NOT simply runn this and apply all the indexes; look for redundancy first, there's likely to be a lot. A couple of notes:

1. Make sure to change the database name in 2 places
2. Decide how many of these you want. I (somewhat arbitrarily) set an 80% impact threshold on what I'm bringing back; you may want to adjust this.

use YourDatabaseName

 

select

      'create index IX_' +

            replace(replace(replace (equality_columns, '[', ''),']',''),', ','_') +

            ' on ' +

            sch.name + '.' + obj.name +

            ' (' +

            equality_columns +

            case when inequality_columns is null

                                    then ''

                                    else ',' +  inequality_columns end +

            ')' +

            case when included_columns is not null then

                  ' include (' +

                  isnull(included_columns,'') +

                  ') ' else '' end +

            ' -- ' + convert (varchar, avg_user_impact) + '% anticipated impact'

      from sys.dm_db_missing_index_details mid join

                  sys.dm_db_missing_index_groups mig

                        on mid.index_handle = mig.index_handle          join

                  sys.dm_db_missing_index_group_stats migs

                        on migs.group_handle = mig.index_group_handle join

                        sys.objects obj on obj.object_id = mid.object_id join

                        sys.schemas sch on obj.schema_id = sch.schema_id

                       

                        where db_name(database_id) = 'YourDatabaseName' and

            avg_user_impact > 80

      order by obj.name, equality_columns --avg_user_impact desc

SQL Server World User's Group

I'm speaking again at their virtual conference... to sign up, go to:

 https://www.vconferenceonline.com/event/regeventp.aspx?id=661

If you do sign up, please use the tracking code to let them know you found out here: VCJEFFREY



 

Indexes not used since the last reboot

This query gives you a list of indexes that have not been used since the last reboot. Notes: 1)     I’m excluding primary key indexes. Amazing how often they’re listed (i.e. primary access is not via primary key). 2)      I am running this for a specific database (see the “database_id” column in the where clause). You can identify a database’s database id by running the command: a.       Select db_id(‘insert database name here’) b.      Or, if you want to go the other way,                                                                i.      Select db_name(‘insert database id here’) 3)      You get three columns, table name, index name, and the drop command to get rid of the index. It’s sorted by table name / index name a.       It might be erring on the side of constructive paranoia to generate a create script for each of the indexes you’re about to drop before dropping them, in case circumstances require a quick recreate. index drops of unused indexes select       object_name(i.object_id),       i.name,       'drop index [' + sch.name + '].[' + 'obj.name' + '].[' + i.name + ']' from       sys.dm_db_index_usage_stats ius join       sys.indexes i on             ius.index_id = i.index_id and             ius.object_id = i.object_id join             sys.objects obj on             ius.object_id = obj.object_id join             sys.schemas sch on             obj.schema_id = sch.schema_id where       database_id = 8 and       user_seeks + user_scans + user_lookups = 0 and       i.name not like 'PK_%' order by       object_name(i.object_id),       i.name

Synchronous Mirroring

There's plenty written about database mirroring already, a cool new feature of SQL which enables you to create a hot standby (read: automatic failover) for a database on your server (read: NOT the whole server)...

One tip: If you are mirroring across any significant distance, and require synchronous mirroring, take lag time into account, as you will nto commit for the 30-90 ms round-trip time it takes to push the change out to the target server.

Free Database Tuneup

Hi,
We're offering a free, one-hour tuneup of your target database server. In order to take advantage, go to:
www.confio.com/sec
to download monitoring software, then email me (jeff@soaringeagle.biz) for a trial key, and we'll set it up.
No cost, no obligation, but we do find that most of the folks who take advantage fall in love with the tool or the service...


Enjoy,
Jeff Garbus