Davide Mauri Blog

Experiences with SQL Server

Useful index scripts

At this address http://www.davidemauri.it/SqlScripts.aspx i've released four scripts that simplify the usage of SQL Server 2005 DMVs to discover, analyze and understand indexes usage and health. They are nothing exceptional, juts a shortcut to common (but long to write) queries. Read more →

Partitioning: Two useful query

To obtain all information on how a table has been partitioned I usually use two views: The first just list all the partitened objects, showing the used partitioned scheme and function: Read more →

A better NTILE implementation

The implementation of the NTILE(n) windowing function is a little bit slow and requires a temp worktable that generates A LOT of I/O. Probably that because, as the BOL says, if you have a number of rows that is not divisible by your "n" bucket value, the function has to make the "Larger groups come before smaller groups in the order specified by the OVER clause". Read more →

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. Read more →

Partitioning Automation - The Basics

Ok, let's start with the basic; again, remember that I assume that you have read the SQL Server 2005 Partitioned Tables and Indexes whitepaper. Another document on partitioning (which explaing more deeply the differences between LEFT and RIGHT partitioning) is also this one. Read more →

Sql Server 2005 Partitioning

A very interesting an powerful feature of Sql Server 2005 is called Partitioning. In a few word this means that you can horizontally partition the data in your table, thus deciding in which filegroup each rows must be placed. Read more →