High CPU, Multiple Apps - Which Profiler settings ?

Question

Post reply

High CPU, Multiple Apps - Which Profiler settings ?

homebrew01

SSC Guru

Points: 55548
More actions
September 13, 2007 at 11:58 am

#153823

We're running several different third party applications in support of our website, and they each have their own database all on the same SQL Server. The applications themselves are on other servers. Are there profiler settings I can use to determine which application's Database is using most of the CPU ?? Or will anything in Windows PerfMon be useful ??

Viewing 6 posts - 1 through 5 (of 5 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

Start by using the Performance Tuning Trace template, include DatabaseId filtering by Number of Reads and Duration.

* Noel

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

Thanks. I just started running a trace, and although I selected database name to display, that column is blank, although there is what looks like a good value in Database ID (based on sysdatabases). Any idea why the DB name would be blank ?

noeld SSC Guru Points: 96590 More actions · Answer 3

I recommended DatabaseID because most event support it. Database Name on the other hand is not supported by many event types. It is not difficult to tell which database is just by using "select db_name(ID)"

hth

* Noel

Paul-490154 SSC-Addicted Points: 487 More actions · Answer 4

I am experiencing the same issue with Database Name, as well as other relevant columns. We are an Active Directory shop and I want to trace usage on application and end-user (I believe columns to include start with NT__). I would also like to see the ApplicationName filled in appropriately, instead of the default ".Net SQLclient..." which is useless. I realize that's configurable on the SQL connection info in WEB.CONFIG (.net) or in the ODBC configuration, but getting the programmers to remember to set that is the real issue.

By the way, here's how to get a quick list of SQL DBID values for your entire server:

use master

select name,dbid from sysdatabases order by dbid

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

I found I was able to get Idera's Diagnostic Manager to connect to the remote server - I had been only using it locally. It gives me much more information, so I will use that instead