SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Behaviour in partitioned database.


Behaviour in partitioned database.

Author
Message
ben.brugman
ben.brugman
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2472 Visits: 2417
Hello all,

At the moment I am trying to come to grips with partitioning.
I am trying a number of scenario's to gain knowledge about partitioning and how to use partitioning. I did read a number of articles on the web, but getting your feet wet with actual code does help as wel.

Queries.
1. Selection on the partkey, (clientid,) startdttm (Est. S c. 0.0071077)
2. Selection on the startdttm (Estimated Subtree cost 0.0183308)
3. Selection on the clientid, startdttm (Est. S. c. 0.10641)


For 3 far more diskreads are done Alien

Index on startdttm is not partitioned. (Used in 1 and 2 and 3)
Index on clientid is partitioned. (Used in 3)

To me this lookes like that the optimizer does not or hardly looks at the fact that some indexes are partitioned and some are not.

Does the optimiser take into account that indexes can be partitioned?

Sorry I am not able to post a complete example with script to script the table and the contents.

My conclusions:
1. Using partitioning is a riscy bussiness.
2. The optimiser does not handle partitioning very wel.

I can make some guesses why this is happening, but can anyone explain this behaviour to me?

ben brugman



dbcc dropcleanbuffers
dbcc freeproccache
SET STATISTICS TIME ON
SET STATISTICS IO ON
select * from contact where
PartKey = 15
and ClientID = 1000000010984
and startdttm = '2012-03-16 07:32:00.000'
-- Table 'contact'. Scan count 1, logical reads 4, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- CPU time = 0 ms, elapsed time = 108 ms.


dbcc dropcleanbuffers
dbcc freeproccache
select * from contact where
startdttm = '2012-03-16 07:32:00.000'
-- Table 'contact'. Scan count 1, logical reads 4, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- CPU time = 0 ms, elapsed time = 91 ms.

dbcc dropcleanbuffers
dbcc freeproccache
select * from contact where
ClientID = 1000000010984
and startdttm = '2012-03-16 07:32:00.000'
--Table 'contact'. Scan count 30, logical reads 62, physical reads 61, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- CPU time = 0 ms, elapsed time = 1202 ms.


Index information
objectname indexname partitions
contact NUNCI_contact_STRTDTTM 1
contact NUNCI_ENCTR_CLIIDENDDT 30
contact PK_contact 30

Index information
2 [PK_contact] nonclustered, unique, primary key located on fclientPScheme [ID], [PartKey]
6 [NUNCI_contact_CLIIDENDDT] nonclustered located on fclientPScheme [clientID], [EndDTTM]
10 [NUNCI_contact_STRTDTTM] nonclustered located on NCIndexFG [StartDTTM]


GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217865 Visits: 46278
https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19119 Visits: 7410
You may or may not need partitioning.

But you absolutely need to determine the proper clustered index for the table.

It's the column(s) you (almost) always specify in a WHERE clause, particularly if they are in a range.

In your case, it's likely "startdttm", although I can't say for sure without more details.

That will give you vastly better performance, partitioned or not.


Then, if you do partition, partition on the clustering key. Partitioning does allow you to specify different compression for each partition, if you want to (i.e., you can compress older, historical data w/o being forced to compress current data, even though both are in the same table). Sadly, I don't think we can yet specify a different FILLFACTOR for each partition, which could be tremendously helpful as well.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
ben.brugman
ben.brugman
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2472 Visits: 2417


Thank you Gail for the link.
From the link:
'summary would be: don't change or implement anything without research and without knowing what it's going to affect. '

My quest at the moment is introducing Clustering for a number of tables. By now I have shown that clustering will give an significant improvement for allmost all actions. (Select/Insert/Update/Delete, complex statements, cache). And no significant degradation for any action.
But I do not have the resources to benchmark all situations. (Partitioning and thousands of users is one of them and a enormously large number of potential statements).

But the company is 'a bit' reluctant to the change. So I am trying to gain some knowledge about partitioning, do this by reading articles (yours) and by performing some simple 'tests', hence my question. With the understanding of internals I am in a stronger position.

Thanks for the link,
Ben Brugman
ben.brugman
ben.brugman
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2472 Visits: 2417
ScottPletcher (1/29/2013)
You may or may not need partitioning.

But you absolutely need to determine the proper clustered index for the table.


I totally agree that a clustered index will help performance in our situation. But to convince the 'decision' makers, I have to know the effect in our sitiuation, hence my questions about partitioning.

Thx,
ben

(The example comes from a table with 13 indexes. And startdttm is not the most used index. Using a clustered index on another field, might give similar results.).
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19119 Visits: 7410
ben.brugman (1/31/2013)
ScottPletcher (1/29/2013)
You may or may not need partitioning.

But you absolutely need to determine the proper clustered index for the table.


I totally agree that a clustered index will help performance in our situation. But to convince the 'decision' makers, I have to know the effect in our sitiuation, hence my questions about partitioning.

Thx,
ben

(The example comes from a table with 13 indexes. And startdttm is not the most used index. Using a clustered index on another field, might give similar results.).



startdttm was the only column used in all three of your sample queries. If you have other queries that are more representative of the normal work load than what you've provided us with as we try to assist, then of course go by the more represenative queries.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
ben.brugman
ben.brugman
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2472 Visits: 2417
ScottPletcher (1/31/2013)

startdttm was the only column used in all three of your sample queries. If you have other queries that are more representative of the normal work load than what you've provided us with as we try to assist, then of course go by the more represenative queries.


This was a query with a suprising effect. If you compare the 2 situation with the 3 situation you can see that the query is almost the same, but that for the 3 situation an extra field is used for selection.

compare:
2: Select * from A where B = 1
3: Select * from A where B = 1 and C = 2

What did suprise me that the second query was a ten fold more expensive. Both in estimated cost as in diskreads.

If for the situation 2 the number of diskreads is 4, for situation 3 the diskreads can always be done in the same number or less reads than in situation 2.
But the optimizer chooses to take another path this did supprise me.
Hence the question.

Ben
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search