Application Hang / SQL Issue?

Question

Application Hang / SQL Issue?

2Tall

SSChampion

Points: 14632
More actions
February 12, 2008 at 5:35 am

#183830

Hi. I am in disagreement with our application vendor. When running their application the local system hangs, task manager records their process as using 99% of the CPU (if I disconnect client from the network the process continues to use 99% CPU). When I kill their process Explorer.exe then uses 99% CPU, the only way to resolve is to kill the system (reboot).
They say it is our Network or SQL server causing the issue.
I say it is their application.
Network runs at GB (SQL server NIC records 2% teamed NIC 2GB)
SQL Server 2005 64 bit on Windows Standard 2003 64bit server (DL380 G5)
2 QUAD Core Xeon Processors
8 GB RAM
RAID 1+0 (OS / TempDB)
RAID 1+0 (Data)
RAID 1+0 (Logs)
Mirrored to identical passive server using dedicated NIC + crossover cable on different subnet.
The application uses a single SQL account to access SQL Server DB
I am running SQL Server Profiler / Activity Monitor.
What should I be looking for?
Any thoughts based on the above that point to it being a SQL issue? Perfmon.exe looks OK (set to monitor Disc Activity).
If you need further information do not hesitate to ask.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 13 posts - 1 through 12 (of 12 total)

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

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

Is the application in question running on the SQL Server, or on another machine?

I would suggest profiler. Start a trace on the SQL Server and watch what the app's doing to the DB, during normal operation and when if hangs like this. That should give you an idea what's happening at the least.

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Also - what type of mirroring is it?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 3

Hi,

Similar to what Matt has requested, where does the application sit infrastructure wise?

What type of application are you running (e.g. web or client-server)?

How long ago was application implemented and did you experience the issue at that point?

Has anything changed recently?

Thanks,

Phillip Cox

2Tall SSChampion Points: 14632 More actions · Answer 4

Hi.

Application runs on SQL

client-server

SQL Profiler running

What columns might be of interest?

If I have database A can I filter by database name?

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

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

Anything sharing a server with SQL is not recommended.

Start with the RPC_Completed and Batch Completed events (under Stored procs and T-SQL respectivly)

Column - Text, cpu, reads, duration, start time, application name, login name should do for starters.

You can filter by db name, but is often better to filter by dbid as not all events populate the db name. You can find a database id using the sys.databases view in SQL.

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

2Tall SSChampion Points: 14632 More actions · Answer 6

Thanks.

I might have to eat humble pie as I have tried to replicate same issue on other machines and have not been able to (have now updated my NVIDIA drivers) had lots of nview_info errors in system log.

I will still monitor as suggested though.

Many Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

2Tall SSChampion Points: 14632 More actions · Answer 7

I am running SQL Profiler and filtered as you suggested, however I could not see how to apply filter by database ID (I identified dbid by running SELECT * FROM sys.databases (which for me was a big thing!! :).

New ERP application has had some performance issues (only a handful of users during testing). I would like to monitor SQL performance + understand the results of the SQL Profiler so I can provide the application vendor with some metrics (rather than just saying 'The application crashes the system'). They are saying it is our Network, SQL Server (I disagree) as SQL Hardware is good. The configuration was validated by Tony Rogerson (SQL consultant) + no more than 4 users are on the system (currently in testing phase).

Thanks,

Phil.

PS: Profiler creates .trc file, can the results only be viewed in SQL?

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

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

In SQL 2005's profiler's create trace dialog, on the second tab (event selection) there's a button at the bottom 'Column Filters' That will let you set whatever filters you like.

You can only filter on a column included in the trace, and dbid is not included by default.

You can import profiler's trc files into SQL by using the fn_trace_gettable SQL function, of by loading the trc into profiler and selecting File->Save As->TraceTable

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

2Tall SSChampion Points: 14632 More actions · Answer 9

Thanks for the response once again.

Regards,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

2Tall SSChampion Points: 14632 More actions · Answer 10

Back Again! When I configure the SS Profiler (adding a Column Filter) to apply a filter to the Database ID as suggested do I enter the Database ID under 'Application Name' then set 'Like' to the ID (i.e. 8).

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 11

Philip Horan (2/27/2008)
Back Again! When I configure the SS Profiler (adding a Column Filter) to apply a filter to the Database ID as suggested do I enter the Database ID under 'Application Name' then set 'Like' to the ID (i.e. 8).
Thanks,
Phil.

Hello Phil,

Under the Application Name, it should be the application that is querying your database and not the database id. You should check the "Show all columns" before applying the filters. After you check that box, then you will be able to see the DatabaseID column filter where you can enter your database id value.

Hope this is clear.

Thanks

Lucky

2Tall SSChampion Points: 14632 More actions · Answer 12

Thanks, I will take a look (see if I can find it!!)

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper