A discussion on SQL Server
The first public CTP of SQL 2008 R2 (formerly known as Kilimanjaro) is now available for download from MSDN and TechNet. The CTP will be available generally on Wednesday For all details, see the post on the data platform blog
On Monday the finalists for the 2009 Exceptional DBA awards will be announced and voting will start to determine who’s the most exceptional of them all.
Visit the Exceptional DBA website from Monday to see who the judges picked as the most exceptional of the candidates and to cast your vote for the candidate you feel is the most exceptional.
May the best DBA win.
The only session that I attended today was Peter Willmot’s level 400 on Extended Events.
Extended events have potentially very low impact, less overhead than Profiler. In fact, in SQL 2008 there’s an extended events session running by default, a health check that has a small number of critical events that it traces Using Extended events needs good xml knowledge in the current version as there’s no built-in GUI. There are a couple 3rd-part apps available for download on Codeplex.
The key portions of Extended events are events (things that occur), actions (what to do when they occur), predicates (to filter to only the ones we’re interested in) and targets (what to do with the info)
Some of the targets (most) are async, some are sync. It is possible to allow events to be missed, in cases where there is too much happening and performance would suffer if all events were captured.
Best practices: Be careful with even retention. More events retained = more overhead. Rather go for the async targets as much as possible. Reduced the waits imposed on the SQL events. Target is async or sync. Predicates are always synchronous.
The closing keynote was way more interesting than the opening was. There were a couple upcoming technologies that they spoke about, then there was a talk on future directions in communication for Africa
AutoCollage. Take a picture library and automatically detect the most representative photos and generate a collage based on those. Face recognition, object detection, complex algorithms.
Natural user interfaces. Video on natural user interfaces. Starts off with stuff that’s not too implausible or too far ahead, gets a little strange towards the end. I have seen that video before, might have been at TechEd last year. After the video they showed that some of this is already in use at Redmond, specifically something called the ‘Executive Wall’, a multi-touch wall that shows information and allows interaction, drill-in and items to be moved around. Apparently Bill Gates demoed it at an executive conference recently.
Communication revolution. Talked about the potential and actual connectivity to the rest of the world. Apparently by 2011 there should be a potential 10 Tb/sec available to South Africa. Potential, because not all the cables will be active immediately. There also looks like there will be some amusing political problems. There are four major players in the whole game. Will be interesting to see how they work together, if they work together.
The future:
I just wish the future was here in terms of airlines. I was supposed to leave Durban at 17h40. Flight was full due to two cancellations and the new flight’s just been delayed. At this rate I’ll only be getting home tomorrow….
I started Tuesday my second presentation, on how to evauate Indexing Strategies. I think the presentation went quite well, it was almost standing room only which, for a whiteboard session is impressive.
The two sessions that I attended that I enjoyed the most today were on the upcoming SQL 2008 R2, Donald Farmer’s session on Project Gemini and Matt Hollingsworth‘s presentation of the new multi-server management features in Kilimanjaro.
Donald Farmer talked about the prevalence of Excel as an analytics tool and the enhancements coming in the Gemini add-in to allow more data and to make it faster. He demonstrated sorting and filtering on 101 million rows in seconds. Also lots of stuff on slicers, filters that are aware of each other, as well as the integration with sharepoint. Looks exciting.
Matt showed the new management features of Kilimanjaro, a utility server that monitors and reports on other servers so that it’s easy to see what servers are under or over utilised. I took a couple photos of the dashboard.
The community lounge was rocking today. Literally. Some of us really shouldn’t give up day jobs.
http://www.youtube.com/watch?v=fgjjvrBvsgI
http://www.youtube.com/watch?v=8phK68a03oQ
The closing party was good, and good food too. Always a bonus. Kinda reminded me of the opening party last year, same kind of atmosphere.
The first day of TechEd is over and, as usual, I feel like I’ve stood under an information waterfall. The first session of the day that I attended was a Sharepoint session giving an overview of Sharepoint 2007 and the roadmap to 2010. Actually, in all honesty it was more an overview of Sharepoint 20047 and very, very little on 2010. Probably because 2010 is still in development and most of the details may not be publically released.
There were a few useful take-aways from that session.
* There’s a Sharepoint best practice guide available at http://technet.microsoft.com/en-us/office/sharepointserver/bb736746.aspx
* There’s a community kit add-on for document libraries that adds ratings and download counts.
* Only Sharepoint 2007 SP2 will be able to upgrade to 2010. There’s an upgrade advisor check in SP2
* Sharepoint 2010 is 64 bit only and is expected first half of 2010
* Some 2010 features: context sensitive Ribbon bar, direct edit in the web parts, easy way to change and upload images, out of box Silverlight webpart
I spent most of the rest of the morning sitting in the community lounge and taking part in the TechEd Online interviews.
After lunch I went to a session on Tips and tricks of Entity Framework.
Some of the things covered there were on the usage of foreign keys within entity Framework and how to get and set foreign key values without retrieving the whole related object. The presenter also showed how to get the exact SQL statement that that the Entity Framework will execute and how to generate alternate queries and how to do granular exception handling fpor when the call to SaveChanges throws an error
Monday ended with a session that I presented on Query Hints and Plan guides. I understand from a couple people that attended that perhaps the reason for chosing to use hints wasn’t clear so, for anyone reading the blog, I’ll make sure I do a couple blog posts covering that, maybe next week.
The TechEd keynote this year had some exciting moments, and unfortunately far too many boring moments.
The Master of Ceremonies was good enough, but several of the demos were a little unexciting and far too long. Probably one of the best demos was Lynn Langit’s demos on Windows 7, several very short demos of new features in Windows 7, including the multi-touch capabilities
The other thing that got a lot of applause was a video showing Project Natal, as part of a discussion of natural user interfaces.
Donald Farmer’s demo of Gemini’s BI add-in was quite impressive, showing the analysis and graphing of 100 million rows in just a couple of seconds.
Up next, the opening party. It was supposed to be a street party, but it’s pouring with rain so the party’s been moved indoors. Still should be good.
Tomorrow the technical stuff starts. For now, some photos from the keynote.
Here’s the slide deck and demos for Shaun’s Table Partitioning talk
This one comes up from time to time, so I thought I’d have a go at addressing it.
Let’s imagine a hypothetical DBA who’s doing some performance tuning. He looks at a query plan for a moderately complex query and panics because there’s a couple of index scans and he wants to rather see index seeks.
Is that correct, are index scans bad and index seeks good?
Well, most of the time yes. Most of the time a scan is a problem and indicates a missing index or a query problem, but there are other times that it’s the most optimal way to get the required rows from the table.
I’ve previously looked at the case where the index seeks actually reads the the entire table, in this post I’m going to be evaluating some common query constructs to see when a seek really is the most optimal operator.
Let’s start with the simplest case, and I’m going to use the AdventureWorks database for these queries.
[source:sql]select ProductID, Name from Production.Product[/source]
In this case I get an index scan on the AK_Product_Name index and that makes perfect sense. I’m asking for all the rows in the table. there is no way that SQL can use a seek to execute that query. For there to be a seek, there has to be a SARGable predicate within the query that can be used for the seek.
The July meeting of the South African SQL Server Usergroup will be held Tue 21 July, 18h30 at the Microsoft Offices in Bryanston, 3012 Willam Nicol Drive.
This month’s meeting is sponsored by Ascent and Shaun Wickstrom will be talking about Table Partitioning
Look forward to seeing you there.
With the introduction of the DMVs in SQL 2005, there’s now a wealth of information available on every aspect of SQL’s behaviour. It’s possible now to interrogate the SQL procedure cache to find out what queries have been running and how well or badly they are performing. So does all this mean that SQL Trace is now obsolete?
It does not. The execution stats available through sys.dm_exec_query_stats are only retained while the plan for the query is in cache. As soon as the plan is removed from cache (for whatever reason), the query stats for that query will be discarded. There are also a couple of reasons for a query’s plan not going into the cache at all.
Let’s try a quick example. I’m going to use the AdventureWorks database (SQL 2008), because it’s convenient. I’m going to create 4 procedures, run them several times, along with a couple other commands and then compare what a trace shows and what a query of the plan cache shows.