Simons SQL Blog : SQL

Loads of things going on at #SQLBits

simonsabin — Sat, 17 Dec 2011 21:30:39 GMT

Wow its been a busy few weeks in SQLBits land. Session Voting We’ve opened up voting for the sessions for SQLBits 10 http://sqlbits.com/information/PublicSessions.aspx Make sure you vote so we pick the sessions you want. You’ve got to register on the...(read more)

SQLBits evening do

simonsabin — Tue, 02 Sep 2008 11:36:25 GMT

We have finally got finalised that, at SQLBits, we will be having a drinks reception after the sessions finish.

There will FREE beer (other drinks will be available) and pizza.

We've also got some entertainment planned so it should be a good evening

To cluster of not

simonsabin — Tue, 26 Jun 2007 12:51:26 GMT

Tony's blogged about the use of heaps http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/06/24/row-fragmentation-hopscotch-heap-v-clustered-and-io-cost.aspx. Whilst this highlights data on a page being out of sequence and thus yoyoing when you scan the data, it doesn't mean you should always have a clustered index.

The stats in Tony's blog are only because the query is a full table scan in the first (heap) query. With a clustered index you are not doing a table scan your are only doing a partial scan and so the reads are different. If you remove the where clause thus forcing a table scan then the heap is qucker because it is more compact, it has to read less pages.

You can also address these issues with a covering index. An non clustered index is effectively the its own special table with a clustered index with the leaf pages also containing the Row id or Primary key column.

Also the issue here is highlighted by a scan. If you are performing an operation that does a seek heaps aren't bad. Also clustered indexes result in the whole row being read, thus if you only need a few rows they can also be worse than a heap with a covering index.

Finally having a clustered index is beneficial if you key size is < 8 bytes because the key is put in the leaf of non-clustered indexes. Whereas a heap the row id is and the row id is 8 bytes

Finally with clustered indexes you will end up with page splits, this can result in page fragmentation across the database file, the worst case would be that in order to read 8 pages (8k), 8 extents(64k) would have to be read because each page is on a different extent. The ALTER INDEX REORGANISE and REBUILD address this fragmentation by the former moving pages to be in the same order on disk as logically and also compacting pages in and the latter rebuilding the index entirely. REORGANISE is an online operation.

Following this post Tony and I have had a very long discussion. The bottom line is that if there is one thing you should learn its the internals of indexes. This would be my starter, heaps and b trees, covering indexes, bookmark lookups, included columns, page fragmentation, page splitting ...

ProClarity 6.3 available on MSDN downloads

simonsabin — Wed, 04 Apr 2007 22:28:43 GMT

I've just logged into MSDN downloads and was very shocked to see ProClarity available for download.

Both server and desktop versions are available.

You can read more on Proclarity here

http://www.proclarity.com/

Two Free MSDN Team Suite Subscriptions

simonsabin — Sun, 12 Nov 2006 23:52:54 GMT

If you want to win one of two MSDN Team Suite Subscriptions worth in excess of £5,000 then its simple. Start contributing the SQL Blog sitte sqlblogcasts.com and you'll be in with a chance. Its free to join up and start blogging so whats stopping you.

To learn more read Tony's blog. Free MSDN Subscriptions just to start blogging

Native Command Substitution is not supported

simonsabin — Thu, 05 Oct 2006 14:29:08 GMT

In response to a recent support incident we raised we got a response about use of detours in SQL Server.

A detour is a means by which you can change what SQL Server does. This is used by Litespeed to change native backup commands into Litespeed backup commands. Whilst I always thought this a neat idea I always felt it a bit risky. If I want to do a litespeed backup I can easily write the code or use the UI to do so.

So it seems my caution has proved correct.

Use of detours by a third-party solution is not supported with SQL Server

Is IDENT_CURRENT the next @@identity

simonsabin — Tue, 11 Jul 2006 19:15:44 GMT

I've just finished watching the webcast on building a wareshouse according to Kimball principles. In it they generate an audit record by inserting a record and then using IDENT_CURRENT to get the identity value.

A few months ago some one on the forums was screaming because the IDENT_CURRENT was not working on a 64 bit server it was returning NULL.

My point is they both wanted the last identity generated for a table, but both missed the point that IDENT_CURRENT is not specific to a session but is server wide. This means you could be getting the identity value generated for an insert in another session. Pretty much like calling select MAX(identityCol) from table (but without the locking).

So just as people with triggers got burnt with the use of @@identity so are people with IDENT_CURRENT() whats more it probably won't show up as a real problem i.e. a failure, you will just have records associated with the wrong parent, your database will slowly become corrupt.

In both of these situations the user should be using SCOPE_IDENTITY to get the value generated by their last insert statement.

A large slice of humble pie please Mr Thomson

simonsabin — Fri, 23 Jun 2006 09:11:00 GMT

In my presentation last night I went through a process by which you can create Raw files so that you can append to them in a for each loop as there is no "Create once for the package" setting for a Raw file. I knew Jamie had covered this in his blog but I was doing a slightly different approach and Jamie raised this in the presentation. As I hadn't read his post properly I dismissed his comments as a heckler from the front row. Well I guess I will be eating humble pie for a while, as Jamie did, when I got home I tested what Jamie had proposed and it worked.

Simply put, the "Append" option will create the file if it doesn't exist. If you need to use the Raw file eslewhere in your package then you will have to create the raw file so that SSIS can determine the columns that will be in the Raw file and as Jamie pointed out this does only need to be done at design time. Jamie has explained with pictures here

Whilst my solution creates an empty file every time the package runs, with Jamie's solution you need to make sure that your package deletes any raw files that already exist.

As for the presentation it went ok towards the end, as Jamie put it "...He comes across as being utterly unprepared..." which was very true for the start. I made the fundamental mistake of moving everything to a machine the night before which screwed everything up. Lesson learnt.

Are you allowed to be sizeist in the database world

simonsabin — Thu, 25 May 2006 16:33:21 GMT

Well even if your not, one of my real bug bears is fat tables. These aren't large tables they are fat tables, wide ones with lots of columns or large data types.

The reason this gets is is people develop databases with 10s of rows in them. Then end up with millions of rows in them in a live environment and find this things just don't perform.

Then managing those tables is a real chore.

Just because you have a table called person doesn't mean all the information relating to a person has to be in that table. Especially when you most of the time all you want is there name. Ok so you can create covering indexes, but then you hurt your insert performance and you can't create a covering index for every situation.

Imageine the situation you have a table that links people to stadium. you can have millions of people visit your stadium but you've only got a few stadium,

Do you have a table that is like this

create table StadiumVisit (personId uniqueidentifier not null
, visitDate datetime not null
, stadium int not null
, comments varchar(200))

create table StadiumVisit2 (personId int not null
, visitDate smalldatetime not null
, stadium tinyint not null)

Whats the different well the first would be ~a minium of 28 bytes per row and the other would be 9 bytes per row (ignoring overhead). With 10 million rows the second takes up 90Mb. Assuming the average comment was 100 bytes the first would take 1280Mb ~1.2Gb

How much memory you got in your box? 1Gb which one is going to perform better?

Even ignoring the comments field thats 3 times more data to read to process a query, the impact is that you put more pages into memory that you don't need, you push pages out of memory that are needed by other processes, you query needs to process more data and thus use more cpu. In a scalable system make sure you do as little as possible, this means it has less impact on anything else and so you server can do more.

For a great book on this look at Pro SQL Server 2005 Database Design and Optimization by Louis Davidson.

But be careful of fat tables, we all no how they get fat but I will leave that to another time.

Performance of MAX trick

simonsabin — Tue, 16 May 2006 07:15:00 GMT

Jamie posted a comment to by post about the trick for finding a maximum value in a set of results. Stating that the CASE statement solution was significantly faster. Being a synic I didn't believe him so tried it out for myself

The following are the two sets of code, and below that is a script to populate the tables, the first set of inserts are there to test the results, the commented out section is to load the dates table with lots of values.

On my server the difference is negligible, often with the subquery mechanism being quicker to execute, if slghtly longer to compile.

However the point to note is that the compile time is ~1ms and the execution time for 1024 rows is ~70ms. Thats pretty quick which ever option you choose and more importantly I know which one I would want to be supporting :)

select case when orderdate > canceldate then
                case when shipdate > deliverydate then
                        case when orderdate > shipdate then orderdate else shipdate end
                     else
                        case when orderdate > deliverydate then orderdate else deliverydate end
                end
            else
                case when shipdate > deliverydate then
                        case when canceldate > shipdate then canceldate else shipdate end
                     else
                        case when canceldate > deliverydate then canceldate else deliverydate end
                end
            end
, *
from dates
go

select (select max(datevalue)
from (select orderdate datevalue union all select canceldate union all select shipdate union all select deliverydate )d)
, *
from dates

create table dates (orderdate datetime, canceldate datetime,shipdate datetime, deliverydate datetime)
go
insert into dates values ('1/1/2005', '1/2/2005', '1/3/2005', '1/4/2005')
insert into dates values ('1/1/2005', '1/2/2005', '1/4/2005', '1/3/2005')
insert into dates values ('1/1/2005', '1/3/2005', '1/2/2005', '1/4/2005')
insert into dates values ('1/1/2005', '1/3/2005', '1/4/2005', '1/2/2005')
insert into dates values ('1/1/2005', '1/4/2005', '1/2/2005', '1/3/2005')
insert into dates values ('1/1/2005', '1/4/2005', '1/3/2005', '1/2/2005')
insert into dates values ('1/2/2005', '1/1/2005', '1/3/2005', '1/4/2005')
insert into dates values ('1/2/2005', '1/1/2005', '1/4/2005', '1/3/2005')
insert into dates values ('1/2/2005', '1/3/2005', '1/1/2005', '1/4/2005')
insert into dates values ('1/2/2005', '1/3/2005', '1/4/2005', '1/2/2005')
insert into dates values ('1/2/2005', '1/4/2005', '1/1/2005', '1/3/2005')
insert into dates values ('1/2/2005', '1/4/2005', '1/3/2005', '1/1/2005')
insert into dates values ('1/3/2005', '1/2/2005', '1/1/2005', '1/4/2005')
insert into dates values ('1/3/2005', '1/2/2005', '1/4/2005', '1/1/2005')
insert into dates values ('1/3/2005', '1/1/2005', '1/2/2005', '1/4/2005')
insert into dates values ('1/3/2005', '1/1/2005', '1/4/2005', '1/2/2005')
insert into dates values ('1/3/2005', '1/4/2005', '1/2/2005', '1/1/2005')
insert into dates values ('1/3/2005', '1/4/2005', '1/1/2005', '1/2/2005')
insert into dates values ('1/4/2005', '1/2/2005', '1/3/2005', '1/1/2005')
insert into dates values ('1/4/2005', '1/2/2005', '1/1/2005', '1/3/2005')
insert into dates values ('1/4/2005', '1/3/2005', '1/2/2005', '1/1/2005')
insert into dates values ('1/4/2005', '1/3/2005', '1/1/2005', '1/2/2005')
insert into dates values ('1/4/2005', '1/1/2005', '1/2/2005', '1/3/2005')
insert into dates values ('1/4/2005', '1/1/2005', '1/3/2005', '1/2/2005')
go
--insert into dates select getdate() + 100*rand(),getdate() + 100*rand(),getdate() + 100*rand(),getdate() + 100*rand()
--go 1000
set statistics time on
go