May 8, 2017 at 11:21 pm
Our web based product is directly accessed by many. out of which 10 customers suddenly report slowness. There sql2008 standard edition installed. upon investigation it was found that everything is fine at application and web end but all requests are taking time on given database server.
I checked for blocking/deadlocks if any and also for long running transaction but nothing found
Please let me know if anything else needs to be checked
thanks
May 9, 2017 at 12:33 am
atulyan.aries - Monday, May 8, 2017 11:21 PMOur web based product is directly accessed by many. out of which 10 customers suddenly report slowness. There sql2008 standard edition installed. upon investigation it was found that everything is fine at application and web end but all requests are taking time on given database server.I checked for blocking/deadlocks if any and also for long running transaction but nothing found
Please let me know if anything else needs to be checked
thanks
Do you maintain the instance. Are the statistics updated? You should check the indexing strategy as well.
Igor Micev,My blog: www.igormicev.com
May 9, 2017 at 8:01 am
A good place to start with general troubleshooting may be looking at the database waits:
https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
this can give you a general feel for what's the biggest problem in your instance, memory, CPU, I/O.
From there, it's probably a good idea to see what are the worst performing queries in your instance:
http://www.sqlpassion.at/archive/2015/04/20/how-to-find-your-worst-performing-sql-server-queries/
I tend to use this query:
SELECT TOP 25
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
qs.total_worker_time / qs.execution_count / 1000000.0 AS avg_cpu_seconds,
qs.total_worker_time / 1000000.0 AS total_cpu_seconds,
qs.total_logical_reads, qs.total_logical_writes,
qs.total_logical_reads / qs.execution_count AS average_logical_reads,
qs.total_logical_writes / qs.execution_count AS average_logical_writes,
qs.execution_count, qs.last_execution_time,
o.name AS object_name, o.modify_date,
qp.query_plan, qs.sql_handle, qs.plan_handle,
DB_NAME(qt.dbid) AS database_name--, qt.text
FROM sys.dm_exec_query_stats qs
OUTER APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
WHERE qt.dbid = DB_ID()
ORDER BY qs.total_seconds DESC; --longest time to complete
--ORDER BY qs.total_logical_reads DESC; --most potential I/O
-- ORDER BY qs.total_worker_time DESC; --most CPU
May 9, 2017 at 10:13 pm
atulyan.aries - Monday, May 8, 2017 11:21 PMOur web based product is directly accessed by many. out of which 10 customers suddenly report slowness. There sql2008 standard edition installed. upon investigation it was found that everything is fine at application and web end but all requests are taking time on given database server.I checked for blocking/deadlocks if any and also for long running transaction but nothing found
Please let me know if anything else needs to be checked
thanks
Are you sure the issue is on the database side?
Did you rule out WEB, network, AppServer issues?
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply