Index Maintenance across all Databases in an Instance

I needed to make a job for a customer that rebuilds all indexes in an instance that have a fragmentation of more than 10%. Usually we use a maintenance plan for rebuilding indexes. Although that is a fine approach is has one disadvantage: it rebuilds all indexes in a database regardless of the level of…

2

Table Partitioning Conclusions

Today I tested all my code and made changes where I made mistakes. And I made quite a few! My biggest mistake was with the GetDate() function. I made partitions with milliseconds which was not my intention! I changed the code in my last post where necessary. If you still encounter bugs, please contact me….

2

Table Partitioning Sliding Window Case

As I mentioned in my last post I’m currently working on a complex partitioning project for a customer. They need partitioning in order to speed up inserts on the table. Lots of inserts take place on this table. Right now they have around 2 million inserts per day  and this will grow quickly to 4…

5

Table Partitioning Basics(2)

In my last blog I looked at SPLIT and MERGE operations on partition functions. SPLIT and MERGE are nice functions but I use them with care. A merge is not the best performing function there is…. And that is completely logical when you think about everything that is happening during execution: Data needs to be…

2

Table Partitioning Basics

Partioning objects in SQL Server starts with defining Partition Functions and Partition Schema’s. The functions split your data and the schema’s place it somewhere. A function can be used by multiple schema’s. You start out by creating a function:     CREATE PARTITION FUNCTION [pfSample](datetime) AS RANGE LEFT FOR VALUES (1, 100, 1000);    …

4

Synonyms and other Oracle stuff

Every now and then I meet Oracle specialists at customers sites. Sometimes they start a good discussion on the differences between the Oracle engine and the SQL Server engine. I don’t mind a good discussion on the differences but sadly lots of Oracle guys compare 11G to SQL Server 6.5. (btw don’t get me wrong…


Table Partitioning in SQL2008(2)

Today I proceeded with partitioning a large table in SQL 2008. The objective is to build a partition function with a sliding window. Usually you use staging tables to get to this. This customer wants to keep all his data in one table so staging tables cannot be used. That’s a pity because then we…

1

Table Partitioning in SQL 2008

At the moment I’m working for a client that wants to partition some tables. They need to do that because the tables have grown to immense proportions. One table has 800.000.000 rows. Obviously a lot of inserts are happening on this tables and they hope to improve the performance of these inserts by partitioning the…

4