- DBCC SHRINKDATABASE
Most
of SQL Server developers and even some DBAs think that we can shrink
size of a database by executing DBCC SHRINKDATABASE. You can just
reacquire unused space of database and it’s never going to compress your
database and change magically low size. Advantage is very very low as
compared to performance loss. Why DBCC SHRINKDATABASE is so awful SQL
Server Guru Pinal Dave has explained it here.
- SET AUTO_CREATE STATISTICS OFF
SQL
Sever by default SET this option to ON and create statistics for all
the columns used in join and filters, for even those columns on which no
index exists. SQL Server is made quite intelligent to create statistics
on required columns only and even drop statistics which are no more in
use. So always keep AUTO_CREATE STATISTICS ON.
- RECOMPILE hint in Stored Procedures
You
can provide RECOMPILE hint for stored procedure to recompile it every
time it is executed. RECOMPILE hint for SPs is nothing but a performance
overhead.
- Query hint to force some index usage
Index
of your own choice can be forced to use for a query BUT never ever do
this on production servers. Query Optimizer is intelligent enough to
select, which index is better to use for a query. Let optimizer work.
- Heap table structure
Tables
without any clustered index are called HEAP tables and records for
these tables are not kept physically in order. Always create a clustered
index on each of your database table because lot of hard work is
required by SQL Server to perform any type of query on these heap
tables.
- Clustered index on UNIQUEIDENTIFIER column
To
types of data types are used to create a surrogate key (artificial key)
i.e. INT (as identity column) and UNIQUEIDENTIFIER. Problems occur when
clustered indexes are created on UNIQUEIDENTIFIER data type column.
Value for UNIQUEIDENTIFIER are never sequential (I am not talking about
Sequential GUID in Sql Server 2008) and always play a giant role in
index fragmentation not only for clustered index but due to this wide
key, non-clustered indexes need more space and maintenance time. Read
more about this in early post UNIQUEIDENTIFIER column as primary key a worst choice.
- Index on BIT data type columns
Though,
we are allowed to create index on columns with BIT data type. Yet
creating an index on a BIT data type column by itself is not a big
advantage since such a column can have only two unique values. Such
indexes are just over head as we SQL server still need to maintain these
indexes.
- COUNT(*) to check records existence
Never use COUNT (*) to check existence of record, instead use
IF EXISTS (SELECT 1 FROM YourtTable WHERE YourFilterCondition)
(Update: http://connectsql.blogspot.com/2011/01/sql-server-why-we-should-avoid-count.html)
(Update: http://connectsql.blogspot.com/2011/01/sql-server-why-we-should-avoid-count.html)
- User Tables in System Databases
Always create user tables in your own user databases and never use system databases for this purpose.
No comments:
Post a Comment