Avatar of Daniel Wilson
Daniel WilsonFlag for United States of America

asked on 

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?

Microsoft SQL Server 2005Windows Server 2008Microsoft SQL Server

Avatar of undefined
Last Comment
Daniel Wilson
Avatar of sventhan
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of tlovie

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.
Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image


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!
Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image


combined with
sp_who2 to show blocked queries is pointing out some culprit queries ... I think.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo