Performance improvement by orders of magnitude when merging partitions in SQL Server 2008R2

I am publishing this Blog on behalf of Hermann Daeubler, our SAP expert Scenario: Let’s assume we have a table consisting of five partitions and the one in the middle needs to  be dropped. In SQL Server we need the following steps to accomplish this task a)      Create a non-partitioned target table with the same structure and… Read more

TechEd session video available…

The session I gave at TechEd this year on ‘Secrets of Fast Detection and Recovery from Database Corruptions’ was videotaped as part of the Its Showtime! TechEd program. The video is now available to watch at http://www.microsoft.com/emea/itsshowtime/sessionh.aspx?videoid=549. This is the same session I’ve been delivering to user groups around the world for the past few months but… Read more

What happens to non-clustered indexes when the table structure is changed?

Here’s a topic that’s cropped up several times during Q&As at TechEd this year – what happens to non-clustered indexes when changes are made to the underlying table? Are they always rebuilt or not?   Before we get into that discussion, I’ll give you a little background.   One way to describe a non-clustered index… Read more

Can you mix-n-match backup devices?

I was asked this question yesterday and didn’t know the answer so thought it would be good for a quick post. Can you use devices from your main and mirrored backup media sets together to perform a restore? The code below creates a single-device backup and then examines it. BACKUP DATABASE AdventureWorks TO DISK =… Read more

How can you tell if an index is being used?

Whenever I’m discussing index maintenance, and specifically fragmentation, I always make a point of saying ‘Make sure the index is being used before doing anything about fragmentation’.   If an index isn’t being used very much, but has very low page density (lots of free space in the index pages), then it will be occupying a… Read more

How to avoid using shrink in SQL Server 2005?

Late night blog post to round out spring-break vacation…   A number of customers I’ve spoken to in the last few weeks have been making use of database or file shrink in situations where they don’t really need to. There are few recurring scenarios I’ve seen: Deleting a lot of data and then taking a… Read more

SP2 Maintenance Plan bugs fixed

From the tools team here in Redmond: We recently posted updates to address an issue in two SP2 Maintenance Plantasks. The KB article describes the symptoms as follows:FIX: The Check Database Integrity task and the Execute T-SQL Statement taskin a maintenance plan may lose database context in certain circumstances inSQL Server 2005In Microsoft SQL Server… Read more

VLDB Maintenance – quick addendum

So it seems that Microsoft’s email system barfs when some people try to send email with ‘survey’ in. And I was unclear how big a database I’m interested in. To be honest, anything over 100 GB or so starts to require careful maintenance planning. So, if you tried to send email and it bounced, or… Read more

VLDB Maintenance Practices and Problems?

It’s survey time again. I’ll be doing lots around VLDB maintenance in the coming months so I’d like to get more info from all of you about what’s happening in the field. I’ll be working on Katmai features, strategizing about Katmai+, blogging on maintenance topics, planning lectures, including a session for TechEd – VLDB Maintenance… Read more