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:
What else should I be checking?
Thanks!
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!
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
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.
ASKER
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!
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!
ASKER
sp_whoisactive
combined with
sp_who2 to show blocked queries is pointing out some culprit queries ... I think.
combined with
sp_who2 to show blocked queries is pointing out some culprit queries ... I think.
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.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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.