Query ran slower than usual last night...

Question

Post reply

Query ran slower than usual last night...

DBAgal

Hall of Fame

Points: 3185
More actions
January 26, 2012 at 10:06 am

#391414

I had a query that ran in double the time it usually does last night. I checked for blocking and there wasn't anything reported. When I looked at the disk utilization, there were quite a few intervals where disk utilization was at 100%. I reported it to the Windows Admin, but he isn't seeing anything ununusal; read latency is mostly under 15ms.
What else can I check for? I see a little bit of I/0 wait for certain queries, but not for the one that was reported slow.
I would assume that the I/O operations would be slower due to long running batch jobs at night , nightly maintenance jobs, and backup jobs running on the SAN.
My SAN is a RAID5, and I can't change that per the storage admins.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

umasingh Ten Centuries Points: 1182 More actions · Answer 1

Please post the Query execution plan. it will help to analyse more.

DBAgal Hall of Fame Points: 3185 More actions · Answer 2

I unfortunately don't have the query execution path; its actually a group of queries, all very unoptimized, that I can't touch because it was written by a third party.

I'm actually more worried that the disk utilization was so high...I'm not sure what caused it and if the slow query is related to that or not.

Are there certain values in the sys.dm_os_wait_stats dmv that I should be looking at? My cxpacket wait is high,followed by latch_ex, oledb, and pageiolatch_sh.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Data volume or statisics changes could have caused a change in exec plan that resulted in more IOs. Hard to debug properly without exec plans before and after.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DBAgal Hall of Fame Points: 3185 More actions · Answer 4

GilaMonster (1/26/2012)
Data volume or statisics changes could have caused a change in exec plan that resulted in more IOs. Hard to debug properly without exec plans before and after.

I completely agree that having the execution plans would be helpful; it would be a lot easier to diagnose.

I do know the weekly update stats job ran on Sunday night. The query ran normal on the following Tuesday, but it was slow on Wednesday. Update_stats is turned on, so I guess that could affect the exec plan.

What do you mean by data volume change? What kind of changes should I be looking for?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

An increase in rows in any table could have caused the plan to change, can even be as little as 1 row in some cases.

First thing to identify - is this consistent? Is the performance slow again or is it back to usual. If it's slow, can that be reproduced on another server? If so, then start looking at exec plans, queries and indexes to see if a cause is obvious.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DBAgal Hall of Fame Points: 3185 More actions · Answer 6

Below is part of the query structure. There are 14 CROSS JOINS and 5 INNER JOINS in the entire query. It touches many tables and columns, so any change in the database could cause a performance difference.

SELECT Stuff

FROM (SELECT STUFF

FROM Table

STUFF

CROSS JOIN

STUFF

CROSS JOIN

STUFF

CROSS JOIN

STUFF

CROSS JOIN

STUFF

CROSS JOIN

STUFF

CROSS JOIN

(SELECT STUFF

FROM (SELECT STUFF

FROM TABLE)

STUFF

CROSS JOIN

(SELECT STUFF

INNER JOIN

STUFF

ON STUFF

AND STUFF

venoym SSCarpal Tunnel Points: 4161 More actions · Answer 7

DBAgal (1/26/2012)
Below is part of the query structure. There are 14 CROSS JOINS and 5 INNER JOINS in the entire query. It touches many tables and columns, so any change in the database could cause a performance difference.
SELECT Stuff
FROM (SELECT STUFF
FROM (SELECT STUFF
FROM (SELECT STUFF
FROM (SELECT STUFF
FROM Table
STUFF
CROSS JOIN
STUFF
CROSS JOIN
STUFF
CROSS JOIN
STUFF
CROSS JOIN
STUFF
CROSS JOIN
STUFF
CROSS JOIN
(SELECT STUFF
FROM (SELECT STUFF
FROM (SELECT STUFF
FROM (SELECT STUFF
FROM (SELECT STUFF
FROM TABLE)
STUFF
CROSS JOIN
(SELECT STUFF
INNER JOIN
STUFF
ON STUFF
AND STUFF
AND STUFF

The way you wrote that reminds me of a paper written by my brother... I want to ask... is all of that stuff like green growing things? is it going to take over the world?