Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Performance Issue Expand / Collapse
Author
Message
Posted Monday, November 19, 2012 7:48 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:29 AM
Points: 3,559, Visits: 2,671
We have a procedure which performs _NO_ update or delete operation but only read. This procedure runs daily without any issue and gets completed within 1-2 minutes but one bad day, it ran continously for over 10 hours and we had to kill it. We tried rebuilding indexes and updating statistics but of no use. We finally gave up for the day. The very next day it ran again and strangely got completed within 1-2 minutes.

I took the backup from production environment for the bad day and restored it in test environment. Again I ran that procedure and somehow found the query which was causing the issue. The query contains one CTE, one physical table and one temporary table.

While running the query, the spid was showing CXPACKET as waitresourcetype which suggests high degree of parallelism and the query worked fine with MAXDOP=0. However I didn't want to do that change as the procedure ran wonderully for all other days.

The activity montior was showing following:
exchangeEvent id=Pipe 17758cf50 WaitType=e_waitPipeGetRow nodeId=4

I removed back that MAXDOP option and rather created clustered index on the temporary table (I know it should have been there in the very first place). Now it worked perfect. But my quesiton is why the issue arouse for that one day only ? There is no 'WITH RECOMPILE' option for that procedure either. It worked perfectly fine before that day and after that day. There was no drastic change in any of the involved table's volume.
Post #1386408
Posted Monday, November 19, 2012 3:04 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:10 PM
Points: 3,115, Visits: 3,239
sqlnaive (11/19/2012)
We have a procedure which performs _NO_ update or delete operation but only read. This procedure runs daily without any issue and gets completed within 1-2 minutes but one bad day, it ran continously for over 10 hours and we had to kill it. We tried rebuilding indexes and updating statistics but of no use. We finally gave up for the day. The very next day it ran again and strangely got completed within 1-2 minutes.

I took the backup from production environment for the bad day and restored it in test environment. Again I ran that procedure and somehow found the query which was causing the issue. The query contains one CTE, one physical table and one temporary table.

While running the query, the spid was showing CXPACKET as waitresourcetype which suggests high degree of parallelism and the query worked fine with MAXDOP=0. However I didn't want to do that change as the procedure ran wonderully for all other days.

The activity montior was showing following:
exchangeEvent id=Pipe 17758cf50 WaitType=e_waitPipeGetRow nodeId=4

I removed back that MAXDOP option and rather created clustered index on the temporary table (I know it should have been there in the very first place). Now it worked perfect. But my quesiton is why the issue arouse for that one day only ? There is no 'WITH RECOMPILE' option for that procedure either. It worked perfectly fine before that day and after that day. There was no drastic change in any of the involved table's volume.


Hi,

At that moment there had been a high degree of parallelism or memory pressure or cpu pressure. If your stats had been updated and indexes rebuilt then you should have decreased the MAXDOP and changed the cost threshold for parallelism.

Regards
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1386594
Posted Monday, November 19, 2012 5:39 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:47 PM
Points: 2,330, Visits: 3,510
SQL Server has frankly always had some internal issues with parallelizing queries. Parallelization is great for (re)building indexes, but for queries it's always had its quirks.

Unless you are absolutely sure the tables are properly indexed, use MAXDOP to drop the parallelization to a small number, 2-3, to help keep SQL from getting "lost". Of course for very large tables you'll need to bump it up some from that, but I still wouldn't let MAXDOP = 0 be specified or implied.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1386645
Posted Tuesday, November 20, 2012 12:05 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:29 AM
Points: 3,559, Visits: 2,671
"""""""I removed back that MAXDOP option and rather created clustered index on the temporary table (I know it should have been there in the very first place). Now it worked perfect. But my quesiton is why the issue arouse for that one day only ? There is no 'WITH RECOMPILE' option for that procedure either. It worked perfectly fine before that day and after that day. There was no drastic change in any of the involved table's volume."""""""

As I said in the above quotes, the issue got cleared after creating clustered index on temporary table. so that might be it but I have one doubt here:

"Why it was running perfectly without any issues before and after that one bad day ?"

Secondly, when once again I removed the clustered index and ran the query, it started working fine even without clustered index. So other doubt is:

"What got changed after I created the clustered index on temporary table for once (even after trying dropping clustered index and/or dropping and recreating that temporary table) ?"

Post #1386700
Posted Tuesday, November 20, 2012 12:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 2:16 AM
Points: 2,840, Visits: 3,983
sqlnaive (11/20/2012)

"Why it was running perfectly without any issues before and after that one bad day ?"

Secondly, when once again I removed the clustered index and ran the query, it started working fine even without clustered index. So other doubt is:

"What got changed after I created the clustered index on temporary table for once (even after trying dropping clustered index and/or dropping and recreating that temporary table) ?"

i dont think the restored back could make the issue reproducible , restoration often help to trace or catch the issue with the data or functionality (only available with that back up) it nothing to do with resource contention (like IO CPU etc ) which could be player/culprit in this issue .

Test , are you getting same delaey .slow response from same backed up data EVERYTIME ????????


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1386713
Posted Tuesday, November 20, 2012 1:28 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:29 AM
Points: 3,559, Visits: 2,671

Test , are you getting same delaey .slow response from same backed up data EVERYTIME ????????


Thats what I said. I got that DB restored to my test environment and found the issue as it was.
Post #1386719
Posted Tuesday, November 20, 2012 9:11 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 10:03 AM
Points: 1,499, Visits: 2,814
Might have been a bad plan in cache on the day it was running bad and that plan got flushed out and a correct plan was created the next day.

you can use this to check the cached plans for the proc and how often that plan has been used.
SELECT UseCounts,RefCounts, Cacheobjtype, Objtype,
dbid AS DatabaseName, text AS SQL
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
where text like '%<proc name>%'
ORDER BY usecounts DESC;



Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #1386991
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse