Simons SQL Blog

I would like to thank everyone that turned up on Saturday to make SQLBits IV the biggest SQLBits so far. We (the oganisers) have all been amazed by the turn out. We will be soon planning the next one and so if you attended we would appreciate it greatly if you will complete the feedback we will send out in the next few days. With it we can make the next one even better.

We are sorry for those wanting to watch online, due to a network problem beyond our control we coudn't stream most of the day. We did however record all the sessions and these will be available in about 5 weeks (mid May).

If you didn't attend then you might like to look at some of the pictures Brad has put on his blog 

 http://www.sqlservercentral.com/blogs/aloha_dba/archive/2009/03/29/sqlbits-a-smashing-success.aspx

I love the keyboard shortcuts but in writing demos for SQLBits I've been getting very frustrated that I can't select some text for an object that is schame based and then use the keyboard shortcuts to get things like table definitions, etc.

The issue is that when you select Person.Person, is this added to the end of the stored proc call as follows

sp_help Person.Person

This obviously fails because of the period what should executed is

sp_help 'Person.Person'

But there is no way to get SSMS to wrap your selected text in quotes. If you would like it fixed vote on this connect item https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=349116&wa=wsignin1.0

In the mean time you can use the following stored proc. Unfortunately you can't change sp_help in 2005 and onwards and you can't access schema tables from sp stored procs as you could in SQL 2000 and before. Once you have run the code below you can assign a shortcut to it and now simply when highlighting Person it will figure out the schema. Be warned if you have more than one matching object it will pick one of the schemas.

use master

go

create  procedure  sp_help2

@object_name varchar(100)

as

declare @schema varchar(100)

declare @sql nvarchar(1000)

set @sql = 'select @schema = schema_name(schema_id)

              from ' + db_name() + '.sys.objects

              where name = @object_name'

exec sp_executesql @sql

                , N'@schema nvarchar(100) OUTPUT, @object_name nvarchar(100)'

                ,  @schema OUTPUT, @object_name

set @object_name = db_name() + '.' + @schema + '.' + @object_name

exec sp_help @object_name

On of the companies exhibiting at SQLBits is Mcgraw Hill .

They are offering 40% off books bought on the day.

So make sure you bring a big bag, with this off, the stuff we are giving you and all the prizes on offer you could have a lot to take home.

The schedule builder is now on the SQLBits site. http://www.sqlbits.com/schedulebuilder/

If you want to plan your day then this is for you.

Whilst you can print in Firefox we have an issue with printing in IE. One option is to do a screen grab using OneNote.

If you've got questions about the new SDS model then your question might be answered here.

http://blogs.msdn.com/ssds/archive/2009/03/12/9471765.aspx

There were some changes to SQL Server 2008 before RTM in the area of minimal logging.

Thankfully in January the SQLCAT team have documented the changes that were made in SQL 2008 and some more that can be enabled with a trace flag.

You can read the article here http://msdn.microsoft.com/en-us/library/dd425070.aspx.

Whats nice is that you can now get minimal logging with INSERT INTO SELECT (sorry for doubting you JRJ). However if the target isn't a heap (a table without a clustered index) then you need to enable the trace flag. Be warned of the performance considerations of enabling the trace flag, its not always the best thing to do.

This is an amazing article fro the SQLCAT team which goes into a lot of detail about bulk logged operations, the ins and outs, trouble shooting and performance tuning bulk operations.

I was recently emailed by one of my readers asking about SQL Server scaleout. They had been told of something but couldn't find it, and wondered if I knew what it was, I didn't but they eventually came back and told me they had found Gridscale.

So I looked into Gridscale. Kevin has a post with some good comments regarding Gridscale http://sqlblog.com/blogs/kevin_kline/archive/2009/02/02/product-watch-scalable-sql-server-grid-with-xkoto-could-this-be-mssql-s-answer-to-oracle-rac.aspx

Looking at it, it scales about by acting as a middle man in between your app and SQL Server. It distributes writes to all nodes to make sure all nodes are consistent but it only sends reads to a single node. Given most apps are heavily read intensive this means you can scale your system beyond what you can on one server. However, in a well tuned system, reads are going to come from cache but writes have to write to disk and so you have an inherent performance difference between reads and writes.

If your server is maxing out on writes then I can't see how Gridscale will help you. Because all the servers will have the same write IO needs.

One of the benefits of how Gridscale works is the that servers can be taken off line, patched and then bought back on line. From a maintenance, DR perspective this is great. I really wanted this at Totaljobs as I wanted to implement a two active data center operation. We already had a replication topology that allowed reads to be served from different servers, ths downside was we replicated from one node and failing over replication to a new site is a pain (for the distribution database).

My biggest concern with any replication type solution is what happens when something goes wrong. What happens when one server goes out of sync, whats the process for rebuilding it.

So in summary I think this a good solution although I would like to see more information. You are going to have n copies of your data and so need storage to support it. Whats more your storage needs to support the n times number of IOs your servers will generate. I have no idea on costs but I would expect that the cost will be similar to implementing your own replication topology.

Whats more if you really what scale out you need to design your application to scale out, this has to involve partitioning or some sort.

But with servers becoming faster, memory being cheap and solid state disks on the way. Do we need to scale out?

For those of you I met yesterday asking about Reading usergroup meetings, Tony has put the dates up on the Usergroup site.

Many events have a TBA agenda but the theme is set. The content will always be good so why not get registered

http://www.sqlserverfaq.com/Default.aspx

If you want to get your hands on the CTP of the Silverlight Virtual Earth Control then you will need to go to Connect.

There you will have to complete a short survey and I guess you will then be informed when the download is available.

https://connect.microsoft.com/silverlightmapcontrolctp

[SS 20:53] The download is now available

https://connect.microsoft.com/silverlightmapcontrolctp/Downloads