SQL Server Performance Forum – Threads Archive
Need Help Urgent – Server Pinned!Hi Everyone,<br /><br />Here’s the situation: Server is pinned at 100% CPU utilization. When I look to see what is killing my sql server I have a list of connections from a particular application. The problem is I don’t know what code this app is running that is so bad. When I run a Profiler trace using the Events: Sessions:Existing Connection, StoredProcedure:RPCStarting, RPCCompleted, SPStarting, SPCompleted,SPStmtStarting,<br />TSQL<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />QLBatchStarting, <br />SecurityAudit:AuditLogin,AuditLogout <br />but it only tells me that its an existing connection and the textdata is:<br /><br />- network protocol: TCP/IP<br />set quoted_identifier off<br />set implicit_transactions off<br />set cursor_close_on_commit off<br />set ansi_warnings on<br />set ansi_padding on<br />set ansi_nulls on<br />set concat_null_yields_null on<br />set language us_english<br />set dateformat mdy<br />set datefirst 7<br /><br /><br />Any idea on how to see what code this app is running that already executed the commands and is hung? This happened once before and the only way I got the server to come back to normal was to take the node offline (drop all connections) and then bring it back online.<br /><br />Thanks in advance,<br />Anthony
Try DBCC INPUTBUFFER (spid). Hope it helps.
Thanks for your help. I executed the statement and got: EventTypeParametersEventInfo
RPC Event0 sp_executesql;1 I don’t have any idea what this sp is?
This proc executes dynamic sql
Thanks for your help. They should not be executing anything outside of an sp. Looks like I have some policing to do. Thanks again mmarovic,
Better first profile it. This proc can be used to execute stored procedure too.
Yep it is. Its a dynamic sp that queries a linked Oracle server. I found that its an Oracle problem because the sp does not complete when i run the code directly on Oracle. They might need to update their indexes or stats or something. Thanks again for your help,