Posts
49
Comments
50
Trackbacks
26
October 2005 Blog Posts
SQL Server 2005 Indexes: How many and how big?

This little snip of code allows you to list how many indexes are present on a table, their type and how big (kb and mb) they are:

select
    [name],
    type_desc,
    space_used_in_kb = (page_count * 8.0),
    space_used_in_mb = (page_count * 8.0 / 1024.0)
from
    sys.indexes I
inner join
   
sys.dm_db_index_physical_stats(db_id(), object_id('.'), null, null, null) P on I.[object_id] = P.[object_id] and I.[index_id] = P.[index_id]

 

posted @ Monday, October 31, 2005 3:59 PM | Feedback (5)
Sql Server Broker Management Studio

SQL Server 2005 Service Broker is a killer application...but you may get lost with all the configuration settings and objects you need to set up and manage so that you can use it effectively.

For this reason this great guy has done a great tool, that allows you to do everything visually, using a sort of Service Broker dedicated management studio.

http://www.digitaldeposit.net/blog/2005/09/sql-service-broker-management-studio.html

This is a tool-to-have if you're going to use Service Broker!

posted @ Monday, October 31, 2005 8:19 AM | Feedback (0)
The most important developers Italian Event: WPC 2005

For the 3rd consecutive year i'll be at the most important developers conference: WPC. I have four sessions and i'll talk about:

  1. Indexes
  2. Reporting Services
  3. Integration Services
  4. Service Broker

If you're interested you can find more detalis here (Italian language only).

posted @ Sunday, October 30, 2005 6:18 PM | Feedback (0)
Beware! SELECT TOP is BUGGED!

Yes it is true. And this bug has just been discovered by me and confirmed by the Query Optimizer Project Lead (Conor Cunningham) himself, that i have the chance to meet at SQL PASS 2005.

He has been very kind and after I exposed the problem to him, after a little bit of suprise :-), in few days he confirmed that in some circustances SET ROWCOUNT, which is deprecated, is faster than the usually better SELECT TOP.

You can test it yourself, downloading the script right here. I must say that the problem only shows when you need to limit the rows returned by your query while you're sorting over a colum with no index. At least this is my experience. Fortnately this is really an uncommon scenario so the bug is not really a tremendous one, but ouy should better know it since using SET ROWCOUNT under this cirumnstances can speed up your query by more that 3/4 times!

I've been also confirmed that this bug will not be fixed in the forthcoming version of SQL Server 2005 but will be fixed in the first service pack.

So, as we say in Itay: "Man advised man half saved" :-)

 

posted @ Friday, October 21, 2005 1:35 PM | Feedback (10)
News