SQL blog by Luke Jian
I am honored to preview one of my sessions for PASS Summit 2014 – A Tale of Two Joins: Merge vs. Hash to the Chicago SQL Server Users Group at the Microsoft Technology Center, 200 East Randolph Drive, Suite 200 Chicago, IL 60601.
It is a true honor to present in front of my own user group
where I learned so much in the last 7 years.
Look forward to see everyone there!
I will be presenting my session on “Optimizing SQL Server I/O with Solid State Drives” at SQL Saturday #119 in Chicago on Saturday May 19, 2012!
Since this is the 4th time I’m presenting this and I kept accumulating a lot of ideas related to Solid State Drives I decided to start a series of One SSD Technology a Day. Stay tuned!
Location is:
DeVry University – Addison Campus,
1221 North Swift Road, Addison, IL 60101-6106
View Larger Map
SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. Admittance to this event is free, all costs are covered by donations and sponsorships. Please register soon as seating is limited, and let friends and colleagues know about the event.
If you are attending please stop by and introduce yourself. Also if you are on Twitter don’t forget to use hashtag #sqlsat119 and see what others are doing.
Hope to see you there!
I’ve been meaning to write my blog post on last week’s SQL Saturday experience so finally I got to work so here it is:
After the previous weekend marked a record with 5 (five) SQL Saturday events all around the world on Apr 14 it was time for Wisconsin to show what they can do. And boy did they put on a great event. The organizing team did a fabulous job, helping 227 hungry minds learn about SQL Server from 33 speakers in 36 sessions organized on 6 tracks. They almost made it look easy.
I remember 2 years ago meeting the person responsible for this at SQL Saturday #31 in Chicago. Back then Jes Borland (b|t) had traveled 160 miles to volunteer at the event. She enjoyed it so much that she came back the next year as a speaker and a year after that with the help of her great friends from MADPASS , she put on an epic first time event! This goes to show a how important a single person can be in the SQL Family and anyone who thinks that they will never be able to raise to the level of some of the speakers has to learn a lesson from Jes. Every marathon starts with a first step (and she will run one in Kenosha the next week too ) and every little helps.
I left work Friday afternoon, after a pretty busy day with Vince (b|t) and we headed north to the land of beer and cheese. We got to Madison, checked in at our hotel and shortly after I was headed to the Speaker Dinner. The atmosphere was great, beer was good and the lasagna was too much for me to handle in one sitting. The highlight of the night was the paper airplane fight started by Aaron Lowe (b|t) and continued in part by me. We had a lot of fun unwary that above our head was a clothesline with underwear and shirts to complete the Italian experience.
The next day we woke up bright and early, had a frugal breakfast at the hotel and headed to Madison Area Technical College for the event, registered, grabbed some coffee and a bagel and after finding the speaker room we chose Mike Donnelly’s (b|t) SSIS: Figuring Out Configuring session, his first SQL Saturday presentation ever. He did a great job with a demo packed session that had a very good audience.
Next we decide to take Erin Stellato’s (b|t) DBCC Commands: The Quick and the Dangerous. She did a great job condensing all DBCC commands in one session. I could not help not to drop the ” unicorns will die if you shrink the database” slogan and the room liked it. The same room hosted an extraordinary fun session on what happens when you think outside of the reporting box. Stacia Misner’s (b|t) (Way Too Much) Fun with Reporting Services was spiced up with a lot of humor with the help of her daughter Erika Bakse (b|t) . They started playing Words With Friends inside a SSRS report and then went under the covers to explain how it is done and mainly how to use Report Actions in SSRS to create interactions inside a report.
It was lunchtime and the menu included tasty brats, burgers and Cows of a Spot tables, the Wisconsin version of Birds of a feather. I sat at the Data Profiling table and had a great conversation with Ira Whiteside (b|t) and his lovely wife Theresa Whiteside. They where very kind to share with me and Vince some of the experience accumulated during the course of their vast career working with Data Profiling and Data Quality as well as some of the methods that were presented in Ira’s session that started right after lunch. It was a extremely interesting session titled Creating a Metadata Mart w/ SSIS – Data Governance and we learned a lot from it.
It was time for my session on Optimizing SQL Server I/O with Solid State Drives a session that seems to be very popular (same session was selected for SQL Saturday #119 in Chicago as well at Washington DC and Tampa before). I had some great feedback that I will try to use to improve the format and add some fresh content (whiteboard on TRIM, Wear Leveling and Bad Block Management). I had the honor to have in the audience Norm Kelm (b|t), Craig Purnell (b|t) and Matt Cherwin (t). We did an impromptu drawing for a Ted Krueger’s book (signed by Ted himself) and we stayed in the same room for the last session of the day with Sanil Mhatre (b|t)on Asynchronous programming with Service Broker . He did an excellent job and it was his first SQL Saturday presentation ever.
It was a great day and an epic event by MADPASS which made me look forward to the next SQL Saturday in Madison.
This is the second time I’m writing a post in the “Meme Monday” series and this time it originates from Tom LaRock (blog | twitter).
The topic is : What #SQLfamily means to me.
This weekend I participated in a #SQLfamily reunion in Washington, DC. Some might argue that this is called SQL Saturday #96 but I prefer to see it as a family reunion because this is how it felt. It started with a #SQLfamily dinner where we had a good time told stories (some would even make a good Agatha Christie book)… but I digress. We all sat around the table and felt like we are part of a family. I met Tom LaRock, Joey Dantoni, Karen Lopez, Andy Leonard, Brian Moran, Aaron Nelson, Matt Velic, Chris Skorlinski, Wayne Sheffield, Sebastian Meine and many others. I truly felt like a family reunion and it was.
On Saturday we continued, had fun presenting our sessions, learning new things, hanging out in the speaker room and talking about all kinds of things. Everybody contributed to making this #SQLfamily reunion an event to remember. Tom even offered to make coffee and but had no idea what to do with the extension cord. (…after the last power outage they had in New England you would think he should know better 🙂 )
Looking back I realize that every #SQLfamily event I have participated to has made me feel like this whether it was a SQL Saturday in Indianapolis or in Chicago or a user group meeting I never felt left out. I had my brothers and sisters and uncles and aunts and they made me feel like I am part of the #SQLfamily.
And the best part is that our #SQLfamily is growing every day. Every reunion we discover more people we can relate to even though we are not related (but in a way we are).
God bless our #SQLfamily!
I am deeply honored that I was chosen to present 2 sessions at SQL Saturday #96 in Washington, DC this Saturday, November 5, 2011!
Location is:
Microsoft MTC
5404 Wisconsin Ave, Suite 700
Chevy Chase, MD 20815
Subway: Friendship Heights Metro Station
SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. Admittance to this event is free, all costs are covered by donations and sponsorships. Please register soon as seating is limited, and let friends and colleagues know about the event.
If you are attending please stop by and introduce yourself. Also if you are on Twitter don’t forget to use hashtag #sqlsat96 and see what others are doing.
The 2 sessions I will be presenting are :
Optimizing SQL Server I/O with Solid State Drives and Join operations – Explaining Merge,Hash and Loop
Hope to see you there!
Update: 11-09-2011
I have uploaded the slide decks to the session pages:
Thanks to all that attended and don’t forget to leave feedback at:
http://speakerrate.com/sensware
Last week Erin Stellato (blog| twitter) has asked a very interesting question on Twitter that triggered this post:
So how is it you can have good perf in Oracle, w/o IOTs, but in SQL Server, everyone says need CIs for good perf?
There are two aspects to that question
Until now I have not realized that this subject is not very well covered and since it involves diving into the internals I offered to make an attempt to fill this gap over the Memorial Day long weekend and soon realized that there is more to this than I originally thought it is. This will be the first post in a set that I am planning to write in the next weeks.
First, let’s begin by comparing the different types of indexes supported by Oracle and SQL Server:
| Type of index | SQL Server 2008 R2 | Oracle 11g |
| B-tree indexes | Yes (Non-clustered) | Yes (Default) |
| B+ tree indexes | Yes (Clustered Indexes) | Yes (IOT) |
| Function Based Indexes | Yes (Column has to exist in table) | Yes |
| Filtered Indexes | Yes | Yes |
| Hash cluster indexes | No | Yes |
| B-Tree cluster indexes | No | Yes |
| Bitmap Indexes | No | Yes |
So both Oracle and SQL Server support B-tree indexes which are ordered lists of key values, associated with the storage location of the table record that contains the respective value. So far it doesn’t seem to be a big difference between the two RDBMS. Both also support B+ tree indexes although there are differences between the two implementations. B+ trees are similar to B-trees but the table records are stored in the leaf nodes of the primary key index. This allows fast access for singleton searches (exact match) or range searches on the primary key of the table. Microsoft calls them clustered indexes (CI) while Oracle uses the term Index Organized Tables (IOT).
Let’s look at non-clustered indexes which are the default indexes in Oracle. Here the two implementations are quite similar: Indexes are using a B-tree structure that contains the key values and a unique reference to the storage location of the record. This is where the implementations start to separate:
In Oracle every record has a unique (inside the table) 10 byte pseudocolumn ROWID representing the physical location on disk that the record is stored. When printed, each ROWID is displayed in BASE64 format (A-Za-z0-9+/): AAAAaoAATAAABrXAAA
ROWID has a four-piece format, OOOOOOFFFBBBBBBRRR:
OOOOOO: The data object number that identifies the database segment (AAAAao in the example). Schema objects in the same segment, such as a cluster of tables, have the same data object number.FFF: The tablespace-relative datafile number of the datafile that contains the row (file AAT in the example).BBBBBB: The data block that contains the row (block AAABrX in the example). Block numbers are relative to their datafile, not tablespace. Therefore, two rows with identical block numbers could reside in two different datafiles of the same tablespace.RRR: The row in the block.Users can use ROWIDs directly for several functions:
There are two completely different perspectives when migrating from one RDBMS to another:
Most Oracle users have no concept of clustered or non clustered indexes because primary key indexes created in Oracle are balanced non-clustered indexes that include ROWID for fast access.
A common mistake that Oracle users make is assume that the indexes are implemented the same way in SQL server and underestimate the performance hit that comes with using a large composite primary key in SQL server on a Clustered table. It takes a while to understand that the non clustered index size index size in SQL server can be exponentially bigger in SQL server and is not necesarly proportional to the size of the columns specified in the non clustered index. Based on this, some Oracle experts say that the clustered indexes in SQL Sever have a 110% overhead because index key is stored “twice”, once with the index entry in the intermediate index nodes – and again with the row data at the leaf level.
Another performance hit can come from the default value of the free space (PCTFREE) that is left on a page as Oracle leave by default 10% space on each block (at 91% block is split). After a data block is filled to the limit determined by PCTFREE, Oracle considers the block unavailable for the insertion of new rows until the percentage of that block falls beneath the parameter PCTUSED.
The alternative of using a non clustered table (heap) is not recommended in most cases and Paul Randal ( blog | Twitter ) has some excellent posts on this subject as well as Erin’s recent blog post on Internals and Deletes for SQL University which I highly recommend reading.
On the opposite camp, most SQL Server users are looking for an alternative to clustered indexes and end up choosing to use Index Optimized Tables (IOT) instead of using simple tables just because they search for something that has the same structure as clustered tables in SQL Server in order to have comparable performance.
In the next part of my series I will continue with access methods in Oracle, and other index types.
Today Mladen Prajdic posted a on Twitter a SQL statement that captured my attention: (http://twitter.com/MladenPrajdic/status/9878219727)
SELECT CONVERT(varchar(50), N'æøåáäĺćçčéđńőöřůýţžš') COLLATE Cyrillic_General_CI_AIreturns aoaaalcccednooruytzs
If you notice the conversion to Cyrillic_General_CI_AI collation his shows an interesting mechanism for code page translation that we could not explain:
Why does Cyrillic_General_CI_AI or Greek_CI_AI collation successfully convert accents to their base ascii chars?
I found an answer to this on this KB page: http://support.microsoft.com/kb/153449
What Happens when a Character on One Code Page is Unavailable on Another
If a conversion occurs and a character on one code page is unavailable on another, the character gets converted to its closest equivalent character on the other code page. For example, ASCII 224 (“alpha”) on code page 437 is converted to 97(“a”) on the ANSI code page. When you convert it back to code page 437, you receive 97(“a”). This is a problem, because you have now lost the original 224(“alpha”). This can potentially cause incorrect data to be saved on your server, as happens in the following two examples:
Example 1:
You have an OEM server in which you are inserting data from an ANSI client, and your AutoAnsiToOem option is enabled. If the character that you are inserting is valid in both code pages, the correct data will be saved. However, if the character is valid in ANSI but not in OEM, the correct data will not be saved.
Example 2:
You insert data into an ANSI server from an OEM client, and the AutoAnsiToOem option is enabled. If the character that you are inserting is valid in both code pages, the correct data will be saved. However, if the character is valid in OEM and not in ANSI, then correct data will not be saved.
Nevertheless I this is very useful to exotic strip accents from strings (i.e before an export).
After attending a presentation on SQL Azure by Microsoft’s George Huey an interesting topic came to my mind in regards to the actual use for a cloud based database that has over 1400 incompatibilities with SQL Server: What’s the use for this since it is not a viable alternative to a production database.
I would say that it is an alternative to MS Access!!!
Let me explain that. I definitely see the use for it when you need to deploy a solution quickly for an event or conference and you need a reliable solution for the backend of your application that fits a tight budget and an even tighter schedule or even as George suggested for a startup that has no money for a datacenter hosted db environment.
in these situations deploying a 1GB database to host non-confidential information for under $10/mth seems a no-brainer. But I would not recommend this when HIPAA compliance is required for instance.
My thanks to George for a great presentation.
I’m heading home from the August session of Chicago PASS and SSUG meeting.
First off, this was the biggest meeting in Chicago. Over 80 people signed up and for a very good reason.
After a very interesting Microsoft SQL Server 2008 Administration with Windows PowerShell presentation by The MAK and Yan Pan, Allan Hirt managed to cram the essentials of the new MS Failover Cluster Manager and he made it in less than an hour!
One very interesting and long awaited feature is Live Migration. Basically for the first time you can failover a MS Cluster without dropping any transaction. This is only happening for planned failovers of course but this is a phenomenal achievement for the MS team. Until now any time you would failover a cluster, the operation was the equivalent of stopping the SQLServer service an restarting it on a different machine. Obviously any pending transactions were dropped. With Live Migration the when a planned failover is initiated data from memory is copied over to the new active node and all transactions are seamlessly ported over as well. There might be some performance decrease but no transactions are dropped achieving true High Availability.
And last but not least all this is free with Windows Server 2008 Enterprise or Datacenter
For more information read this blog from Microsoft.
http://blogs.technet.com/virtualization/archive/2009/05/06/microsoft-hyper-v-server-2008-r2-release-candidate-free-live-migration-ha-anyone.aspx
Thanks Allan!
Eduard gives us the following problem:
The result is that every 4 years for a day you realized the money spent on a Zune are wasted
http://www.engadget.com/2008/12/31/30gb-zunes-mysteriously-begin-to-fail-at-12am-december-31st/
Now for the Microsoft solution (Please refrain from ROTFL :-D! ):
Follow these steps: