FindSpool amount used? 1

[DugzDMan]

IS there a way to check the amount of spool spaced used by a query?

I have 100Gb of spool, but the people who use my reports only have 40. I need to redo the query so that they can run it and want to check it instead of sending it out and waiting to see if they get an error.

Thanks!

 

[dnoeth]

For a running query you need PMon, the only other way is to reset MaxSpool (you probably have to be an admin to do that) before running the query and check it afterwards.

I use this query to check for Users running out of spool:
SELECT
DatabaseName
,CAST(Max(PeakSpool)/Max(MaxSpool) * 100 AS DEC(5,2)) AS PercentInUse
,Max(PeakSpool) AS PeakPerAMP
,Max(MaxSpool) AS SpoolPerAMP
,Sum(PeakSpool) AS GlobalPeak
,Sum(MaxSpool) AS GlobalSpool
FROM dbc.DiskSpace
WHERE MaxSpool > 0
GROUP BY 1
HAVING PercentInUse > 90
ORDER BY 2 DESC;

It's important to look at spool at AMP level, in most cases a query fails because it's spool is skewed.
Having current statitics helps the optimizer to avoid skewed spools.

Dieter

 

[DugzDMan]

Thanks, I have current stats on the tables I am using. Is there someplace I should look for rules on what to collect stats on? Should I use the PI, what the query is using to join the tables, both, neither?

I ran the query you mentioned and here is what I got back:
DatabaseName PercentInUse PeakPerAMP SpoolPerAMP GlobalPeak GlobalSpool
MyDB 100.19 231,323,648 230,891,719 437,483,570,688 579,999,998,128

Can you actually have more than 100% in use? What does this translate to??

Thanks!

 

[dnoeth]

"Can you actually have more than 100% in use?"
Yes, as PeakSpool is checked against maxspool *after* a spool block has been assigned.

You should add
Min(PeakSpool) and Avg(PeakSpool)
to the query (i forgot), but it looks only a bit skewed. Maybe you should ask your admin to reset peakspool and then run it again.
Your spool looks very large, but it's only 231MB per AMP, so maybe you really hit the spool limit set by your admin. If so ask him to increase spool ;-)


Regarding statistics:
thread328-568325 according to the output you're running on a very large system, looks like a large telco in the states...

Dieter

 

[DugzDMan]

You nailed my job right on the head. Can you pick tonight's lottery numbers to? ;-)

I'll check the thread on stats.

Thanks for the info!

 

[DugzDMan]

One more thing. I'm assuming I can run this query putting a where clause on DatabaseName = '...' (users id) to analyze the end user accounts as well, right?

 

[dnoeth]

"You nailed my job right on the head. Can you pick tonight's lottery numbers to?"
No, but i can divide GlobalSpool by SpoolPerAMP
And i know the size of the largest system...

"I'm assuming I can run this query putting a where clause on DatabaseName = '...' (users id)"

Yes, just remove the
HAVING PercentInUse > 90

Dieter

 

[DugzDMan]

Sneaky ;-)

Thanks for all the help. I think I'll check the users to see what they look like (and see if I can get anything out of the numbers). I'm sure that will help figure out if I am making the queries more efficient.

Thanks for all your help!! I'd say you deserve a ... star.