Simon Sabin UK SQL Consultant's Blog

Shocking news for SQL 2008 R2

simonsabin — Wed, 04 Nov 2009 17:07:00 GMT

News about SQL 2008 R2 brings some amazing news. On the bad news, Enterprise edition will only support 8 sockets (the view that with 8 cores coming you will still get 64 cores) In addition you no longer get unlimted vritauliastion with Enterprise edition...(read more)

How to get the most out of 96 cores

simonsabin — Mon, 02 Nov 2009 09:44:13 GMT

Henk van der Valk is a consultant for Unisys. If you've never heard of him then he is one of the guys that worked on the ETL world record for loading a database last year. This was done with 64 cores.

Now with support for 256 cores, Henk got his hands on a 96 core server and showed you what you could achieve with such a server.

Have a look at this video to see what Henk could come up with http://www.youtube.com/watch?v=2qjrq8kpZUs

If you are really interested in SQL Server performance then make sure you come along to SQLBits to learn how you can really scale SQL Sever.

Limited places left for SQLBits V

simonsabin — Tue, 20 Oct 2009 08:45:30 GMT

We are nearing the max numbers we can afford for the Saturday of SQLBits V. So if you are thinking of coming then make sure you register soon.

More importantly, if you have said you are coming to the Saturday (most of you) and you can no longer make it, please update your registration accordingly. We have to cater for all the number registered and so if you don't come we will still be paying the venue for you. So please keep your registration updated.

Great post from Luke on going to SQLbits

simonsabin — Sat, 10 Oct 2009 15:23:57 GMT

Luke Hayler's been talking about going to SQLBits, Network and logistics of getting there

http://www.lukehayler.com/2009/09/sqlbits-here-i-come/

Next Cambridge Usergroup on the 29th October

simonsabin — Fri, 09 Oct 2009 12:39:54 GMT

We are going to an evening of SQL Server Query tuning on the 29th October in Cambridge

Its going to be at the Bango offices.

Keep posted for more details.

If you want to notified of the event, then use the contact form above.

Ordering Integration Services Packages in Management Studio

simonsabin — Mon, 05 Oct 2009 09:54:58 GMT

Call me picky but it bugs me when things aren't in order. The SQL team seem to be really bad at this particular annoyance. They seem unable to put things in a nice order.

If you are loking at packages in Management Studio then you will find the packages in totally random order, why is that you. Well they haven't put an ORDER BY on the query that lists the packages, and we all know that without and ORDER BY there is not guarantee of the order of the results.

To fix this you can hack one of the system stored procs. sp_dts_listpackages

This package is found in msdb it is classed as a system procedure and so is in the system procedures folder (if looking in object explorer).

If you change it to

ALTER PROCEDURE [dbo].[sp_dts_listpackages]

@folderid uniqueidentifier

SELECT

name,

id,

description,

createdate,

folderid,

datalength(packagedata),

vermajor,

verminor,

verbuild,

vercomments,

verid

FROM

sysdtspackages90

WHERE

[folderid] = @folderid

ORDER BY name

You will get your packages ordered nicely by name.

SQLBits Early Bird has been extended

simonsabin — Wed, 30 Sep 2009 21:34:27 GMT

SQLBits is not around to make moeny, its here to build the SQL Community. What that means is that when start to cover our costs then there is no point in charging people more. We've reached that point and so what we've done is push back the end of the early bird to the end of October.

This means that if you need a little longer to convince your bos you should go to the best SQL Server conference around then you now have 4 more weeks.

I look forward to seeing you all there and hope you can convince your boss its well worth your while. It must be the best value training you can get for SQL Server.

Oh and if you can't get your Boss to open his wallet then don't forget it's still free to come on the Saturday.

Don't forget the early bird ends on Wednesday - SQLBits

simonsabin — Mon, 28 Sep 2009 23:53:26 GMT

If you are umming and ahhing about registering for SQLBits. Get in quick as the early bird ends on Wednesday.

If you are going to the Data Management Conference today then I will see you there and if you are lucky ther migth be some interesting giveaways with regards to SQLBits. So come and see us, I think a group of us will be in an "Ask the experts" type area.

Oh and don't forget to get your pictures for the SQLBits mugshot competition. We will be deciding on the winner at our committee meeting tonight (Tuesday). There have been some great entries so far.

The column reference is not allowed because it refers to a base table that is not being modified in this statement.

simonsabin — Mon, 28 Sep 2009 16:20:27 GMT

This error occurs when you try and populate a table using the OUTPUT clause AND the target of the main statement has a trigger on it.

Shame, currently I don't know of any work around. BOL clearly states that you can't have triggers and populate an table from the OUTPUT clause.

Early bird discount for SQLBits ends on Wednesday

simonsabin — Fri, 25 Sep 2009 15:17:26 GMT

Just a gentle reminder that if you are considering coming to SQLBits then you need to register and pay by next Wednesday in order to get the £100 early bird discount.

Don't forget that if you book 6 places on the full conference then you get a 20% discount.

The numbers are looking good so it will be another great SQLBits.

Also don't forget the Mug Shot competition for a chance to win a free ticket and accomodation for the whol conference. The entries are building up, but there is still a great chance to win.

SQLBits Hotel price reduction

simonsabin — Wed, 23 Sep 2009 23:23:36 GMT

If you've been considering going to SQLBits and looked at hotel prices for the Celtic Manor. You might have found the Thursday or Friday night cheaper using laterooms etc. The Hotel have kindly agreed to match the price on these sites as long as they have allocation left. Whats more if you've already booked and said you are coming to SQLBits then your room price should have been reduced.

So now you can get a superior double inc breakfast on Thursday and Friday for only £99.

There should be quite a few of us staying in the Hotel so I look forward to seeing you there.

This price will only be available whilst they have allocation so get in quick.

Q&A from the query tuning webcast

simonsabin — Tue, 22 Sep 2009 16:22:05 GMT

The following are the questions asked following the query tuning webcast I did with Kevin Kline

If you have any further questions please let me know.

Hue Holleran Asked: Shouldn't the _primary_ objective be to minimise _overall_ server load, ie. reducing logical reads is the most important thing from stats io (followed by CPU from stats time) ?

Simon Sabin Answered : Yes you are correct CPU is also important and SET STATISTICS TIME can provide that. Also sys.dm_exec_requests can also provide the CPU used per request

Neesha Kanji Asked: Could you also save a subquery result set in a temp table and join that on the main query? Would the query be just as quick?

Simon Sabin Answered: Sometimes, however including the join in the query gives the optimiser the option to flatten the query and possibly save reading data multiple times. Often writing as a derived table can be a better solution. Using the temp table can often aid developing the code as its easier to test each section. BUT that doesn't mean you should split up all your queries as that removes the benefits of doing SET based processing.

Phil Pinto Asked: Recommend a performance book, please

Simon Sabin Answered: Personally you need to understand the internals to be able to understand performance. And so the Internals books are good. I learnt using a book from Ken Henderson many years ago so its worth trying his book http://www.amazon.com/Gurus-Guide-Server-Architecture-Internals/dp/0201700476/ref=sr_1_4?ie=UTF8&s=books&qid=1253631035&sr=1-4

Kevin Kline (Qsft) Answered: I also like Kalen Delaney's internals book. If I could only own one SQL Server book, it'd be hers. Itzik Ben-Gan's T-SQL books are also excellent.

Phil Pinto Asked: Thanks for book recommended. Indexes are useful but can be a maintenance overhead if only a very infrequent used query.

Kevin Kline (Qsft) Answered: KK> I also like Kalen Delaney's internal book - in fact, if I could only own one book, Inside SQL Server would be the one.

*** Baker Asked: database query in SSMS (previously QA) allows one do obtain ESTIMATED Query Plan (i.e. Ctrl-L). The question is does the ACTUAL QP always follow that "compile-time" decision strategy, or may it deviate (eg the SET STATISTICS IO ON which is actual after the statement completion)? For example the DEC RdB database (now subsumed into Oracle) would dynamically review several strategies : perhaps pick a simple FAST-FORWARD approach, but might switch to a more complex strategy after some pause (ie jettison the FF workinprogress)

Simon Sabin Answered: The only different between compile and execution time is the parallelism. The engine might decide to remove parallelism and run a non parallel query

Richard Asked: 2008 SSMS suggests missing indexes in Query Plans, regardless of version it is looking at

Simon Sabin Answered: Very good point. Its also worth noting that just because a missing index warning doesn't come up doesn't mean that there isn't a better index option available

Andy Irving Asked: it's only automatic use of indexed views which is EE only, in standard you have to use NOEXPAND hint

Simon Sabin Answered: Very true, often even in EE you need to use NOEXPAND because the indexed view isn't considered until late on in the query optimisation phase

*** Baker Asked: please highlight diff between temp table and temp table variable. can you index a table variable? what about PK (implied CI) perhaps #temp better than @temp for certain cases ? thanks!

Simon Sabin Answered: temp table and table variables use the same structure and are stored in tempdb. Table variables are not in memory structures. Depending on the load of the server either may be held in memory and both are stored on disk. Temp tables don’t have statistics which means that if you join to them you can result in poor query plans. Temp tables do have statistics but that itself can cause problems as it can cause recompilations of your code. You are correct that you cannot create indexes on a table variable, however you can indirectly create them by creating primary key and unique constraints. You can create indexes on temp tables. I generally say that table variables are great for small rows or where you are only going to be scanning from the table variable.

Session Voting Open for SQLBits V

simonsabin — Tue, 22 Sep 2009 01:29:07 GMT

If you are registered on the site you can now vote on the sessions you want to see at the next SQLBits.

Click here to make your vote

ts vital that you vote so that you get the sessions. If you don't we've no idea it you would prefer index tuning or piano tuning.

So please spend some time looking for the sessions you really want to see. If you are coming on Friday and Saturday its even more important you vote as we have some scope to repeat sessions if they are very popular.

So get voting

Open letter to all SQL Server Widows

simonsabin — Mon, 21 Sep 2009 22:20:21 GMT

Dear SQL Server Widow,

As a partner of a SQL Server Professional, I am sure you may be aware of the quirkily named SQLBits conferences organized in various parts of the country twice each year. Your partner may have disappeared for a weekend and returned enthused and eager to try out the many new things he has learnt, all SQL Server related unfortunately. Up to now this may have been a bit of a chore having to put up with them going on about this, but now that doesn’t have to be the case.

The next SQLBits conference, cunningly called “SQLBits goes West” will be at the luxury Golf and Spa resort, the Celtic Manor. Perfect for a relaxing weekend away.

If your partner books before the 30th September they will save up to £200 on the registration fee. That’s £200 that you could use on one of the Spa Experience days provided by the resort. You could just choose your own combination from the range of therapies, massages, holistic treatments on offer; including the Rasul Mud Ritual, Dry Floatation and Hydrotherapy, Facials, Manicures and Pedicures.

So make sure they signs up at www.sqlbits.com so you can get treated to the relaxing weekend you are entitled to at one of the top Health Spas in the UK.

Yours faithfully,

SQLBits Committee

SQLBits Mugshot competition extended

simonsabin — Mon, 21 Sep 2009 14:23:04 GMT

If you want a free trip to SQLBits then you;ve got 1 extra week to submit entries. We've only got 5 entries so far and so the chance of winning is very high.

To have a look at the current entries and submit your pictures go to the Mugshot competition page

So far we've got a mug on holiday

and one in a field of cows