Simons SQL Blog

Yes we're doing it again. SQLBits (www.sqlbits.com) will be back on the 1st March at The Lakeside Conference Centre in Birmingham.

Its a bigger venue than TVP so we shouldbe able to let more people attend.

Registrations aren't open yet but if you register on the site we will let you now when it does open.

In the meantime if you want to submit a session then please do so following the instructions here.

http://www.sqlbits.com/information/SessionSubmission.aspx

Following my preview two posts about using filter and with IN in the multivalue parameters. Rob Farley has posted a bit more detail on how Reporting services cheats.

http://msmvps.com/blogs/robfarley/archive/2007/11/23/how-multi-value-query-parameters-in-ssrs-are-treated-by-sql-server.aspx

It would be good if it still used parameterised SQL but it doesn't it hard codes the values and thus causes an adhoc SQL statement and so a completely unique plan. Oh dear.

Read Rob's post for more details.

One of the most common requests I've seen when people write queries for applications is to be able to pass a delimited list of values and use it in an IN clause, i.e.

However if you are using reporting services you can do the the first one, when the parameter is a multi valued parameter. You may ask how come reporting services can do it and I can't in my application.

Well the reason is that RS is cheating.

It takes the query, identifies that you are using the parameter with and IN clause and then when it runs the query it breaks down the parameter into individual ones so the query then becomes.

create procedure up_OrdersGetByIds

  @orderIds varchar(100)

as

begin

select orderId, orderDate

from dbo.OrderDetails

where orderId in (@orderId1, @orderId2, @orderId3, @orderId4,....)

I was really surprised by this and the documentation isn't all that clear, but it works. I wonder whether in Kamai they will support the new table valued parameter. I will need to go and find out.

So we all like to knock Microsoft now and again. And we al like to find bugs, however I've seen lately lots of submissions to connect (http://connect.microsoft.com/SqlServer) that aren't bugs and could be resolved by posting to a forum.

What benefit do you have posting to the forum,

You can find the Katmai forums here http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=428&SiteID=1

If no one can resolve your issue and it is a bug then if you raise a bug and link to your forum post the item will probably get better consideration because of the discussion that is likely to have taken place on the forum.

Just to get some buzz going, we will be making an anouncement in the next week about the next SQLBits. So if you're interested in doing a session then start thinking up ideas before al those christmas parties kick in.

We've taken on board lots of your feedback from the last one so this one should be even better.

I've been delving into the world of MDX recently and it is another world. You really have to get your head thinking all sorts of different ways to try and grasp the tuples, slices, hierachies etc.

What writing calculated measures I have often found the result not to be what I expected.

This is often due to me navigating the wrong hierarchy, naming something wrong or navigating to a the wrong place in a dimension. For example if you want the last an average of the last 12 months, then you need to find the period that is 12 months before the current one. Functions like parallel period help in allowing you to specify just that, however I found that for a fiscal calendar of 544 (weeks). It wasn't working, the number where incorrect.

Aggregate({ParallelPeriod([Date].[Date Hierarchy].[Year],1,Date.[Date Hierarchy].CurrentMember):Date.[Date Hierarchy].CurrentMember}

So where do you start in resolving this. The key is to start from the bottom and work up. In this case I need to check that the period returned by ParallelPeriod was the right one. You can't just use the ParallelPeriod as that returns a member and not a value. The trick I started using was to create a calculated measure that returned the name of the member, simple really.

So my calculated measure was something like,

ParallelPeriod([Date].[Date Hierarchy].[Year],1,Date.[Date Hierarchy].CurrentMember).Name

This is also useful if you spell something wrong, because you won't get an error if you reference a level. So if you break down your main calculation and you still get (null) it means you've navigated into a blackhole, where nothing exists. In my case I'd done the cut and paste error in copying between my normal date hierarchy and the fiscal one. The Fiscal one doesn't have a [Year] level it has a [Fiscal Year] level.

Filters are not always a good thing in reports as it generally indicates you are getting more data back from the database than you need. However I often find that I want to use virtually the same data in a report jst formatted differently, i.e. a chart, a table, etc. This is were filters come into their own.

You can filter on many things in a report. Generally these are anything that has a set of data, i.e. a table, a group, a series for a chart.

I recently wanted to filter using the IN operator, but couldn't find out how.

I guessed based on previous experience that a delimted lst of values probably wasn't going to cut it. So I took my delimited list and used the SPLIT  function on it. This returns an array, and it works.

I can only guess that RS loads the array into a dictionary object and does a lookup against it. For that reason it needs an array that it can loop over to load into the dictionary.

So an example of an expression is as follows

="Simon,Sabin,SQL,Server".Split(,)

Go into an filters dialog, select the field (or expression) you want to filter on, select the "In" operator and then enter the expression as above.

Interestingly the Value property of a Multivalue parameter is an array object already so if you ave one of those in your report you should be able to do something like,

=Parameters!MyMultiValueParameter.Value

How many people actually create accounts for the SQL Server services to use? I would expect that once you step out of the enterprise the majority wouldn't be. Even though its bet practice.

Why do I think that? Because its generally a pain. If installing the full suite, you have the engine, analysis services, reporting services, integration services, the browser and sql agent all needing service accounts.

So thats 6 accounts, you then need to assign the relevant service to the relevant group.

Then you need to add the SQL Agent user access to the SQL Server.

So what do people do, I suspect most either, use their own account (which is likely to be a domain account and/or a local admin), the local admin account, the network service and the local system account. None of which follow the principle of running with least priviledges.

That results in lots of setups that are running with high elevated priviledges and/or situations where the network or local system accounts have ben used which result in certian features not working, and thus casing confusion and annoyance.

Since the setup fr SQL has change in CTP5 and the service account selection is very different some MVPs have been discussing the options. Hugo Kornelis came up with a great idea. Why doesn't setup create the accounts for us. I remember IIS used to do that to avoid a highly priviledge uer being used as the default IIS account. So why can't SQL.

Here is Hugo's suggestion on conect, https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=310609. If you think its a good idea please vote. Even though the item is still closed, if we get enough votes MS should reconsider it.

Katmai increases the concurrency of SSIS packages by a change to the pipeline.

In SQL 2005 a dataflow is split into sections, This is called the execution tree. Each section has the same buffer strucuture and the buffers are passed to each component in the section of the pipeline. A new secton starts when an asynchronous component is found. i..e the buffer in is not the buffer out like the aggregation component

The key here is that the data is not copied to a new buffer for each component. Rather each component accesses the same buffer (one after each other). This is great because copying data is an expensive operation.

However it does mean that you are limited to one active buffer. So if you have multiple components only one is accessing the buffer at anyone time.

Well that was the case in SQL 2005. In SQL 2008 the situation changes. Each component will be able to access a buffer thus greatly increasing the ability for you package to run quicker. You are still restrcited by the resources on your machine, CPU and memory being the main ones for SSIS.

I was reading Alberto Ferrari's blog and though of this http://sqlblog.com/blogs/alberto_ferrari/archive/2007/11/15/ssis-multicast-and-trash-or-separate-and-union.aspx. In Albertos situation he initially uses multicast and conditional split. Whilst you might think these cause new sections in the execution tree, they don't they are in fact synchronous components. What this means is that in the first package he is only maintaining one copy of the data.

However in the second the use of the UNION which is an asynchrone ous component, a new section is started and thus the data is copied into a new buffer and so the time difference he experiences is due to the fact that the data is being copied.

In summary once data is in the SSIS section of a pipeline if you use it or not is irrelevant. If a piece of data is not used at all, then don't put it in the pipeline. Thats why you get the warning message saying a column is't being used. Your package may perform better if it is not used.

So bring on Katmai and we should see Albertos scenario improve even further.

If you attended SQLBits you may have seen my SQL Myths session. One of the myths I didn't cover was that a Disk Queue length > 2 is bad. Bob Dorr wrote a great blog post on it back in February. I like is analogies. I've got another one.

You go into an Irish bar and you need 20 pints of guiness.You have a number of options.

If you order one at a time and wait at the bar, its going to take forever and your not going to get much conversation with your 19 friends.

You could order 1 and then sit down with your friends, get up when its ready and order the next one. This will get you more conversation time, but your still going to be wasting time getting up and down to the bar.

Your other option is to order 4 at a time and then get the bar man to bring the pints over. This means you will get your pints quicker and you can maximise your conversation time.

What ordering 4 pints allows the barman to do is use more of his pumps. He's got 4 pumps, he can be pooring 4 pints at once.

If he had 20 pumps, you could order 20 at a time and that way you would get all the pints quicker.

The barman also has another trick. If you've ever been in a good Irish bar you will see that they have pints waiting for people to order them.

This is exactly how the IO works in SQL, the server sends a batch of IO requests to the IO system and then gets on with something else. If SQL realises that the IO system can handle more requests, i.e. we have more bar men and more pumps it sends large requests, because it knows the system can handle it. Having pints outstanding in your order isn't a big deal because you know the Barman can get them served in the time that you want.

As for the barman having pints ready. This is just like IO readahead, he knows more people are going to want Guiness so he's got them ready on his bar (cache).

http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx

I mentioned a wiki of SQL Server Myths at my talk. This is now done and we are reviewing the content so keep posted.