Table Partitioning - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Table Partitioning

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2013-07-15 : 15:40:41

Hi

We have introduced table partioning on the table dbo.Dat_EAI_FAGLFLEXB for the column FISCPER (where the value will be
2013001,2013002,2013003,... and so on). Now during execution how to monitor whether it exactly takes from the corresponding partitioning.

Query:


SELECT  RACCT,RBUKRS,RCNTR,PRCTR,ZZMPMPRD,ZZMARKET,ZZFORMAT,ZZTERTRY,ZZSRCSYS,ZZRFACT
	   ,ZZBLART,ZZREFYR,ZZSPKUNNR,ZZFLMDIM,ZZVENDOR,ZZAUFNR,RASSC,CURTYPE,CURRUNIT
	   ,RTCUR,a.FISCPER,TURNOVER
  INTO #TEMP	   
  FROM dbo.Dat_EAI_FAGLFLEXB as A WITH (NOLOCK) INNER JOIN (SELECT FISCPER from #fiscPerToReload WHERE sno=@i) as B
    ON (A.FISCPER=B.FISCPER)
 WHERE ((DEBIT IS NOT NULL AND ABS(DEBIT) >.00000001) OR (CREDIT IS NOT NULL AND ABS(CREDIT) >.00000001) 
	    OR (TURNOVER IS NOT NULL AND ABS(TURNOVER) >.00000001))
		AND CURRUNIT <> '' AND RTCUR <> '' AND CURTYPE in('00','10','30')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-16 : 01:48:24

use $partition to get partition details

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2013-07-16 : 23:04:24

My question is:
when we query the Dat_EAI_FAGLFLEXB table for any particular period,say for example:


select * from Dat_EAI_FAGLFLEXB where FISCPER ='2013001'

whether this will pickup from the particular partioning? or explictly we need to specify the partioning like $partioning.fnname(2013001) like that.

Correct me if i'm wrong.

quote:
Originally posted by visakh16

use $partition to get partition details

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-17 : 02:09:06

quote:
Originally posted by sent_sara

My question is:
when we query the Dat_EAI_FAGLFLEXB table for any particular period,say for example:

select * from Dat_EAI_FAGLFLEXB where FISCPER ='2013001'

whether this will pickup from the particular partioning? or explictly we need to specify the partioning like $partioning.fnname(2013001) like that.

Correct me if i'm wrong.

quote:
Originally posted by visakh16

use $partition to get partition details

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

you just need to apply $partition over field based on which you created partition. Then it will return the number of partition from which data was retrieved.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2013-07-17 : 13:42:32

When i tried to execute the below query:


select COUNT(*) from dbo.Dat_EAI_FAGLFLEXB (nolock)
where $partition.FAGLFLEXB_part_func(FISCPER) = 33 and FISCPER=2013001

select COUNT(*) from dbo.Dat_EAI_FAGLFLEXB (nolock)
where FISCPER=2013001

In execution plan,for the first query its:2%
for the second query its:98%

so now my question is:
1) first query took only 2% cost to produce the result.so whenever the query cost is less whether that should be consider for better performance?

quote:
Originally posted by visakh16

quote:
Originally posted by sent_sara

My question is:
when we query the Dat_EAI_FAGLFLEXB table for any particular period,say for example:

select * from Dat_EAI_FAGLFLEXB where FISCPER ='2013001'

whether this will pickup from the particular partioning? or explictly we need to specify the partioning like $partioning.fnname(2013001) like that.

Correct me if i'm wrong.

quote:
Originally posted by visakh16

use $partition to get partition details

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

you just need to apply $partition over field based on which you created partition. Then it will return the number of partition from which data was retrieved.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mfemenel
Professor Frink

1421 Posts

Posted - 2013-07-17 : 16:13:21

You can get this information without having to count from your source table. http://weblogs.sqlteam.com/mikef/archive/2013/07/14/fetching-partition-information.aspx

Mike
"oh, that monkey is going to pay"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 00:59:54

quote:
Originally posted by sent_sara

When i tried to execute the below query:

select COUNT(*) from dbo.Dat_EAI_FAGLFLEXB (nolock)
where $partition.FAGLFLEXB_part_func(FISCPER) = 33 and FISCPER=2013001

select COUNT(*) from dbo.Dat_EAI_FAGLFLEXB (nolock)
where FISCPER=2013001

In execution plan,for the first query its:2%
for the second query its:98%

so now my question is:
1) first query took only 2% cost to produce the result.so whenever the query cost is less whether that should be consider for better performance?

quote:
Originally posted by visakh16

quote:
Originally posted by sent_sara

My question is:
when we query the Dat_EAI_FAGLFLEXB table for any particular period,say for example:

select * from Dat_EAI_FAGLFLEXB where FISCPER ='2013001'

whether this will pickup from the particular partioning? or explictly we need to specify the partioning like $partioning.fnname(2013001) like that.

Correct me if i'm wrong.

quote:
Originally posted by visakh16

use $partition to get partition details

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

you just need to apply $partition over field based on which you created partition. Then it will return the number of partition from which data was retrieved.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Thats just time taken by each of queries within the batch.
Try running them separately to get execution stats for each.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2013-07-18 : 14:06:01

how to add the screenshot in the below [img] tag?
[img]

[/img]

quote:
Originally posted by visakh16

quote:
Originally posted by sent_sara

When i tried to execute the below query:

select COUNT(*) from dbo.Dat_EAI_FAGLFLEXB (nolock)
where $partition.FAGLFLEXB_part_func(FISCPER) = 33 and FISCPER=2013001

select COUNT(*) from dbo.Dat_EAI_FAGLFLEXB (nolock)
where FISCPER=2013001

In execution plan,for the first query its:2%
for the second query its:98%

so now my question is:
1) first query took only 2% cost to produce the result.so whenever the query cost is less whether that should be consider for better performance?

quote:
Originally posted by visakh16

quote:
Originally posted by sent_sara

My question is:
when we query the Dat_EAI_FAGLFLEXB table for any particular period,say for example:

select * from Dat_EAI_FAGLFLEXB where FISCPER ='2013001'

whether this will pickup from the particular partioning? or explictly we need to specify the partioning like $partioning.fnname(2013001) like that.

Correct me if i'm wrong.

quote:
Originally posted by visakh16

use $partition to get partition details

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

you just need to apply $partition over field based on which you created partition. Then it will return the number of partition from which data was retrieved.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Thats just time taken by each of queries within the batch.
Try running them separately to get execution stats for each.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources