Archive for March 15th, 2009
The following is an example of how to create an XML Schema in SQL Server.
To check your XML Schema:
Method 1: Go to your database > Programmability > Types > XML Schema Collections
Method 2: You can use the xml_schema_namespace function to query the schema from within SSMS
Later on I will post additional sample schemas which use different SQL XML data types.
I love Japanese Green teas. I am not a tea connoisseur but I can tell the difference between a good Japanese tea and a not so good one.
I think I started checking out tea stores and online tea shops 2-3 years ago. I have found a really good online Japanese tea shop, Hibiki-an, where I purchase my tea from. Yups, my tea is flown directly from Japan. They also used to have free international shipping if you purchase $30 USD (I think) or more. Right now though they increased that price to $38 USD (ouch).
My favorites are:
Genmaicha is a mellow blend of roasted and puffed brown rice grains and Sencha that has been well-loved by green tea fans in Japan for centuries.
Our Genmaicha Matcha-iri is a wonderful blend of Sencha, puffed brown rice, and Matcha. The Matcha powder lightly coats the Sencha leaves and rice, adding a fresh and mellow taste. Due to the rice, this tea has the rich and hearty flavor of roasted grains or coffee but the freshness of Matcha and Sencha. It is very well balanced, full flavored and smooth.
Added Matcha, it becomes mellow taste and its brewed water is light green color like Gyokuro’s.
In many cases Genmaicha is made from a mix of puffed rice and low grade Sencha, low grade Nibancha (tea from the second harvest of the year) or Sanbancha (tea from the third harvest of the year). Our Genmaicha Matcha-iri is made from only Ichibancha (tea from the first harvest of the year) and is available at a reasonable price. So we are certain that its taste and aroma is much more flavorful and smooth than any other Genmaicha.
Genmaicha Matcha – iri is enjoyed as a popular everyday tea in Japan, so we have priced it just right for your everyday enjoyment!
Sencha – the sencha is a very fresh, flavorful green tea, and the one from Hibiki-an is definitely the highest quality one I’ve tasted to date.
Gyokuro - it has a subtle sweetness and buttery taste to it. Yum yum. I usually get the Superior one, I have yet to try the Super Premium, but that will have to come later. The price point is way above my budget.
If you’re planning to get the teas, I suggest you also look at getting Chazutsu, a stainless steel airtight tea container that has both an outer and an inner lid. Perfect for keeping that tea freshness.
And nope, I’m not getting paid to blog about Hibiki-an I wish I was, or I wish I’d get some freebie yummy Japanese green teas. But I’m not. I just happen to love their teas.
One of my dev friends asked me how he can drop all the stored procedures in his test database.
One way to do it is by using a cursor to get the names of the stored procedures in a system table, INFORMATION_SCHEMA table or DMV, and then executing a dynamic query that drops the sproc.
This is a follow up post to : Why SQL Fragmentation Remains High
As mentioned in this previous post, our dilemma was we were rebuilding every index on a nightly basis, and :
- the process proves to be very resource intensive
- we don’t see the benefit. The fragmentation for some of the indexes remain high.
After some digging, this is what we found:
Rebuild the index only if there will be at least 1000 pages affected. If < 1000 pages will be affected, fragmentation will not really be affected and potentially would remain high.
This is the script that we now use to selectively identify which indexes to rebuild, which indexes to reorganize, and which indexes to leave alone. This script uses the following criteria in determining which action to take:
- current fragmentation %
- number of pages used by the index
The current logic is:
- reorganize index : if fragmentation is > 10 % but < 30% and number of pages > 1000
- rebuild index : if fragmentation is > 30 and number of pages > 1000
In one of companies I worked for, we used to do nightly index rebuilds. What was baffling is even after the nightly rebuilds, the fragmentation for some of the tables remain high.
I did some digging; here’s an explanation on why some index fragmentations remain high.
Both Paul Randal and Kalen Delaney (highly respected, very reputable SQL Server developers/trainers) suggest to rebuild the index only if there’s at least 1000 pages affected. If < 1000 pages, they say fragmentation will not really be affected and potentially would remain high.
I checked the "questionable" tables in our databases, and yes, Paul and Kalen were right on the ball. These pages had <1000 pages each.
- tableA has 9 pages and initial fragmentation of 875. After rebuilding, fragmentation flip flops between 66% and 77%, and never improves beyond 66%
- tableB has almost 95000 pages and initial fragmentation of 69%. After rebuilding indexes, fragmentation drops to 0.01%. Succeeding rebuilds keep fragmentation to 0.01%
This makes sense, because for smaller tables an index will not really help – SQL Server will usually prefer to do a table scan.
I will post the script that I used to determine and apply selective index rebuilds.
Follow Up: Script is posted in