Query performance differs significantly across partitions of the same table

Question

Query performance differs significantly across partitions of the same table

LondonDJ

Newbie

Points: 2
More actions
June 1, 2009 at 11:31 am

#200996

Hi,
We have a table with a few hunderd million rows of data in it. It is partitioned based on a date column. Each partition contains about 80 million rows.
Today we are having major performance issues. When we run a query for yesterday's date it is running in about 10 minutes. When we run a query for today's date it is running in about 2 hours. We have the same volume of data for each date and each query returns a similar number of rows.
We are analysing the query plans but I'm wondering if there is a problem with some of the partitions now since it works fine for some partitions and runs very slowly from others.
Any thoughts or opinions would be appreciated.
Many Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

What do the index fragmentation and density levels look like between the two partitions? What differences are there in execution plans, and what if any differences exist for the Statistics IO are there between the two runs?

LondonDJ Newbie Points: 2 More actions · Answer 2

Thanks for your reply Jonathan

How do I get the index fragmentation for a partition in the table?

How do I get the Statistics IO?

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 3

You can get IO Statistics using SET STATISTICS IO and can then turn them on using SET STATISTICS IO ON and turn them off later on using

SET STATISTICS IO OFF.

Index fragmentation can be obtianed using DMF sys.dm_db_index_physical_stats.

And if u find too fragmented then u can defragmented ur indesex using ALTER INDEX command

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

Use the sys.dm_db_index_physical stats DMF to get the fragmentation information:

http://msdn.microsoft.com/en-us/library/ms188917.aspx