October 29, 2014 at 12:42 am
Hi there,
I need some guidance as to how to investiagte a potential performance issue on our production system.
We had an issue where there was a build up of transactions on seen on the frontend. According to the developers there was a query that took a long time to complete. They have a table in the database that updates and reads from the table everytime a transaction occurs. When this build up of transactions occurs the reading and writing to the table takes longer.
We have perfmon logs running against our server. i have the logs but can someone give me a guidline as to what i should look for when investigating slow responses on the database when looking at the perfmon logs.
Please advise.
IC
October 29, 2014 at 3:24 am
Not sure what you mean by perf logs. Since you're on SQL Server 2012, I'd be using the extended events to capture query executions in order to understand which queries are running slow. But, since you already know which table and which query, instead, I'd focus on identifying why your transactions are running so slow on that table. Examine the query to ensure it's not doing something like functions in the WHERE clause on columns or using a multi-statement user-defined table valued function. Examine the execution plan for the query to see how the optimizer is resolving the query and using your indexes and statistics. Capture the query wait metrics to see what resources are causing it to slow down.
Then, with this information, you can make a determination on what you need to do in order to speed things up.
"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
October 29, 2014 at 5:40 am
I would use sp_whoisactive (AWESOME freebie found on sqlblog.com) to see real-time query execution metrics (including query plans). I would also use profiler to capture simple execution metrics and use Qure from DBSophic (another AWESOME freebie) to do aggregate execution analysis. Often the query(s) you REALLY need to tune aren't the ones that run for 5 minutes. They are the ones that run for 1 second but get called 10 times per second.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply