Simons SQL Blog

Don't forget Saturday its your last chance to get the early bird discount for the SQLBits training days

Advanced TSQL and performance tuning for scalability with Simon Sabin Understanding TSQL performance is crucial to building a successful scalable database solution. In this session we will cover the fundamentals of performance tuning your SQL to ensure your application can scale.  We will use query plans, sql profiler, dmvs, resource utilization and contention to diagnose performance and scalability issues. We will then look at using advanced TSQL techniques and indexing to show you how to improve your performance and scalability.
Introduction to MDX with Chris Webb If you want to get the most out of your Analysis Services cube it’s essential to know and understand MDX: you need it to be able to write queries and calculations, and also to be able to use more advanced features such as cell security. This one-day seminar, aimed at those with little or no previous MDX experience, will teach you the basic concepts of the language as well as show you how to write common queries and calculations.  The seminar will be taught by Chris Webb.
Advanced Troubleshooting Workshop for SQL Server 2005 with Christian Bolton The Advanced Troubleshooting Workshop for SQL Server 2005 provides attendees with SQL Server internals knowledge, practical troubleshooting skills and a proven troubleshooting methodology.   The workshop will enable attendees to tackle complex SQL Server problems with confidence.
Implementing and Maintaining Reporting Services 2008 with Chris Testa-O'Neil This seminar will provide you with the information required to implement and maintain reporting services . You will start by exploring the different installation options for reporting services. Create compelling reports, manage and distribute the reports. You also explore how to manage the reports effectively and maintain the reporting server

With so much being online nowadays everyeon wants to build an online community.

Julie has some interesting points covered in this blog http://evolvingnewsroom.co.nz/online-community-management-101.

Its almost like becoming an MVP. you should strive to be an MVP you should do what you do. If what you are doing is the right thing, then it will happen.

Clearly there are somethings that you shouldn't do and Julie's article covers some of thos nicely.

If you've been trying to register on the site today you will have notice a problem. The site wasn't available.

We had a slight problem with our hosting which has now been resolved.

You can now register by going to http://www.sqlbits.com/beta/information/Registration.aspx

It always surprises me how many ways you can write a query and how the different ways can have an amazing impact on the performance of your queries. Often unfortunately the simplest solution doesn't perform the best. I will be running a training seminar before SQLBits on how you can write your queires and index your database to get the best performance out of SQL Server. I will also be focusing on scalability. Its very easy to write a query that runs for one user, understanding how queries scale is essential to ensuring your application doesn't grind to a halt when the numbers of users increase.

Most of what I will cover will apply to SQL2000 as well as 2005 and 2008

To register go to http://book.registrationisopen.com/book.aspx?cust=sqlbits&eid=sqlbits20090301ss

Overview

Unlike many languages, SQL is a language where you don't explicitly control how the code is executed. With SQL you define intent and then the optimiser builds a query plan based on that. We will look at differing ways in which queries can be written, and how some of these can have an amazing impact on the performance and scalability of your system. To achieve this we will look at the tools we can use to achieve this including profiler, query execution plans and DMVs

We will extend this knowledge to understand how the optimiser builds a query plan and how parameters and indexes affect the query plan chosen. This will give you the knowledge to know when query plan resuse is good and bad, how to avoid or benefit from parameter sniffing and ultimately write queries that perform better.

Why Attend

After attending this course you will understand how you can write your queries and index your tables to get the best performance.

Topics

Understanding Resources 

• CPU
• IO
• Memory

Diagnosing performance

• Profiler
• Query Execution plans
• Dynamic Management Views
• Perfmon

Advanced TSQL

• Dynamic search conditions
• Ranking functions
• Windowed aggregates
• Use of cursors
• Scalar functions

To register go to http://book.registrationisopen.com/book.aspx?cust=sqlbits&eid=sqlbits20090301ss

The mapping services from microsoft aren't limited displaying maps in Virtual Earth, and not only limited to websites.

There are numerous webservices for,  resolving locations to longtiudes and latitudes as well as a routing api.

I am working on something for SQLBits to enable people to lift share. For this I am using the routing API. This is very very easy to use, its just a webservice which means we can use it from a website or from a windows application.

I'm then putting the routes in the database, so I need to convert the set of points that make the route into a SqlGeography type.

This I thought would be easy using the builder (SqlGeographyBuilder) (As shown with the post SQL 2008 and Virtual Earth : Converting a VERoute to a SqlGeography) but I found that I was getting an error saying the geography was invalid.

I thought of a few things but couldn't find the answer. I pinged Johannes Kebeck (http://johanneskebeck.spaces.live.com/blog/) and he reminded me that even with lines, the set of points can be invalid if the intersect i.e. like an ampersand &. Now I had thought of this but the Geography type doesn't have a MakeValid function which will make a geometry type that is invalid into a valid shape that doesn't overlap. So I thought self intersecting geographies where fine. After speaking to Johannes I changed my code following is suggestion, whilst there is no MakeValid for SqlGeography you can just use the SqlGeometry type, use the MakeValid function which gets your points in the corect order, and then convert to binary format (WKB) and create a SqlGeography instance from that WKB. This works because the WKB doesn't have any knowledge of the base type, it just describes the points of a shape, this applies to WKT as well i.e. LINESTRING(0 0,0 10,10 82, 10 11) is a valid format for a Geography type as it is a Geometry type. The only difference is that for Geograpahy types you are limited to the values of the latitude values (-90 to 90).

After all that this is the code to convert a route from the virtualearth route service to a SqlGeography.

RouteResponse routeResponse = routeService.CalculateRoute(routeRequest);

SqlGeometryBuilder gb = new SqlGeometryBuilder();
gb.SetSrid(0);

gb.BeginGeometry(OpenGisGeometryType.LineString);

bool start = true;

foreach (var rt in routeResponse.Result.RoutePath.Points)

{

    if (start) { gb.BeginFigure(rt.Longitude,rt.Latitude, rt.Altitude, null); start = false; }

    else

    {

        gb.AddLine(rt.Longitude,rt.Latitude, rt.Altitude, null);

    }

}

gb.EndFigure();

gb.EndGeometry();

SqlGeography g = SqlGeography.STGeomFromWKB(gb.ConstructedGeometry.MakeValid().STAsBinary(), 4326);

[Update 2009-02-22 SS: Due to the nuance of the the way MS defined Lat and Long in the early stages of SQL2008 the positions for Lat and Long are sometimes reversed. In this case I had to reverse the Lat and Long values being passed to the builder compared with passing them to the GeographyBuilder, if you find your routes seem to be rotated by 90 degrees this is the problem]

Event Summary

Business intelligence is even more important during these tough economic times. People have to make really important decisions and critical strategic moves.

In this one-day seminar, Rafal Lukawiecki aims to show IT Managers and BI practitioners how to improve decision making and getting the valuable business insight by using Microsoft solutions. This seminar will look at an end-to-end approach to building and delivering a Microsoft BI solution, from automated data integration and consolidation to delivering insight in reports, scorecards, and Excel.  To make this seminar easier and more pleasant to follow we structured it so that the first half focuses on the goals of BI, performance management, and the knowledge worker. The second half will stress the underlying technologies and the development and deployment processes.

Thursday 26^th March
Cardinal Place, Auditorium 1&2, 100 Victoria Street, London SW1E 5JL

Not a SQL thing at all but something I am sure I will forget. When using some controls one often finds that style settings are made at the element level which overrite any css setting you put in place because anything specified explicitly on an element takes precedence

Well in getting help for the telerik scheduler component I found out that if you put

!important;

 after your style element in your css it overwrites any element level settings. Not sure how it works in the hierarchy of css but thats for another day

The NxtGen guys have got their next venue going. Its in Hereford.

I'm fully aware not much gets out to that area of England, having been brought up just a bit further north, in Shropshire.

Where's Shropshire people ask. The easiest answer is "Between Birmingham and Wales" not the most attractive of descriptions. As for Hereford its a bit further South.

So if you are into .Net, SQL, Microsoft development generally get your self along to the NxtGen Usergroup. 

The first meeting to be held at NxtGenUG Hereford will be on 17th March 2009 at 7.00-9.00 in the Shirehall Hereford.   If people wish to register for the meeting which is free to all comers, go to http://www.nxtgenug.net/ViewEvent.aspx?EventID=203. UK Stalwart Guy Smith Ferrier will be speaking, and Dave McMahon will be doing a quick nugget about NxtGenUG and what benefits there are in getting involved in the UK Community as a whole!

Its very last minute but we have got a usergoup meeting this Thursday http://www.sqlserverfaq.com/events/152/SSIS-Tips-and-Tricks-Lessons-learnt-using-LINQ-and-LINQ-to-SQL.aspx.

We've got a great session on SSIS and also one on LINQ to SQL and LINQ based on my experiences with the SQLBits website

We've already hit 330 registrations of which 30 are for online viewing.

We still have more places but make sure you register quick to avoid disappointment.

If you are in Manchester have you seen there is a usergroup meeting startin up. The first meeting is on next Thursday (26th Feb)

http://www.sqlserverfaq.com/events/149/Tony-Rogerson-talks-on-Bits-and-Bobs-to-help-the-developer-and-DBA-with-TSQL-Martin-Bell-looks-at-Table-Valued-Parameters-new-to-SQL-2008.aspx