June 3, 2010 at 3:07 am
Hi everyone,
I am SQL Administrator in a insurance company in Denmark. Some times our users in different department trigger querys that slows the systems down.. this get very critical due to our customer services cannot work.. they call our SPOC and tell us everything is slowed down etc.
I am using SQL Heartbeat from SQLSOLUTIONS to view the performance.. I then can verify the wait milisecond pr sec is HUGE and is ongoing... in this program i can also see the online processes and its spid, SQL code etc.,, BUT - if the process is finished, i am not able to look back and see who made the query!..
Usually from the time that customers services call us,, to i open the program etc. the process is finished and i am not able to pinpoint the user who ran that mega large query!. GRRR... Yes i could be running a profiler in advance, then stop it when customer services call, and try to detect the process in there - but based on our activity it would generate HUGE amounts of data.. this would fill my disk up quickly =). (sure i could also try to add filter Reads > 1000 ) buuut still... am not interrested in starting/stoping profiler..
I just installed Quest Spotlight that has this feature i am looking for!.. (SQL Activity CTRL+A).. That shows me recent sessions.. (pls look at the attached picture). But as you know Quest is quite expensive.. and therefore,, can anyone help me creating a database, which all sessions are placed in... only for the past 15 minuttes. It should keep inserting all the sessions (maybe also with a filter to limit amount of data). Then i can run a select * from database, to view all the sessions and pinpoint the user who made the large query =)
I hope you understand what i want. =)
June 3, 2010 at 6:28 am
From the sounds of things, you should be setting up a server side trace to capture the completed queries along with their performance metrics. That way you can see what was called and the parameters used.
In addition to this, you can also query the dynamic management views to see which queries have been called, the number of times called, longest running time, etc. using sys.dm_exec_query_stats to see aggregate information. But, it won't show you who made the calls or the parameters used. That's way I'm suggesting the server side trace.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 3, 2010 at 3:44 pm
Not easy unless you are running a continuous trace, and then you have to store the results, parse the results, ick.
I came up with a way to pull stats from the instance and then pull the results from sys.processes in when a threshhold was reached.
created my own stats table -
CREATE TABLE [dbo].[adm_PerformanceStats](
[Counter] [varchar](50) NULL,
[Value] [numeric](38, 13) NULL,
[Timestamp] [datetime] NOT NULL
) ON [PRIMARY]
periodically run a query like the following (how often depends on your needs, i ran it every minute at one point to try to find my offender)
insert into dbo.adm_PerformanceStats
SELECT 'Workers waiting for CPU', CONVERT(VARCHAR, (COUNT(*))) +' '+ t2.scheduler_id, getdate() Timestamp
FROM sys.dm_os_workers AS t1, sys.dm_os_schedulers AS t2
WHERE t1.state ='runnable' AND
t1.scheduler_address=t2.scheduler_address AND
t2.scheduler_id>255
GROUP BY t2.scheduler_id
Then I ran a query to dump the sys.processes into a table based on the above results.
Table:
CREATE TABLE [dbo].[adm_AlertProcess](
[spid] [smallint] NOT NULL,
[kpid] [smallint] NOT NULL,
[blocked] [smallint] NOT NULL,
[status] [nvarchar](30) NOT NULL,
[blocking_session_id] [smallint] NULL,
[lastwaittype] [nchar](32) NOT NULL,
[last_batch] [datetime] NOT NULL,
[loginame] [nchar](128) NOT NULL,
[hostname] [nchar](128) NOT NULL,
[program_name] [nchar](128) NOT NULL,
[cmd] [nchar](16) NOT NULL,
[memusage] [int] NOT NULL,
[physical_io] [bigint] NOT NULL,
[cpu] [int] NOT NULL,
[cpu_time] [int] NOT NULL,
[total_elapsed_time] [int] NOT NULL,
[pull_time] [datetime] NOT NULL,
[query_text] [nvarchar](max) NULL
) ON [PRIMARY]
INSERT INTO adm__dba.dbo.adm_AlertProcess
SELECT P.spid, P.kpid, P.blocked, R.status, R.blocking_session_id, P.lastwaittype,P.last_batch,P.loginame, P.hostname
,P.program_name,P.cmd,P.memusage,P.physical_io,P.cpu,R.cpu_time,R.total_elapsed_time, GETDATE()pull_time
,(select substring (text, statement_start_offset/2 +1,
(case when statement_end_offset= -1
then len(convert(nvarchar(max), text)) *2
else statement_end_offset
end - statement_start_offset)/2)
from sys.dm_exec_sql_text(r.sql_handle)) as query_text
FROM sys.sysprocesses P
JOIN SYS.DM_EXEC_REQUESTS R ON P.SPID=R.session_id
WHERE spid>50
AND cmd<>'AWAITING COMMAND'
AND P.spid<> (SELECT @@SPID)
and P.program_name not like 'DatabaseMail%'
ORDER BY P.blocked,P.spid
I am not going to suggest for a moment that this is an elegant solution, or that you don't need to tweek this, but it works like gang busters for me. Goodluck, I'm constantly telling my people that it is inappropriate to just kick of a query and go to lunch ... or leave for the weekend!
😎
June 3, 2010 at 3:56 pm
oh, and i've also used alerts to kick off that second query on the sysprocesses ...
IN SMS
1. SQL SERVER AGENT
2. RIGHT CLICK ALERTS
3. SELECT NEW ALERT
4. SELECT SQL SERVER PERFORMANCE CONDITION ALERT
5. PICK YOUR OBJECT/COUNTER/ALERT LEVEL
6. THEN SET YOUR RESPONSE TO EXECUTE A JOB THAT RUNS THE 2ND SCRIPT
😎
June 4, 2010 at 1:16 am
thanks alot journyman!.. just what i needed. i will try it out and get back to you. thanks so far.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply