May 27, 2011 at 9:55 am
Hello All,
we have a production server that has been running fine for a while,lately the queries start to take longer to complete.
we have a quest spotlight monitoring tool and since one week or so we are getting alerts that
queries taking longer ( different queries at different times)
theses are the queries that been running fine for a while now.
what could be the issues i could be facing.
sry performance i am a bit new at so some instruction might be very helpful
how to use spotlight to solve and all or to find out what is the issue that is causing the queries to suddenly run slow.
Thanks in Advance
May 27, 2011 at 12:59 pm
qur7 (5/27/2011)
Hello All,we have a production server that has been running fine for a while,lately the queries start to take longer to complete.
we have a quest spotlight monitoring tool and since one week or so we are getting alerts that
queries taking longer ( different queries at different times)
theses are the queries that been running fine for a while now.
what could be the issues i could be facing.
sry performance i am a bit new at so some instruction might be very helpful
how to use spotlight to solve and all or to find out what is the issue that is causing the queries to suddenly run slow.
Thanks in Advance
So what queries are taking longer?
Have you checked the execution plan's for the slow running queries?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 28, 2011 at 1:28 am
For Disk Delays
===============
select DB_NAME(database_id) DB_NAME, di.file_id,df.name,io_stall_read_ms ,num_of_reads
,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
,io_stall_write_ms,num_of_writes
,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'
,io_stall_read_ms + io_stall_write_ms as io_stalls
,num_of_reads + num_of_writes as total_io
,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
from sys.dm_io_virtual_file_stats(null,null) di inner join sys.database_files df on df.file_id = di.file_id
where DB_NAME(database_id) = 'your database name'
order by avg_io_stall_ms desc
IO pendings
===========
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
For Memory Presure
==================
select * from sys.dm_os_performance_counters
where counter_name like 'page life%'
For Queries Utilizations
========================
SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST
WHERE STATUS !='SLEEPING'
ORDER BY CPU DESC
For CPU Usage
=============
select
scheduler_id,
current_tasks_count,
runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255
For System Info
=============
Select * from sys.dm_os_sys_info
provide result of these queries
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 28, 2011 at 4:09 am
Performance tuning is a large topic. Gail Shaw has some excellent articles[/url] on Simple-Talk. I'd start there. Once you've got those under your belt, pick up a copy of my book, Query Performance Tuning Distilled, linked below. That will get you further down the road. Once you identify which queries are running slow, if you get stuck, post the query, the data structure, sample data and an actual execution plan in a question here on SSC and people will help.
"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
May 28, 2011 at 3:13 pm
Thank you guys, let me dig in to it based on ur advices,
since the queries are ramdonmly been picked..so not so sure if it a particular query.
May 29, 2011 at 2:02 am
Different queries , different times ?....
Could be something outside of SqlServer, are you on a virtualised machine , Anti viral (seen that more than once), is sqlserver memory being paged out , even copying a large file off the server can cause that.
May 30, 2011 at 9:22 am
qur7 (5/27/2011)
how to use spotlight to solve and all or to find out what is the issue that is causing the queries to suddenly run slow.Thanks in Advance
1) RTFM - Read The Fine Manual
2) Pay Quest to assist you in using their tool more effectively
3) Pay a consultant to help you either interpret the Quest data or delve into root cause analyses based on their own knowledge/experience
4) Check for recently changed settings, code, schema, etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply