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

Data type mismatches causing SQL Server performance issues

I've been talking about this for years, but every once in a while I see a nose rubbed in it, and the results can be dramatic.

The SQL Server optimizer, in some versions (this is SQL 2005 SP3) will not properly resolve statistics if the data types of the declared variables & the compared columns don't match.

The solution: Match up your data types.

In the code sample below, I was looking at a query that was accessing SQL Server from Ruby--on-Rails. The ONLY difference in the code is that in the first example, the data type is being passed in as nvarchar(4000), and in the second exanmple it's varchar(255) the actual data type.

You can see that the relative plan costs are 99% to 1% (actual costs are more like a factor of 10,000).

Code (reproduced with the kind permission of my client):

declare @p0 nvarchar(4000)

SELECT this_.clinicalContactID as clinical1_138_0_,
this_.clinicalContactTypeID as clinical2_138_0_,
this_.socialSecurityNumber as socialSe3_138_0_,
this_.contactNumber as contactN4_138_0_,
this_.firstName as firstName138_0_,
this_.lastName as lastName138_0_,
this_.middleName as middleName138_0_,
this_.dateOfBirth as dateOfBi8_138_0_,
this_.age as age138_0_,
this_.gender as gender138_0_,
this_.raceTypeID as raceTypeID138_0_,
this_.maritalStatusTypeID as marital12_138_0_,
this_.createdOn as createdOn138_0_,
this_.createdBy as createdBy138_0_,
this_.lastUpdated as lastUpd15_138_0_,
this_.lastUpdatedBy as lastUpd16_138_0_
FROM ClinicalContacts this_
WHERE this_.socialSecurityNumber = @p0

Plans:

5 SQL Tricks for making code disappear

Ask your CIO – the cost of maintaining a $1M project over the life time of a project is about $7M. This means that the less code you write, the less expensive it is to maintain your application Here are 5 quick tips for reducing the amount of code you have to write.



1) Try/Catch logic blocks

SQL Server 2005 introduced the “Try/Catch” code block. Instead of checking the value of @@error after every data modification statement, you can put all of your dml in a “try” block, and handle any exceptions in a “Catch” block. Note: This is NOT transactional, you’ll still need to manage transactions externally

2) Common Table Expressions (CTE)

We’ve seen performance both ways: CTEs can both eliminate performance issues or create them. That said, when properly implemented, we’ve also used CTEs to avoid some very extensive and complicated recursion logic.

3) File Stream

For those of you who are taking .doc, .pdf, .jpg, etc., apart & putting them into varchar(max) or varbinary(max) columns are not only working too hard, you’re using up too much storage. Databases don’t store that information efficiently. Instead, use the new (with SQL 2008) Filestream data type, store it in the database as itself.

4) Merge

a. Data warehousing (and keeping cubes, etc. up to date) has caused us to need to write a lot of “take the rows form this table that have been updated, update the corresponding rows from over there… unless there isn’t a row over there, in which case add a row” logic. Hey, we’re programmers, that’s what we do. The catch is, you don’t’ have to work that hard anymore. The new “Merge” statement even gives you error-processing.

5) Hierarchy data type

We’ve personally written a lot of code to define and manage hierarchy structures. We have one client in particular who might be able to eliminate as much as 75% of their code (all they do is manage hierarchies) by using the new hierarchy data type.