jreeves399
Posts: 1
Score: 0
Joined: 8/22/2008
Status: offline
|
Hi
I manage a number of SMS/SCCM environments and am also responsible for the SQL server implementations. I have a problem in that our asset management team are trying to implement a huge query that I suspect is going to cause significant performance problems and the tempdb to grow. SMS/SCCM inventory stores all the hardware and software information within the SQL database. The asset management team want to run a query that returns all Add/Remove programs entries for every workstation within the environment. Some of our SMS environments have up to 30,000 workstations, and this will result in a huge output, millions of rows. I am concerned that this query will cause performance problems as the database is already very busy and I'm concerned the tempdb may consume all remaining disk space which is already an issue for us. I have suggested that they replicate the database or take a copy of it to run the query from there. Am I correct to be concerned about this? I'm not an experienced SQL engineer/programmer and would welcome any advice. My main concern are the customer environments that I manage, and I dont want the services we provide to become jeapordized by a huge query like this consuming all the disk space. any help or advice greatly appreciated. The query they want to use is this:
'SELECT DISTINCT
v_GS_ADD_REMOVE_PROGRAMS."DisplayName0", v_GS_ADD_REMOVE_PROGRAMS."Publisher0", v_GS_ADD_REMOVE_PROGRAMS."Version0",
Computer_System_DATA."Name0", Computer_System_DATA."UserName0",
v_GS_LastSoftwareScan."LastScanDate",
v_GS_PC_BIOS."SerialNumber0",
v_R_System."AD_Site_Name0", v_R_System."User_Name0"
FROM
{ oj ((("v_GS_ADD_REMOVE_PROGRAMS" v_GS_ADD_REMOVE_PROGRAMS INNER JOIN "Computer_System_DATA" Computer_System_DATA ON
v_GS_ADD_REMOVE_PROGRAMS."ResourceID" = Computer_System_DATA."MachineID")
INNER JOIN "v_GS_PC_BIOS" v_GS_PC_BIOS ON
Computer_System_DATA."MachineID" = v_GS_PC_BIOS."ResourceID")
INNER JOIN "v_R_System" v_R_System ON
Computer_System_DATA."Name0" = v_R_System."Name0")
INNER JOIN "v_GS_LastSoftwareScan" v_GS_LastSoftwareScan ON
Computer_System_DATA."MachineID" = v_GS_LastSoftwareScan."ResourceID"}
ORDER BY
v_GS_ADD_REMOVE_PROGRAMS."DisplayName0" ASC,
Computer_System_DATA."Name0" ASC,
v_GS_LastSoftwareScan."LastScanDate" DESC' : 21/08/2008 14:31
cheers
|
Printable Version
T-SQL Best Practice - 
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread