Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Weird SQL Server performance problem

Posted on 2011-10-03
4
Medium Priority
?
217 Views
Last Modified: 2012-06-22
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!
0
Comment
Question by:Daniel Wilson
  • 2
4 Comments
 
LVL 18

Accepted Solution

by:
sventhan earned 2000 total points
ID: 36904136
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
 
LVL 7

Expert Comment

by:tlovie
ID: 36904150
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
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 36904605
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
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 36905812
sp_whoisactive
combined with
sp_who2 to show blocked queries is pointing out some culprit queries ... I think.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question