December 14, 2009 at 3:23 am
Hi all
I need to undestand if what i see on my instance is normal.
I enabled the Data Collection in SQL 2008 and when i look for the history of the queries I only see the 10 most expensive queries, which is fine but those queries are :
------------------------
SELECT s.schedule_id,
'schedule_name' = name,
enabled,
freq_type,
freq_interval,
freq_subday_type,
freq_subday_interval,
freq_relative_interval,
freq_recurrence_factor,
active_start_date,
active_end_date,
active_start_time,
active_end_time,
date_created,
'schedule_description' = FORMATMESSAGE(14549),
js.next_run_date,
js.next_run_time,
s.schedule_uid
INTO #temp_jobschedule
FROM msdb.dbo.sysjobschedules AS js
JOIN msdb.dbo.sysschedules AS s
ON js.schedule_id = s.schedule_id
WHERE ((@job_id IS NULL) OR (js.job_id = @job_id))
AND ((@schedule_name IS NULL) OR (s.name = @schedule_name))
AND ((@schedule_id IS NULL) OR (s.schedule_id = @schedule_id))
----------------------------------------------
SELECT @job_name = name,
@owner_sid = owner_sid
FROM msdb.dbo.sysjobs_view
WHERE (job_id = @job_id)
--------------------------------------------------
SELECT *, (SELECT COUNT(*) FROM sysjobschedules WHERE sysjobschedules.schedule_id = #temp_jobschedule.schedule_id) as 'job_count'
FROM #temp_jobschedule
ORDER BY schedule_id
CAN YOU EXPLAIN WHY I DO NOT SEE USERS QUERIES? or the APPLICATION QUERIES? Even if i try to look for them in the database where the collection store all its data....they are not there....IS THIS NIRMAL????
December 14, 2009 at 12:33 pm
From what I see in your query, you are looking at Jobs.
FROM msdb.dbo.sysjobschedules AS js
JOIN msdb.dbo.sysschedules AS s
Both of them are for Jobs. Thats why you are looking in the MSDB Database. The User queries and application qieries are not stored in these objects
Check the DMV sys.dm_exec_requests dynamic management view and see if that helps
-Roy
December 14, 2009 at 3:57 pm
Hi Roy maybe you did not get the question....
Basically in SQL 2008 we have a new functionality called DATA COLLECTION.
When that is enabled the SQL SERVER take a sample every 30 sec (in my case) and see the most expensive queries in term of duration, CPU, physical reads.....etc....etc....
in that i see only queries i posted....I was asking if other people experience the same......
also the most expensive queries are not the same as per SQL Dashboard (reports you can download from microsoft)....
anybody any idea....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply