November 2007 - Posts
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
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.
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.
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.
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 you will be told this is not possible. You have to
split your delimited list into values either use a subquery or a derived table
to do the filter i.e.
in (select id from dbo.split(@orderIds))
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
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
create procedure up_OrdersGetByIds
select orderId, orderDate
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
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
that aren't bugs and could be resolved by posting to a forum.
What benefit do you have posting to the forum,
You get notified of all
MVPs and anyone else can respond
The forums will build up to have all the common issues and so you should be
able to find your answer easier.
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
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
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.
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,
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
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
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
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
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
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
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
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
I mentioned a wiki of SQL Server
Myths at my talk. This is now done and we are reviewing the content so keep
More Posts Next page »