February 26, 2014 at 7:01 am
We use a lot of in-house vb6 applications.
These applications open a connection to one or more databases when they start.
During off hours, one server has an average of 20 batches / sec, but 400 user connections.
This same server sporadically gets severity 17 alerts (There is insufficient system memory to run this query).
I am working with our VB6 developer to change this.
So far we have reduced the amount of idle connections by 80 and seen a reduction in the alerts.
Is it possible to see how much "system memory" is available?
Is it possible to relate a SPID to internal resources?
Weekday perfmon data during a 10 min period of high severity 17 alerts.
COUNTER_NAMEAVG_VALUE
Available MBytes1946.200000
Buffer cache hit ratio99.862666
Page life expectancy25938.066666
Transactions/sec33.458000
User Connections175.866666
Memory Grants Pending0.000000
Batch Requests/sec19.824000
SQL Compilations/sec1.440000
SQL Re-Compilations/sec0.040666
% Usage8.102666
% Processor Time5.738000
Processor Queue Length0.000000
February 28, 2014 at 2:40 pm
The number of connections does not look troublesome to me. I have had SQL 2005 server running with 200 - 300 connections with little to no problems (we have a few developers who routinely optimize by omitting the close connection step). Is this a 32 bit system? The memory pools used by SQL Server in a 32 bit system sometimes needed help. Especially if you were moving around a lot of BLOBs, or CLR objects.
February 28, 2014 at 2:55 pm
Is your application using Connection Pooling ? Try to check these counters too ..
SQLServer:Memory Manager: Total Server Memory (KB)
SQLServer:Memory Manager: Target Server Memory (KB)
Checkpoint pages per second
Lazy writes / sec
Memory Object: Pages/Sec
PhysicalDisk\Avg. Disk Sec/Read
PhysicalDisk\Avg. Disk Sec/Write
Did you capture any problematic queries that run during that time ? How abt your daily Maintenance Plans ? Are they running successfully ..
--
SQLBuddy
March 3, 2014 at 1:23 am
Is this a 32 bit system? The memory pools used by SQL Server in a 32 bit system sometimes needed help. Especially if you were moving around a lot of BLOBs, or CLR objects.
Yes, this is a 32 bits system, no BLOBs or CLR though. The errors occur on a server with build 9.0.3042 (SP2). I am looking to upgrade this server, but it's a 24/7 server with multiple DBs.
Is your application using Connection Pooling ?
Nope. VB6. Each appplication just has one connection. If multiple DBs are involved, one connection per DB.
Connections are opened when the applications start, closed when the applications close. Users run the applications in a Terminal Server 2003 32bit environment. Because these connections are open for so long, I was wondering what resources they are holding on to and if they are the cause of these errors.
Did you capture any problematic queries that run during that time ? How abt your daily Maintenance Plans ? Are they running successfully ..
There is a job that runs every 2 hours from 8:23 am till midnight. If it's this job that's causing the errors then it's unusual that the errors aren't happening all the time. Anyway, I shortened the schedule for the job to stop well before the errors start occuring. The other jobs that are failing are log backups.
I'll start collecting those other counters you mentioned.
Thanks for the help guys!!
March 3, 2014 at 8:47 am
I haven't typically seen connection issues taking many resources in SQL2K5+ instances. In SQL 7, it could be an issue, but usually in the 1000s of connections.
Does the app have retry logic? If so, you could kill some older, idle connections and potentially free space, but typically an idle connection isn't using much in the way of resources. The scheduler threads are re-used, and memory is minimal
http://msdn.microsoft.com/en-us/library/aa337559%28v=sql.90%29.aspx
March 3, 2014 at 9:22 am
Dennis Post (3/3/2014)
Is this a 32 bit system? The memory pools used by SQL Server in a 32 bit system sometimes needed help. Especially if you were moving around a lot of BLOBs, or CLR objects.
Yes, this is a 32 bits system, no BLOBs or CLR though. The errors occur on a server with build 9.0.3042 (SP2). I am looking to upgrade this server, but it's a 24/7 server with multiple DBs.
Is your application using Connection Pooling ?
Nope. VB6. Each appplication just has one connection. If multiple DBs are involved, one connection per DB.
Connections are opened when the applications start, closed when the applications close. Users run the applications in a Terminal Server 2003 32bit environment. Because these connections are open for so long, I was wondering what resources they are holding on to and if they are the cause of these errors.
Did you capture any problematic queries that run during that time ? How abt your daily Maintenance Plans ? Are they running successfully ..
There is a job that runs every 2 hours from 8:23 am till midnight. If it's this job that's causing the errors then it's unusual that the errors aren't happening all the time. Anyway, I shortened the schedule for the job to stop well before the errors start occuring. The other jobs that are failing are log backups.
I'll start collecting those other counters you mentioned.
Thanks for the help guys!!
Additionally ..
As you mentioned it's a 32-bit system, what are your memory configurations .. Total memory, AWE, PAE, Lock Pages in Memory, Min&Max Memory ..
Check your Index Fragmentation Levels and if you have outdated statistics ..
And run a server side trace to capture any problematic\long-running queries that are triggering these alerts..
--
SQLBuddy
March 4, 2014 at 12:53 am
Steve Jones - SSC Editor (3/3/2014)
I haven't typically seen connection issues taking many resources in SQL2K5+ instances. In SQL 7, it could be an issue, but usually in the 1000s of connections.Does the app have retry logic? If so, you could kill some older, idle connections and potentially free space, but typically an idle connection isn't using much in the way of resources. The scheduler threads are re-used, and memory is minimal
http://msdn.microsoft.com/en-us/library/aa337559%28v=sql.90%29.aspx
No the application doesn't have any retry logic. I'm working with the developer to address the many open idle connections, unfortunately it's slow going.
Thanks for the link, very useful.
March 4, 2014 at 6:40 am
As you mentioned it's a 32-bit system, what are your memory configurations .. Total memory, AWE, PAE, Lock Pages in Memory, Min&Max Memory ..
32Bits
16GB RAM
/PAE
AWE Enabled
Lock Pages in Memory = SQL Service Account (Domain account)
Min memory 0
Max memory 13310
This configuration is the same as the other 2 nodes in this FCI (Windows 2003 R2)
Check your Index Fragmentation Levels and if you have outdated statistics
I'm using Ola Hallengren's index optimizer. Runs every sunday. CommandLog states all is good. Just rechecked the index fragmentation, looks bad. I'll schedule my old defragmentation script. I think i'm using Ola's job incorrectly. All the commands being logged look correct. Maybe they are not being run.
And run a server side trace to capture any problematic\long-running queries that are triggering these alerts..
Think I found the culprit. That job I rescheduled to stop earlier. The errors came back when it restarted it's schedule. Strange that it only happens late at night.
Step 1 : Record table usage. (We have too many rogue tables floating around. When found renamed)
Step 2 : Record SP usage from sys.dm_exec_query_stats. (No sys.dm_exec_procedure_stats :crying:)
I'll disable the SP step and see what happens.
I'll also run a trace.
Thanks for all the feedback and ideas guys!
March 4, 2014 at 8:28 am
Dennis Post (3/4/2014)
As you mentioned it's a 32-bit system, what are your memory configurations .. Total memory, AWE, PAE, Lock Pages in Memory, Min&Max Memory ..
32Bits
16GB RAM
/PAE
AWE Enabled
Lock Pages in Memory = SQL Service Account (Domain account)
Min memory 0
Max memory 13310
This configuration is the same as the other 2 nodes in this FCI (Windows 2003 R2)
Check your Index Fragmentation Levels and if you have outdated statistics
I'm using Ola Hallengren's index optimizer. Runs every sunday. CommandLog states all is good. Just rechecked the index fragmentation, looks bad. I'll schedule my old defragmentation script. I think i'm using Ola's job incorrectly. All the commands being logged look correct. Maybe they are not being run.
And run a server side trace to capture any problematic\long-running queries that are triggering these alerts..
Think I found the culprit. That job I rescheduled to stop earlier. The errors came back when it restarted it's schedule. Strange that it only happens late at night.
Step 1 : Record table usage. (We have too many rogue tables floating around. When found renamed)
Step 2 : Record SP usage from sys.dm_exec_query_stats. (No sys.dm_exec_procedure_stats :crying:)
I'll disable the SP step and see what happens.
I'll also run a trace.
Thanks for all the feedback and ideas guys!
Memory Settings looks good.
Ola Script is an pretty good one. We use it on our PRD servers. Try to schedule an index reorg , update stats on nightly basis and index rebuild during the weekend. Index Fragmentation often leads to bad performance problems.
Sure. Also try to see if SQL Server is recommending any missing indexes that could improve the performance.
--
SQLBuddy
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply