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 →
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 →
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 →
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 →
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.
Read more →
I've put online the second article of the three: you can find it here.
Read more →
wip
Read more →
<o:p></o:p>
After the introduction given in the article 1, we now have all the information we need in order to import data in a fast and non-locking way into our partitioned table.
Read more →
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 →
As you should have read in the fantastic Kimberly WhitePaper, partioning gives you an incredible opportunity to load data into your table minimizing locking and having the best performace you can imagine.
Read more →
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 →