This blog post is about SQL Azure and not SQL server. And it’s also not about importance of indexes. We know that indexes are important in the world of SQL server as well as in the world of SQL Azure. But in the world of SQL Azure, there is an important concern – when you create indexes, they would occupy “Space” and it will increase the database size. So what’s the point here?  The point is that the pricing model of SQL Azure is database size driven. So bigger the database size, bigger would be your bill. It’s that simple. So to figure out the cost of indexes, we need to know the size of indexes and, We do have a TSQL query to know the size of each index in a database/table.

So here is a TSQL Query that would list indexes in a given database along with its size in KB.

[code]
SELECT
SI.name, SUM(PS.reserved_page_count) * 8.0 as "Size in KB"
FROM sys.indexes AS SI
JOIN sys.dm_db_partition_stats AS PS
ON SI.object_id = PS.object_id
AND PS.index_id = SI.index_id
GROUP BY
SI.name
ORDER BY
2 DESC
[/code]

I ran the above query by logging into the SQL Azure database ‘AdventureWorksLTAZ2008R2’ (which is a sample database for SQL Azure) as a service administrator and here’s the result:

You can also list indexes along with its size for a particular table in a database. Here’s the TSQL code (Replace SalesLT.product name with your table name):

[code]
SELECT
SI.name, SUM(PS.reserved_page_count) * 8.0 as "Size in KB"
FROM sys.indexes AS SI
JOIN sys.dm_db_partition_stats AS PS
ON SI.object_id = PS.object_id
AND PS.index_id = SI.index_id
WHERE SI.object_id = object_id(‘SalesLT.product’)
GROUP BY
SI.name
ORDER BY
2 DESC
GO
[/code]

Now once you know the size of the index – the next task is to calculate the cost.
The cost of SQL Azure is dependent on the size of a database and here is the pricing model: https://www.windowsazure.com/en-us/pricing/details/#database

And one can definitely embed the pricing logic in the TSQL Query we just saw. check this article for reference: http://blogs.msdn.com/b/sqlazure/archive/2010/08/19/10051969.aspx – Just note that the pricing model is slightly different now. The pricing model is same for database size upto 50 GB. But when you above 50 GB (upto MaxSize 150 GB) there is NO additional cost. so essentially. 50 GB db = $499.95 per month and 150 GB = $499.95 per month. Update your TSQL queries accordingly!

So, Make sure that you use indexes that you have created And Drop indexes that are not used. you need to evaluate performance gain vs cost for your scenario.

Note:

• I am not implying that Indexes on SQL server are “free”. They consume resources in the world of SQL server too. This post just happen to focus on SQL Azure side of it.
• The Pricing Model of SQL Azure may change in future.
• You also need to evaluate performance impact of creating indexes on create, update and delete queries