[SQLDownUnder] SQL Server Sessions - 001

[Victor . Isakov]

Hi all,

Welcome to the first every edition of the SQL Server Technical Mailing
List! Otherwise to be known as the "SQL Server Sessions".

What I plan to do is send out a fortnightly newsletter focusing mainly
on technical content, but also containing news and other relevant
information regarding SQL Server.

I envisage the newsletter having a number of Sections covering different
material, namely:

	01.00	News
	02.00	Database Administration
	03.00	Database Development
	04.00	Business Intelligence
	05.00	Problems/Quiz/Survey
	06.00	Products/Vendors/Conferences/Websites
	07.00	Feedback

I would very much like to encourage people to participate, so if you
have any interesting hints/news/information I would very much like to
hear from you.

I believe there is a lot of SQL talent in this country and I would like
that to be shared and reflected in this Newsletter. If you would like to
contribute articles/material you are most welcome, it will certainly
make my life a lot easier ;o)

This newsletter represents an opportunity for the SQL Server Community
to help itself, so I would also like to have a Problem/Quiz/Survey
Section. So if you have a perplexing problem, or interesting quiz send
it this way. Likewise with answers. What I will do is pick the problems
that are of most value to the SQL Server Community and the best
technical solutions for inclusion in the Mailing List.

When sending me any correspondence to be potentially included in the
Mailing List, please indicate whether you want to be acknowledged.

Your feedback is alway appreciated, so don't be shy.

IF YOU FIND THIS OF VALUE PLEASE FORWARD IT TO OTHERS THAT MIGHT BE
INTERESTED IN SUBSCRIBING TO THIS NEWSLETTER. Details on how to
subscribe are at the very end.

Otherwise, with no further ado, welcome to the "SQL Server Sessions"!


Kind Regards,

Victor
mailto:sql@xxxxxxxxxxxxxxxx


01.00 News
==========
Being the first Session lets get some of the recent news out of the way,
so in case you didn't know...

01.01 SQL Server Special Interest Groups
========================================
If you weren't aware there is a SQL Server Special Interest group in
your capital city. It represents a great opportunity to learn more about
the existing product, what will come out in the next release, let alone
mingle with other SQL Server Gurus to share ideas and swap stories.
Whether you like to listen, or present, check out
http://www.sqlserver.com.au for more information about your state!

01.02 Microsoft Announces Pricing for SQL Server 2005
=====================================================
The certainly has been a lot of hype building up as Yukon slowly
progresses to SQL Server 2005! Microsoft has finally released the
pricing for the different Editions of SQL Server from the free Express
Edition to the full-blown Enterprise Edition.
http://www.microsoft.com/sql/spotlight/expandsqlserver.asp

01.03 New SQL 2000 Edition
==========================
Designed for small-to-medium organizations Microsoft has announced a new
edition of SQL Server 2000, namely the Workgroup Edition. It still
supports Full-Text Searching, DTS, Replication (Snapshot / Merge /
Transactional), but is limited to 2 CPUs, 2GB RAM amongst other features
(notice no OLAP Services). Dell (US) already have packages available,
remains to be seen how well it is taken up by the rest of the market:
http://www.dell.com/SQL. It will be available from the 1st April 2005.n
There's certainly no surprise that it has achieved the top spot in the
Transaction Processing Performance Councils TPC-C benchmark:
http://www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=105022401.

01.04 SQL Server 2000 Service Pack 4 Beta
=========================================
SQL Server Service Pack 4 has been released as a Beta 4. It's been long
overdue, to put it mildly. I suspect that has to do with the pressure of
meeting milestones in developing Yukon. It does not only include
patches, but new features such as support for AMD64. You can sign up for
the Beta at http://www.microsoft.com/sql/evaluation/betanominations.asp


02.00 Database Administration - Automatic UPDATE STATISTICS
===========================================================
As SQL Server uses a cost-based (versus a rules-based) optimizer
(although SQL Server does default to rules when no other information is
available), it is important to have your statistics up-to-date. This
option is turned on by default. But how does SQL Server automatically
update statistics? By this simplified algorithm for permanent tables:

	* For tables with less than 500 rows when 500 rows change.
	* For tables with more 500 rows when (500 + 20% rows of table)
changes.

SQL Server does this by monitoring the [SYSINDEXES].[DBO].[ROWMODCTR]
column.

You can monitor how often SQL Server updates statistics by turning on
Trace Flag 8721, upon which you will see an entry in the Error Log
whenever SQL Server runs an AutoStats.

The problem with automatically updating statistics is that it can
potentially slow down system performance during production hours.
Although SQL Server does limit the number of concurrent UPDATE
STATISTICS tasks to 4/CPU you cannot control the number of concurrent
UPDATE STATISTICS being run.

You can turn off automatic updating of statistics (database option), but
you should then schedule UPDATE STATISTICS to run regularly outside
production hours.


03.00 Database Development - PRIMARY KEY as a CLUSTERED INDEX
=============================================================
One of the most common database designs I have seen since SQL Server 6.0
days is that a lot of people automatically make the PRIMARY KEY (PK)
Column the CLUSTERED INDEX (CI). Especially since SQL Enterprise Manager
(SEM) does it by default. Yet another reason not to use the GUI ;o)
You'll notice that Query Analyser (QA) generates a NONCLUSTERED INDEX
(NCI) by default. Since you can only have one CI (as it dictates the
physical order of the data in the table) it is important to get it
right. Sure, the PK is a very important concept in a relational database
and every tables should have one, but the trick with SQL Server is then
chose the correct index for it. 

CI work best where there little selectivity (uniqueness) or a high
density (large number of duplicates) with the data. Whereas NCI do not
work as well on columns that have a high density.

Which means that you might be creating a NCI, with all the storage and
maintenance overhead, but SQL Server will not be using it in most cases,
as it will be more efficient to use a table scan coupled together with
the  Read-Ahead Manager and it's more efficient 64KB I/O.

So when you think about the data in your PK it's extremely selective, so
much so that it's called unique. Thus, whether you use a CI or a NCI the
performance will be pretty much the same.

So CIs work best where the data is "clustered" together for queries,
such as in the case where there are lots of duplicates for a given key
(high density), range queries, FOREIGN KEYs potentially (depends on
density), etc. Whereas NCIs only really work best with point queries in
comparison. So how are you querying your PK?

Of course, you have to counter-balance this against keeping the CI
small, as the keys of the CI will be used in all of the NCIs.

I'm certainly not saying that PKs shouldn't be the CI. Especially where
you are searching on the PK in a range (WHERE PK BETWEEN '20050101
00:00.000' AND 20050102 23:59.997'), but in certain cases (think about
the ISBN) there might be better candidates.


04.00 Business Intelligence - Improving Performance in the DTS Data Pump
========================================================================
A little known / remembered tip is to always try to use many-to-many
column mappings in the Transform Data Task (or data pump). That's
because each arrow represents a separate data pump operation.
Unfortunately DTS's default Auto-Mapping implements a many-to-many
mapping.

So you should get into the habit of always going to the
"Transformations" tab, deleting all the default mappings, selecting all
the columns in both tables (or like applicable transformations),
clicking on the "New" button and choosing the "Copy Column" option and
then the "OK" button.

You should now see a single multi-tailed / multi-headed arrow. More
importantly faster DTS operations!


05.00 Problems/Quiz/Survey
==========================
To start of the SQL Server Sessions, I thought we'd have both a Problem
and a Quiz, so I am looking forward to your responses. I would really
like to see people replying to the Surveys as it will hopefully provide
some interesting, if not useful feedback to the SQL Server Community.

05.01 Transaction Log Problem
=============================
"Hi Victor,

I seem to have a problem with my transaction log. I cannot shrink it for
some reason. It has blown out to 1GB. Whenever I try to shrink it to the
allowed minimum in SQL Enterprise Manager, it remains at it's current
size. What is going on? Why can't I shrink it?

Desperate DBA"

Can anyone explain what's going on with the Transaction Log in this case
and how to solve the problem?

05.02 SQL Server 2000 Workgroup Edition Quiz
============================================
Question: 
	Given the cheaper price-tag and release at the end-of-life cycle
of SQL Server 2000, how likely will you consider implementing a SQL
Server 2000 Workgroup Edition?
Choices:
	A. I see no need for my organisation to use SQL Server Workgroup
Edition.
	B. Although I am not interested in the SQL Server 2000 Workgroup
Edition, I will consider using the SQL Server 2005 Express Edition when
it is released.
	C. Although I am not interested in the SQL Server 2000 Workgroup
Edition, I will consider using the SQL Server 2005 Workgroup Edition
when it is released.
	D. There is no need to implement the SQL Server 2000 Workgroup
Edition as MSDE 2000 satisfies my requirements.
	E. There is no need to implement the SQL Server 2000 Workgroup
Edition as Access satisfies my requirements.
	F. I will be using/consider using SQL Server 2000 Workgroup
Edition


06.00 Products/Vendors/Conferences/Websites - Transaction Log Utility
=====================================================================
A lot of people would be familiar with Lumigent's utility that allows
you to read and "manage" SQL Server's Transaction Log. Looks like there
is another competitor on the market, which can only be good for
competition. It's called Log P.I and an evaluation version is available
from http://www.logpi.com.


07.00 Feedback
==============
None.


*** SUBSCRIPTION DETAILS ***

To subscribe to this mailing list please send an email to
mailto:sql@xxxxxxxxxxxxxxxx using one of the following subject lines
depending on the email format you want to receive:

	Subscribe - TXT
	Subscribe - HTML
	Subscribe - RTF

To unsubscribe from this mailing list please send an email to
mailto:sql@xxxxxxxxxxxxxxxx using the following subject line:

	Unsubscribe

*** PRIVACY STATEMENT ***
You can subscribe / unsubscribe any time to this Mailing List by
following the above directions. Your email address will ONLY be used for
the purpose of this Mailing List. Mailing List email addresses are not
stored on a public server and can only be accessed by authorised staff.
Your email address will NEVER be disclosed.

This message and any attachment is confidential and may be privileged or otherwise protected from disclosure.  If you have received it by mistake please let us know by reply and then delete it from your system; you should not copy the message or disclose its contents to anyone.

==============================================================================



to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net