The Refresh Orlando Heirs

Many people know the importance of creating indexes on SQL Server database tables. Indexes greatly improve the performance of a database. However, while many people create indexes on their SQL Server tables, many people don’t maintain them properly to ensure queries run efficiently as possible. I’ll begin by giving a quick overview of how SQL Server 2000 stores data and how indexes improve performance. Then, I’ll spend quite a bit of time explaining why, when, and how to maintain indexes with DBCC SHOWCONTIG and DBCC INDEXDEFRAG to ensure queries run in the most efficient manner.

SQL Server 2000 stores data into what is known as a heap. A heap is a collection of data pages containing rows for a table. The data isn’t stored in any particular order and the data pages themselves aren’t in any sequential order. The data is just there with no real form or organization. When SQL Server accesses data in this form, it does a table scan. This means SQL Server starts reading at the beginning of the table and scans every page until it finds the data that meets the criteria of the query. If a table is very large, this could greatly decrease the performance of queries.

Indexes will hasten the retrieval of data. It is important to understand how data is used, the types of queries being performed and the frequency of the queries that are typically performed when planning to create indexes. An index is far more efficient when the query results return a low percentage of rows and the selectivity is high. High selectivity means a query is written so it returns the lowest number of rows possible. As a rule, indexes should be created on columns that are commonly searched; this includes primary and foreign keys. It follows that columns that contain few unique values should never be indexed; this will increase the number of rows returned in a query.

There are two types of indexes to consider when planning: Non-Clustered and Clustered Indexes.

A non-clustered index stores data comparable to the index of a text book. The index is created in a different location than the actual data. The structure creates an index with a pointer that points to the actual location of the data. Non-clustered indexes should be created on columns where the selectivity of query ranges from highly selective to unique. These indexes are useful when providing multiple ways to search data is desired.

A clustered index stores data similar to a phone directory where all people with the same last name are grouped together. SQL Server will quickly search a table with a clustered index while the index itself determines the sequence in which rows are stored in a table. Clustered indexes are useful for columns searched frequently for ranges of values, or are accessed in sorted order.

Each table can have only one clustered index, however up to 249 clustered indexes can be added per table. For more information on how Clustered and Non-Clustered indexes store data visit http://www.sql-server-performance.com/gv_index_data_structures.asp

While I could go on and on about how SQL Server 2000 stores and accesses data in a heap and in an Index architecture, I will move on to discuss maintaining indexes with DBCC SHOWCONTIG and DBCC INDEXDEFRAG.

Once indexes have been created, it is important to maintain indexes to ensure the best possible performance. If indexes are not maintained, over time the data will become fragmented. Fragmentation is the inefficient use of pages within an index*. There are a number of tools available that will help with optimizing indexes to ensure they are running well, however I will only discuss DBCC SHOWCONTIG and DBCC INDEXDEFRAG in this article.

The DBCC SHOWCONTIG command will provide fragmentation information on data and indexes within a specified table and it will also determine if the data and index pages are full. If a page is full, SQL Server must split the page to make room for new rows. This statement should be run on heavily modified tables, tables that contain imported data, or tables that seem to cause poor query performance. When the statement is executed, here is what will be returned:
Statistic Description
Pages Scanned Number of pages in the table or index.
Extents Scanned Number of extents in the table or index.
Extent Switches Number of times the DBCC statement moved from one extent to another while it traversed the pages of the table or index.
Avg. Pages per Extent Number of pages per extent in the page chain.
Scan Density [Best Count: Actual Count] Best count is the ideal number of extent changes if everything is contiguously linked. Actual count is the actual number of extent changes. The number in scan density is 100 if everything is contiguous; if it is less than 100, some fragmentation exists. Scan density is a percentage.
Logical Scan Fragmentation Percentage of out-of-order pages returned from scanning the leaf pages of an index. This number is not relevant to heaps and text indexes. An out of order page is one for which the next page indicated in an IAM is a different page than the page pointed to by the next page pointer in the leaf page.
Extent Scan Fragmentation Percentage of out-of-order extents in scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order extent is one for which the extent containing the current page for an index is not physically the next extent after the extent containing the previous page for an index.
Avg. Bytes free per page Average number of free bytes on the pages scanned. The higher the number, the less full the pages are. Lower numbers are better. This number is also affected by row size; a large row size can result in a higher number.
Avg. Page density (full) Average page density (as a percentage). This value takes into account row size, so it is a more accurate indication of how full your pages are. The higher the percentage, the better.

The DBCC INDEXDEFAG command will rebuild a specified index or all indexes for a specific table. This command also allows use of the fillfactor option which reduces the number of page splits per data or index page. Using the fillfactor option increases performance on insert and upstate statements. If a data page is full, SQL Server must split the page to make room for the new rows. The fillfactor allows specification of a percentage of space to leave available on the data pages for inserts and updates.

Let’s observe an example:

Running the query on a table called member:
&nbspDBCC SHOWCONTIG (member) WITH ALL_INDEXES

DBCC SHOWCONTIG scanning ‘member’ table…
Table: ‘member’ (786101841); index ID: 2, database ID: 14
LEAF level scan performed.
- Pages Scanned…………………………..: 192
- Extents Scanned…………………………: 26
- Extent Switches…………………………: 187
- Avg. Pages per Extent……………………: 7.4
- Scan Density [Best Count:Actual Count]…….: 12.77% [24:188]
- Logical Scan Fragmentation ………………: 48.96%
- Extent Scan Fragmentation ……………….: 96.15%
- Avg. Bytes Free per Page…………………: 6721.0
- Avg. Page Density (full)…………………: 16.96%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Running the query on a table named provider:
&nbspDBCC SHOWCONTIG (provider) WITH ALL_INDEXES

DBCC SHOWCONTIG scanning ‘provider’ table…
Table: ‘provider’ (850102069); index ID: 2, database ID: 14
LEAF level scan performed.
- Pages Scanned…………………………..: 3
- Extents Scanned…………………………: 1
- Extent Switches…………………………: 0
- Avg. Pages per Extent……………………: 3.0
- Scan Density [Best Count:Actual Count]…….: 100.00% [1:1]
- Logical Scan Fragmentation ………………: 33.33%
- Extent Scan Fragmentation ……………….: 0.00%
- Avg. Bytes Free per Page…………………: 5596.0
- Avg. Page Density (full)…………………: 30.86%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

There are a few specific things to take note of and will help determine if index pages are full or if they are heavily fragmented.

The fullness of the index pages can be determined by reading the “Avg. Bytes free per page” and “Avg. Page density (full)” statistics. The “Avg. Bytes free per page” figure should be low and the “Avg. Page density (full)” figure should be high. You’ll notice that both tables likely have very full pages.

The fragmentation level of an index can be determined by comparing the values of “Extent Switches” and “Extents Scanned” and having a clear understanding “Logical Scan Fragmentation” and “Extent Scan Fragmentation” values. The “Extent Switches” should be almost equal to “Extents Scanned.” Based on the examples above, this is the way it should look. “Logical Scan Fragmentation” and “Extent Scan Fragmentation” values give a good indication of a table’s fragmentation level. These values should be as close to zero as possible (10% may be acceptable). The ‘member’ table is highly fragmented and the provider table is slightly fragmented based on the numbers above.

These issues can be corrected by dropping and recreating a clustered index with the FILLFACTOR option specified. Also, the DBCC INDEXDEFRAG command will compact an index, taking into account its FILLFACTOR, which will improve the statistics.

After running the queries below:
&nbspDBCC DBREINDEX (member, ”, 80)
&nbspDBCC DBREINDEX (provider, ”, 80)

Running the queries:
&nbspDBCC SHOWCONTIG (member) WITH ALL_INDEXES
&nbspDBCC SHOWCONTIG (provider) WITH ALL_INDEXES

DBCC SHOWCONTIG scanning ‘member’ table…
Table: ‘member’ (786101841); index ID: 2, database ID: 14
LEAF level scan performed.
- Pages Scanned…………………………..: 41
- Extents Scanned…………………………: 6
- Extent Switches…………………………: 5
- Avg. Pages per Extent……………………: 6.8
- Scan Density [Best Count:Actual Count]…….: 100.00% [6:6]
- Logical Scan Fragmentation ………………: 0.00%
- Extent Scan Fragmentation ……………….: 0.00%
- Avg. Bytes Free per Page…………………: 1657.0
- Avg. Page Density (full)…………………: 79.53%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC SHOWCONTIG scanning ‘provider’ table…
Table: ‘provider’ (850102069); index ID: 2, database ID: 14
LEAF level scan performed.
- Pages Scanned…………………………..: 2
- Extents Scanned…………………………: 2
- Extent Switches…………………………: 1
- Avg. Pages per Extent……………………: 1.0
- Scan Density [Best Count:Actual Count]…….: 50.00% [1:2]
- Logical Scan Fragmentation ………………: 0.00%
- Extent Scan Fragmentation ……………….: 0.00%
- Avg. Bytes Free per Page…………………: 4346.0
- Avg. Page Density (full)…………………: 46.31%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

As a result of running the DBCC SHOWCONTIG and DBCC INDEXDEFRAG commands, we were able to diagnose and greatly reduce fragmentation on the ‘member’ and ‘provider’ tables. The member table is almost perfect and the ‘provider’ table shows great improvement. This will result in an extraordinary performance increase on queries that are run against these tables.

*For more information on fragmentation, visit this link:
www.sql-server-performance.com/

Desiree Harris is a support specialist with ORCS Web, Inc. - a company that provides managed hosting services for clients who develop and deploy their applications on Microsoft Windows platforms.

About the Author

Desiree Harris is a support specialist with ORCS Web, Inc. - a company that provides managed hosting services for clients who develop and deploy their applications on Microsoft Windows platforms.

TWO FAIRY TALES REVEAL THE SECRET OF LIFE When we were young we
became acquainted with the stories of Red Riding Hood and
Goldilocks. Both these little girls have encounters with wild
beasts and one is eaten, whilst the other gets clean away. Why
were the fates of the little girls so different? If we knew the
reason, it might be useful in everyday life. When I first heard
the stories, I enjoyed them at face value and, like all
children, did not possess the ‘power’ to understand the depth of
meaning behind them. Then I grew up and, after much folly and
suffering, began my search ‘in the wilderness’ for the meaning
of life. One day the connection between the two apparently
unconnected stories just came to me from out of the blue. Bear
with me as I recount the tales. You will remember that Red
Riding Hood’s head was full of kind and considerate thoughts.
These had been passed on to her, like a virus, from her mother,
who, in turn, had learned them from her parent. When Red Riding
Hood was asked by mum to take some groceries to granny who lived
in the woods she jumped at the idea. She was warned against
talking to strangers and to keep to the path. All went well
until the way became crooked at the entrance to the woods. Here
she meets the wolf; a beast that immediately suggests picking
more berries and fruits for granny. Red Riding Hood finds the
idea irresistible and gathers a lot more produce. Meanwhile the
wolf creeps away to the cottage in the woods where he gobbles up
granny. Then the little girl arrives loaded down with the
groceries and fruits. The wolf is now in granny’s clothes and he
promptly adds Red Riding Hood to the menu. However, a hunter is
nearby and he kills the wolf and cuts him open, thus releasing
the two ladies. Now we come to Goldilocks. Here is girl who
liked to roam in the woods alone, eating wild strawberries when
she was hungry and sleeping on dry moss when she was tired. The
scene changes to the cottage of the three bears where mummy bear
has been making porridge for her family. It is too hot to eat,
so all the bears go out for a while. A little later Goldilocks
passes by and, because she is hungry and tired, enters the
cottage of the three bears. You will remember how she tries all
the bowls of porridge, all the chairs and all the beds to find
the ones that are just right for her. She is asleep in baby
bear’s bed when the beasts arrive home. There is a commotion,
but Goldie escapes and runs all the way home. We are told that
she never goes back to the woods again. It’s great to hear those
stories again isn’t it? What do you mean, you would rather watch
‘Prisoner in Cell Block H’ on TV? Listen, those stories reveal
the secret of life. The Red Riding Hood story depicts what
happens when you ‘go too far’. You see, it is very powerful to
exercise personal preferences or choices. Preferences are your
own, but the knowledge of right and wrong you receive from your
parents or teachers. They pass their values on to you like a
virus. You then find yourself like Eve in the Garden of Eden,
‘eating of the tree of the knowledge of good and evil’. Having
‘gone too far’, both Adam and Eve were kicked out of the garden
and became powerless. Red Riding Hood’s mother, like mothers
everywhere, committed an act of extreme folly when she told her
daughter to keep to the path and not to talk to strangers. Her
daughter was then forced to abandon her own preferences in favor
of a received wisdom. When this happens you have overstepped the
mark, you have been tripped over a threshold into a state of
mind that becomes available to a living trend. This is what
happened to Red Riding Hood. Living trends emerge spontaneously
from increasingly complex conditions. In the story, escalating
complexity is symbolized when the straight path turns ‘crooked’
at the entrance to the woods. (In the story of Adam and Eve,
‘crookedness’ is symbolized by the serpent.’) Also, in fairy
tales, the forest trees, straining towards the light, depict
living trends. Red Riding Hood meets the wolf when the path goes
‘crooked’. He tempts her to go beyond the threshold of what is
‘just enough’ groceries for granny. So, having already ‘gone too
far’ with her ideas of right and wrong, the little girl now
picks an excess of fruit. Red Riding Hood also has imperfect
knowledge of the motives of the wolf. In general, there will
always exist a state of imperfect knowledge between grown men
and women - even if they are mentally compatible. This is due to
the existence of the female fertility cycle. Because a man
cannot know what it is like to be a woman, the level of
imperfect knowledge is high enough to make both parties
available to living trends. In fact the fertility cycle is often
at the root of those disembodied ‘forces’ called living trends.
Red Riding Hood’s mum and dad were obviously living under the
spell of a living trend and had drawn their daughter into it.
Because of the connection of imperfect knowledge with the
fertility cycle, the daughter is depicted as wearing a ‘Red
Hood’. Therefore, to sum up, it is Red Riding Hood’s failure to
‘tap the world lightly’, together with her received tendency to
‘eat of the tree of the knowledge of good and evil’, plus her
‘imperfect knowledge’ of the motives of the wolf, that combine
to cause ‘crookedness’. The latter is the sign that the little
girl is about to participate in a new living trend. She now
enters the world of living trends, the ‘forest’ and gets to
discover that the ‘road to hell really is paved with good
intentions’. Now Goldilocks is a loner. She has no teachers or
mother to infect her with one-sided ideas of right and wrong and
‘imperfect knowledge’ of the world. Goldie does not wear a ‘Red
Hood’, but instead her golden curls are emphasized - a symbol of
her independent spirit. Her view of life does not lead to
over-the-top responses and so, for her, the road does not turn
‘crooked’. She is on the ’straight way’ through life and
therefore does not meet the wolf and is not tempted into further
one-sidedness. So, at any given moment, even though she is in
the ‘forest’ of living trends, Goldie is not available to any of
them. When she is in the cottage of the three bears, she does
have ‘imperfect knowledge’ of the danger she is in, but is saved
by her own acts of ‘power’. She tries all the chairs and all the
bowls of porridge and all the beds to find what is ‘just right’
for her. By exercising a sober choice or personal preference,
she ‘taps the world lightly’ and never goes beyond what is ‘just
enough’. Therefore when the bears return she has the ‘power’ or
‘luck’ to escape. In contrast, Red Riding Hood was gripped by a
second-hand desire to do good works and she indulged it to the
full. She was a driven person who committed no acts of ‘power’.
Therefore, she was eaten. So you can see that life is not best
served by learning right from wrong. Those things take care of
themselves when life becomes a practical matter of uncommon
knowledge and ‘power’. When it comes to the crunch in life, you
succeed or fail, live or die, depending on how much ‘power you
have in the bank’. How much do you have? How much ‘treasure in
heaven’ have you accrued through your own acts of ‘power’? Or is
your imperfect knowledge of the world and one-sided responses
making you available to living trends? Is a ‘wolf’ stalking you
and, through cross infection of your world-view, is he pursuing
any of your loved ones right now? This would be a wolf, not in
’sheep’s clothing’, but in ‘granny’s clothes’. You’ll know he’s
got to you when you find yourself thinking those self-righteous,
judgematic thoughts about right and wrong that have been passed
down the generations like a virus and which always propel you
beyond what is just enough. If this wolf in ‘granny’s clothes’
is on your trail, he will tempt all of you in turn and then
steal your most valuable possession, which is your ‘power’ to
generate nice surprises.

I believe I know what the Void feels like. Do you? It’s cold…and very lonely, because it’s wanting creation and nothing exists there as of yet.

Spend enough time there, and you may fear that you could die of it. Too much emptiness for the soul to endure; too much nothing. Until you hear (or see, or feel) it…the voice of one who has been there, too - endured it and survived to tell the tale.

I believe that we love artists most when they get us in touch with our inner selves in a way that the world outside us cannot. Much has been said about the price that they pay for their wondrous creations: loneliness and misunderstanding, addictions, emotional imbalance and depression, the life of a misanthrope. What are seldom conjectured, though, are the reasons for the artists’ sufferings.

Well, they’re living somewhere beyond the safely-defined place we call reality. They are the frontier explorers of the unknown. And that means that they exist in a place where they have nothing but their own intuition and insight to guide them…because they got there first! No one else has experienced what they have; these impressions are new, hard to fathom and even much harder to articulate. No wonder the visionaries of our world are oftentimes so lonely and desperate. Who can they confide in, and who can offer them condolences? Mankind may not catch up to the place where the artist succumbed until long after he or she is gone.

When we DO reach that place, we feel the chill too. But we have one consolation: the words or music or visual images bequeathed to us from one who has gone before. The art of our visionary creates a veritable map for us to find our way. We are no longer in the void where nothing makes sense; Art has given form to our world.

I never believed that art and science were at such odds with each other. Both are approaches to structuring our universe, and neither are strong and true for all time. Evolution continues - art offers up new inspirations, and science gives us new explanations. Joseph Campbell said that the motifs of myth had to be constantly reinvented, lest the life-giving forces slip away. Our artists keep that mythic fire alive so that it can be passed, intact, to succeeding generations.

And each generation must make its own journey into the unknown and face the long stretch of trials. Perils lurk in the wood, upon airy mountain heights and deep within the bowels of the Earth. But we will have help: the wise old guide, the maiden who knows the way out of the labyrinth…

And we’ll know that we’re embarked upon the only noble adventure left for modern man: the exploration of the inner world. Where else can we go? Everything has been settled and claimed, turned into megamalls and urban sprawl. We have to take the classic hero’s lead, and trust that the way within will illuminate the way out.

I doubt that our political leaders can offer us much in the way of good advice for that journey. But Tolkien provided me with some useful roadmaps, as did Stephen R. Donaldson, Arthur Rimbaud, Joseph Campbell, Rumi, Kandinski, and so many others.

Art is rain for thirsty souls.

Seth Mullins is the author of “Song of an Untamed Land”. Visit his complete blog at http://www.writingup.com/blog/seth_mullins

Original cartridges, filled with ink or toner, arrive in the box
with your new printer or fax equipment. These cartridges are
made by the same manufacturer or produced for that manufacturer
as brand new equipment and no parts have ever been used.
Happily, you set up your new equipment, install the cartridge
and begin to print or fax. After some time, the ink or toner
runs out and you have to purchase another cartridge. This is
where sticker shock can occur. The cost of toner cartridges or
ink cartridges can be quite expensive, especially if you print
or fax a lot.

Original cartridges are always a choice you can make when
purchasing equipment for your printer or fax. But they are not
the only solution. Non-original cartridges can be purchased at
quite a savings.

Non-original cartridges are available in two different types:
compatible cartridges and remanufactured cartridges. But what is
the different? There’s a great deal of different between the two
types.

Non-original cartridges are simply those made by a manufacturer
other than the original equipment manufacturer that fit your
printer or fax perfectly. These cartridges carry a different
brand name than that of your printer or fax machine but, if you
choose the cartridge that is clearly marked to be the correct
one for your equipment, these will work perfectly and cost less
than the original cartridges.

Remanufactured cartridges are actually recycled cartridges. If
remanufactured by a reputable company, these work just like
original equipment. This type of non-original cartridge is,
quite simply, an empty cartridge that has been cleaned, parts
replaced where necessary, ink or toner refilled, quality checked
and then the refreshed cartridge is packaged for sale. These
cartridges not only save the environment by spotting empty
cartridges from ending up in the landfill, but they can save you
lots and lots of money. If you choose a reputable company, these
cartridges are every bit as good as the original cartridges and
any reputable company will guarantee their cartridges, replacing
any that do not work correctly.

If you have ended up with stacks and stacks of empty toner or
ink cartridges hidden in your storage room because you don’t
know what to do with these empties, they should be recycled. You
can locate a cartridge remanufacturing company in your area and
drop off these empties, doing your part of help the environment.

If you have tried refilling ink jet cartridges, you already know
what a messy, messy job that can be and how often the process
doesn’t work right at all. Either the refilled cartridge dumps
ink all over your page or doesn’t feed ink at all. Non-original
cartridges, because of their low price, allow you to avoid this
hassle. It is so much easier to just remove the non-original
cartridge from the box and install it than to clean up after a
messy refill attempt that may not work anyway

The Following is an introduction to Spiritual Guidance Tarot Reading as it is taught in my upcoming book/class “Spiritual Guidance Tarot - A Journey in Self-Discovery”.

In my many years of reading and interpreting the cards I have discovered that the most effective readings tend to focus not on “fortune telling” but on inspiring and motivating people to seek their own truths within. I seek to show the individual methods they can use to unlock their fullest potential. Personal Empowerment should be the goal of any reading. .

In my time doing readings I have seen so many people who thought that I could just tell them what is going to happen. I know it is human nature to want to have all the answers, to be in control. In the past I often “abused” the Tarot myself attempting to effect answers and solutions in advance. The most important thing I tell people is that although many times difficult, life is a beautiful learning experience. Every decision we make, every thing that we do serves our growth. When we reach those low points of grief and anger we have to try our hardest to recognize them as a blessing. Yes a blessing, because without sorrow we would not be able to express or understand joy, without tears there would be no laughter. Life experience is merely a matter of choice. We can not change the outer world, but we can change ourselves and the way in which we choose to react to events. When we make a conscious attempt to do this we transform not only our own lives but also the lives of all we touch. The present and future is a beautiful gift given to each one of us to shape and mold as we will. When we attempt to shape this for another person; we overstep our boundaries. When we seek to show people different ways of looking at their present situation, we empower them to make better decisions for their futures. Empowerment is the best thing a reading can provide to someone who is Troubled rather than false hope

My spiritual guidance or what I also refer to as my personal empowerment spread consists of the following:

* The first position is the background, which points out the general areas of concern in the querrent’s present situation

* The second Position expands on this a bit by showing me what is motivating the individuals concern, what drives or compels their actions.

* The third position I use to point out fears and underlying problems that the person needs to address, things that block their progress.

* The fourth position I use to point out gently things that they may be overlooking or different perspectives to explore.

* The fifth position I use as an “action plan’ to point out ways in which the individual can move past the problems/fears, restrictions, and go forward with new awareness.

The Spread itself is laid out from left to right, shuffling and reflecting on the position meaning as each card is drawn.

Sometimes this reading is a little difficult to hear, as it shows us things we don’t always want to see. Our job as an effective reader is not to placate someone or simply say what we think they want to hear. Our goal should always be truth even if it is uncomfortable. The reason that is so is that it allows for growth for the individual, placating someone does not serve their highest good. Of course that doesn’t mean that one should be overly abrasive or condescending in any manner. When reading about a painful situation, try to understand why the querrent feels the way they do while still remaining objective. In the next lesson we will learn in detail how to do a Spiritual Guidance Reading for ourselves and others.

About the Author

Christin Snyder is a Spiritual Guidance and Personal Empowerment coach who offers a wide variety of free self-empowerment and transformational tools on her websites. Visit today at http://www.dailypowerwords.com or http://www.spiritualguidancetarot.com