The Future of Monitoring

For some time now I have been participating in usability sessions with Red Gate Software for their product SQL Response. This has been a great chance to shape a commercial application to do what I wanted it to do. I've seen ideas of mine appear in their latest version (ok,ok - other people MAY have had similar thoughts too...)

The team behind the application are now looking to create a new version, and are looking for input from real users, with real monitoring and performance issues. They are going to build the next version of their product to do exactly what users want, so get along to their website at The Future of Monitoring, have a look at what they've done so far and contribute YOUR ideas.

They are even giving away free copies of their new monitoring tool (when it's finished) - all you have to do is submit a design (doesn't have to be anything fancy, they just want ideas), and you'll be entered into the draw.

COUNT(DISTINCT) OVER() aggregate window function

Recently have been using some of the new partition and aggregate window functions in SQL Server 2005.(see here for the BOL entry).

However when I tried to change a count(distinct ...) query, I ran into an error!

create table TableA(
id int,
category varchar(10),
subcategory varchar(10),
sometext varchar(100)
)
go
insert into TableA (id, category, subcategory, sometext)
select 1,'Category A','fruit','apple'
union select 2,'Category A','fruit','banana'
union select 3,'Category A','fruit','cherry'
union select 4,'Category A','animal','cat'
union select 5,'Category A','animal','dog'
union select 6,'Category A','animal','lion'
union select 7,'Category B','vegetable','carrot'
union select 8,'Category B','vegetable','potato'
union select 9,'Category B','vegetable','onion'
union select 10,'Category B','name','alice'
union select 11,'Category B','name','bob'
union select 12,'Category B','place','london'
union select 13,'Category B','place','manchester'
go


from this sample data we can easily count the number of subcategories per category using


select category, count(distinct subcategory)
from TableA
group by category

which gives

category CountSubcategory
---------- ----------------
Category A 2
Category B 3

(2 row(s) affected)

but if we try to use the OVER() clause

select category, count(distinct subcategory) over(partition by category)
from tableA

we get

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'distinct'.


Check BOL, and there's nothing that says that the DISTINCT argument cannot be used in the COUNT function when used in an aggregate window function. How confusing. Then I discover I'm not the only one to have the same thought, as this item on Connect shows.





So if you have the same issue and want this resolved, please log on to Connect and vote this up.

MSc Business Intelligence at Dundee University

Whilst I don't normally like just repeating what others blog about, once in a while you come across something that you just have to share!

Tony Rogerson has blogged about a new MSc in Business Intelligence that Dundee University are hoping to start in January. I say 'hoping' as if no-one registers then it wont be run! Although having spoken to Mark Whitehorn, the organiser, if the amount of interest generated so far is anything to go by, it is a certainity.

Tony doesn't mention it, but the course will run over 2 years for those wanting to complete it part-time via distance learning, and you will be required to attend in person for 2 weeks per year. The content seems to be product-agnostic, teaching the concepts of BI, although there will be some discussion of various technologies and techniques, and the final module will use MDX as the implementation language.


The cost of the course (if you enrol this year, - no guarantees this price will stay) is £1,700 per year, which when compared to the cost of commercially available courses that come in at about £2,000 for 5 days training, is a very good price. I also see the value of an MSc far outweighing the value of any product-specific certification (mentioning no names), but that's just a personal view.


I'm hoping to secure some support from my current employer, and get myself on the course ASAP. Haven't been in formal education for over 15 years, so it might be a bit of a shock to the system.


Anyway don't take my word for it, check out Tony's blog for some more info and get in touch with Mark (email address on Tony's blog)

Manchester SQL Server User Group

Attended my first EVER user group meeting a couple of weeks back. I had been meaning to get to one of these ever since they started in Manchester earlier this year, but always something else had conspired against me.

Sure I've done SQL Bits, and various MSDN/TechNet type stuff, but this was different, more personal and certainly made me feel more involved. Looking forward to many more of these, and maybe next time I will network properly - I talked to many people but didn't get their details!

If you've never attended one of these, or perhaps are not sure they are for you, don't worry just get along to one. You'll meet people working in SQL at all levels, and even if you have nothing in common with anyone else, then you'll be able to share your 'uniqueness' with them. There's no pressure to network, speak or contribute, but after just one I bet you'll be itching to get more involved!

They are organised by the UK SQL Server User Group , the Manchester one by Chris Testa-O'Neill ( blog ), who shows great enthusiasm for the community.

Next Manchester one is planned for 15 Oct 2009, more details here, but check out the UK SSUG website for other events near you. They do Leeds, Dundee, Reading, Bracknell, Cardiff and the obligatory London.

Linking 2 tables based on row positions - SQL2000 vs SQL2005

A simple (and common??) scenario - you have 2 tables of ordered integers and want to combine them into one recordset where the first row of the first table is paired with the first row of the second table and so on, like

Table1
val(int)
---
1
3
5
9

Table2
val(int)
---
4
9
14
17

we want to return

val val
--- ---
1   4
3   9
5   14
9   17

And we want to achieve this WITHOUT the use of cursors (obviously!).

If we could assign a row number to each table then we can join on the row number, row1 = row1, row2=row2, etc... now SQL 2005 gives us a couple of ways of doing this using either the RANK() function or the ROW_NUMBER() function.

if object_id('tempdb..#t1') is not null drop table #t1
if object_id('tempdb..#t2') is not null drop table #t2

create table #t1 (val int)
create table #t2 (val int)

insert into #t1 values (1)
insert into #t1 values (3)
insert into #t1 values (5)
insert into #t1 values (9)

insert into #t2 values (4)
insert into #t2 values (9)
insert into #t2 values (14)
insert into #t2 values (17)

using RANK()

select t3a.val, t3b.val
from
(select val, rank() over (order by val) as seq from #t1) t3a
join
(select val, rank() over (order by val) as seq from #t2) t3b
on t3a.seq=t3b.seq

or using ROW_NUMBER()

select t3a.val, t3b.val
from
(select val, row_number() over (order by val) as seq from #t1) t3a
join
(select val, row_number() over (order by val) as seq from #t2) t3b
on t3a.seq=t3b.seq

But in SQL2000 these functions do not exist. An elegant way to achieve this is to join the table to itself and determine the 'rank' by counting the number of elements that are less than or equal to the current i.e.

select t11.val, count(*) as seq
from #t1 t11, #t1 t12 where t11.val>=t12.val group by t11.val

returns

val seq
--- ---
1   1
3   2
5   3
9   4

hence we can use this as the ranking function

select t3a.val, t3b.val
from
(select t11.val, count(*) as seq
from #t1 t11, #t1 t12 where t11.val>=t12.val group by t11.val) t3a
join
(select t21.val, count(*) as seq
from #t2 t21, #t2 t22 where t21.val>=t22.val group by t21.val) t3b
on t3a.seq=t3b.seq

[Credit goes to Richard Dean for the original idea]

SQL Server scalability

A while back I was asked to look at the scalability of our database environment, and what could be done to achieve a 'doubling in capacity' for what was going to be a busy trading period. [For info : the db was supporting a load-balanced server farm of web servers (providing both UI and web services) - daily traffic was expected to hit the 1 million mark]

Here is a summary of what my research found....

Scalability of SQL server can be achieved 2 ways - either scaling up or scaling out. Scaling up involves increasing the performance of a single server, i.e. adding processors, replacing older processors with faster processors, adding memory etc.., and is essentially what can be acheived by replacing existing hardware with a newer, bigger, faster machine. Scaling up works in the short term as long as the budget allows, however there are ultimately levels of volume that one server cannot handle.

Scaling out is to distribute the load across many (potentially lower cost) servers - this concept works for the infinite long-term, and is constrained by the amount of physical space, power supply, etc..., far less than the constraints of budget and individual server performance.

There are 2 approaches to scaling out:

1. multiple servers that appear to the website as a single database - known as federated databases - the workload is then spread across all servers in the federation.
2. multiple servers that appear as individual servers, however the workload is load balanced or the servers are dedicated to a particular function or work stream (e.g. a set of 1 or more web servers)

The main concern at the heart of any scaled-out SQL solution, is how to ensure that data is consistent across all the database servers. With option 1, the data is consistent as there is only one database, and data is spread rather than replicated across each server. The main downfall of this approach is the poor availability - if one of the federated databases fails, the whole federation fails.

With option 2, some data needs to be replicated from all servers to all servers. There are many methods of implementing such an architecture, however it seems to be clear that this general approach is the best, long-term, for scalability in this environment. Some data can differ across each database server as long as the user remains 'sticky' to that database (for example, session information), investigations started into this idea, using the web servers to remain 'sticky' to a particular database. [The idea was to split the load-balanced web farm over 2 database servers and issue a cookie from the Content Services Switch to keep the users on one half of the web farm - we never actually got this to work successfully]

It is worth pointing out at this stage a common misunderstanding about clustering SQL servers using Microsoft Cluster Services. Clustering itself does NOT provide scalability - it only provides high-availability. The only true way to scale a clustered SQL solution is to scale up - i.e. increase the power of each server in the cluster.

Have bust my zip - I'm too large

Tried to compress a large file today, using the inbuilt compression in Windows XP, but got an error ....

The compression cannot be performed because the size of the resulting Compressed (zipped) Folder is too large

Eh? The whole point of compression is to make large files smaller, isn't it?

I presume this is a 2Gb limit!