Weird SQL Server performance problem

Posted on 2011-10-03
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?

Question by:Daniel Wilson
    LVL 18

    Accepted Solution

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

    Expert Comment

    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.
    LVL 32

    Author Comment

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

    Author Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now