Partitions on RAID Array

We have a 12 disk 2.4TB Raid 10 Array, where we have 3 large database data
files, theres been a suggestion that we split the physical drive into 3
partitions so if we have to grow the files in future the files will stay
contiguous on the disk.. ..I'm not worried about splitting the array into
three from a size point of view we have bags of space for future growth..
...I'm aware the databases should be sized properly so they don't autogrow and
that minimal fragmentation at the os level will have little to no impact on
the performance... ...however for sake of argument will splitting the RAID
array into the 3 partitions keep the database files contiguous on the disks,
and would there be any performance hit on the array by splitting it into 3
volumes?
Thanks in advance
Ben
I'd be inclined to not create three logical partitions. I don't have any
current empirical data to back me up on this. But I do remember a Compaq
study a few years ago on using a single partition vs. multiple logical
partitions on a physical device, and the single partition configuration came
out better performance wise. I'm not claiming that the Compaq study is still
relevant. But then I don't see any substantial benefit of creating three
logical partitions.
The practice I follow has always been: single physical device (as presented
to the OS) -> single partition -> single NTFS volume.
Do note that if you have files on the same volume, moving a file to another
place on that same volume is a filesystem metadata operation. Moving a file
across volumes, however, must move data. If you don't plan to move your
files, this is not an issue.
Linchi
"Ben UK" wrote:

> We have a 12 disk 2.4TB Raid 10 Array, where we have 3 large database data
> files, theres been a suggestion that we split the physical drive into 3
> partitions so if we have to grow the files in future the files will stay
> contiguous on the disk.. ..I'm not worried about splitting the array into
> three from a size point of view we have bags of space for future growth..
> ..I'm aware the databases should be sized properly so they don't autogrow and
> that minimal fragmentation at the os level will have little to no impact on
> the performance... ...however for sake of argument will splitting the RAID
> array into the 3 partitions keep the database files contiguous on the disks,
> and would there be any performance hit on the array by splitting it into 3
> volumes?
> Thanks in advance
> Ben

Partitions on RAID Array

We have a 12 disk 2.4TB Raid 10 Array, where we have 3 large database data
files, theres been a suggestion that we split the physical drive into 3
partitions so if we have to grow the files in future the files will stay
contiguous on the disk.. ..I'm not worried about splitting the array into
three from a size point of view we have bags of space for future growth..
..I'm aware the databases should be sized properly so they don't autogrow a
nd
that minimal fragmentation at the os level will have little to no impact on
the performance... ...however for sake of argument will splitting the RAID
array into the 3 partitions keep the database files contiguous on the disks,
and would there be any performance hit on the array by splitting it into 3
volumes?
Thanks in advance
BenI'd be inclined to not create three logical partitions. I don't have any
current empirical data to back me up on this. But I do remember a Compaq
study a few years ago on using a single partition vs. multiple logical
partitions on a physical device, and the single partition configuration came
out better performance wise. I'm not claiming that the Compaq study is still
relevant. But then I don't see any substantial benefit of creating three
logical partitions.
The practice I follow has always been: single physical device (as presented
to the OS) -> single partition -> single NTFS volume.
Do note that if you have files on the same volume, moving a file to another
place on that same volume is a filesystem metadata operation. Moving a file
across volumes, however, must move data. If you don't plan to move your
files, this is not an issue.
Linchi
"Ben UK" wrote:

> We have a 12 disk 2.4TB Raid 10 Array, where we have 3 large database data
> files, theres been a suggestion that we split the physical drive into 3
> partitions so if we have to grow the files in future the files will stay
> contiguous on the disk.. ..I'm not worried about splitting the array into
> three from a size point of view we have bags of space for future growth..
> ..I'm aware the databases should be sized properly so they don't autogrow
and
> that minimal fragmentation at the os level will have little to no impact o
n
> the performance... ...however for sake of argument will splitting the RAID
> array into the 3 partitions keep the database files contiguous on the disk
s,
> and would there be any performance hit on the array by splitting it into 3
> volumes?
> Thanks in advance
> Ben|||Hi Ben,
I tend to agree with Linchi, that splitting it up might not give you
much in term of performance. If you should gain from splitting the files
up, you should split them up on physical different spindles and best of
all on seperate disk controllers. If you go down that route, you should
at the same time add some more spindles to each array - otherwise I
don't think you'll gain anything from the change.
You can also try to look at www.storageperformance.org to see if you can
find some usefull info in there.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
Ben UK wrote:
> We have a 12 disk 2.4TB Raid 10 Array, where we have 3 large database data
> files, theres been a suggestion that we split the physical drive into 3
> partitions so if we have to grow the files in future the files will stay
> contiguous on the disk.. ..I'm not worried about splitting the array into
> three from a size point of view we have bags of space for future growth..
> ..I'm aware the databases should be sized properly so they don't autogrow
and
> that minimal fragmentation at the os level will have little to no impact o
n
> the performance... ...however for sake of argument will splitting the RAID
> array into the 3 partitions keep the database files contiguous on the disk
s,
> and would there be any performance hit on the array by splitting it into 3
> volumes?
> Thanks in advance
> Ben

Partitions OLAP SQL 2005

Hi,
How I can improve particions on Analysis services?
Somebody can recommend me a link on this...?
Thanks,
Cecilia
Chile
My implementation is on SQL 2005
Thanks,
Cecilia
Chile
|||is your question more about "How to use partitions to improve performance?"
"Hoody" <Hoody.cu@.gmail.com> wrote in message
news:1163432116.400716.46580@.h48g2000cwc.googlegro ups.com...
> Hi,
> How I can improve particions on Analysis services?
>
> Somebody can recommend me a link on this...?
>
> Thanks,
> Cecilia
> Chile
>

Partitions OLAP SQL 2005

Hi,
How I can improve particions on Analysis services?
Somebody can recommend me a link on this...?
Thanks,
Cecilia
ChileMy implementation is on SQL 2005
Thanks,
Cecilia
Chile|||is your question more about "How to use partitions to improve performance?"
"Hoody" <Hoody.cu@.gmail.com> wrote in message
news:1163432116.400716.46580@.h48g2000cwc.googlegroups.com...
> Hi,
> How I can improve particions on Analysis services?
>
> Somebody can recommend me a link on this...?
>
> Thanks,
> Cecilia
> Chile
>

Partitions OLAP SQL 2005

Hi,
How I can improve particions on Analysis services?
Somebody can recommend me a link on this...?
Thanks,
Cecilia
ChileMy implementation is on SQL 2005
Thanks,
Cecilia
Chile|||is your question more about "How to use partitions to improve performance?"
"Hoody" <Hoody.cu@.gmail.com> wrote in message
news:1163432116.400716.46580@.h48g2000cwc.googlegroups.com...
> Hi,
> How I can improve particions on Analysis services?
>
> Somebody can recommend me a link on this...?
>
> Thanks,
> Cecilia
> Chile
>

Partitions not stored in the cube file

[Sorry - I'm throwing alot up here today - I know]

All partition info is stored in a <cubename>.partitions file.

I'm just wondering why this information is not stored in the <cubename>.cube file in the BIDS project. Especially considering that, if you view the code of <cubename>.cube there is an empty <Partitions /> tag for each Measure group.

Seems a bit strange to me. Any idea why this is?

-Jamie

The partitions are written in a separate file because it's common for projects to have many partitions and they can create a performance problem. The infrastructure for BI projects doesn't allow (or it allows with considerable effort not worth doing) loading a project file partially, on-demand as various elements are accessed in UI. Instead, the entire project file content is loaded into memory structures (AMO objects) that are displayed and edited in UI. By having the partitions into a separate file, we can open the cube editor for common task without loading all the partitions. And when the user goes to the Partitions tab (of cube editor), then partitions are loaded.

Adrian Dumitrascu

|||

Adrian,

Thanks, that makes sense. One other question then, why is there an empty <Partitions> element in the .cube file for each measure group?

Regards

Jamie

|||

The empty Partitions element is optional there. As it's now implemented, it's a sign that the MeasureGroup has Partitions in the separate .partitions file, but this might change in the future, it's not a guaranteed behaviour.

Adrian Dumitrascu

|||

Adrian Dumitrascu wrote:

The empty Partitions element is optional there. As it's now implemented, it's a sign that the MeasureGroup has Partitions in the separate .partitions file, but this might change in the future, it's not a guaranteed behaviour.

Adrian Dumitrascu

Thanks again Adrian!

Partitions as SQL Server Resources in Clustering Services

I've got 2 Arrays on my SAN.
Array 1 is 66 gb.
Array 2 is 230 gb.
I've partitioned the drives in MS2003 this way:
Array 1, Drive Q: (Quarum) 2gb
Array 1, Drive S: 64gb
Array 2, Drive R: 230gb.
I installed SQL Server after setting up MSCS.
Everything went fine.
Now I want to add the Drive S: as a resource in MSCS, so I can store
my transaction logs on it.
MSCS seems to look for a Physical Disk.
Is this possible?
Oh its possible all right. MSCS has to have a Physical disk, as you have
noticed. You have two to the OS> 1 - 66 GB, 1 230 GB.
Cheers,
Rod
"Travis" <twillard@.generasystems.com> wrote in message
news:bc4cf00.0406161203.1b035e4a@.posting.google.co m...
> I've got 2 Arrays on my SAN.
> Array 1 is 66 gb.
> Array 2 is 230 gb.
> I've partitioned the drives in MS2003 this way:
> Array 1, Drive Q: (Quarum) 2gb
> Array 1, Drive S: 64gb
> Array 2, Drive R: 230gb.
> I installed SQL Server after setting up MSCS.
> Everything went fine.
> Now I want to add the Drive S: as a resource in MSCS, so I can store
> my transaction logs on it.
> MSCS seems to look for a Physical Disk.
> Is this possible?
|||Sorry, I'm probably not being clear.
Cluster Group has 1 physical disk, Q:
SQL Server has 1 physcial disk, R:
I've broken the cluster's physical disk into two partitions: Q and S.
How can I get my SQL Sever group to see the S partition as a resource,
so i can store my logs there?
When I try to add it as a physical drive, it of course doesn't show up.
Is there another resource type that allows me to see the partition?
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Ok, so here is my trouble.
When i go into add a resource as a phyical dive, no drive letter shows
up.
How do I add the S: drive so that the SQL Cluster can see it?
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||You have all 3 drive letters already in the cluster. Double click on the Q
drive and look at parameters, notice it says Q & S. That is because
clustering goes by the physical disk and not partitions.
You add another array or have your logs and quorum on the same physical
disk, but different partitions of it.
Cheers,
Rod
"Travis Willard" <twillard@.generasystems.com> wrote in message
news:e2%23Tjs%23UEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Sorry, I'm probably not being clear.
> Cluster Group has 1 physical disk, Q:
> SQL Server has 1 physcial disk, R:
> I've broken the cluster's physical disk into two partitions: Q and S.
> How can I get my SQL Sever group to see the S partition as a resource,
> so i can store my logs there?
> When I try to add it as a physical drive, it of course doesn't show up.
> Is there another resource type that allows me to see the partition?
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||See my other reply
Cheers,
Rod
"Travis Willard" <twillard@.generasystems.com> wrote in message
news:%23dkIms%23UEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Ok, so here is my trouble.
> When i go into add a resource as a phyical dive, no drive letter shows
> up.
> How do I add the S: drive so that the SQL Cluster can see it?
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it
|||Hi Travis,
It depends on how exactly the drives have been configured in Array 1. If Q:
and S: are file system partitions on the same 'disk' (LUN in the array) then
you will not be able to add a new Physical Disk resource. If they are single
partitions each on their own 'disk' (LUN in the array) then you will be able
to if you first set the disk up for the o/s, basically by creating and
formatting the 64 GB NTFS partition (if you don't use NTFS the disk won't
appear in the GUI when you try to add the resource).
The Cluster Administrator GUI to add a physical disk resource queries the
Clusdisk key in HKLM\System\CCS\Services and looks for disk signatures (4
byte ID numbers generated by the o/s). These signatures are created and
written to the disk's master boot record (defined as first sector on the
disk, before the file system partitions begin, but remember in an array this
all virtualised) when you first use the Disk Management snap-in on a disk in
Computer Management.
Upshot of this is after you have configured the LUNs in the array you have
to configure the disk in Disk Management for it to be visible to the
clusdisk driver and therefore appear in the GUI to add a Physical Disk
resource.
Hope this helps.
"Travis" <twillard@.generasystems.com> wrote in message
news:bc4cf00.0406161203.1b035e4a@.posting.google.co m...
> I've got 2 Arrays on my SAN.
> Array 1 is 66 gb.
> Array 2 is 230 gb.
> I've partitioned the drives in MS2003 this way:
> Array 1, Drive Q: (Quarum) 2gb
> Array 1, Drive S: 64gb
> Array 2, Drive R: 230gb.
> I installed SQL Server after setting up MSCS.
> Everything went fine.
> Now I want to add the Drive S: as a resource in MSCS, so I can store
> my transaction logs on it.
> MSCS seems to look for a Physical Disk.
> Is this possible?
|||Thanks Peter,
I was able to solve the problem with a little trail and error. I added a
new Array and then created a new partition and was able to add it as a
physical disk resource. Great! SQL Server would still not see it! So
finally after beating my head on the wall, I figured out that you need
to add the physical disk as a dependency of the SQL Server resource.
Problem solved!
I've not got an array that is raid 1, for my quorum and some space for
backing up. 1 array for my data (raid 5) and another for my logs (raid
5).
Thanks for responding!
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Partitions and Slices without query binding

I have a fairly straightforward cube in SQL 2005 SSAS, with date as one of the dimensions. I created a partition for each year and quarter, and set the slice appropriately. However, in order to create the partition, I had to use a source query, because I got an error about re-using the same table. I then created a single partition that was based off the table and set a slice and reviewed the SQL that SSAS issued, and it did not restrict based on the slice.
My question is: Can you make SSAS (2005) work like SQL 2000 where it automatically generated (albeit not perfectly) a where clause to restrict the partition?
Thanks,
Doug

Hi Doug,

The way I got this to work is by using AMO to clone a base partition, and then update the underlying view (again in AMO by connecting to a SQL Server). So before the partition is processed, the view is updated.

I used Script Task in SSIS to do the AMO.

This is a nice generic solution.

Hope it helps.

Rahil

|||

Rahil,

Any reason that you didn't use the Analysis Services DDL Task in SSIS?

-Jamie

|||

Hi Jamie:

The AS DDL Task in SSIS will not dynamically create a new partition - whereas in the AMO or XMLA script you can achieve that. I guess - that you could argue that we can break that up into two steps and do the partition creation in code and then subsequent processing in the DDL Task.

Thanks.

Suranjan

Partitions and Slices without query binding

I have a fairly straightforward cube in SQL 2005 SSAS, with date as one

of the dimensions. I created a partition for each year and

quarter, and set the slice appropriately. However, in order to

create the partition, I had to use a source query, because I got an

error about re-using the same table. I then created a single

partition that was based off the table and set a slice and reviewed the

SQL that SSAS issued, and it did not restrict based on the slice.

My question is: Can you make SSAS (2005) work like SQL 2000 where

it automatically generated (albeit not perfectly) a where clause to

restrict the partition?

Thanks,

Doug

Hi Doug,

The way I got this to work is by using AMO to clone a base partition, and then update the underlying view (again in AMO by connecting to a SQL Server). So before the partition is processed, the view is updated.

I used Script Task in SSIS to do the AMO.

This is a nice generic solution.

Hope it helps.

Rahil

|||

Rahil,

Any reason that you didn't use the Analysis Services DDL Task in SSIS?

-Jamie

|||

Hi Jamie:

The AS DDL Task in SSIS will not dynamically create a new partition - whereas in the AMO or XMLA script you can achieve that. I guess - that you could argue that we can break that up into two steps and do the partition creation in code and then subsequent processing in the DDL Task.

Thanks.

Suranjan

Partitions and performance

I copied 5million records into a table that has no indexes and partitions and
it took 90mins.I then truncated this table and setup a Partition and created
clustered index based on the Partitioning Column. I copied the same 5million
records into this table but it took only 60mins.
Shouldnâ't the copy in the second case be taking more time because of the
presence of the Clustered Index? Or did the created partition increase the
performance?
Later, I subjected the table to a set of queries and compared the
performance against the non-partitioned table and I could not see much of a
performance gain.
Considering all else equal, ie the query is based on the Partitioned Column
which is inturn based on one of the keys in the Clustered Index, I expected
good performance gain. But I was disappointed to see that it did not offer
much.
I just wanted to hear from others and see what their experiences are.
Thanks,
rgnHi
You don't give information about the partitioning or the clustered index and
what the data is that you are inserting. Posting DDL and an example would
help. You may answer the first question yourself by loading into the table
with the clustered index and no partition.
Check out the query plans to see if your partioning is working.
John
"rgn" wrote:
> I copied 5million records into a table that has no indexes and partitions and
> it took 90mins.I then truncated this table and setup a Partition and created
> clustered index based on the Partitioning Column. I copied the same 5million
> records into this table but it took only 60mins.
> Shouldnâ't the copy in the second case be taking more time because of the
> presence of the Clustered Index? Or did the created partition increase the
> performance?
> Later, I subjected the table to a set of queries and compared the
> performance against the non-partitioned table and I could not see much of a
> performance gain.
> Considering all else equal, ie the query is based on the Partitioned Column
> which is inturn based on one of the keys in the Clustered Index, I expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
> I just wanted to hear from others and see what their experiences are.
> Thanks,
> rgn
>|||rgn
I don't think that Partition is designed to gain performance benefit, in my
opinion it is more for structuring the data , however , I'm sure that
performance plays its role as well
Read Nigel's great article
http://www.simple-talk.com/sql/sql-server-2005/partitioned-tables-in-sql-server-2005/
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:ED3E72A3-962E-439B-A6E3-60DA95C529FB@.microsoft.com...
>I copied 5million records into a table that has no indexes and partitions
>and
> it took 90mins.I then truncated this table and setup a Partition and
> created
> clustered index based on the Partitioning Column. I copied the same
> 5million
> records into this table but it took only 60mins.
> Shouldn?t the copy in the second case be taking more time because of the
> presence of the Clustered Index? Or did the created partition increase the
> performance?
> Later, I subjected the table to a set of queries and compared the
> performance against the non-partitioned table and I could not see much of
> a
> performance gain.
> Considering all else equal, ie the query is based on the Partitioned
> Column
> which is inturn based on one of the keys in the Clustered Index, I
> expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
> I just wanted to hear from others and see what their experiences are.
> Thanks,
> rgn
>|||>I copied 5million records into a table that has no indexes and partitions
>and
> it took 90mins.I then truncated this table and setup a Partition and
> created
> clustered index based on the Partitioning Column. I copied the same
> 5million
> records into this table but it took only 60mins.
I think there must be something else going on here. Was the database space
pre-allocated? Were the non-partitioned and partitioned tables on the same
filegroups and physical disks? Is the source data in the same sequence as
the clustered index?
> Considering all else equal, i.e. the query is based on the Partitioned
> Column
> which is intern based on one of the keys in the Clustered Index, I
> expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
Partitioning is more for manageability than performance. For example,
partitioning can reduce intermediate space requirements for (re)building a
clustered index or allow you to place historical read-only data on different
filegroups. Partitioning can improve performance of certain queries through
partition elimination but this mostly helps scan operations.
Indexing is the real key to performance of both partitioned and
non-partitioned tables. The real performance sweet spot for partitioning is
when a design allows mass data load/archive using SWITCH.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:ED3E72A3-962E-439B-A6E3-60DA95C529FB@.microsoft.com...
>I copied 5million records into a table that has no indexes and partitions
>and
> it took 90mins.I then truncated this table and setup a Partition and
> created
> clustered index based on the Partitioning Column. I copied the same
> 5million
> records into this table but it took only 60mins.
> Shouldnâ't the copy in the second case be taking more time because of the
> presence of the Clustered Index? Or did the created partition increase the
> performance?
> Later, I subjected the table to a set of queries and compared the
> performance against the non-partitioned table and I could not see much of
> a
> performance gain.
> Considering all else equal, ie the query is based on the Partitioned
> Column
> which is inturn based on one of the keys in the Clustered Index, I
> expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
> I just wanted to hear from others and see what their experiences are.
> Thanks,
> rgn
>

Partitions and performance

I copied 5million records into a table that has no indexes and partitions and
it took 90mins.I then truncated this table and setup a Partition and created
clustered index based on the Partitioning Column. I copied the same 5million
records into this table but it took only 60mins.
Shouldn’t the copy in the second case be taking more time because of the
presence of the Clustered Index? Or did the created partition increase the
performance?
Later, I subjected the table to a set of queries and compared the
performance against the non-partitioned table and I could not see much of a
performance gain.
Considering all else equal, ie the query is based on the Partitioned Column
which is inturn based on one of the keys in the Clustered Index, I expected
good performance gain. But I was disappointed to see that it did not offer
much.
I just wanted to hear from others and see what their experiences are.
Thanks,
rgn
Hi
You don't give information about the partitioning or the clustered index and
what the data is that you are inserting. Posting DDL and an example would
help. You may answer the first question yourself by loading into the table
with the clustered index and no partition.
Check out the query plans to see if your partioning is working.
John
"rgn" wrote:

> I copied 5million records into a table that has no indexes and partitions and
> it took 90mins.I then truncated this table and setup a Partition and created
> clustered index based on the Partitioning Column. I copied the same 5million
> records into this table but it took only 60mins.
> Shouldn’t the copy in the second case be taking more time because of the
> presence of the Clustered Index? Or did the created partition increase the
> performance?
> Later, I subjected the table to a set of queries and compared the
> performance against the non-partitioned table and I could not see much of a
> performance gain.
> Considering all else equal, ie the query is based on the Partitioned Column
> which is inturn based on one of the keys in the Clustered Index, I expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
> I just wanted to hear from others and see what their experiences are.
> Thanks,
> rgn
>
|||rgn
I don't think that Partition is designed to gain performance benefit, in my
opinion it is more for structuring the data , however , I'm sure that
performance plays its role as well
Read Nigel's great article
http://www.simple-talk.com/sql/sql-server-2005/partitioned-tables-in-sql-server-2005/
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:ED3E72A3-962E-439B-A6E3-60DA95C529FB@.microsoft.com...
>I copied 5million records into a table that has no indexes and partitions
>and
> it took 90mins.I then truncated this table and setup a Partition and
> created
> clustered index based on the Partitioning Column. I copied the same
> 5million
> records into this table but it took only 60mins.
> Shouldnt the copy in the second case be taking more time because of the
> presence of the Clustered Index? Or did the created partition increase the
> performance?
> Later, I subjected the table to a set of queries and compared the
> performance against the non-partitioned table and I could not see much of
> a
> performance gain.
> Considering all else equal, ie the query is based on the Partitioned
> Column
> which is inturn based on one of the keys in the Clustered Index, I
> expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
> I just wanted to hear from others and see what their experiences are.
> Thanks,
> rgn
>
|||>I copied 5million records into a table that has no indexes and partitions
>and
> it took 90mins.I then truncated this table and setup a Partition and
> created
> clustered index based on the Partitioning Column. I copied the same
> 5million
> records into this table but it took only 60mins.
I think there must be something else going on here. Was the database space
pre-allocated? Were the non-partitioned and partitioned tables on the same
filegroups and physical disks? Is the source data in the same sequence as
the clustered index?

> Considering all else equal, i.e. the query is based on the Partitioned
> Column
> which is intern based on one of the keys in the Clustered Index, I
> expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
Partitioning is more for manageability than performance. For example,
partitioning can reduce intermediate space requirements for (re)building a
clustered index or allow you to place historical read-only data on different
filegroups. Partitioning can improve performance of certain queries through
partition elimination but this mostly helps scan operations.
Indexing is the real key to performance of both partitioned and
non-partitioned tables. The real performance sweet spot for partitioning is
when a design allows mass data load/archive using SWITCH.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:ED3E72A3-962E-439B-A6E3-60DA95C529FB@.microsoft.com...
>I copied 5million records into a table that has no indexes and partitions
>and
> it took 90mins.I then truncated this table and setup a Partition and
> created
> clustered index based on the Partitioning Column. I copied the same
> 5million
> records into this table but it took only 60mins.
> Shouldn’t the copy in the second case be taking more time because of the
> presence of the Clustered Index? Or did the created partition increase the
> performance?
> Later, I subjected the table to a set of queries and compared the
> performance against the non-partitioned table and I could not see much of
> a
> performance gain.
> Considering all else equal, ie the query is based on the Partitioned
> Column
> which is inturn based on one of the keys in the Clustered Index, I
> expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
> I just wanted to hear from others and see what their experiences are.
> Thanks,
> rgn
>

Partitions and FileGroups

I'm writing a SP to automate the creation & maintenance of Partitions and
Filegroups. Every 1st of a month, I have to create a new partition to move
all the previous months data to a new partition and at the end of the year I
have to consoldiate all the years partition into just one Partition.
I have automated the creation part and now I'm in the middle of automating
the deletion of the 11 partitions and moving the data (from all these 11
partitions) into the 12th partition.
Having moved these 11 partitions, I would like to drop the files and
filegroups associated to these partitions. To accomplish this, I need to find
out which partitions were mapped to which filegroups. Is there a way to find
out?
Thanks,
rgn
I think that the following query should get you what you need. There's a
mapping table called sys.destination_data_spaces that maps the partition
scheme's "data space ID" to the data space of the filegroup:
select P.[partition_id], P.[object_id], P.[index_id], P.[partition_number],
FG.name 'filegroup_name'
from sys.partitions P
join sys.indexes I on P.index_id = I.index_id and P.object_id =
I.object_id
join sys.partition_schemes S on I.data_space_id = S.data_space_id
join sys.destination_data_spaces DDS on S.data_space_id =
DDS.partition_scheme_id
join sys.filegroups FG on DDS.data_space_id = FG.data_space_id
order by P.object_id, P.index_Id, P.partition_number
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:F4D34E12-ED79-46FF-A067-01067410BBF6@.microsoft.com...
> I'm writing a SP to automate the creation & maintenance of Partitions and
> Filegroups. Every 1st of a month, I have to create a new partition to move
> all the previous months data to a new partition and at the end of the year
> I
> have to consoldiate all the years partition into just one Partition.
> I have automated the creation part and now I'm in the middle of automating
> the deletion of the 11 partitions and moving the data (from all these 11
> partitions) into the 12th partition.
> Having moved these 11 partitions, I would like to drop the files and
> filegroups associated to these partitions. To accomplish this, I need to
> find
> out which partitions were mapped to which filegroups. Is there a way to
> find
> out?
> Thanks,
> rgn

Partitions and FileGroups

I'm writing a SP to automate the creation & maintenance of Partitions and
Filegroups. Every 1st of a month, I have to create a new partition to move
all the previous months data to a new partition and at the end of the year I
have to consoldiate all the years partition into just one Partition.
I have automated the creation part and now I'm in the middle of automating
the deletion of the 11 partitions and moving the data (from all these 11
partitions) into the 12th partition.
Having moved these 11 partitions, I would like to drop the files and
filegroups associated to these partitions. To accomplish this, I need to find
out which partitions were mapped to which filegroups. Is there a way to find
out?
Thanks,
rgnI think that the following query should get you what you need. There's a
mapping table called sys.destination_data_spaces that maps the partition
scheme's "data space ID" to the data space of the filegroup:
select P.[partition_id], P.[object_id], P.[index_id], P.[partition_number],
FG.name 'filegroup_name'
from sys.partitions P
join sys.indexes I on P.index_id = I.index_id and P.object_id =I.object_id
join sys.partition_schemes S on I.data_space_id = S.data_space_id
join sys.destination_data_spaces DDS on S.data_space_id =DDS.partition_scheme_id
join sys.filegroups FG on DDS.data_space_id = FG.data_space_id
order by P.object_id, P.index_Id, P.partition_number
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:F4D34E12-ED79-46FF-A067-01067410BBF6@.microsoft.com...
> I'm writing a SP to automate the creation & maintenance of Partitions and
> Filegroups. Every 1st of a month, I have to create a new partition to move
> all the previous months data to a new partition and at the end of the year
> I
> have to consoldiate all the years partition into just one Partition.
> I have automated the creation part and now I'm in the middle of automating
> the deletion of the 11 partitions and moving the data (from all these 11
> partitions) into the 12th partition.
> Having moved these 11 partitions, I would like to drop the files and
> filegroups associated to these partitions. To accomplish this, I need to
> find
> out which partitions were mapped to which filegroups. Is there a way to
> find
> out?
> Thanks,
> rgn

Partitions and Common Practice

Hello All,
I posted a simillar question sometime back but I did not see any response. I
have modified my question quite a bit with more info and posting it hoping to
see some response this time :)
I have a Policy Master data file whose uniqueness is identified by the
following
columns (Business Key).
[A] Policy Number
[B] Effective Date
[C] Endorse Number
[D] Endorsement Date
It also has an other column called [Transaction Date] which indicates the
datetime when the transaction was entered into the system and since it is a
DataWarehousing system lot of queries will be based on the transaction date.
We loaded this file into the Staging table of SQLServer 2005 database --
STGPOLICY. The data in this staging table, STGPOLICY, will be processed and
populated into FACPOLICY table.
STGPOLICY Table has these 5 important columns apart from the other columns
Policy Number --> Natural Key1
Effective Date --> Natural Key2
Endorse Number --> Natural Key3
[Endorsement Date] --> Natural Key4
[Transaction Date] --> Partition Key
FACPOLICY Table has these 6 important comlumns apart from the other columns
UniqID --> Surrogate Key
Policy Number --> Natural Key1
Effective Date --> Natural Key2
Endorse Number --> Natural Key3
[Endorsement Date] --> Natural Key4
[Transaction Date]
Surrogate Key, UniqID, is an identity column that gets incremented as
records are inserted into the table.
For better performance and Maintainability, we decided to use Partitions on
FACPOLICY table with [Transaction Date] being the Partition Key.
FACPOLICY Table has these 6 important comlumns apart from the other columns
UniqID --> Surrogate Key
Policy Number --> Natural Key1
Effective Date --> Natural Key2
Endorse Number --> Natural Key3
[Endorsement Date] --> Natural Key4
[Transaction Date] --> Partition Key
For faster loading and processing, I have to Partition the STGPOLICY table
on [Transaction Date] column as this column is used for extensive
search/processing. So, I created the partition with [TransactionDate] as the
Partition column. However, when I try to create a unique index on the natural
keys the system gives an error as Transactiondate is not part of the Unique
Key comprising of Policy Number,Effective Date,Endorse Number,[Endorsement
Date].
I went through the BOL and found that this is the ugly side of PArtitions.
Does that mean that I cannot define a primary key on the natural key when the
natural key is not a Partition Key? I'm left with either creating a nonunique
key or including the Partition Key along with the Natural Key. Is this a
common practice?
Now, with the FACPOLICY table, the UniqID adds another level of intrigue
since the Primary Key would be the UniqID (assuming that there is no
Partitions). With partitions, would it mean that I have to include
TransactionDate to UniqID to create a Primary Key ?
I'm looking for some common practices. Please help.
Thanks,
rgn
Hello All,
Iâ'm new to SQLServer 2005 though I know SQLServer 2000 well. Iâ'm working on
Partitioning some of the tables for a datawarehousing project and need some
guidance/help.
I have a Policy Master table whose uniqueness is identified by the following
columns via a Primary Key Clustered constraint.
[A] Policy Number
[B] Effective Date
[C] Endorse Number
[D] Endorsement Date
It also has an other column called [Transaction Date] which indicates the
datetime when the transaction came into the system and since it is a
DataWarehousing system lot of queries will be based on the transaction date.
I created a Partition Function, Partition Scheme and the Created the table
on the Partition Scheme with the [Transaction Date] as the Partitioning
Column. Things looked good till this point. However, when I tried to create
the Primary Key on the above 4 columns, I got the following error:
Msg 1908, Level 16, State 1, Line 1
Column 'TRANDATE' is partitioning column of the index 'PK_POLICYHDR'.
Partition columns for a unique index must be a subset of the index key.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors
I understand what this error means. But I cannot modify the Primary Key by
adding TRANDATE to it. Iâ'm not sure if Iâ'm missing something. Can someone
help?
Thanks,
rgn> Does that mean that I cannot define a primary key on the natural key when
> the
> natural key is not a Partition Key? I'm left with either creating a
> nonunique
> key or including the Partition Key along with the Natural Key. Is this a
> common practice?
Another other alternative is to not partition the natural key. The downside
is that you can't use SWITCH to quickly load data with the non-partitioned
index in place. If SWITCH is an important part of you partitioning
strategy, you can drop the non-partitioned unique constraint/index, SWITCH
in the new data and then recreate the constraint/index afterward.
I wouldn't go as far as to say that it is a common practice to add the
partitioning column to a unique constraint/index solely to facilitate
partitioning, but I suspect it is not all that unusual. Bending the
database design rules in favor of performance and manageability is an option
as long as you have mechanisms in place to ensure data integrity.
> Now, with the FACPOLICY table, the UniqID adds another level of intrigue
> since the Primary Key would be the UniqID (assuming that there is no
> Partitions). With partitions, would it mean that I have to include
> TransactionDate to UniqID to create a Primary Key ?
All partitioned indexes include the partitioning column and unique
partitioned indexes must explicitly include the partitioning column in the
key. The partitioning column is implicitly included in non-clustered
indexes (but not part of the key) if not already part of the clustered or
non-clustered key. So, like the natural key, you'll need to add the
partitioning column to the surrogate key to form a composite surrogate key.
It's a little more inconvenient to deal with the composite key but more
palatable that adding the partitioning key to a natural key.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:7B2D7BEE-F322-46B0-8CB4-4493C7C7E86B@.microsoft.com...
> Hello All,
> I posted a simillar question sometime back but I did not see any response.
> I
> have modified my question quite a bit with more info and posting it hoping
> to
> see some response this time :)
> I have a Policy Master data file whose uniqueness is identified by the
> following
> columns (Business Key).
> [A] Policy Number
> [B] Effective Date
> [C] Endorse Number
> [D] Endorsement Date
> It also has an other column called [Transaction Date] which indicates the
> datetime when the transaction was entered into the system and since it is
> a
> DataWarehousing system lot of queries will be based on the transaction
> date.
> We loaded this file into the Staging table of SQLServer 2005 database --
> STGPOLICY. The data in this staging table, STGPOLICY, will be processed
> and
> populated into FACPOLICY table.
> STGPOLICY Table has these 5 important columns apart from the other columns
> Policy Number --> Natural Key1
> Effective Date --> Natural Key2
> Endorse Number --> Natural Key3
> [Endorsement Date] --> Natural Key4
> [Transaction Date] --> Partition Key
> FACPOLICY Table has these 6 important comlumns apart from the other
> columns
> UniqID --> Surrogate Key
> Policy Number --> Natural Key1
> Effective Date --> Natural Key2
> Endorse Number --> Natural Key3
> [Endorsement Date] --> Natural Key4
> [Transaction Date]
> Surrogate Key, UniqID, is an identity column that gets incremented as
> records are inserted into the table.
> For better performance and Maintainability, we decided to use Partitions
> on
> FACPOLICY table with [Transaction Date] being the Partition Key.
> FACPOLICY Table has these 6 important comlumns apart from the other
> columns
> UniqID --> Surrogate Key
> Policy Number --> Natural Key1
> Effective Date --> Natural Key2
> Endorse Number --> Natural Key3
> [Endorsement Date] --> Natural Key4
> [Transaction Date] --> Partition Key
>
> For faster loading and processing, I have to Partition the STGPOLICY table
> on [Transaction Date] column as this column is used for extensive
> search/processing. So, I created the partition with [TransactionDate] as
> the
> Partition column. However, when I try to create a unique index on the
> natural
> keys the system gives an error as Transactiondate is not part of the
> Unique
> Key comprising of Policy Number,Effective Date,Endorse Number,[Endorsement
> Date].
> I went through the BOL and found that this is the ugly side of PArtitions.
> Does that mean that I cannot define a primary key on the natural key when
> the
> natural key is not a Partition Key? I'm left with either creating a
> nonunique
> key or including the Partition Key along with the Natural Key. Is this a
> common practice?
> Now, with the FACPOLICY table, the UniqID adds another level of intrigue
> since the Primary Key would be the UniqID (assuming that there is no
> Partitions). With partitions, would it mean that I have to include
> TransactionDate to UniqID to create a Primary Key ?
> I'm looking for some common practices. Please help.
>
> Thanks,
> rgn
>
>
> Hello All,
> Iâ'm new to SQLServer 2005 though I know SQLServer 2000 well. Iâ'm working
> on
> Partitioning some of the tables for a datawarehousing project and need
> some
> guidance/help.
> I have a Policy Master table whose uniqueness is identified by the
> following
> columns via a Primary Key Clustered constraint.
> [A] Policy Number
> [B] Effective Date
> [C] Endorse Number
> [D] Endorsement Date
> It also has an other column called [Transaction Date] which indicates the
> datetime when the transaction came into the system and since it is a
> DataWarehousing system lot of queries will be based on the transaction
> date.
> I created a Partition Function, Partition Scheme and the Created the table
> on the Partition Scheme with the [Transaction Date] as the Partitioning
> Column. Things looked good till this point. However, when I tried to
> create
> the Primary Key on the above 4 columns, I got the following error:
> Msg 1908, Level 16, State 1, Line 1
> Column 'TRANDATE' is partitioning column of the index 'PK_POLICYHDR'.
> Partition columns for a unique index must be a subset of the index key.
> Msg 1750, Level 16, State 0, Line 1
> Could not create constraint. See previous errors
> I understand what this error means. But I cannot modify the Primary Key by
> adding TRANDATE to it. Iâ'm not sure if Iâ'm missing something. Can someone
> help?
> Thanks,
> rgn
>

Partitions / Filegroups

Hi All,
Has anyone managed to link the system views (or any other mechanisms) to
display which partition number is in which filegroup?
e.g. As follows:
Table Name Partition_Number Filegroup_Name No_Rows
Demo_Table 1 OLD_DATA 4,000,000
Demo_Table 2 MED_DATA 3,000,000
Demo_Table 3 CURRENT_DATA 50,000
Thanks in advance,
DBX1
DBX1
Have you tried
SELECT * FROM sys.partitions ?
"DBX1" <DBX1@.discussions.microsoft.com> wrote in message
news:15F0C880-C509-4F68-8810-531DE6A89236@.microsoft.com...
> Hi All,
> Has anyone managed to link the system views (or any other mechanisms) to
> display which partition number is in which filegroup?
> e.g. As follows:
> Table Name Partition_Number Filegroup_Name No_Rows
> Demo_Table 1 OLD_DATA 4,000,000
> Demo_Table 2 MED_DATA 3,000,000
> Demo_Table 3 CURRENT_DATA 50,000
> Thanks in advance,
> DBX1
|||Yup, but this lists the partition something is in, and not the filegroup a
partition resides in, the closest I have come to making this work is the
following:
-- This is not very tidy code currently, and needs to be refactored
-- Partition_Table_Example : This is the table which we are interested in,
for filtering purposes in this example
select DS.destination_id , F.name,
PIX.obj_name, PIX.partition_number, PIX.[rows], PIX.[index_id]
from sys.destination_data_spaces DS
join sys.filegroups F
on DS.data_space_id = F.data_space_id
join (
SELECT OBJECT_NAME(P.[object_id]) as obj_name,
P.partition_number, P.[rows], P.[index_id]
FROM sys.partitions P
WHERE P.[object_id] = OBJECT_ID('[dbo].[Partition_Table_Example]')
) PIX
on PIX.Partition_number = DS.Partition_ID
-- This works, the interesting thing to note is that Partition_number links to
-- Destination_ID on the Partition_ID sys.partitions view
--There may be another link required between sys.partitions and
sys.destination_data_spaces if there are multiple partition schemes/functions
-- This has only been validated where there is a single partition_scheme
"Uri Dimant" wrote:

> DBX1
> Have you tried
> SELECT * FROM sys.partitions ?
>
>
>
> "DBX1" <DBX1@.discussions.microsoft.com> wrote in message
> news:15F0C880-C509-4F68-8810-531DE6A89236@.microsoft.com...
>
>
|||sys.partitions - will give the row-count for the table/individual parititons,
so that is not an issue, Im more interested in a join based query for all the
partitioned objects, I have manged to get something close, with the prior
querys for all objects.
It is missing something currently when there are multi-partition
defined/multi-partitioned objects as it returns some incorrect data.
The problem with the enclosed query is it is limited to a single object or
requires adding for obj_ids. and does not return the filegroup a particular
item or partition resides in.
What I am particulary interested in knowing is what sliced partition resides
in which filegroup.
I am aware that there can be multiple partitions in a single filegroup.
this does not list the filegroup names.
"Dejan Sarka" wrote:

> Hi!
>
> Partitions do not map one-to-one to filegroups. You can have multiple
> partitions on a single filegroup. You can find basic info about partitions
> in sys.partitions view or with $PARTITION function, like
> SELECT $PARTITION.myRangePF1(col1),
> COUNT(*)
> FROM PartitionTable
> GROUP BY $PARTITION.myRangePF1(col1)
> To find number of rows in different filegroups, the query gets complicated:
> WITH
> PartitionRowCount(Partition, NumberOfRowsInPartition) AS
> (
> SELECT $PARTITION.myRangePF1(col1),
> COUNT(*)
> FROM PartitionTable
> GROUP BY $PARTITION.myRangePF1(col1)
> ),
> PartitionsDataSpaces (partition_scheme_id, Partition, data_space_id,
> DataSpaceName) AS
> (
> SELECT dds.partition_scheme_id,
> dds.destination_id,
> dds.data_space_id,
> ds.name
> FROM sys.destination_data_spaces dds
> INNER JOIN sys.indexes i
> ON dds.partition_scheme_id = i.data_space_id
> INNER JOIN sys.data_spaces ds
> ON dds.data_space_id = ds.data_space_id
> WHERE i.object_id = OBJECT_ID('PartitionTable')
> AND i.index_id < 2
> )
> SELECT pds.DataSpaceName,
> SUM(prc.NumberOfRowsInPartition) AS NumberOfRowsInDataSpace
> FROM PartitionRowCount prc
> INNER JOIN PartitionsDataSpaces pds
> ON prc.Partition = pds.Partition
> GROUP BY pds.DataSpaceName;
> I hope I did not miss something.
> --
> Dejan Sarka
> http://www.solidqualitylearning.com/blogs/
>
>
|||Anyone able to validate the code below (I think this is producing the data
that I require):
select distinct object_name(SI.object_id),*
--F.name,SP.rows
from sys.destination_data_spaces DS
join sys.filegroups F on
F.data_space_id = DS.data_space_id
join sys.partition_schemes PS
on PS.data_space_id = DS.partition_scheme_id
join sys.indexes SI on
SI.data_space_id = DS.partition_scheme_id
join sys.partitions SP on
SP.object_id = SI.object_id and
SI.index_id = SP.index_id and
SP.partition_number = DS.destination_id
"DBX1" wrote:
[vbcol=seagreen]
> sys.partitions - will give the row-count for the table/individual parititons,
> so that is not an issue, Im more interested in a join based query for all the
> partitioned objects, I have manged to get something close, with the prior
> querys for all objects.
> It is missing something currently when there are multi-partition
> defined/multi-partitioned objects as it returns some incorrect data.
> The problem with the enclosed query is it is limited to a single object or
> requires adding for obj_ids. and does not return the filegroup a particular
> item or partition resides in.
> What I am particulary interested in knowing is what sliced partition resides
> in which filegroup.
> I am aware that there can be multiple partitions in a single filegroup.
> this does not list the filegroup names.
>
> "Dejan Sarka" wrote:

Partitions / Filegroups

Hi All,
Has anyone managed to link the system views (or any other mechanisms) to
display which partition number is in which filegroup?
e.g. As follows:
Table Name Partition_Number Filegroup_Name No_Rows
Demo_Table 1 OLD_DATA 4,000,000
Demo_Table 2 MED_DATA 3,000,000
Demo_Table 3 CURRENT_DATA 50,000
Thanks in advance,
DBX1DBX1
Have you tried
SELECT * FROM sys.partitions ?
"DBX1" <DBX1@.discussions.microsoft.com> wrote in message
news:15F0C880-C509-4F68-8810-531DE6A89236@.microsoft.com...
> Hi All,
> Has anyone managed to link the system views (or any other mechanisms) to
> display which partition number is in which filegroup?
> e.g. As follows:
> Table Name Partition_Number Filegroup_Name No_Rows
> Demo_Table 1 OLD_DATA 4,000,000
> Demo_Table 2 MED_DATA 3,000,000
> Demo_Table 3 CURRENT_DATA 50,000
> Thanks in advance,
> DBX1|||Hi!

> Has anyone managed to link the system views (or any other mechanisms) to
> display which partition number is in which filegroup?
> e.g. As follows:
> Table Name Partition_Number Filegroup_Name No_Rows
> Demo_Table 1 OLD_DATA 4,000,000
> Demo_Table 2 MED_DATA 3,000,000
> Demo_Table 3 CURRENT_DATA 50,000
Partitions do not map one-to-one to filegroups. You can have multiple
partitions on a single filegroup. You can find basic info about partitions
in sys.partitions view or with $PARTITION function, like
SELECT $PARTITION.myRangePF1(col1),
COUNT(*)
FROM PartitionTable
GROUP BY $PARTITION.myRangePF1(col1)
To find number of rows in different filegroups, the query gets complicated:
WITH
PartitionRowCount(Partition, NumberOfRowsInPartition) AS
(
SELECT $PARTITION.myRangePF1(col1),
COUNT(*)
FROM PartitionTable
GROUP BY $PARTITION.myRangePF1(col1)
),
PartitionsDataSpaces (partition_scheme_id, Partition, data_space_id,
DataSpaceName) AS
(
SELECT dds.partition_scheme_id,
dds.destination_id,
dds.data_space_id,
ds.name
FROM sys.destination_data_spaces dds
INNER JOIN sys.indexes i
ON dds.partition_scheme_id = i.data_space_id
INNER JOIN sys.data_spaces ds
ON dds.data_space_id = ds.data_space_id
WHERE i.object_id = OBJECT_ID('PartitionTable')
AND i.index_id < 2
)
SELECT pds.DataSpaceName,
SUM(prc.NumberOfRowsInPartition) AS NumberOfRowsInDataSpace
FROM PartitionRowCount prc
INNER JOIN PartitionsDataSpaces pds
ON prc.Partition = pds.Partition
GROUP BY pds.DataSpaceName;
I hope I did not miss something.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||Yup, but this lists the partition something is in, and not the filegroup a
partition resides in, the closest I have come to making this work is the
following:
-- This is not very tidy code currently, and needs to be refactored
-- Partition_Table_Example : This is the table which we are interested in,
for filtering purposes in this example
--
select DS.destination_id , F.name,
PIX.obj_name, PIX.partition_number, PIX.[rows], PIX.[index_id]
from sys.destination_data_spaces DS
join sys.filegroups F
on DS.data_space_id = F.data_space_id
join (
SELECT OBJECT_NAME(P.[object_id]) as obj_name,
P.partition_number, P.[rows], P.[index_id]
FROM sys.partitions P
WHERE P.[object_id] = OBJECT_ID('[dbo].[Partition_Table_Example]
')
) PIX
on PIX.Partition_number = DS.Partition_ID
-- This works, the interesting thing to note is that Partition_number links
to
-- Destination_ID on the Partition_ID sys.partitions view
--There may be another link required between sys.partitions and
sys.destination_data_spaces if there are multiple partition schemes/function
s
-- This has only been validated where there is a single partition_scheme
"Uri Dimant" wrote:

> DBX1
> Have you tried
> SELECT * FROM sys.partitions ?
>
>
>
> "DBX1" <DBX1@.discussions.microsoft.com> wrote in message
> news:15F0C880-C509-4F68-8810-531DE6A89236@.microsoft.com...
>
>|||sys.partitions - will give the row-count for the table/individual parititons
,
so that is not an issue, Im more interested in a join based query for all th
e
partitioned objects, I have manged to get something close, with the prior
querys for all objects.
It is missing something currently when there are multi-partition
defined/multi-partitioned objects as it returns some incorrect data.
The problem with the enclosed query is it is limited to a single object or
requires adding for obj_ids. and does not return the filegroup a particular
item or partition resides in.
What I am particulary interested in knowing is what sliced partition resides
in which filegroup.
I am aware that there can be multiple partitions in a single filegroup.
this does not list the filegroup names.
"Dejan Sarka" wrote:

> Hi!
>
> Partitions do not map one-to-one to filegroups. You can have multiple
> partitions on a single filegroup. You can find basic info about partitions
> in sys.partitions view or with $PARTITION function, like
> SELECT $PARTITION.myRangePF1(col1),
> COUNT(*)
> FROM PartitionTable
> GROUP BY $PARTITION.myRangePF1(col1)
> To find number of rows in different filegroups, the query gets complicated
:
> WITH
> PartitionRowCount(Partition, NumberOfRowsInPartition) AS
> (
> SELECT $PARTITION.myRangePF1(col1),
> COUNT(*)
> FROM PartitionTable
> GROUP BY $PARTITION.myRangePF1(col1)
> ),
> PartitionsDataSpaces (partition_scheme_id, Partition, data_space_id,
> DataSpaceName) AS
> (
> SELECT dds.partition_scheme_id,
> dds.destination_id,
> dds.data_space_id,
> ds.name
> FROM sys.destination_data_spaces dds
> INNER JOIN sys.indexes i
> ON dds.partition_scheme_id = i.data_space_id
> INNER JOIN sys.data_spaces ds
> ON dds.data_space_id = ds.data_space_id
> WHERE i.object_id = OBJECT_ID('PartitionTable')
> AND i.index_id < 2
> )
> SELECT pds.DataSpaceName,
> SUM(prc.NumberOfRowsInPartition) AS NumberOfRowsInDataSpace
> FROM PartitionRowCount prc
> INNER JOIN PartitionsDataSpaces pds
> ON prc.Partition = pds.Partition
> GROUP BY pds.DataSpaceName;
> I hope I did not miss something.
> --
> Dejan Sarka
> http://www.solidqualitylearning.com/blogs/
>
>|||> this does not list the filegroup names.
It lists data space names for filegroups. Data space names include filegroup
& partition scheme names.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||Anyone able to validate the code below (I think this is producing the data
that I require):
select distinct object_name(SI.object_id),*
-- F.name,SP.rows
from sys.destination_data_spaces DS
join sys.filegroups F on
F.data_space_id = DS.data_space_id
join sys.partition_schemes PS
on PS.data_space_id = DS.partition_scheme_id
join sys.indexes SI on
SI.data_space_id = DS.partition_scheme_id
join sys.partitions SP on
SP.object_id = SI.object_id and
SI.index_id = SP.index_id and
SP.partition_number = DS.destination_id
"DBX1" wrote:
[vbcol=seagreen]
> sys.partitions - will give the row-count for the table/individual paritito
ns,
> so that is not an issue, Im more interested in a join based query for all
the
> partitioned objects, I have manged to get something close, with the prior
> querys for all objects.
> It is missing something currently when there are multi-partition
> defined/multi-partitioned objects as it returns some incorrect data.
> The problem with the enclosed query is it is limited to a single object or
> requires adding for obj_ids. and does not return the filegroup a particula
r
> item or partition resides in.
> What I am particulary interested in knowing is what sliced partition resid
es
> in which filegroup.
> I am aware that there can be multiple partitions in a single filegroup.
> this does not list the filegroup names.
>
> "Dejan Sarka" wrote:
>

partitions

Hi,
I have a question in the middle beetween Windows and SQL.
I have RAID 5 system and then I can put SQL database and log file where i
want...
But..
Can be important create more logical partitions in RAID system for improve
defrag efficiency?
Example: I have only one database in SQL. I create a system partition C, a
database partition D and a log partition E.
Can I have better performance putting database in D and log in E?
Thank's a lot.. and sotty for my english..
Andrea
Since the drives are all a single Raid 5 you will not gain any performance by
spreading the data to the D drive and logs to the E drive.
"Rusty73" wrote:

> Hi,
> I have a question in the middle beetween Windows and SQL.
> I have RAID 5 system and then I can put SQL database and log file where i
> want...
> But..
> Can be important create more logical partitions in RAID system for improve
> defrag efficiency?
> Example: I have only one database in SQL. I create a system partition C, a
> database partition D and a log partition E.
> Can I have better performance putting database in D and log in E?
> Thank's a lot.. and sotty for my english..
> Andrea
>
>
|||I know that log files are writing sequentially to the disk btu with RAID 5 i
can decide to block the writing only on log disk...
but I can have no better performance to the defrag of log?
"fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...[vbcol=seagreen]
> Since the drives are all a single Raid 5 you will not gain any performance
> by
> spreading the data to the D drive and logs to the E drive.
>
> "Rusty73" wrote:
|||The best way to combat file fragmentation is to create the database files
once, as large as you will need them. If the files are allocated
contigueously, and never grow, then no file fragmentation will occur. Make
sure you give enough room for the Index Defrags, that will use internal data
pages, not file fragments to reorganize, again, as long as the file does not
grow.
Go ahead and leave the AUTOGROW feature on, but only as a fail-safe, in case
you've underestimated the space you will need.
Besides, file fragmentation will only affect scans and DSS type queries.
For a true OLTP system, the disk and database page access will be pretty
random, which will not be affected as much by file level fragmentation.
Sincerely,
Anthony Thomas

"Rusty73" <rusty77@.libero.it> wrote in message
news:%23iqlJn6tFHA.3932@.TK2MSFTNGP15.phx.gbl...
I know that log files are writing sequentially to the disk btu with RAID 5 i
can decide to block the writing only on log disk...
but I can have no better performance to the defrag of log?
"fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...[vbcol=seagreen]
> Since the drives are all a single Raid 5 you will not gain any performance
> by
> spreading the data to the D drive and logs to the E drive.
>
> "Rusty73" wrote:
|||Hi there,
Raid 5 will give you better performance for the DB.
For logs is best to create a mirror pair for improved performance, exactly
because of sequential writing.
My server is configured as follows:
system partition C - array of 2 mirrored drives - RAID 1
DB partition - array of a few disks in raid 5
log partition - another pair of mirrored drives - RAID 1
I agree with the previous comment that having different logical partitions
on the same disk array will not improve performance.
Hope this helps.
Kind regards,
Doru
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u7bc6z$tFHA.2848@.TK2MSFTNGP10.phx.gbl...
> The best way to combat file fragmentation is to create the database files
> once, as large as you will need them. If the files are allocated
> contigueously, and never grow, then no file fragmentation will occur.
> Make
> sure you give enough room for the Index Defrags, that will use internal
> data
> pages, not file fragments to reorganize, again, as long as the file does
> not
> grow.
> Go ahead and leave the AUTOGROW feature on, but only as a fail-safe, in
> case
> you've underestimated the space you will need.
> Besides, file fragmentation will only affect scans and DSS type queries.
> For a true OLTP system, the disk and database page access will be pretty
> random, which will not be affected as much by file level fragmentation.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Rusty73" <rusty77@.libero.it> wrote in message
> news:%23iqlJn6tFHA.3932@.TK2MSFTNGP15.phx.gbl...
> I know that log files are writing sequentially to the disk btu with RAID 5
> i
> can decide to block the writing only on log disk...
> but I can have no better performance to the defrag of log?
> "fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
> news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...
>

partitions

Hi,
I have a question in the middle beetween Windows and SQL.
I have RAID 5 system and then I can put SQL database and log file where i
want...
But..
Can be important create more logical partitions in RAID system for improve
defrag efficiency'
Example: I have only one database in SQL. I create a system partition C, a
database partition D and a log partition E.
Can I have better performance putting database in D and log in E?
Thank's a lot.. and sotty for my english..
AndreaSince the drives are all a single Raid 5 you will not gain any performance b
y
spreading the data to the D drive and logs to the E drive.
"Rusty73" wrote:

> Hi,
> I have a question in the middle beetween Windows and SQL.
> I have RAID 5 system and then I can put SQL database and log file where i
> want...
> But..
> Can be important create more logical partitions in RAID system for improve
> defrag efficiency'
> Example: I have only one database in SQL. I create a system partition C, a
> database partition D and a log partition E.
> Can I have better performance putting database in D and log in E?
> Thank's a lot.. and sotty for my english..
> Andrea
>
>|||I know that log files are writing sequentially to the disk btu with RAID 5 i
can decide to block the writing only on log disk...
but I can have no better performance to the defrag of log?
"fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...[vbcol=seagreen]
> Since the drives are all a single Raid 5 you will not gain any performance
> by
> spreading the data to the D drive and logs to the E drive.
>
> "Rusty73" wrote:
>|||The best way to combat file fragmentation is to create the database files
once, as large as you will need them. If the files are allocated
contigueously, and never grow, then no file fragmentation will occur. Make
sure you give enough room for the Index Defrags, that will use internal data
pages, not file fragments to reorganize, again, as long as the file does not
grow.
Go ahead and leave the AUTOGROW feature on, but only as a fail-safe, in case
you've underestimated the space you will need.
Besides, file fragmentation will only affect scans and DSS type queries.
For a true OLTP system, the disk and database page access will be pretty
random, which will not be affected as much by file level fragmentation.
Sincerely,
Anthony Thomas
"Rusty73" <rusty77@.libero.it> wrote in message
news:%23iqlJn6tFHA.3932@.TK2MSFTNGP15.phx.gbl...
I know that log files are writing sequentially to the disk btu with RAID 5 i
can decide to block the writing only on log disk...
but I can have no better performance to the defrag of log?
"fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...[vbcol=seagreen]
> Since the drives are all a single Raid 5 you will not gain any performance
> by
> spreading the data to the D drive and logs to the E drive.
>
> "Rusty73" wrote:
>|||Hi there,
Raid 5 will give you better performance for the DB.
For logs is best to create a mirror pair for improved performance, exactly
because of sequential writing.
My server is configured as follows:
system partition C - array of 2 mirrored drives - RAID 1
DB partition - array of a few disks in raid 5
log partition - another pair of mirrored drives - RAID 1
I agree with the previous comment that having different logical partitions
on the same disk array will not improve performance.
Hope this helps.
Kind regards,
Doru
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u7bc6z$tFHA.2848@.TK2MSFTNGP10.phx.gbl...
> The best way to combat file fragmentation is to create the database files
> once, as large as you will need them. If the files are allocated
> contigueously, and never grow, then no file fragmentation will occur.
> Make
> sure you give enough room for the Index Defrags, that will use internal
> data
> pages, not file fragments to reorganize, again, as long as the file does
> not
> grow.
> Go ahead and leave the AUTOGROW feature on, but only as a fail-safe, in
> case
> you've underestimated the space you will need.
> Besides, file fragmentation will only affect scans and DSS type queries.
> For a true OLTP system, the disk and database page access will be pretty
> random, which will not be affected as much by file level fragmentation.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Rusty73" <rusty77@.libero.it> wrote in message
> news:%23iqlJn6tFHA.3932@.TK2MSFTNGP15.phx.gbl...
> I know that log files are writing sequentially to the disk btu with RAID 5
> i
> can decide to block the writing only on log disk...
> but I can have no better performance to the defrag of log?
> "fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
> news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...
>

partitions

Hi,
I have a question in the middle beetween Windows and SQL.
I have RAID 5 system and then I can put SQL database and log file where i
want...
But..
Can be important create more logical partitions in RAID system for improve
defrag efficiency'
Example: I have only one database in SQL. I create a system partition C, a
database partition D and a log partition E.
Can I have better performance putting database in D and log in E?
Thank's a lot.. and sotty for my english..
AndreaSince the drives are all a single Raid 5 you will not gain any performance by
spreading the data to the D drive and logs to the E drive.
"Rusty73" wrote:
> Hi,
> I have a question in the middle beetween Windows and SQL.
> I have RAID 5 system and then I can put SQL database and log file where i
> want...
> But..
> Can be important create more logical partitions in RAID system for improve
> defrag efficiency'
> Example: I have only one database in SQL. I create a system partition C, a
> database partition D and a log partition E.
> Can I have better performance putting database in D and log in E?
> Thank's a lot.. and sotty for my english..
> Andrea
>
>|||I know that log files are writing sequentially to the disk btu with RAID 5 i
can decide to block the writing only on log disk...
but I can have no better performance to the defrag of log?
"fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...
> Since the drives are all a single Raid 5 you will not gain any performance
> by
> spreading the data to the D drive and logs to the E drive.
>
> "Rusty73" wrote:
>> Hi,
>> I have a question in the middle beetween Windows and SQL.
>> I have RAID 5 system and then I can put SQL database and log file where i
>> want...
>> But..
>> Can be important create more logical partitions in RAID system for
>> improve
>> defrag efficiency'
>> Example: I have only one database in SQL. I create a system partition C,
>> a
>> database partition D and a log partition E.
>> Can I have better performance putting database in D and log in E?
>> Thank's a lot.. and sotty for my english..
>> Andrea
>>|||The best way to combat file fragmentation is to create the database files
once, as large as you will need them. If the files are allocated
contigueously, and never grow, then no file fragmentation will occur. Make
sure you give enough room for the Index Defrags, that will use internal data
pages, not file fragments to reorganize, again, as long as the file does not
grow.
Go ahead and leave the AUTOGROW feature on, but only as a fail-safe, in case
you've underestimated the space you will need.
Besides, file fragmentation will only affect scans and DSS type queries.
For a true OLTP system, the disk and database page access will be pretty
random, which will not be affected as much by file level fragmentation.
Sincerely,
Anthony Thomas
"Rusty73" <rusty77@.libero.it> wrote in message
news:%23iqlJn6tFHA.3932@.TK2MSFTNGP15.phx.gbl...
I know that log files are writing sequentially to the disk btu with RAID 5 i
can decide to block the writing only on log disk...
but I can have no better performance to the defrag of log?
"fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...
> Since the drives are all a single Raid 5 you will not gain any performance
> by
> spreading the data to the D drive and logs to the E drive.
>
> "Rusty73" wrote:
>> Hi,
>> I have a question in the middle beetween Windows and SQL.
>> I have RAID 5 system and then I can put SQL database and log file where i
>> want...
>> But..
>> Can be important create more logical partitions in RAID system for
>> improve
>> defrag efficiency'
>> Example: I have only one database in SQL. I create a system partition C,
>> a
>> database partition D and a log partition E.
>> Can I have better performance putting database in D and log in E?
>> Thank's a lot.. and sotty for my english..
>> Andrea
>>|||Hi there,
Raid 5 will give you better performance for the DB.
For logs is best to create a mirror pair for improved performance, exactly
because of sequential writing.
My server is configured as follows:
system partition C - array of 2 mirrored drives - RAID 1
DB partition - array of a few disks in raid 5
log partition - another pair of mirrored drives - RAID 1
I agree with the previous comment that having different logical partitions
on the same disk array will not improve performance.
Hope this helps.
Kind regards,
Doru
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u7bc6z$tFHA.2848@.TK2MSFTNGP10.phx.gbl...
> The best way to combat file fragmentation is to create the database files
> once, as large as you will need them. If the files are allocated
> contigueously, and never grow, then no file fragmentation will occur.
> Make
> sure you give enough room for the Index Defrags, that will use internal
> data
> pages, not file fragments to reorganize, again, as long as the file does
> not
> grow.
> Go ahead and leave the AUTOGROW feature on, but only as a fail-safe, in
> case
> you've underestimated the space you will need.
> Besides, file fragmentation will only affect scans and DSS type queries.
> For a true OLTP system, the disk and database page access will be pretty
> random, which will not be affected as much by file level fragmentation.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Rusty73" <rusty77@.libero.it> wrote in message
> news:%23iqlJn6tFHA.3932@.TK2MSFTNGP15.phx.gbl...
> I know that log files are writing sequentially to the disk btu with RAID 5
> i
> can decide to block the writing only on log disk...
> but I can have no better performance to the defrag of log?
> "fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
> news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...
>> Since the drives are all a single Raid 5 you will not gain any
>> performance
>> by
>> spreading the data to the D drive and logs to the E drive.
>>
>> "Rusty73" wrote:
>> Hi,
>> I have a question in the middle beetween Windows and SQL.
>> I have RAID 5 system and then I can put SQL database and log file where
>> i
>> want...
>> But..
>> Can be important create more logical partitions in RAID system for
>> improve
>> defrag efficiency'
>> Example: I have only one database in SQL. I create a system partition C,
>> a
>> database partition D and a log partition E.
>> Can I have better performance putting database in D and log in E?
>> Thank's a lot.. and sotty for my english..
>> Andrea
>>
>