SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Table Partitioning
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sent_sara
Constraint Violating Yak Guru

India
370 Posts

Posted - 07/15/2013 :  15:40:41  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 07/16/2013 :  01:48:24  Show Profile  Reply with Quote
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
Go to Top of Page

sent_sara
Constraint Violating Yak Guru

India
370 Posts

Posted - 07/16/2013 :  23:04:24  Show Profile  Reply with Quote
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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/17/2013 :  02:09:06  Show Profile  Reply with Quote
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
Go to Top of Page

sent_sara
Constraint Violating Yak Guru

India
370 Posts

Posted - 07/17/2013 :  13:42:32  Show Profile  Reply with Quote
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


Go to Top of Page

mfemenel
Professor Frink

USA
1421 Posts

Posted - 07/17/2013 :  16:13:21  Show Profile  Visit mfemenel's Homepage  Reply with Quote
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"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/18/2013 :  00:59:54  Show Profile  Reply with Quote
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
Go to Top of Page

sent_sara
Constraint Violating Yak Guru

India
370 Posts

Posted - 07/18/2013 :  14:06:01  Show Profile  Reply with Quote
how to add the screenshot in the below tag?

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


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000