Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 SQL query blocking

Author  Topic 

sql_msg
Starting Member

13 Posts

Posted - 2014-08-01 : 15:17:51
Hi,

We are running the query and its very slow, what and all i need to check to see whats going on wrong ?

I used profiler to see the query running.I could't see much of the information ?

Also i ran sp_who2 and status for this process id shows as suspended.

What next i could check to say some problem is there ?

Thanks,
Gangadhara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-01 : 16:25:36
Check the execution plan.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sql_msg
Starting Member

13 Posts

Posted - 2014-08-01 : 16:33:24
Developer will be running the query and i can't run the query to see the execution plan.
As DBA what are all i can check and say this is the problem ?
I am new to this dba work and i am learning things,if you post any references will be helpful.

Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-01 : 17:07:16
The developer can either provide it if he/she is running the query in SSMS, or you can grab it Profiler. I prefer the Showplan XML event class. You can open it up in SQL Sentry's Plan Explorer tool to dig into it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tm
Posting Yak Master

160 Posts

Posted - 2014-08-05 : 10:08:48
You should be able to ask the developer for the query since you are the DBA.

There are great information here on SQLTeam.
Here are some other sites (and a lot more that I may have missed) you can check out:
Brentozar.com
SQLSkills.com
mssqltips.com
Simple-talk.com


I cannot remember which site I got the information from but below is an example script to get query information from DMV (Dynamic Management Views).

We use a modified version of below script running in a job every x minutes against one of our database to get notification of blocks or queries running greater than a certain period of time.

You need to change the code below to capture the developer "where (SES.login_name = 'USERID'"

From the information captured:
>> wait_type and last_wait_type
>> plan_handle - use sys.dm_exec_query_plan to get plan information
>> text - SQL Query that was being run
>> Blocking_session_id



if OBJECT_ID('TempDB.dbo.#MonitorInfo') IS NULL
BEGIN

CREATE TABLE [dbo].[#MonitorInfo](
[MonId] [int] IDENTITY(1,1) NOT NULL,
[dttime] [datetime] NULL,
[login_name] [nvarchar](128) NULL,
[host_name] [nvarchar](128) NULL,
[session_id] [smallint] NOT NULL,
[total_elapsed_time] [int] NOT NULL,
[status] [nvarchar](30) NOT NULL,
[blocking_session_id] [smallint] NULL,
[program_name] [nvarchar](128) NULL,
[wait_type] [nvarchar](60) NULL,
[wait_time] [int] NOT NULL,
[last_wait_type] [nvarchar](60) NOT NULL,
[start_time] [datetime] NOT NULL,
[open_transaction_count] [int] NOT NULL,
[percent_complete] [real] NOT NULL,
[estimated_completion_time] [bigint] NOT NULL,
[reads] [bigint] NOT NULL,
[writes] [bigint] NOT NULL,
[deadlock_priority] [int] NOT NULL,
[command] [nvarchar](16) NOT NULL,
[statement_start_offset] [int] NULL,
[statement_end_offset] [int] NULL,
[database_id] [smallint] NOT NULL,
[sql_handle] [varbinary](64) NULL,
[plan_handle] [varbinary](64) NULL,
[text] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED
(
[MonId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


ALTER TABLE [dbo].[#MonitorInfo] ADD DEFAULT (getdate()) FOR [dttime]

END



-- ===========================================================
-- Save into table multiple times
-- ===========================================================

insert into [dbo].[#MonitorInfo]
(dttime, login_name, host_name
, session_id, total_elapsed_time, status
, blocking_session_id, program_name, wait_type
, wait_time, last_wait_type, start_time
, open_transaction_count, percent_complete
, estimated_completion_time, reads, writes
, deadlock_priority, command, statement_start_offset
, statement_end_offset, database_id, sql_handle
, plan_handle, text)
select
GETDATE()
, SES.login_name
, SES.[host_name]
, RE.session_id
, RE.total_elapsed_time
, RE.[status]
, RE.blocking_session_id
, SES.[program_name]
, RE.wait_type
, RE.wait_time
, RE.last_wait_type
, RE.start_time
, RE.open_transaction_count
, RE.percent_complete
, RE.estimated_completion_time
, RE.reads
, RE.writes
, RE.[deadlock_priority]
, RE.command
, RE.statement_start_offset
, RE.statement_end_offset
, RE.database_id
, [sql_handle]
, plan_handle
, txt.text
from sys.dm_exec_requests RE
cross apply sys.dm_exec_sql_text(RE.sql_handle) txt
left outer join sys.dm_exec_sessions SES
on RE.session_id = SES.session_id
where (SES.login_name = 'USERID') -- change to specific user running the query

WAITFOR DELAY '00:00:01'; -- Change Wait time

GO 60 -- Change to repeat for shorter or longer


select *
from [dbo].[#MonitorInfo]

-- Drop temp database after review
/*

drop table [dbo].[#MonitorInfo]

*/

Go to Top of Page

sql_msg
Starting Member

13 Posts

Posted - 2014-08-06 : 23:14:16
Thanks tm and tara for all your suggestion and reference.
Go to Top of Page
   

- Advertisement -