Hi,

I have an issue with Microsoft when a particular form is opening the CPU on my database server spikes. On the application the form takes ages to open(up to 40 minutes).

I ran a Profiler trace selecting the ShowplanXML, STMT Batch starting, STMT Batch Completed, SP Starting, SP Completed, SP STMT Starting, SP STMT Completed and nothing obvious appears.

Is there anything else I should check for?

Cheers

Paul

Look at the execution plan of the query causing the high CPU for missing indexes  - it is usually table scans that cause high CPU

That's the thing. I can't see any query causing this.

Check which process is consuming high CPU in Task Manager. If it is SQL Server, are you saying none of the queries are causing high CPU?

Yes, SQL Server is consuming the CPU but I can't see any queries that are causing it. I may be missing something or even not selecting the correct events in Profiler.

I've just realised that the issue doesn't occur all the time.

Yesterday each time I tried to open the form I got the issue and I had to wait 40-50 mins for it to open and the DB CUP was increased during this time.

This morning I tried it again and the form opened instantly and there was no impact on CPU. Does this indicate how I should investigate? Is it possible there was another application running yesterday? I certainly didn't see any application that I would have considered to be an issue.

Anybody have more suggestions for Paul?

Paul, do you think this is a bug?

Thanks!

Hi runnerpau

>> I ran a Profiler trace ... nothing obvious appears.

Can you post the information for our review, before we start the "guessing game"?

>> when a particular form is opening...

Since you are using external application and describe the behaviour of that application, we have to monitor the real reason and not just the result behaviour. It might be the SQL Server impacted by this specific external app,  or different application/actions, or that you did not monitor the right CPU thread (TASK and THREAD are not the same, by the way).

My recommendation at this point are

(1) look out for waits while you hav this problem
http://www.brentozar.com/responder/triage-wait-stats-in-sql-server/
http://www.digitalconcourse.com/dropzone/MSCOMM/PASSMN/PASSEVT20090616/Joe%20Sack%20Performance%20Troubleshooting%20with%20Wait%20Stats.pdf

* pls post the information and don't just tell us that you think that it is all OK, if you want us to have the information :-)

(2) While you see the problem occurs, try to execute the same query using the SSMS, and report the different behavior.

Paul,

As far as my little experience goes with dynamics you should consider looking at Dynamics side for this problem, this might no be issue related to SQL Server. Dynamics had some issues related to that can you post same piece of question on dynamics forum and see what experts say

Hi,

I finally got that query. It's:

exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 bigint,@P2 nvarchar(5),@P3 datetime2,@P4 datetime2,@P5 datetime2,@P6 datetime2,@P7 bigint,@P8 nvarchar(5)',N'SELECT T1.AGREEMENT,T1.ITEMCODE,T1.ACCOUNTCODE,T1.ITEMRELATION,T1.ACCOUNTRELATION,T1.QUANTITYAMOUNTFROM,T1.FROMDATE,T1.TODATE,T1.AMOUNT,T1.CURRENCY,T1.PERCENT1,T1.PERCENT2,T1.DELIVERYTIME,T1.SEARCHAGAIN,T1.PRICEUNIT,T1.RELATION,T1.QUANTITYAMOUNTTO,T1.UNITID,T1.MARKUP,T1.ALLOCATEMARKUP,T1.MODULE,T1.INVENTDIMID,T1.CALENDARDAYS,T1.GENERICCURRENCY,T1.DEL_PRICEINCLVAT,T1.TASTATUS,T1.QUOTATIONID,T1.PRIORITYPRICE,T1.DISREGARDLEADTIME,T1.MODIFIEDBY,T1.CREATEDDATETIME,T1.CREATEDBY,T1.RECVERSION,T1.PARTITION,T1.RECID,T2.INVENTDIMID,T2.INVENTBATCHID,T2.WMSLOCATIONID,T2.WMSPALLETID,T2.INVENTSERIALID,T2.INVENTLOCATIONID,T2.CONFIGID,T2.INVENTSIZEID,T2.INVENTCOLORID,T2.INVENTSITEID,T2.INVENTSTYLEID,T2.RECVERSION,T2.PARTITION,T2.RECID FROM PRICEDISCTABLE T1 CROSS JOIN INVENTDIM T2 WHERE (((T1.PARTITION=@P1) AND (T1.DATAAREAID=@P2)) AND (((T1.FROMDATE<@P3) OR (T1.FROMDATE=@P4)) AND ((T1.TODATE>@P5) OR (T1.TODATE=@P6)))) AND (((T2.PARTITION=@P7) AND (T2.DATAAREAID=@P8)) AND (T1.INVENTDIMID=T2.INVENTDIMID)) ORDER BY T1.RELATION,T1.ACCOUNTCODE,T1.ACCOUNTRELATION,T1.CURRENCY,T1.ITEMCODE,T1.ITEMRELATION,T1.UNITID,T1.QUANTITYAMOUNTFROM,T1.FROMDATE,T1.AGREEMENTHEADEREXT_RU OPTION(FAST 20)',@p5 output,@p6 output,@p7 output,5637144576,N'agl','2015-01-31 00:00:00','2015-01-31 00:00:00','2015-01-31 00:00:00','2015-01-31 00:00:00',5637144576,N'agl'
select @p1, @p2, @p5, @p6, @p7

When I execute it in SSMS I takes 2.45 and I get the following plan:

I'm not sure about the FAST 20 as I only have 6 cores in my server and only 4 are used by SQL. If I remove the FAST 20 and run the query in SSMS it executes in 11 seconds and produces the below plan:

Whilst this is a big improvement I cant help but feel the plan is still inefficient. Would anybody agree?

Cheers

Paul