Using dm_db_missing_index_details can be dangerous

Using the DMV sys.dm_db_missing_index_details to identify and create “missing” indexes could be doing you more harm than good. If you read Microsoft books online carefully for information on this DMV, you’ll notice that under the remarks it states that data in the DMV is “updated when a query is optimized by the query optimizer”. Ok, big deal, who cares? You should! Why? Because every single query is passed through the query optimizer, and if it believes an index would be useful whilst optimizing the query, a record will be placed in sys.dm_db_missing_index_details.

What this means is that someone could have run a simple one-time query, with no intention of ever running it again, but a record will be placed into the DMV.

With that in mind, you should never, ever, blindly create an index suggested by this DMV. In doing so, you run the risk of solving a problem that isn’t really there. Having the extra, unnecessary, index will consume valuable overhead whilst performing maintenance tasks and slow the insertion of new data.

So, what is this DMV used for? It’s still a great little tool in helping identify useful indexes. If you’re working on a query and believe an index could improve your performance, it may be worth checking in here to see if many other queries could have been helped by the same index.

One of the most useful things you can do with dm_db_missing_index_details, is combine it with dm_db_missing_index_columns, allowing you to apply aggregate functions with ease and see exactly which columns are being utilized heavily and may require an index. I’ve written the below script which does exactly that:

SELECT TableName,
       ColumnName,
       INCLUDE,
       EQUALITY,
       INEQUALITY
 
FROM (
      SELECT D.statement [TableName],
	     C.column_name [ColumnName],
	     C.column_name,
	     C.column_usage
      FROM sys.dm_db_missing_index_details D
	   CROSS APPLY sys.dm_db_missing_index_columns(D.index_handle) C
     ) Cols
 
PIVOT
 
     (
      COUNT(column_name) FOR column_usage IN ([INCLUDE], [EQUALITY], [INEQUALITY])
     ) PVT
 
ORDER BY EQUALITY DESC

Running the above query will give you every single column in the instance you run it against which has, in one way or another, been identified by the query optimizer as requiring an index. The more useful part of the query is the count within the pivot, this is allows you to see how many times the optimizer has deemed an index was required, and for what purpose.

It’s important to remember that data is cleared down from this DMV every time the instance is restarted, so it has next to no use for a short while after a restart. This again plays a part in making this DMV a dangerous indicator of missing indexes.

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Stored Procedure Definition Search

I just re-discovered this little script in my “Magic SQL” folder, and thought I’d share it.

Below is a script to search the definition of all stored procedures in a database for a specified value – useful in circumstances when you’re trying to figure out if a particular column is used anywhere in procedure code, or something like that.

USE DatabaseName
 
DECLARE @SearchString VARCHAR(500)
SET @SearchString = 'Search'
 
SELECT DISTINCT
       Name
 
FROM sysobjects SO
     JOIN syscomments SC ON SO.id = SC.id
 
WHERE SO.[type] = 'P'
      AND SO.category = 0
      AND SC.[text] LIKE '%' + @SearchString + '%'

To use the script, change the USE statement to point at the database you’d like to search, and change the value of @SearchString to the value you’d like to search for.

The result set will provide the name of any procedures containing the value defined within @SearchString.

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Finding a list of user defined tables with schemas within a SQL Database

A couple of weeks ago I did a blog post on finding a list of user defined tables within a SQL Database. Well, today I’ve improved upon that example to include the schema that the table belongs too as well, plus a little trick to generate you some useful scripts.

USE Database1
 
SELECT S.name + '.' + O.name [SchemaTable]
 
FROM sys.all_objects O JOIN
     sys.schemas S ON S.schema_id = O.schema_id
 
WHERE type = 'U' AND
      is_ms_shipped = 0 AND
      S.schema_id IN (5, 6, 7, 8, 9)
 
ORDER BY O.name

The reason I needed a list of tables and the schemas they belong to in this format was because I needed to add two columns to each of them.

I’m far to busy to open up each of these tables (there were 50 in total) and add the two columns manually, so I used the above SQL, as well as some concatenation to write the query for me:

USE Database1
 
SELECT 'ALTER TABLE ' + S.name + '.' + O.name + ' ADD Child VARCHAR(100) NULL,
       GrandChild VARCHAR(100) NULL' [SQL]
 
FROM sys.all_objects O JOIN
     sys.schemas S ON S.schema_id = O.schema_id
 
WHERE type = 'U' AND
      is_ms_shipped = 0 AND
      S.schema_id IN (5, 6, 7, 8, 9)
 
ORDER BY O.name

I was then able to copy the results into a new query window and execute, saving myself a lot of time.

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Managing Primary Key Constraints

Primary Key Constraints keep your rows unique, and therefore help maintain your datas integrity.

They can be applied to a single column, or across multiple columns (A maximum of 16). Any column or combination of columns referenced by the Primary Key will be unique, and because of this, they’re usually chosen as the Identity column as well.

A Primary Key column cannot be nullable, due to the fact that it forces uniqueness in the table. Computed columns are allowed to be used as a Primary Key.

When a Primary Key is created, the columns it’s created on also have a clustered index created on them. If you’d like to override this functionality, you can do so by specifying NONCLUSTERED during the creation of the key.

Adding a Primary Key Constraint to an existing table

ALTER TABLE Products
ADD PRIMARY KEY (ProductId)

The above code will modify the Products table to add a Primary Key Constraint to the ProductId column.

ALTER TABLE Products
ADD PRIMARY KEY NONCLUSTERED (ProductId, ProductName)

The above code will modify the Products table to add a Primary Key Constraint across two columns, but also overrides the default action of creating a clustered composite index, and creates a nonclustered composite index instead. What do I mean by composite? This means that the index references multiple columns.

Adding a Primary Key Constraint during table creation

CREATE TABLE Products 
  (
   ProductId INT PRIMARY KEY IDENTITY,
   ProductName VARCHAR(50))

In the above example, we’re adding the Primary Key to the integer column ProductId. The same column is also being specified as the identity column for the table, as I mentioned earlier, this is fairly common.

Removing a Primary Key Constraint

A table can only ever have one Primary Key, and you may reassess your needs and have to drop the existing Primary Key.

In order to do this, you’ll first need to find the name of the primary key that you’d like to drop. You can do this by executing the below code (replace “Schema.Table” with your details, but keep the single quotes):

SELECT I.name [PrimaryKeyName]
 
FROM sys.tables T 
     JOIN sys.indexes I ON I.OBJECT_ID = T.OBJECT_ID
 
WHERE T.OBJECT_ID = OBJECT_ID('Schema.Table') 
      AND I.is_primary_key = 1

Once you’ve run the above code, and the name has been returned, you can run the below code to drop the Primary Key:

ALTER TABLE table_name
DROP CONSTRAINT primary_key_constraint_name
Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Moving Database Objects between Schemas the quick way

I’ve been doing a bit of database re-structuring lately, and I came across a nice and quick way to move database objects between schemas. Instead of dropping and recreating the object in the schema you require it to be in – simply execute the below:

ALTER SCHEMA MySchema TRANSFER dbo.Sproc;

The above example will move the object “dbo.Sproc” into the “MySchema” schema.

Short and sweet.

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Managing Check Constraints

Check Constraints are an important part of data validation, and should be used at every opportunity.

A lot of DBAs seem to trust the checking done against data before it reaches their database is sufficient enough, but this is a very bad assumption to make. Sometimes people miss things, human error – it happens, unfortunately!

A website may be missing one small detail in its validation, which could cause a major head ache later down the line, and this is why it’s always important to do your own validation.

How they work

A check constraint is essentially a WHERE clause added to a table to confirm that the data being entered into the table is valid.

The boolean check of the constraint decides whether or not the data is allowed to be inserted.

Adding a Check Constraint to an existing table

ALTER TABLE Products
ADD CONSTRAINT ProductsPriceCheck CHECK (Price > 0)

The above code will add a check constraint, named ProductsPriceCheck, to the Products table which checks that the column Price is greater than zero. If someone attempts to enter a value which breaks this rule, an error will be raised.

Please remember that even though NULL isn’t greater than zero, it will get through this check! If you don’t want NULL values to be entered, specify “AND Price IS NOT NULL” in the check – or better yet, add “NOT NULL” to the column.

Adding a Check Constraint during table creation

CREATE TABLE dbo.Products 
   (
    ProductId INT IDENTITY PRIMARY KEY,
    ProductName VARCHAR(45) NOT NULL,
    Price MONEY NOT NULL,
    CONSTRAINT ProductsPriceCheck CHECK (Price > 0)
   )

The syntax here is very similar, but there is now no longer any need for the keyword ADD, as you arn’t adding the constaint to an existing table, it’s being created along with the table.

A check constraint can span multiple columns within the table, but cannot reference anything outside of the table it’s being created against. In order to use multiple columns, reference them exactly as you would when writing a WHERE clause against the table.

A very practical example of a Check Constraint doing what it does best would be against a column that holds an email address:

CREATE TABLE dbo.Person 
   (
    PersonId INT IDENTITY PRIMARY KEY,
    FirstName VARCHAR(45) NOT NULL,
    Surame VARCHAR(45) NOT NULL,
    Email VARCHAR(100) NOT NULL,
    CONSTRAINT PersonEmailCheck CHECK (Email LIKE '%@%' AND Email LIKE '%.%')
   )

This example makes sure that the column Email has an @ symbol and a period – two of the things that absolutely every email address will have.

Removing a Check Constraint

Should you decide that a Check Constraint is no longer needed, then you’ll be able to delete it from the Object Explorer by browsing to the Server > Database > Tables > Expand the table you’re deleting the check constraint from > Constraints > Right click on the constraint and select Delete.

You can also remove the constraint by executing the below SQL:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name
Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Finding a list of user defined tables within a SQL Database

Like most companies working with development, we have a production database server and a test database server. The developers do their testing from the test server, and once they’ve finished their development phase and the project is signed off – the database is backed up and restored on the live server.

Occasionally, additional development is required after the site is live (Phase 2, say) – which could mean more database tables to be added.

The above situation happened to me today, and one of the developers asked me to compare the two databases tables, and create any that weren’t already on the production server.

I found a quick way of comparing the two servers, rather than sitting there writing out a list of the two servers tables.

That quick way was the below query:

USE Database1
 
SELECT *
 
FROM sys.all_objects
 
WHERE type = 'U' AND
      is_ms_shipped = 0
 
ORDER BY name

This will find all of the user defined tables associated with the database its run against.

If you remove “AND is_ms_shipped = 0″ from the statement, then you’ll notice that a table named “dtproperties” is listed. This is actually a system table used to store information about database diagrams. It comes back as a user defined table due to the way it’s stored internally. Luckily Microsoft have added the is_ms_shipped flag to the table, so that we can easily filter items like this out.

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Managing Foreign Key Constraints

Foreign Key Constraints identify links between the tables and columns within your database. The values within a column with a Foreign Key Constraint on it must also appear in the table they reference – creating a link between the columns in the two tables. These columns are commonly used to join tables together in the FROM clause.

There are several different options to choose from and consider when setting up a Foreign Key Constraint, and they are:

  • No Action: This is the default option, so if none of the others are specified, this one will come into play. Under this option, if a record referenced by a Foreign Key is deleted, then an error is raised stating that an attempt to break the Foreign Key Constraint has been made.
  • Set Null: When data in a column referenced by a Foreign Key is modified, then all of the data in the column of the referencing record is set to NULL
  • Set Default: When data in a column referenced by a Foreign Key is modified, then all of the data in the column of the referencing record is set to its default value.
  • Cascade: When data in a column referenced by a Foreign Key is modified, then all of the data in the column of the referencing record is set to the same value set in the referenced table. Note that if you delete the referenced record, the referencing record will also be deleted!

It’s important to know that these values can be set per DML statement type (Update, Delete). For example, you may choose No Action when an item in the referenced table is deleted, but Cascade for when an item is updated.

Adding a Foreign Key Constraint to an existing table

ALTER TABLE dbo.Products
 
ADD CONSTRAINT FK_Products_ProductType FOREIGN KEY (ProductTypeId)
 
REFERENCES dbo.ProductType (ProductTypeId)
ON DELETE NO ACTION
ON UPDATE CASCADE

In the above example, we are adding a Foreign Key to the Products table, and calling it “FK_Products_ProductType”. We are saying that the column ProductTypeId in the Products table will reference the ProductTypeId in the ProductType table.

The ON DELETE NO ACTION means that should anyone try to delete a row from the parent table (dbo.ProductType) whilst referencing records exist in the child table (dbo.Products), then an error will be raised.

ON UPDATE CASCADE means that if someone updates a Foreign Key column in the ProductType table (dbo.ProductType.ProductTypeId), then this will be replicated to the child columns (dbo.Products.ProductTypeId).

Adding a Foreign Key Constraint during table creation

CREATE TABLE dbo.Products 
 (
  ProductId INT PRIMARY KEY IDENTITY,
  ProductTypeId INT NOT NULL,
  CONSTRAINT FK_Products_ProductType FOREIGN KEY (ProductTypeId) REFERENCES ProductType(ProductTypeId)
 )

Not much changes between adding a Foreign Key Constraint to an existing table or during table creation, other than the removal of the keyword “ADD”. Notice that I haven’t included any “On Delete Cascade” or any other variation of this option. This means that the default (No Action) is set in all instances.

Removing a Foreign Key Constraint

In an instance where you would like to remove a Foreign Key Constraint, you can do so by executing the below code:

ALTER TABLE Products
DROP FOREIGN KEY FK_Products_ProductType
Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)