SQL Server Partitioned Tables - Execution Plan

Hi,

Please can someone clarity the following related points for me.

I have a bunch of partitioned tables, view sat ontop.
Simplified table/view definitions at the end.

Now.... When I do this:
SELECT * FROM teamPart WHERE gameID='FFFF'

The Executiuon Plan in Query Analyser shows that it directly accesses the team_FFFF table, as I'd expect.

When I do this:
DECLARE @gameID CHAR(4)
SET @gameID='FFFF'
SELECT * FROM teamPart WHERE gameID=@gameID

The Execution Plan in Query Analyser shows that it includes all the tables in the view, I'm assuming because it doesn't know what the value of @gameID will be.

My question(s); obviously I'm talking generically and I appriciate each case is different.

1,
When the second @gameID query is actually run, does it still perform the Index Seek on every table?
or
Does it **really** just go to the partitioned table?

2,
Is there an inherent performance hit from using partitioned tables (generally speaking)
eg, Say a table has 10,000,000 rows, and we partitioned by gameID CHAR(4), into (say) 20 tables, most with roughly the same number of rows, a couple of the gameID partitions would have next to no rows. Let's assume indexes are perfect. All stored procs would always be able to pass in the @gameID
Would the partitioned "version" be inherently "slower" than the all rows in one table variety?

3,
Is there a (general) point at which the balance of performance swings in favour of partitioned tables.
eg, 100k rows, 1M rows, 10M rows, 100M rows etc.


Finally,
WHY do I ask? In my situation I have an application which has organically grown over 5 years, approx 1/4 million users use the site(s) that run on this database 24/7. There is only one DEVELOPER / dba (small dba, ie, I pretend to know what I'm doing but the pro's would grimace!) and I've only got a window of 1 month (July) each year in which it is realistic to make major changes to the structure.

We have a new client / website which could potentially bring 4 million users this year - so I need to quickly and dirtily allow the database to scale upto 10 times.

Currently the databse performance is fine under even the heaviest loads, except for one daily process when a seriously huge  query (in terms of its joins, calculations, updates, index updates across the data) needs to run to update pretty much all of the "important" data.

I figure that by partitioning the "important" tables into the @gameID I can do my updates one game at a time, and so at least only lock up one site in turn, rather than all at once.

Gentlemen, your thoughts please. URGENT, so 500 points.



--Simplified table/view definitions for the examples above:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.team_PRMU (
      gameID char(4) NOT NULL CHECK (gameID='PRMU'),
      teamID int NOT NULL,
      name char(10) NOT NULL
, CONSTRAINT PK_team_PRMU PRIMARY KEY CLUSTERED ( teamID ) WITH  FILLFACTOR = 90 ON [PRIMARY]
)
ON [PRIMARY]
GO
CREATE TABLE dbo.team_FFFF (
      gameID char(4) NOT NULL CHECK (gameID='FFFF'),
      teamID int NOT NULL,
      name char(10) NOT NULL
, CONSTRAINT PK_team_FFFF PRIMARY KEY CLUSTERED ( teamID ) WITH  FILLFACTOR = 90 ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE VIEW [dbo].[teamPart] AS
SELECT * FROM team_FFFF
UNION ALL
SELECT * FROM team_PRMU

GO
COMMIT


chris000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
testing ...
chris000Author Commented:
Anyone??

Any thoughts???
amit_gCommented:
I can answer partially ...

>> 1,
>> When the second @gameID query is actually run, does it still perform the Index Seek on every table?
>> or
>> Does it **really** just go to the partitioned table?

No. Index seek is not done on every table. Only dbo.team_FFFF will be scanned. The execution plan cannot show the correct plan as it doesn't have value of @gameID. Run the query with SET STATISTICS IO and you will see that there is no activity in any other table.

I think there should not be a performance issue as long as each query passes @gameID but I haven't done or seen real benchmarks so I can only say that theoritically. For the same reason I can't offer the breaking point if there is one.

Post a new 20 point question with a link to this question in the same TA and may be someone who has previously missed this question can offer some help. DO NOT post a follow-up link until next week as it is most likely to be missed by experts who don't login or are not logged in continuously during the weekend.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

chris000Author Commented:
Thanks Amit - I'm window of opportunity for the changes has now closed.... so the answers are now academic.

I'n August I'll find out for real in the live environment if I've messed up big time or not!

Closing the Q and will attempt to award you 1/3 of the points = 170pts of answering 1/3 of the Q's.
Hope you don't think I'm being stingy.....
chris000Author Commented:
Nope........ can't do that........ I guess you can have all the points then!
amit_gCommented:
Please keep us informed of your experience.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.