Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Can anyone shed some light on this?

On our dev boxes, our database resides entirely in the PRIMARY filegroup, and everything works fine.

On one of our production servers, recently upgraded from 2005 to 2008, we noticed it was performing slower than it should. On this machine, there are two filegroups - PRIMARY and INDEXES. Both filegroups contain 1 file per logical volume, one logical volume per CPU, (and each logical volume is a RAID 10 of 4 physical disks).

We isolated a few queries that were performing fast on the dev boxes and slow (up to 40x slower) on the production machine. Turned out these queries were using the non-clustered indexes that resided in the INDEXES filegroup. Tweaking some of the queries to only use clustered indexes that were in the PRIMARY filegroup dropped their times back to normal.

As a final confirmation, we redeployed the same database on the same machine to have everything in PRIMARY, and things went back to normal!

Here's the statistics output of one of the queries, run identically on the machine with different filegroup configurations (table names changed to protect the innocent):

FAST (everything in PRIMARY filegroup):

(3 row(s) affected)
Table '0'. Scan count 2, logical reads 14, ...
Table '1'. Scan count 0, logical reads 0, ...
Table '1'. Scan count 0, logical reads 0, ...
Table '2'. Scan count 2, logical reads 7, ...
Table '3'. Scan count 2, logical reads 1012, ...
Table '4'. Scan count 1, logical reads 3, ...

 SQL Server Execution Times:
   CPU time = 437 ms,  elapsed time = 445 ms.

SLOW (indexes split into their own filegroup):

(3 row(s) affected)
Table '0'. Scan count 209, logical reads 428, ...
Table '1'. Scan count 0, logical reads 0,...
Table '2'. Scan count 1021, logical reads 9043,....
Table '3'. Scan count 209, logical reads 105754, ....
Table '4'. Scan count 0, logical reads 0, ....
Table '5'. Scan count 1, logical reads 695, ...
**Table '#46DA8CA9'. Scan count 205, logical reads 205, ...**
Table '6'. Scan count 6, logical reads 436, ...
Table '7'. Scan count 1, logical reads 12,....

 SQL Server Execution Times:
   CPU time = 17581 ms,  elapsed time = 17595 ms.

Notice the weird temp table and extra tables involved in the slow query. It seems clear that having a second file group is making SQL Server batty with choosing an execution plan. What the heck is going on?

share|improve this question
    
Do you guys have your max degree of parallelism hard coded? –  Robert Kaucher Oct 7 '10 at 17:31
    
No. Also, no tables or indexes are partitioned. –  womp Oct 7 '10 at 17:44
    
Can you post the execution plans for the two executions please? Also more storage details, are you using a SAN/DAS and what HBA/Storage controllers are being used? –  John Sansom Oct 11 '10 at 12:55

1 Answer 1

I believe the fact that the non-clustered indexes are on a different filegroup is irrelevant here, from the STATISTICS IO output above it's clear that you have two very different execution plans.

Did you rebuild indexes and update statistics after upgrading from SQL 2005 to SQL 2008? If not, you should do so as soon as possible. If you can post the execution plans of both runs (as well as the plan from the dev box), it may be possible to provide more insight.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.