Avatar of marrowyung
marrowyung
 asked on

WITH XXX AS statement

Dear all,

I get the script form http://www.sqlmag.com/blog/practical-sql-server-45/sql-server-2012/disk-space-monitoring-143552 to monitor the available disk space of the disk SQL server owns:

it create SP using this:

CREATE PROC dbo.sp_drivespace_alerts
	@from varchar(100),
	@to varchar(200),
	@subject varchar(100),
	@threshold int  -- number of MB under which to launch an alert
AS

	SET NOCOUNT ON

	DECLARE @list nvarchar(2000) = '';

	WITH core AS ( 
		SELECT DISTINCT
			s.volume_mount_point [Drive],
			CAST(s.available_bytes / 1048576 as decimal(12,2)) [AvailableMBs]
		FROM 
			sys.master_files f
			CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s
	)

	SELECT @list = @list + ' ' + Drive + ', '
	FROM core
	WHERE AvailableMBs < @threshold

	IF LEN(@list) > 3 BEGIN
		DECLARE @msg varchar(500) = 'Low Disk Space Notification. The following drives are currently reporting less than ' 
		+ CAST(@threshold as varchar(12)) + ' MB free: ' + @list
				
		EXEC msdb.dbo.sp_send_dbmail @profile_name = 'xxxxx',
		@recipients = @to,
		@subject = @subject,
		@body = @msg
		
	END

	RETURN 0
GO

Open in new window


question:

1) I don't quite understand what is the WITH XXX As is about even from this article: http://msdn.microsoft.com/en-au/library/ms175972(v=sql.105).aspx
2) I dont' see it return the result of @threshold, from where @threshold has a value ? I don't see it has a value before it goes to "WHERE AvailableMBs < @threshold"
3) "IF LEN(@list) > 3 BEGIN" , what is the LEN for ? I read http://msdn.microsoft.com/en-au/library/ms190329.aspx and it seems LEN works like parser, to return number of character returned?

I don't see how this script works.
Microsoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
marrowyung

8/22/2022 - Mon
SOLUTION
x-men

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Ross Turner

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
x-men,

"1) WITH XXX AS (Select A from C); select x from XXX
is the same as
Select x from (Select A from C)"

then I can't see why the issuer make it so hard to read !

"2) @threshold is the 4th argument on the sp_drivespace_alerts procedure"

I know but I don't see it assigned a value. for example, before "WHERE AvailableMBs < @threshold", where we see it assigned a value before that ??

"3) LEN is the number of chars in a string (length) "

excellent, thanks. Yeah, for me, it just like what a parser do, check the length.

PortletPaul,

"When a SQL query uses "with XXX as " it is giving the name XXX to the stuff that follows inside the brackets."

XXX is the temporay table ?

"select * from xxx
union all
select * from XXX"

so it return the same time 2 times.

"CTE's are mostly used help with improving query performance."

how ? in this example, it just a select !
marrowyung

ASKER
So  this one:

USE master
 GO
 
CREATE PROC dbo.sp_drivespace_alerts
         @from varchar(100),
         @to varchar(200),
         @subject varchar(100),
         @threshold int  -- number of MB under which to launch an alert
 AS
         SET NOCOUNT ON
 
        DECLARE @msg varchar(500)
         SET @msg = 'Low Disk Space Notification. The following drives are currently reporting less than ' + CAST(@threshold as varchar(12)) + ' MB free: '
 
        CREATE TABLE #drives (
                 drive char,
                 [free] int
         )
         
        INSERT INTO #drives
         EXEC master..xp_fixeddrives
         
        IF EXISTS (SELECT null FROM #drives WHERE [free] < @threshold) BEGIN
                 DECLARE @list varchar(30)
                 SET @list = ''
                 SELECT @list = @list + ' ' + drive + ',' FROM #drives WHERE [free] < @threshold
                 SET @list = LEFT(@list, LEN(@list) -1)
                 
                SET @msg = @msg + @list
                 PRINT @msg
                 -- send the email...    
                 EXEC master..sp_send_cdosysmail @from, @to, @subject, @msg
         END
         
        DROP TABLE #drives
 
        RETURN 0
 GO

Open in new window


designed for SQL server earlier than SQL server 2008 R2 SP1 ?
PortletPaul

>>XXX is the temporay table ?
sort of, it's very similar, opinions differ about the comparisons of temp tables to CTEs
e.g. http://stackoverflow.com/questions/690465/which-are-more-performant-cte-or-temporary-tables

CTEs are in memory, temp tables are persisted (so can be reused within a session) but CTEs don't go to the tempdb and can outperform. It's one of those options in SQL without a definitive yes/no answer.

>>how ? in this example, it just a select !
by avoiding repetition of the base selection, the example was deliberately simple.
let's say you have an existing query that requires multiple subqueries, and several of these require "the same base data". Then 1 CTE may be created for "the same base data" , and reused in all subsequent subqueries - this way several select operations are avoided.
does this make sense now?
I used the CTE to first select the subset based of the indexed columns to first cut these tables down to a few thousand relevant rows each and then joined the CTE to my main query. This exponentially reduced the runtime of my query.
quote from the above url
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
marrowyung

ASKER
forget to ask,

if we want SQL to tell me if the space left is 10GB, how I modify it? It seesm I can't as the statement is if LEN(@list) >3 then ....

"
& The thing to really know is: CTE's are mostly used help with improving query performance. "

the stuff created is much faster then using temp table ? and we don't even need to drop the "stuff" after created by using drop statement ?
PortletPaul

>> we don't even need to drop the "stuff" after created by using drop statement ?
correct, no need for 'drop' with a CTE

>>the stuff created is much faster then using temp table ?
I don't think one could claim "much" faster... and perhaps not even faster at all
did you read the URL provided earlier?
Opinions differ: temp tables can still be a better option "in some circumstances", particularly if you are using stored procedures and wish to pass results from one into another of these (as table variables). However, temp tables require "persistence" (i.e. I/O to write the data)  plus you can add indexes (which also require I/O) but those indexes can then speed subsequent uses.
A CTE doesn't require the same I/O overheads but they exist ONLY during the life of the query that defined them.

You can use both temp tables and CTEs, they are not mutually exclusive choices.

them: just in case it isn't clear you can have multiple CTEs in one query

suggested reading:
http://www.sqllion.com/2010/08/common-table-expressions-cte/
https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/
marrowyung

ASKER
"by avoiding repetition of the base selection, the example was deliberately simple.
let's say you have an existing query that requires multiple subqueries, and several of these require "the same base data". Then 1 CTE may be created for "the same base data" , and reused in all subsequent subqueries - this way several select operations are avoided.
does this make sense now?


excellent and very clear, but need more example on "You can use both temp tables and CTEs, they are not mutually exclusive choices. "

so for the Reuseable query, if any index changed for the table inside that CTE, it will optimized automatically ?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
it seems that CTE is going to replace VIEW, from the link:

"Stored procedures are mainly used to deal with complex logics either for security purpose or to accomplish some combined tasks, Or in most of the cases we use temporary tables or table variables to keep intermediate results which at the end tempt to be deleted when not in use. But among all these, CTE is the best as it act as a normal view only.""

but from https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/:

"Introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement."
PortletPaul

no, that's not a good interpretation. Views will still exist (and do) - they have a good role to play still and this will continue.

I think use of the word of "view" is better interpreted in that quote to mean "read only".

CTEs ARE a query, with a name, inside a bigger query.

CTEs produce a "derived table" that is held in memory and that "derived table" can be re-used in the bigger query. Once the bigger query is complete the CTE "derived table" disappears (and the memory it used available for other purposes).

>>but need more example on "You can use both temp tables and CTEs, they are not mutually exclusive choices. "
sorry, not providing examples of this, I believe the statement is clear. You are able to create temp tables and you are able to use CTEs together - that is all the statement means.

>>so for the Reuseable query, if any index changed for the table inside that CTE, it will optimized automatically ?
it's not magic, CTEs don't fly about on carpets ;)
NO.
marrowyung

ASKER
ok,

one question

1) any way you can make the subject return different server name and which driver is reaching the critical disk usage?

2) By this SP, it seems that we can specify in % of disk space free for each driver, it is XX MB left in all driver before it fire an alerts?

3) Why the condition is "IF LEN(@list) > 3 BEGIN" then email alert rise ?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
PortletPaul

:) LOL one question: 1) 2) 3)
1 no idea
2 no idea
3 no idea
which part of the (many questions/comments) above are you referring to?
PortletPaul

3) IF LEN(@list) > 3 BEGIN

the logic here is (see lines 21-23) before this test @list is "set" to a value IF available space is less than the given threshold: if it is set by that query (space is less than threshold) and length will be 3 or more, so proceed to send a warning.

2)
EXEC master.dbo.sp_drivespace_alerts
        @threshold = 12288, -- 12GB
        @from = 'sqlalerts@noreply.com',
        @subject = 'Low Disk Space Alerts - SERVERNAMEHERE',
        @to = 'youremail@address.here,other@email.here'
from the original article please refer to it as it supplied the procedure

1) still no idea (more magic carpets?)
marrowyung

ASKER
"IF LEN(@list) > 3 BEGIN

the logic here is (see lines 21-23) before this test @list is "set" to a value IF available space is less than the given threshold: if it is set by that query (space is less than threshold) and length will be 3 or more, so proceed to send a warning."

ok, I got it but the logic means it is more than 3 because there is something to return ( change it to         SELECT *
         FROM core
         WHERE AvailableMBs < @threshold)

Then  the result should be more than 4, not just 3 ,agree ?

what I don't understand is the logic is not really quite correct on this matter.

for 1)  
"EXEC master.dbo.sp_drivespace_alerts
        @threshold = 12288, -- 12GB
        @from = 'sqlalerts@noreply.com',
        @subject = 'Low Disk Space Alerts - SERVERNAMEHERE',
        @to = 'youremail@address.here,other@email.here'"

for this, this means for that for each SERVERNAMEHERE, we have to set it differently for each server !! not quite intellgence .

Ignore it if you still dont understnad what it is.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PortletPaul

--Then  the result should be more than 4, not just 3 ,agree ?
> 3 will do the job I think, but yes I think you could make that change, probably isn't needed

making this procedure more multi-server oriented is probably a good thing to do, and when you achieve that you could also locate the server name more intelligently instead of hard-coding it as it is now. Perhaps with:

SELECT SERVERPROPERTY ('InstanceName')

http://msdn.microsoft.com/en-au/library/ms174396.aspx
marrowyung

ASKER
sorry, how to add (select SERVERPROPERTY ('ServerName')) to display the server name here:

       IF LEN(@list) > 3 BEGIN
                 DECLARE @msg varchar(500) = 'Low Disk Space Notification.
                 The following drives in' + (select SERVERPROPERTY ('ServerName')) + 'are currently reporting less than '
                + CAST(@threshold as varchar(12)) + ' MB free: ' + @list
                                 

It seem the format if char is not compatible .
PortletPaul

use @@servername
(it provides the same information)

e.g.
DECLARE @threshold int = 12288

DECLARE @list nvarchar(2000) = ''

DECLARE @msg varchar(500) = 'Low Disk Space Notification. 
                 The following drives in' 
+ @@servername
+ ' are currently reporting less than ' 
+ CAST(@threshold as varchar(12)) + ' MB free: ' + @list


select @msg

Open in new window

this is a sample only, apply into your code as appropriate e.g. you don't have to declare @threshold as it is a parameter (& if the value held by that variable isn't in MB divide for an appropriate result).
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
marrowyung

ASKER
ok , I get it right now !forget something.

what if I pass the server name when calling the SP:

DECLARE @servername varchar(100);
         
set @servername= CAST((select SERVERPROPERTY ('ServerName') ) as varchar (100));
               
EXEC master.dbo.sp_drivespace_alerts
        @threshold = 204800, -- 10240, -- 10GB
        @from = ' xxx' ,
        @subject = 'Low Disk Space Alerts -' + @servername + '' ,
        @to = 'yyy'

it seems it is complianing about "Incorrect syntax near '+'.

Am I miss something ?
marrowyung

ASKER
I use this instead:

        DECLARE @list nvarchar(2000) = '',
               @servername varchar(100);

set @servername= CAST((select SERVERPROPERTY ('ServerName') ) as varchar (100));
.
.
.
.
.
 The following drives of ' + @servername+ ' are currently reporting less than '
                + CAST(@threshold as varchar(12)) + ' MB free: ' + @list
PortletPaul

If you pass a servername as parameter, then you message should include that parameter

otherwise what you have done should work (like the @@servername does).

If you require further assistance on "generalizing" this script you might want to ask a new question.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
marrowyung

ASKER
but by this script, we still calculating using MB instead of GB.

Any convertion we can do to make the alert report using GB figure?
PortletPaul

?? the parameter is a number

?? the article you got this from used 12288 and stated this represented Gb
(note:I cannot guarantee the accuracy of that article or the proc)

a Gb is 1024 Mb, therefore perhaps you want

*1024

i.e. you can manipulate the number(s) by whatever factors you choose

but I'm not debugging the whole proc, I was answering your Q abut the meaning of XXX

are we done on this now?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
marrowyung

ASKER
ok, you get bored.
marrowyung

ASKER
I just think I can do more by this even the article say it is in MB not in GB.
PortletPaul

no, not bored, you really don't need my assistance in many of the points you are raising - and you will learn more by doing than on being instructed.

To decide if it is Mb or Gb and output in the desired units: I think you are more than capable of deciding this.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
ok , you are helpful like Mark_wills, the speak of EE in TED.

that's why I give you more marks.,

Please keep helping me.

but one thing, I need some check on how to convert from MB to GB (I am not from programminer backgroud), as the write of that article already say we CAN'T, but I think it is too rush.
PortletPaul

1 Gb = 1024 Mb

if you have 10 Gb then multiply by 1024 to get 10240 Mb

if you have 10240 Mb divide by 1024 to get 10 Gb
i.e.

10240 Mb / 1024 = 10 Gb
10 Gg * 1024 = 10240 Mb

This question is finished. If you have other topics you must open new questions
No offence intended; it's the way this site is meant to operate.
marrowyung

ASKER
"This question is finished. If you have other topics you must open new questions
No offence intended; it's the way this site is meant to operate. "

yes I knew, Mark_wills and other also say that before and I just have too much to ask to make it prefect!

you are welcome and you are coaching me.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
marrowyung

ASKER
let me open a question for this:

"1 Gb = 1024 Mb

if you have 10 Gb then multiply by 1024 to get 10240 Mb

if you have 10240 Mb divide by 1024 to get 10 Gb
i.e.

10240 Mb / 1024 = 10 Gb
10 Gg * 1024 = 10240 Mb"

https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28090915.html