SQL Centre of Excellence

One thing people come to us for is advanced performance tuning. No I don’t mean adding indexes to remove table scans I means complex issues involving either more sophisticated resource bottlenecks or problems with huge scale.

With this in mind we have two advanced tuning events coming up in November:

  • The first three day workshop is a deep dive for performance tuning and troubleshooting techniques on the database engine and is run by internationally recognised SQL Server expert and certified MCM Klaus Aschenbrenner.This is the first time Klaus has come to Ireland to present his popular performance tuning workshop. Level 300+
  • The second one day seminar is covering Super Scaling SQL Server. As the topic suggests this is not for the feint hearted. You’ll probably need either heave good experience with SQL server troubleshooting or have attended some of our other advanced performance sessions to get the most out of this session. Thomas Kejser is undoubtedly one of the greatest minds in the SQL server technical community when it comes to scale and performance. Level 400+

-----------------------------------------------------------------------------------------------------------------

SQL Server Performance Tuning & Trouble Shooting Workshop (3 days)

with Klaus Aschenbrenner , November 14th – 16th 2012, Dublin

Cost – €1,799

Registration: http://www.prodata.ie/events/2012/SQL-Performance_tuning_workshop.aspx

We all know the situation only too well, Friday evening and the weekend is flirting with you promisingly ….. then the dreaded email arrives telling you that your SQL Server has enormous performance problems.

What can you do to identify the problem and resolve it quickly? Which SQL Server tools will help you rescue your free time, or in the absence of a good weekend plan, can help you free up time for more fulfilling and proactive tasks?

This course is delivered by Klaus Aschenbrenner, author of Pro SQL Server 2008 Service Broker. Over the course of this 3-Day workshop Klaus will:

- Delve into the most prominent performance bottlenecks inside SQL Server

- Show you the different tools at your disposal to enable effective SQL Server performance troubleshooting.

- Teach you how to identify and resolve performance bottlenecks quickly

********************************************

Super-scaling SQL Server (1 day)

With Thomas Kejser, November 23rd 2012, Dublin

Cost - €799

Registration: http://www.prodata.ie/events/2012/SQL-super-scaling.aspx

This course is not for the faint-hearted…. In fact you could describe it as performance tuning on steroids. In one intense day, Thomas will take participants beyond indexes to the next steps in optimising the performance of their SQL Servers. It aims to take you beyond best practices, below the surface into the realm of measurement and KNOWLEDGE about why your implementation works or doesn’t.

Formerly of the SQL CAT Team, Thomas is now CTO of Fusion IO. Building multi-terabyte- sized data warehouses, setting up OLTP systems for extreme scale and tuning world record data movement speed are some of Thomas’ recent achievements.

The course is level 400+ and targets the sweet spot between designing for performance and maintaining performance and is therefore relevant to Architects, Developers and DBAs alike, and as for Consultants… you will be guaranteed a brand new power-tool in the tool box

The second in the series of the 2012 master class series is this Friday 26th October 2012 covering Mission Critical availability with a guest speaker, Microsoft Certified Master, and friend of Prodata: Gavin Payne.

http://www.prodata.ie/Events/2012/SQL-Mission_Critical.aspx

For those of you who have been to previous sessions you know what to expect. We plan to bring you expert level content and access to some of Europe's best SQL consultants covering popular topics relating to SQL Server. The session is packed into half a day to ensure that you can still get professional development and not miss too much of that precious work time. Super important if you are a billable consultant.

If you are thinking of planning or implementing high availability then this is a session you should not miss.

See you there !

Firstly, Hello all!

I’ve decided that after years of learning and “borrowing” ideas, info and techniques from other peoples blogs, it might be about time

I put one up and share some of the interesting, useful or just downright odd things I have come across in my years working in IT and particularly with SQL Server.

Hopefully you will find some of these blogs useful, interesting, or at the very least give you something to argue with me over!

Back Story – I work as a SQL Consultant for Prodata, who are the top SQL Server & BI consultants in Ireland (shameless plug!) .

My main area of expertise is as a production DBA, but I also dip into BI, SSIS, Office (well, excel) a little virtualization and general IT work.

 

Today’s blog is concerned with SQL Server Views, or more the issues that are encountered when using views, and how to sidestep them.

 

For this example I am using a subset of the DimCustomers table in AdventureWorksDW2012, available on codeplex. It’s a contrived example but gets the message across.

On running a select * from our Customers table, the results look like this:

Imagine we want to expose this table to certain users/groups via a view. This is something we do pretty regularly when exposing Data Warehouse tables via views to help minimize effect of base table changes on reports:

Create View CustomerInfo

AS

Select from Customers 

GO

It’s probably better to try not to use select * in views in general, but during development it can save a lot of time when schemas are changing regularly.

Running a select * from the view returns the results as shown previously, the view is after all just a select * from the underlying table.

 

Now, we have a request to alter the Customers Table, so that TotalChildren will be before the BirthDate column, Phone will be before AddressLine1 and LastName will be changed to Surname:

 

Pretty easy change right? But then look what happens if you execute the view:

 

The column data for TotalChildren & Phone is in the correct position, however the column headers are completely incorrect!  The birthdate column has the data for TotalChildren underneath it, AddressLine1 has the Phone number data underneath it and LastName has not been updated to Surname!

 

So what has happened here?

A schema change to the base table does not force a metadata update on the view. If we query the view metadata we see the column order and names reflect those of the table before the table schema change:

The issue is easily resolved by one of two ways:

·        Drop and recreate the view

·        Execute sp_refreshview

Sp_refresh view is usually the preferred solution, and can be run against a specific view or against all views.

This issue can be completely prevented by creating the view with schemabinding, however it can become quite annoying during development to have to remove schema each time a schema change is required, and a refresh of the schema metadata is still required,

however this will happen when you alter the view to re-add schemabinding.

 

Attached is a short script to demo items discussed.

 

That’s the first blog out of the way! Phew! Any questions, please just give me a shout.

Fintan.

view_metadata_issues_examples.sql (1.71 kb)

 

Page List

Page List