Video Screencast Help

Use of SQL Queries and performance

Created: 15 Aug 2013 | 11 comments

Ok so I have a few queries that take time to run. takes a few minutes. Once I remove most of the items from the select statement it runs nice and quick.

Is there a way to use a temp table or something? I know how to do some basic sql but not the best with stuff like this.

 

so example...

i joined as below. 

took 2 items from t3 (ac_identification) and 26 items from t4 (custom inventory)

 

Select DISTINCT t3.Name, t3.[OS Name], t4.*\
FROM [vCollection] t1 inner join
[CollectionMembership] t2 on t1.GUID = t2.CollectionGUID inner join
[Inv_Aex_AC_Identification] t3 on t2.ResourceGUID = t3._resourceGUID left outer join
Inv_Custom_Inventory t4 on t3._ResourceGuid = t4._ResourceGuid
WHERE t1.[Name] = 'MyCollection'

 

so any way to better write stuff like this? this is an example of say 15 that run slow.

Operating Systems:

Comments 11 CommentsJump to latest comment

SaschaH's picture

Did you try to run it in SSMS on the DB server directly? Still slow there?

Bechtle – your strong IT partner. Today and tomorrow

If that seems to help, please "Mark as Solution"

TeleFragger's picture

no it runs just fine... nice and quick

Did we help you? Please Mark As Solution those posts which resolve your problem,

MichaelCiv's picture

You arent using anything thats too intensvie here so its odd that it takes a few minutes to run...

What kind of hardware are you running your SQL server on? Are you doing any maintnence on the databases? If not, run this query in SSMS...

-------------
declare @PageCount bigint
    set @PageCount = 200
 
SELECT
 [Table] = object_name(stats.[object_id])
,[Index] = si.[name]
,[Avg Fragmentation] = cast(stats.[avg_fragmentation_in_percent] as numeric (6,2))
,[Page Count] = stats.[page_count]
,[Pages Fragmented] = convert( int,(stats.[page_count]*cast(stats.[avg_fragmentation_in_percent] as numeric (6,2)))/100)
,[Index Type] = substring([index_type_desc],1,charindex(' ',[index_type_desc]))
,[Task] = 'Rebuild'
,[Rebuild Script] = 'alter index ['+ si.[name] +'] on ['+ object_name(stats.[object_id]) +'] rebuild; update statistics [' + object_name(stats.[object_id]) + '] [' + si.[name]+ '];'
from sys.dm_db_index_physical_stats (db_id(), null, null, null, 'LIMITED') as stats
join sys.indexes as si on stats.object_id = si.object_id
 and stats.[index_id] = si.[index_id]
 and stats.[index_type_desc] not like 'heap'
where 1=1
  and si.[index_id] > 0
  and convert(int,(stats.[page_count]*cast(stats.[avg_fragmentation_in_percent] as numeric (6,4)))/100) >= @PageCount
order by [Pages Fragmented] desc, 4, 1, 2;
 
-- Approx table size count
select
 [Table] = so.[name]
,[Rows] = max(si.[rows])
,[Index Pages] = max(si.[dpages])
,[Reserved] = max(si.[reserved])
,[Used] = max(si.[used])
from sys.objects so
join sysindexes si on si.id = object_id(so.[name])
where so.[type] = 'U'
  and si.[rows] > 0
group by so.[name]
order by 2 desc
 

-----------

 Of the results, right click on the far right row called Rebuild Script, select that entire column and Copy it into a new query. Execute that and then try to run your report again, see if this helps...

TeleFragger's picture

running ... will let you know...

Did we help you? Please Mark As Solution those posts which resolve your problem,

TeleFragger's picture

so 2:20 to run through Altiris... even after running rebuild code which is weird..

 i run the code and get many that say rebuild... so i copy last row as said.. let run as new query... takes a bit..

run it again and nothing returned.. good?? one would think..

then run code.. still takes 2:20ish.. and then i got some back when i run the code again.... 

 

 

oh and takes 1 second from SSMS

Did we help you? Please Mark As Solution those posts which resolve your problem,

MichaelCiv's picture

Yeah thats all normal and to be expected....this rules out table fragmentation as the source of your issue. 

What do you mean it takes one second from SSMS? 

What kind of system is this database running on? Is it a dedicated box? How many endpoints? As much info would be great. 

TeleFragger's picture

well 4000 endpoints

SSMS as in Sql Server Management 

dedicated sql box with no other DB

HP Proliant DL380 G7 32gb ram dual quad core (or 8 core forget off hand)

Did we help you? Please Mark As Solution those posts which resolve your problem,

SaschaH's picture

So in SSMS the query runs in 1s? Else the query is in a Filter, Report or ASDK? Or how do you use it? If you use it from the Altiris console, try to run Profiler and see if something looks odd in the capture. If its ASDK, CRLs are reachable?

Bechtle – your strong IT partner. Today and tomorrow

If that seems to help, please "Mark as Solution"

TeleFragger's picture

yes 1second in query...

 

other in console is a report. I havent used profiler so will give that a go..

thanks

Did we help you? Please Mark As Solution those posts which resolve your problem,

MichaelCiv's picture

4000 endpoints....what kind of storage system are you running? Im curious if you're having an IOPS issue on your SQL server. 

TeleFragger's picture

Dedicated SQL BOX - CMS installed on a seperate Dedicated box

Server: HP Proliant DL380 G5

OS:Windows Server 2008 R2 Standard SP1

CPU: 2 x Xeon E5450 quad cores

RAM: 32GB

Drives: 6 drives in a Raid 5

C: OS

E: CMS 7.1 SP2 Rollup v4

 

Did we help you? Please Mark As Solution those posts which resolve your problem,