Performance (RSS)

Performance

How to speed up partition merging

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/How to speed up partition merging
The new RSS feed is here

The key to performance with partition management is to deal with empty partitions. That way all you have is meta data changes and no data has to be copied.

We recently encountered a situation where we had two partitions and wanted to merge them. In a merge you have 3 boundaries the left one, the middle one and the right one. In doing a merge you are going to get rid of the middle one and have 1 partition that is bounded by the left and right values. The partition on the left of the middle boundary was empty and the one on the right wasn't. We were using RIGHT partitioning so the partition value for the middle boundary was included in the RIGHT hand partition. We expected this to be very quick. The engine you take the RIGHT hand partition and use it for the new partition.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/How to speed up partition merging

posted Tuesday, July 31, 2007 11:10 PM by SimonSabin with 0 Comments

SQL Server The Truth - Rebuilding a clustered index does not rebuild the non clustered indexes

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/SQL Server The Truth - Rebuilding a clustered index does not rebuild the non clustered indexes
The new RSS feed is here

It used to be fact that the leaf page of a non-clustered index pointed to the row for the page, because it pointed to the file, page and row if the file or page of the row changed the nonclustered index had to be updated.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/SQL Server The Truth - Rebuilding a clustered index does not rebuild the non clustered indexes

posted Friday, July 20, 2007 10:32 AM by SimonSabin with 0 Comments

Blobs block online indexing

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Blobs block online indexing
The new RSS feed is here

If you are lucky enough to be running enterprise edition of SQL you may have looked or want to look into online index rebuilds. Unfortunately having blobs in your index blocks you from being able to do online rebuild of that index. Cruically what that means is that if your table is clustered then you cannot rebuild the clustered index on that table

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Blobs block online indexing

posted Wednesday, June 27, 2007 9:33 AM by SimonSabin with 0 Comments

To cluster of not

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/To cluster of not
The new RSS feed is here

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.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/To cluster of not

posted Tuesday, June 26, 2007 1:51 PM by SimonSabin with 0 Comments

How many indexes do you have that aren't used?

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/How many indexes do you have that aren't used?
The new RSS feed is here

If you run this sql on a sql 2005 box then you will see which indexes aren't used in queries compared with the number of times they are updated.

select object_name(s.object_id) , *

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/How many indexes do you have that aren't used?

posted Wednesday, May 16, 2007 5:02 PM by SimonSabin with 0 Comments

SSIS - Parallel processing

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/SSIS - Parallel processing
The new RSS feed is here

I remember seeing a very early CTP of Yukon and the new DTS. I was very exceited to see a parallel property on the loop container which would allow the contents of the container to be executed in parallel.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/SSIS - Parallel processing

posted Tuesday, May 15, 2007 8:34 PM by SimonSabin with 0 Comments

What is the cost of a query? Is it a beard?

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/What is the cost of a query? Is it a beard?
The new RSS feed is here

I used to work on a project where we discussed what exactly the cost of a query was. You know that figured shown in execution plans. I knew it was used to age plans in a cache but not sure what unit it actually was. One of the guys on the project (Mike I think) decided to call them "Beards". So we could then say that query x cost 54 beards. Its feels better than saying just 50.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/What is the cost of a query? Is it a beard?

posted Wednesday, April 04, 2007 9:15 AM by SimonSabin with 0 Comments

Developer Day 4

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Developer Day 4
The new RSS feed is here

If you've never been to a developer day then your missing a great day.

The day is split into a number of tracks with 5 sessions in each track. All the sessions are given by non-microsoft people and are generally based on real life experience, which makes the content great.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Developer Day 4

posted Thursday, November 23, 2006 11:49 AM by SimonSabin with 0 Comments

DMVs impact on performance

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/DMVs impact on performance
The new RSS feed is here

Bottom line is very little if any. The way the SQLOS has been rearchitected the dmvs have been built into at the lowest level. This means the data exists, it is not a virtual table that is built up when the dmv is queried.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/DMVs impact on performance

posted Thursday, November 16, 2006 2:58 AM by SimonSabin with 0 Comments

Checkpointing is now throttled in SQL 2005

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Checkpointing is now throttled in SQL 2005
The new RSS feed is here

During the iinterview with the SQLOS team this morning Slava mentioned that as part of the new design for the