Posts
10
Comments
14
Trackbacks
0
Easy Fix - Low Hanging Fruit - Get Em 'for they're hot.
I am of the opinion that every table should have a clustered index.  Also, I recently saw somewhere ( maybe SqlTeam, I don't remember ) a quote from Joe Celko, "You don't have a table unless you have a key."  Which totally sounds like something he would say.  BTW, I got to meet him @ Pass one year.  Dude is wicked smart.  Wicked.

Done with the name dropping.

====================================
Basic idea:
You have a poorly performing database.  You can't / don't have time to re-write all of the stored procedures or many of them or whatever.  Code can't change right now.  Run this script, find the tables without Clustered indexes and primary keys and start planning.

Clustered indexes allow SQL Server to work more efficiently.  Primary keys support the use of foreign keys.  Data integrity is sooo sexy.   Do say this to your boss.  They will love it and promote you.

Things to keep in mind:
  • Creating / changing a clustered index on a table means that EVERY row in the table must be moved.  Bad news: a BIG table will take a LONG time.  Good news:  a BIG table will show a HUGEr performance improvement.
  • read up on clustered indexes and primary keys before beginning.  While they are often set on the same column or set of columns, they are not the same thing.
  • Primary keys by definition are on unique columns.  Consider this:  If you aren't sure, I mean 100% positive, that the columns you have chosen for your primary key will be unique, you are probably going to break something.  If there is data in the table, then the creation of the key will not be successful.  But perhaps you are placing the key on a table which at the moment is empty.  Then the application which has been rolling along for the last 2.5 years goes cablooey trying to insert.  Be careful.  ( Yet another good example of why designing first, then coding saves lives ).





====================================
Script:
USE master

go

IF NOT EXISTS ( SELECT TOP 1
1
FROM
sys.databases
WHERE
name = 'MaasSqlTest' )
BEGIN
CREATE DATABASE MaasSqlTest
END

GO

USE MaasSqlTest

GO

IF ( OBJECT_ID('dbo.TablesWithoutPKorCL') IS NULL )
BEGIN
--YEUP, if the script is successful, this table WILL show up in itself
create TABLE [dbo].[TablesWithoutPKorCL]
(
[DBName] [nvarchar](128) NULL
, [SchemaName] [sysname] NOT NULL
, [TableName] [sysname] NOT NULL
, [HasUniqueCnst] [int] NULL
, [HasClustIndex] [int] NULL
, [HasPrimaryKey] [int] NULL
, IFixedThis BIT DEFAULT(0)
, ImNotAllowedToFixThis BIT DEFAULT(0)
, ThisCantBeFixedNow BIT DEFAULT(0)
, UTCDateEntered DATETIME DEFAULT ( GETUTCDATE() )
)
ON [PRIMARY]

END
GO


GO

DECLARE @SQL NVARCHAR(MAX) ;

SET @SQL = '
INSERT INTO [MaasSqlTest].[dbo].[TablesWithoutPKorCL]
(
[DBName]
,[SchemaName]
,[TableName]
,[HasUniqueCnst]
,[HasClustIndex]
,[HasPrimaryKey]
)
select
DB_Name() DBName
, S.name SchemaName
, T.name TableName
, OBJECTPROPERTY( object_id, '
'TableHasUniqueCnst'') HasUniqueCnst
, OBJECTPROPERTY( object_id, '
'TableHasClustIndex'') HasClustIndex
, OBJECTPROPERTY( object_id, '
'TableHasPrimaryKey'') HasPrimaryKey
from sys.tables T
JOIN sys.schemas S
ON T.schema_id = S.schema_id
WHERE OBJECTPROPERTY( object_id, '
'TableHasPrimaryKey'') = 0
OR OBJECTPROPERTY( object_id, '
'TableHasClustIndex'') = 0

'

print @SQL
DECLARE @ExecuteWhere NVARCHAR(MAX) ;
SET @ExecuteWhere = '{@DBName}.dbo.sp_executeSQL @stmt = N''{@SQL}''' ;
SET @SQL = REPLACE(@SQL , '''' , '''''') ;
SET @ExecuteWhere = REPLACE(@ExecuteWhere , '{@SQL}' , @SQL) ;

DECLARE @dbs TABLE ( DBName sysname )
INSERT INTO
@dbs ( DBName )
select
name
from
sys.databases
where
name not like 'Staging%'
DECLARE @DBName sysname ;
DECLARE @ExecuteWhere_DBName NVARCHAR(MAX) ;
while exists ( select top 1
1
from
@dbs )
BEGIN
SELECT TOP 1
@DBName = DBName
FROM
@dbs
SET @ExecuteWhere_DBName = REPLACE(@ExecuteWhere , '{@DBName}' , @DBName) ;

PRINT @ExecuteWhere_DBName ;

EXECUTE ( @ExecuteWhere_DBName ) ;

DELETE
@dbs
WHERE
DBName = @DBName
END

select *
from [MaasSqlTest].[dbo].[TablesWithoutPKorCL]
posted on Wednesday, February 20, 2008 2:21 PM Print
Comments
Gravatar
# re: Easy Fix - Low Hanging Fruit - Get Em 'for they're hot.
Steve G
2/21/2008 6:52 PM
File this one under "Extreme Over-simplification".

Remember, complex problems have simple, easy to understand, wrong answers.
Gravatar
# "Extreme Over-simplification".
Jeff M
2/21/2008 7:56 PM
Steve: I'm not sure to which part of the blog post you objected. Was it the use of clustered indexes on tables? Or should tables not have primary keys? Or are you not a fan of Joe Celko? If you haven't met him in person you really should. He's hilarious!!! I would love to hear your specific explanations to the above disagreement! Thanks for commenting!
Gravatar
# re: Easy Fix - Low Hanging Fruit - Get Em 'for they're hot.
Shaun McG
2/25/2008 9:02 AM
Jeff: Maybe he believes you should have written more or maybe he is cynical enough to think that aeveryone who is a DBA should already know this.
Gravatar
# More Links
Jeff M
2/25/2008 12:46 PM
Here are some additional links:
*http://www.sql-server-performance.com/tips/clustered_indexes_p1.aspx
*http://technet.microsoft.com/en-us/library/aa964133.aspx
*http://msdn2.microsoft.com/en-us/library/ms188270.aspx
*http://support.microsoft.com/kb/297861
*http://www.mssqltips.com/tip.asp?tip=1254
Gravatar
# re: Easy Fix - Low Hanging Fruit - Get Em 'for they're hot.
Cory
2/25/2008 3:55 PM
Jeff, I suspect Steve’s objection was to your one-size-fits-all statement. Read the links you reference. I think you won't find one that says "every table should have a clustered index." They'll probably suggest that you test any configuration change. And that a clustered index is simply one of many options you can employ to improve the performance of your application. As KB297861 says, “Microsoft recommends that you add a clustered index to the table and test the effect of the clustered index on performance.” It also states “With any high insert environment, it is best to test your particular scenario both with and without a clustered index to determine which is best for your situation.”
Gravatar
# Easy Fix - Low Hanging Fruit - Get Em 'for they're hot.
jeff m
2/25/2008 5:46 PM
Rarely are one size fits all statements appropriate.

But some are very appropriate.

Here's my non technical version of the same article: Low Hanging Fruit: Quick and Easy fix!

I am of the opinion that the tires in every car should be kept fully inflated.

Doing so reduces friction, increases the life of your tires, and helps reduce stress on the engine, resulting in increased miles per gallon.

Yes, in some situations, one wants reduced pressure. For instance, lowering the pressure in your tires results in increased friction which is great when your car is stuck in a mud hole.

Some folks might choose to drive with lower air pressure or heaps.

Cluster your tables unless you have a reason not to. If you have a reason not to, I would appreciate it if you choose to send an email to me. I love to learn!
Comments have been closed on this topic.
News