January 26, 2012 at 10:06 am
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.
January 26, 2012 at 10:12 am
Please post the Query execution plan. it will help to analyse more.
January 26, 2012 at 10:25 am
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.
January 26, 2012 at 10:26 am
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
January 26, 2012 at 10:36 am
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?
January 26, 2012 at 10:40 am
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
January 26, 2012 at 11:01 am
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
January 26, 2012 at 2:55 pm
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?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply