Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Measuring the weight of idle connections Expand / Collapse
Author
Message
Posted Wednesday, February 26, 2014 7:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 178, Visits: 547
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_NAME		AVG_VALUE
Available MBytes 1946.200000
Buffer cache hit ratio 99.862666
Page life expectancy 25938.066666
Transactions/sec 33.458000
User Connections 175.866666
Memory Grants Pending 0.000000
Batch Requests/sec 19.824000
SQL Compilations/sec 1.440000
SQL Re-Compilations/sec 0.040666
% Usage 8.102666
% Processor Time 5.738000
Processor Queue Length 0.000000





For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Post #1545374
Posted Friday, February 28, 2014 2:40 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:29 PM
Points: 236, Visits: 429
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.
Post #1546584
Posted Friday, February 28, 2014 2:55 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:23 PM
Points: 1,194, Visits: 2,209
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

Post #1546595
Posted Monday, March 3, 2014 1:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 178, Visits: 547
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!!




For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Post #1546810
Posted Monday, March 3, 2014 8:47 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:28 PM
Points: 33,062, Visits: 15,174
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







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1546956
Posted Monday, March 3, 2014 9:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:23 PM
Points: 1,194, Visits: 2,209
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

Post #1546982
Posted Tuesday, March 4, 2014 12:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 178, Visits: 547
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.




For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Post #1547183
Posted Tuesday, March 4, 2014 6:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 178, Visits: 547
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 )
I'll disable the SP step and see what happens.
I'll also run a trace.

Thanks for all the feedback and ideas guys!




For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Post #1547299
Posted Tuesday, March 4, 2014 8:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:23 PM
Points: 1,194, Visits: 2,209
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 )
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








Post #1547370
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse