Help with a Query

Is there a way to optimize this query, by doing it in fewer steps?
Perhaps by only utilizing and updating one table?
-TIA-

code:

/ ****************************************
****
Import the data from DBCC SQLPERF(LOGSPACE)
 ****************************************
****/
CREATE TABLE #dbcc_sqlperf (
DB_Name varchar(100),
Log_Size decimal (28, 5),
Log_Used_Percent decimal (28, 5),
Status tinyint )
INSERT #dbcc_sqlperf EXEC ('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS')
SELECT * FROM #dbcc_sqlperf --Debug
/ ****************************************
****
Convert the data form DBCC SQLPERF(LOGSPACE)
into a more legible form
 ****************************************
****/
CREATE TABLE #logstats (
DB_ID tinyint,
DB_Name varchar(100),
Logical_Name varchar(100),
File_Type varchar(10),
Total_Space_in_MB decimal (28, 2),
Used_Space_in_MB decimal (28, 2),
Free_Space_in_MB decimal (28, 2),
Percent_Used decimal (28, 2))
INSERT #logstats (DB_Name, File_Type, Total_Space_in_MB,
Used_Space_in_MB,
Free_Space_in_MB, Percent_Used)
SELECT DB_Name = DB_Name,
File_Type = 'Log',
Total_Space_in_MB = log_size,
Used_Space_in_MB = sum (log_size*(log_used_percent/100)),
Free_Space_in_MB = sum (log_size
-(log_size*(log_used_percent/100))),
Percent_Used = log_used_percent
FROM #dbcc_sqlperf
GROUP BY DB_Name, Log_Size, Log_Used_Percent, Status
/ ****************************************
****
Collect the DBID number for each log file
 ****************************************
****/
UPDATE l
SET DB_ID = m.dbid
FROM #logstats l INNER JOIN master..sysdatabases m
ON m.name = l.DB_Name
/ ****************************************
****
colloct the Logical Name for each log file
 ****************************************
****/
UPDATE l
SET Logical_Name = m.name
FROM #logstats l INNER JOIN master..sysaltfiles m
ON m.dbid = l.DB_ID AND m.groupid = '0'
/ ****************************************
****
Display and drop temp tables
 ****************************************
****/
SELECT * FROM #logstats
DROP TABLE #logstats
DROP TABLE #dbcc_sqlperf

What exactly do you expect this script to do? What are you trying to
accomplish?
Anith|||This is a small snippet of a larger project that you can find at
http://groups.google.com/group/micr...939daf22f9796b8
Anyway the idea behind this is to continually (every 15 minutes or so)
go out to all the servers and collect the statistics of the databases.
This will then be stored in a centralized location where we can tract
trends, and or raise alarms if some of our developers do a bulk insert
(with out tell us of course). I just would like to do it the most
efficient way the first time. If I can reduce the I/O's and CPU by
even a couple of points then its worth it.
I think I tend to scare everyone off when I post a huge chunk of code,
so i am doing it piecemeal
Thanks
-Matt-|||>> I think I tend to scare everyone off when I post a huge chunk of code,
Of course you are :-)
Try creating a script & schedule it to run every 15 minutes and insert the
results into a permanent base table. Here is a simple re-write without
changing much of what you posted:
CREATE TABLE #dbcc_sqlperf (
database_name VARCHAR(40), Log_Size DECIMAL (28, 5),
Log_Used_Percent DECIMAL (28, 5), Status TINYINT );
INSERT #dbcc_sqlperf EXEC ('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS')
SELECT DB_ID( database_name ) AS "database_id",
database_name AS "database_name",
( SELECT m.name
FROM master..sysaltfiles m
WHERE m.dbid = DB_ID(l.database_name)
AND m.groupid = '0' ) AS "logical file name",
'Log' AS "file_type",
log_size AS "total space in MB",
SUM (log_size * log_used_percent * .001) AS "used space in MB",
SUM (log_size - (log_size * log_used_percent * .001)) AS "free space
in MB",
log_used_percent AS "percent used"
FROM #dbcc_sqlperf l
GROUP BY database_name, Log_Size, Log_Used_Percent, Status ;
You can use a table variable to avoid temp table usage. Alternatively, you
may be able to retrieve similar information using the system procedure
sp_spaceused as well.
Anith|||Thanks Anith,
I really appreciate this. I am always looking to speed up and
bulletproof my code. if i can save a few extra cycles, during the
lifetime code that will run, could be several hours. Sure this might be
trivial, but if I reuse this code somewhere I know that I have one of
the most efficient pieces premade.