Performance - SimonS Blog on SQL Server Stuff

Browse by Tags

All Tags » Performance (RSS)
08 January 2008 22:41

SQL Server 2008 Spatial - Performance of database calls follow up

I recently blogged about the performance of passing spatial data to the database.( SQL Server 2008 Spatial - Performance of database calls? ) In it I found that the the performance radically dropped off as the number of points increases. Whilst this was...
Posted by simonsabin | with no comments
Filed under: , , ,
07 January 2008 21:32

SQL Server 2008 Spatial - Performance of database calls?

There are 4 ways of holding spatial data, Well Known Text (WKT), Well Known Binary (WKB), Geography Markup Language and the base spatial type (SqlGeometry or SqlGeometry). This leads me on to the question how should you pass the data to SQL Server. I...
Posted by simonsabin | 1 comment(s)
Filed under: , , ,
04 January 2008 17:58

SQL Server 2008 Spatial - Which is faster WKT or WKB?

I've just posted two methods for creating geometries in client code. The examples use Stylus points from an ink canvas but any collection of points will go. After doing that I ran some tests to compare the two. I found that in generally the WKB method...
Posted by simonsabin | 1 comment(s)
Filed under: , , , ,
18 December 2007 15:31

Get the tools the professionals use - ReadTrace is now available for SQL 2005

One gripe about SQL 2005 was that read80trace wasn't available for it. That has now been rectified with the release of RML Utilities for SQL Server. This is an application that has a set of utilities for reading, analysing and replaying trace files. This...
Posted by simonsabin | 1 comment(s)
Filed under: , ,
16 November 2007 11:31

SQL Server Myths - Disk Queue length a bit like buying Guiness

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...
Posted by simonsabin | 1 comment(s)
Filed under: ,
31 July 2007 23:10

How to speed up partition merging

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...
Posted by simonsabin | with no comments
Filed under: , , ,
20 July 2007 10:32

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

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. Now however the non-clustered...
Posted by simonsabin | 3 comment(s)
Filed under: , , , , , ,
27 June 2007 09:33

Blobs block online indexing

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...
Posted by simonsabin | 1 comment(s)
Filed under: , , ,
26 June 2007 13:51

To cluster of not

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...
Posted by simonsabin | 4 comment(s)
Filed under: , , ,
16 May 2007 17:02

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

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 ) , * from sys.dm_db_index_usage_stats s join sys.indexes i on i . index_id...
Posted by simonsabin | 1 comment(s)
Filed under: , , ,
15 May 2007 20:34

SSIS - Parallel processing

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. If you've used the RTM on Yukon you will know...
Posted by simonsabin | with no comments
Filed under: , , ,
04 April 2007 09:15

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

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...
Posted by simonsabin | with no comments
Filed under: , ,
23 November 2006 11:49

Developer Day 4

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...
Posted by simonsabin | with no comments
Filed under: , ,
16 November 2006 02:58

DMVs impact on performance

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. So go for it use the DMVs and...
Posted by simonsabin | with no comments
Filed under: , , ,
16 November 2006 02:47

Checkpointing is now throttled in SQL 2005

During the iinterview with the SQLOS team this morning Slava mentioned that as part of the new design for the SQLOS the checkpoint process is much tightly controlled. If the checkpoint process has issues pages to be written and the latency has become...
Posted by simonsabin | with no comments
Filed under: , ,
More Posts Next page »