Weird SQL Server performance problem

A customer is running SQL Server 2005 Standard edition on a server with Windows 2008, 16 cores (I think it's 4 quad-core CPU's), and 16 GB of RAM.

A lot of queries are running slowly.  I've identified a handful that are timing out just after the 30-second mark.

The weird thing is that, when I run the same queries via SSMS, they run in 0 -7 seconds each ... depending on the query and circumstances.

But run by the application, they time out repeatedly ... one of them almost every time.

I've tried some additional query & index tuning, but a lot of that has been done before.  I got marginal improvements for my efforts last week.

What, at a system or SQL configuration level, should I look at?

I've already checked:
The disk queue counters - they never went above 2 while I was watching them.
 
Processor Affinity -- all 16 are available to SQL Server
CPU load - I never see more than 2 CPU's spike, and then only a few seconds
RAM usage - only 6.5 GB of 16 are in use ... only 3.5 GB in use by SQL Server

What else should I be checking?

Thanks!
LVL 32
Daniel WilsonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sventhanCommented:
download this tool sp_whoisactive and see what's happening when you see the slow performance...

http://sqlblog.com/blogs/adam_machanic/archive/2011/04/27/who-is-active-v11-00-a-month-of-activity-monitoring-part-27-of-30.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tlovieCommented:
probably when you run the queries in SSMS, you are not hitting the same cached execution plans since are likely using different default settings.  SSMS uses ARITHABORT ON, but .NET defaults to ARITHABORT OFF.

I would try setting ARITHABORT OFF in SSMS, then your performance should match more closely.  I would suspect you are seeing an artifact of Query Plan variable sniffing.

Hope this helps.
0
Daniel WilsonAuthor Commented:
Following
SET ARITHABORT OFF
4 problem queries came back in a combined 1 second ... complete with execution plans.

I'll try the Who is Active thing next ... but I did find something interesting.

I think the customer is running SQL Server 32-bit, though Windows 64-bit is running and they have 16 GB of RAM.

Could that explain why SQL Server is using only 3,598,940 KB of RAM, leaving over 9 GB free on the system?

Thanks for the help!
0
Daniel WilsonAuthor Commented:
sp_whoisactive
combined with
sp_who2 to show blocked queries is pointing out some culprit queries ... I think.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.