Contents tagged with Performance

• Script to analyse space usage by your Service Broker

  Tags: Service Broker, Script, Admin, Performance, Monitoring, Space

  Apr 16 2012 12:51 PM

  (521 views) - 0 Comments

  I believe that service broker is one of the best features from SQL server 2005, well along with the xml data type, and I use service broker a lot for messaging between solutions. Over the years it has proven to be extremely reliable; only really stopping because of a poison message or other environment type issue outside the control of the broker.

  Service broker itself uses a number of internal tables which it uses to store details regarding conversations and routing, and there are also any … more

• Revised: Difference between collation SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS

  Tags: Collations, Unicode, Performance, varchar, nvarchar

  Jan 9 2012 11:41 AM

  (5389 views) - 14 Comments

  This is a revised post of an original article that I first published in December. Even though I thought that I was very meticulous in my research, something was pointed out to me that I was totally unaware of - Unicode character expansions. So apologies to the community with regard the first article that I published as some of the original content could have been misleading. Here is a new revised article that explains character expansions and how it can affect the comparison of data with these … more

• Convert comma separated string to table and vice versa

  Tags: Performance, Tally Table

  Oct 27 2011 8:11 PM

  (875 views) - 1 Comment

  I know that this subject has been blogged about numerous times but I thought I'd post an alternative way of shredding a comma separated string to a table that I come up with today, albeit with help from the very clever Itzik Ban-Gan cross join nested CTE that many of us use to generate large tally tables quickly.

  For sure, in the ideal world we wouldn't have to do this as there isn't an easy and efficient way to do this in T-SQL. There are much better ways of passing this type of data to the … more

• Simple XML exist performance enhancement

  Tags: Xml, Performance

  Oct 13 2011 9:11 PM

  (350 views) - 1 Comment

  I came across this really simple best practice from BOL for the xml method exist which when used will give you a much cleaner and faster query plan. If like me you commonly use the name of an element or attribute as part of an XQuery predicate, then you would benefit immediately from this.

  So lets create a table with a single blob of xml and try out two queries to show this in action. The full script can be downloaded from here

  Setup

  --Create example table

  CREATE TABLE XmlTable

  (

  Id INT … more