7 Deadly Sins Of Microsoft SQL Server Database Administration (Sin 2)

It Ironic that as I researched the 7 deadly sins around database administration of both an Oracle database and a Microsoft SQL Server database, that the first two sins are almost identical. They just have a particular Microsoft twist or an Oracle twist to them.

To come up with the 7 deadly sins in each category, I went up to the oracle teams at Ntirety and in this case of Microsoft SQL server our database teams in that practice. Independent of each other, they came up with the same issues.

I wonder if I had told them what the other database team had said, if it would have changed the answer. There is a natural amount of competitiveness between the two camps. I find it interesting that most DBA’s fall into one camp or the other. It’s rare that they will do both. If they are Oracle, they think windows operating system is evil. If they Microsoft SQL server they think Unix/Linux operating system is evil. In may career I have worked on many different relational databases from Oracle to Sybase. I have worked with Oracle since version 3.0. Yet I have to tell you I love the latest version of Microsoft SQL Server. When I look at what it can do and the price points it’s a heck of an offering.

Microsoft SQL Server Database Management Deadly Sin 2 (Providing the SQL Server Optimizer poor or no information).

Microsoft SQL Server has an optimizer that is uses to make decisions on how it responds to requests for information. In oracle we call this the cost based optimizer. Any of the hard core Microsoft SQL Server DBA’s who read this, feel free to correct me in the proper name for this optimizer. This optimizer makes decisions on the best way to retrieve information from the database. It know the difference between a 10 row table and a million row table. Would you rather join a 10 row table to a million row table or a million row table to a ten row table. How you do this makes a big difference on how fast the query will run. It know about table data and how it is distributed across the table. If there are 5 record type “A”’s and 10,000 Record type “B”’s then it will use record type “A” when it can over record type “B”. It knows the different indexes on a table and which one makes sense to use.

This optimizer if given proper information can make excellent decisions on how to help the database perform better. Many times when we look under the covers of a prospects database, we find they have not provided the Microsoft SQL Server optimizer any information or the information that is provided to it is very stale and out of date. Resulting in poor data for the Microsoft SQL Server optimizer with results in poor choices hurting overall database performance.

The easiest way to get your database to perform better is to provide the Microsoft SQL Sever optimizer with good information.

Here is a simple script that will call for a refresh of the index and table statistics for all user tables. This will then enable the optimizer to make appropriate choices.

set nocount on
declare @name sysname, @s nvarchar(250)
declare updtable2 cursor local fast_forward for
select o.name from sysobjects o join sysindexes i on o.id = i.id
where o.xtype = ‘U’ and i.indid < 2
order by 1

open updtable2 fetch next from updtable2 into @name
while @@fetch_status = 0
set @s = N’update statistics ‘ + quotename(@name)
print @s
exec (@s)
fetch next from updtable2 into @name
deallocate updtable2

Posted Michael Corey, Ntirety


View blog top tags

Add to Technorati Favorites

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.