tempdb 35 GB using by query statement- Help

Question

tempdb 35 GB using by query statement- Help

SQL Galaxy

One Orange Chip

Points: 29472
More actions
November 12, 2011 at 2:08 am

#104101

Hi,
In SQL server 2000 with SP5, Totally 10 Lacks record need to be retrieve by using this Query. but with 5 mints tempdb using upto 35 GB. please tell me, Is there alternative way for reducing tempdb space while running this query.
select a.cmmac,a.cpip, min( a.entry_dt) min_dt,max(a.entry_dt) max_dt,b.CPEIP,b.CPEMAC
from cmts_ipadd a, cmts_ipadd_d b where
a.cmmac = b.cmmac group by a.cmmac,a.cpip,b.CPEIP,b.CPEMAC
Thanks
ananda

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

10 lacks is how many?

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

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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

DBCC SHOWCONTIG scanning 'CMTS_IPADD' table...

Table: 'CMTS_IPADD' (407149808); index ID: 0, database ID: 31

TABLE level scan performed.

- Pages Scanned................................: 13788

- Extents Scanned..............................: 1742

- Extent Switches..............................: 1741

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 98.97% [1724:1742]

- Extent Scan Fragmentation ...................: 81.11%

- Avg. Bytes Free per Page.....................: 356.2

- Avg. Page Density (full).....................: 95.60%

DBCC SHOWCONTIG scanning 'CMTS_IPADD' table...

Table: 'CMTS_IPADD' (407149808); index ID: 2, database ID: 31

LEAF level scan performed.

- Pages Scanned................................: 26050

- Extents Scanned..............................: 5224

- Extent Switches..............................: 26042

- Avg. Pages per Extent........................: 5.0

- Scan Density [Best Count:Actual Count].......: 12.51% [3257:26043]

- Logical Scan Fragmentation ..................: 50.18%

- Extent Scan Fragmentation ...................: 96.76%

- Avg. Bytes Free per Page.....................: 6500.3

- Avg. Page Density (full).....................: 19.69%

DBCC SHOWCONTIG scanning 'CMTS_IPADD' table...

Table: 'CMTS_IPADD' (407149808); index ID: 6, database ID: 31

LEAF level scan performed.

- Pages Scanned................................: 8682

- Extents Scanned..............................: 1099

- Extent Switches..............................: 4074

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 26.65% [1086:4075]

- Logical Scan Fragmentation ..................: 19.65%

- Extent Scan Fragmentation ...................: 36.94%

- Avg. Bytes Free per Page.....................: 1493.8

- Avg. Page Density (full).....................: 81.54%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-------------------------------------------

DBCC SHOWCONTIG scanning 'CMTS_IPADD_D' table...

Table: 'CMTS_IPADD_D' (39148497); index ID: 0, database ID: 31

TABLE level scan performed.

- Pages Scanned................................: 5700

- Extents Scanned..............................: 724

- Extent Switches..............................: 723

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 98.48% [713:724]

- Extent Scan Fragmentation ...................: 99.03%

- Avg. Bytes Free per Page.....................: 376.6

- Avg. Page Density (full).....................: 95.35%

DBCC SHOWCONTIG scanning 'CMTS_IPADD_D' table...

Table: 'CMTS_IPADD_D' (39148497); index ID: 2, database ID: 31

LEAF level scan performed.

- Pages Scanned................................: 11509

- Extents Scanned..............................: 3236

- Extent Switches..............................: 11501

- Avg. Pages per Extent........................: 3.6

- Scan Density [Best Count:Actual Count].......: 12.51% [1439:11502]

- Logical Scan Fragmentation ..................: 50.18%

- Extent Scan Fragmentation ...................: 98.15%

- Avg. Bytes Free per Page.....................: 6229.7

- Avg. Page Density (full).....................: 23.03%

DBCC SHOWCONTIG scanning 'CMTS_IPADD_D' table...

Table: 'CMTS_IPADD_D' (39148497); index ID: 6, database ID: 31

LEAF level scan performed.

- Pages Scanned................................: 4107

- Extents Scanned..............................: 520

- Extent Switches..............................: 1953

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 26.31% [514:1954]

- Logical Scan Fragmentation ..................: 20.06%

- Extent Scan Fragmentation ...................: 88.27%

- Avg. Bytes Free per Page.....................: 1558.6

- Avg. Page Density (full).....................: 80.74%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

----------------------------------

Table Defination - CMTS_IPADD

Interfacevarcharno25 yesnonoSQL_Latin1_General_CP1_CI_AS

CMMACvarcharno20 nononoSQL_Latin1_General_CP1_CI_AS

CPIPvarcharno20 yesnonoSQL_Latin1_General_CP1_CI_AS

CPCOUNTvarcharno2 yesnonoSQL_Latin1_General_CP1_CI_AS

ENTRY_DTdatetimeno8 yes(n/a)(n/a)NULL

Index defination - CMTS_IPADD

Inx_CMTS_IPAddnonclustered located on PRIMARYCMMAC, ENTRY_DT

IX_CMTS_IPADDnonclustered located on PRIMARYCMMAC

Table Defination - CMTS_IPADD

CMMACvarcharno20 yesnonoSQL_Latin1_General_CP1_CI_AS

CPEMACvarcharno20 yesnonoSQL_Latin1_General_CP1_CI_AS

CPEIPvarcharno20 yesnonoSQL_Latin1_General_CP1_CI_AS

ENTRY_DTdatetimeno8 yes(n/a)(n/a)NULL

index defination - CMTS_IPADD_D

INX_CMTS_IPAdd_d nonclustered located on PRIMARYCMMAC, ENTRY_DT

IX_CMTS_IPADD_Dnonclustered located on PRIMARY CMMAC

thanks

ananda

LutzM SSC Guru Points: 107049 More actions · Answer 3

It seems like there's no clustered index on the table.

Based on the information provided so far I recommend to make cmmac the clustered index.

After that, rerun the query and post the actual execution plan.

Edit: do you really need to have the data type of cmmac as varchar(20)?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

Hi, Thanks for your reply..

I have done as following steps, but It is not hope again same issues and query batch was not able to completed due to tempdb full space, and there is no free space in physical HD on D drive, Totally Frees pace 35 GB, during query running 00:18:48 this time HD is getting full also tempdb. I could not take execution plan while running this query.

CREATE CLUSTERED INDEX idx_CMMAC

ON CMTS_IPADD (CMMAC)

GO

CREATE CLUSTERED INDEX idx_CMMAC_d

ON CMTS_IPADD_D (CMMAC)

GO

SET STATISTICS PROFILE ON

GO

select a.cmmac,a.cpip, min( a.entry_dt) min_dt,max(a.entry_dt) max_dt,b.CPEIP,b.CPEMAC

from cmts_ipadd a, cmts_ipadd_d b where

a.cmmac = b.cmmac group by a.cmmac,a.cpip,b.CPEIP,b.CPEMAC

Thanks

ananda

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Why can't you take an execution plan? Did you read the article I referenced?

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

GilaMonster (11/12/2011)
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
10 lacks is how many?

1 lack = 100K.

So 1M here.

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 7

kindly find the attached Execution plan about that query.

I really sorry for the late replay...

10 Lacks Records Means - 1000000 Rows count.

Please Provide the solution

thanks

ananda

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Widen this index: IX_CMTS_IPADD nonclustered located on PRIMARY CMMAC. Add cpip as a second column.

Widen this index: IX_CMTS_IPADD_D nonclustered located on PRIMARY CMMAC. Add CPEIP, CPEMAC

Not sure if CMMAC is a good choice for a clustered index. Up to 20 bytes wide, no indication if it's unique, ever-increasing or unchaining. Be careful, picking a clustered index is something that has to be done carefully, not on the basis of a single query.

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

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 9

ananda.murugesan (11/12/2011)
kindly find the attached Execution plan about that query.
I really sorry for the late replay...
10 Lacks Records Means - 1000000 Rows count.
Please Provide the solution
thanks
ananda

I believe, according to the following information in the execution plan, that you have an "accidental cross join" in the data and it produces 431,702,750 internal rows in TempDB in the process. That could easily amount to 35 GB of space in TempDB.

|--Merge Join(Inner Join, MANY-TO-MANY MERGE: (.[CMMAC])=([a].[CMMAC]), RESIDUAL: (.[CMMAC]=[a].[CMMAC]))

How many duplicated values for each value in the cmmac column are there in each of the two tables?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 10

GilaMonster (11/12/2011)

Widen this index: IX_CMTS_IPADD nonclustered located on PRIMARY CMMAC. Add cpip as a second column.

Widen this index: IX_CMTS_IPADD_D nonclustered located on PRIMARY CMMAC. Add CPEIP, CPEMAC

Thanks for reply Gila, Still I am not getting solution.

Already I have created clustered Index those two columns, As you told you those index is not helpful, so again add cpip,CPEIP, CPEMAC columns in exiting Nonclustred index.

In this case what will be impact, Can I dorp index and again create clustred index in table.

syntax by create computed nonclustred index columns

create nonclustred Index [IX_CMTS_IPADD] on [IX_CMTS_IPADD_D] [cpip],[CPEIP], [CPEMAC]

thanks

ananda

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 11

ananda.murugesan (11/13/2011)

GilaMonster (11/12/2011)
Widen this index: IX_CMTS_IPADD nonclustered located on PRIMARY CMMAC. Add cpip as a second column.
Widen this index: IX_CMTS_IPADD_D nonclustered located on PRIMARY CMMAC. Add CPEIP, CPEMAC
Thanks for reply Gila, Still I am not getting solution.
Already I have created clustered Index those two columns, As you told you those index is not helpful, so again add cpip,CPEIP, CPEMAC columns in exiting Nonclustred index.
In this case what will be impact, Can I dorp index and again create clustred index in table.
syntax by create computed nonclustred index columns
create nonclustred Index [IX_CMTS_IPADD] on [IX_CMTS_IPADD_D] [cpip],[CPEIP], [CPEMAC]
thanks
ananda

Even a clustered index won't help the "accidental cross join" present in the query. You need to resolve that first.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 12

Thanks --Mr.Jeff for your valuable reply....

I created clustred index after that it won't help aslo same issues, as you suggested me that is correct after creating clustred index.

Once drop clustred index which was created already after that Can I create computed nonclustred index those colum?

Please guide me, "accidental cross join" present in the query. how to resolve that first?

thanks

ananda

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Stop playing with indexes. Your problem is likely that you are missing information about the keys of that join. My guess is that you need to have 1-2 more keys to that join. That'll stop the cross join from hapenning.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 14

ananda.murugesan (11/14/2011)
Thanks --Mr.Jeff for your valuable reply....
I created clustred index after that it won't help aslo same issues, as you suggested me that is correct after creating clustred index.
Once drop clustred index which was created already after that Can I create computed nonclustred index those colum?
Please guide me, "accidental cross join" present in the query. how to resolve that first?
thanks
ananda

I can't help much on the "accidental cross join" because I don't have all the data that you have. I can only go by an execution plan from here (see the second link in my signature line below for how to provide one that we can actually use).

In this case, you could look at the execution plan an look for a really fat arrow with the row count I mentioned. That will help you identify the two tables in the "accidental cross join (many-to-many)" and then you can do one of four things... either figure out what additional criteria you need or figure out which data you need to de-dupe and isolate in a Temp Table or figure out that the overall requirements of the query can't be supported by the current design of the tables or figure out that the tables may need a bit of redesign (the last two are a bit extreme... try the first two methods first).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)