Solved

SQL Server 2kSP3a Memory problems

Posted on 2003-11-18
8
501 Views
Last Modified: 2011-09-20
It is a new server ~month old: 2.4Ghz Intel on Intel board, Hyper-threaded, 1GB DDR, RAID 10 SATA150 on Promise FastTrak S150 TX4, 4000MB Virtual Memory (swapfile, pagefile).

The performance monitoring shows two other interesting items.
Page Faults and Transitions Faults running parallel with peaks of 1500 within every minute.
Just trying to find some reference to what is the normal scale I found SBS Health Monitor’s default for Page Faults > 200 to trigger a warning.  Well I sure as hell got that beat!

My understanding of Faults: Transitions Faults is where it is the pagefile just in a different location than where it thought (originally) it was. Page Faults means it isn’t in memory at all.
To clarify on what I meant my parallel is that Page Faults is ~10% more than Transition Faults, following each other almost perfectly in the monitored view. Thus if Page Faults jumps up to 1500 then Transition Faults jump right along with it to 1400.
I think Page Faults includes Transitions Faults, so Actual (Hard) Page Faults are 90% less than what the monitor is showing. So now the question is why so many Transition Faults?
Hyper-threading Technology?
Is it normal for SQL to have peaks of 1500 Page Faults on a 50Mb, ~60k Record one to many (8 more tables) database server.


The good news is that I haven’t had a system crash, since I disabled Promise’s PAM services.

I wrote a script.vbs to restart SNMP (net stop service, net start service) and used scheduler it to run every 24 hours. Until I find out what is going on.

Do I need 2 GB or memory? Or what?

I’ll give a 1000 points to who ever gets this nightmare solved.
Via another 500pts Question just referring this one, if it doesn’t violate any EE rules.

Since it is running on a dedicated W2k SP4 Server
I also put it in Windows 2000 area,

Please see: http://www.experts-exchange.com/Operating_Systems/Win2000/Q_20802243.html
And http://www.experts-exchange.com/Operating_Systems/Win2000/Q_20787881.html


0
Comment
Question by:Suburb-Man
  • 4
  • 4
8 Comments
 
LVL 34

Expert Comment

by:arbert
Comment Utility
1gb of ram isn't all that much--you would be better with the most you can get.

Also, believe it or not, hyperthreading can actually SLOW things down on a Windows2000 server.  Microsoft really doesn't give any best practices, but they do tell you to run test with hyperthreading enabled and disabled to see which one better suits your needs.

Is SQL Server the only thing running on this machine?  How do you have the memory configured on the SQL Server?  Dynamic?
0
 
LVL 1

Author Comment

by:Suburb-Man
Comment Utility
Thanks for your prompt reply especially considering the complexity of my problem.

It is a Small Business Server (SBS) but I didn't install Exchange.
Terminal Services in Admin mode for Remote Admin.
IIS, WWW, RRAS services but there is nothing on them.
Of course it is a DC, DNS, AD, but I’m using SQL authentication on connections.
Cause clients are of a different domain, and SBS won’t allow Trusts.
Ave. 5 concurrent users, peak 15 with half being Read-Only Snapshot users.
We purchased SBS server is for SQL only.

Yes, Default Memory settings: Dynamically, Min. Query 1024k (would like to change to 512k, seems more reasonable.).
Processors Use all available, tried to set
“Boot SQL Server Priority” but it won’t keep setting.
I had to edit the registry just to get it to keep the SQL & Windows authentications.
It keeps changing my selections back to the defaults. I still don’t know why.
I use SQL Ent. Manager Locally logged on with Admin. Rights.

The only thing I’ve noticed different about W2k Server and SBS 2000 is the custom mmc that SBS comes with “SBS Admin and Personal console.

0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
NOOOO, don't choose Boost SQL Server priority--just compounds problems usually.  I don't know why MS puts the option there, 'cause the first thing they ask you on support calls is to disable it lol

So are you running DC, DNS, and AD on the same machine, or were you just saying that was your network setup?  Definately don't want to run DC on a sql box.  

If you're not using IIS, stop the service and don't let it start.

Do your configuration options "stick" if you run SP_configure from Query Analyzer?

Also, the min query isn't all that muchs--just 1024 per connection.  We actually bumped our setting up for better query performance (we're playing with 8gigs of ram though too :)  )

Brett
0
 
LVL 1

Author Comment

by:Suburb-Man
Comment Utility
SQL Boost is OFF. No worries.

SBS server demands it is a Stand-alone DC, and doesn’t DC require DNS and AD.
I do have DHCP disabled and I think it doesn’t like that either.
SBS won’t allow Trust Relationships.
I’m cool with domain less SQL Server, but I don’t know how to configure it.

There are only 10 Windows users configured in the domain, and only active during console sessions.  Which never happens, except by me usually after 5pm.
The other accounts are built-in and I’ve disabled 3 of them: Guest, IUSR, and Tsinternet.

I'll look into if SBS will allow an only SQL install.

I thought SQL authentication/security uses IIS?
Doesn’t replication pub/sub auth/security use IIS?
Doesn’t SQL and or ISA use IIS since SQL Clients/connections are not using windows auth/security, SQL Server’s?

Either way Terminal Services uses IIS and that is what I’m using for RA Admin.

The monitors show very little resources used by IIS, TS, DNS, or other Server processes.
SQL is the FAT HOG by far!!

I did get Query Analyzer to work:

USE master
EXEC sp_configure 'show advanced option', '1'

RECONFIGURE
EXEC sp_configure

USE master
EXEC sp_configure 'recovery interval', '3'
RECONFIGURE WITH OVERRIDE

Now the recovery interval is 3 (min).  I want to give it time before anybody logs back in, especially after a crash.

I’ll work on a proposal to Bus.Dept. to acquire more RAM.
Do you know of a good MS SQL doc supporting RAM requirements, so I can add to the proposal?

Here is a summary of the Server’s Status Report:

************************************************

Server Status Report
11/19/2003
11:30:03 AM

SBS SERVER Normal

Performance Counters

Memory - Available Bytes 383,414,272
Memory - Committed Bytes 691,310,592
Memory - Pool Nonpaged Bytes 27,590,656
Memory - Cache Faults/sec 23
Memory - Page Faults/sec 443
Memory - Page Reads/sec 1
Memory - Pages Input/sec 1
Memory - Pages/sec 1
Memory - % Free Memory 35
Processor - _Total % Processor Time 3
Processor - _Total Interrupts/sec 162
LogicalDisk - C: % Idle Time 99
LogicalDisk - D: % Idle Time 99
LogicalDisk - _Total % Free Space 78
LogicalDisk - _Total % Idle Time 99
PhysicalDisk - 0 C: D: Avg. Disk Bytes/Transfer 3503
PhysicalDisk - 0 C: D: Avg. Disk Queue Length 0.02
PhysicalDisk - 0 C: D: % Disk Time 1
System -  System Up Time 104033
Server Work Queues - 0 Queue Length 0
Server Work Queues - 1 Queue Length 0
Server Work Queues - Blocking Queue Queue Length 0

Service Status
            
Running        Auto        Alerter
Running        Auto        APC UPS Service
Running        Auto        Automatic Updates
Running        Auto        Computer Browser
Running        Auto        DHCP Client
Running        Auto        Diskeeper
Running        Auto        Distributed File System
Running        Auto        Distributed Link Tracking Client
Running        Auto        Distributed Link Tracking Server
Running        Auto        Distributed Transaction Coordinator
Running        Auto        DNS Client
Running        Auto        DNS Server
Running        Auto        Event Log
Running        Auto        File Replication Service
Running        Auto        FTP Publishing Service
Running        Auto        IIS Admin Service
Running        Auto        Intel Client Instrumentation for DMI
Running        Auto        Intel(R) Active Monitor
Running        Auto        Intel(R) NMS
Running        Auto        Intersite Messaging
Running        Auto        IPSEC Policy Agent
Running        Auto        Kerberos Key Distribution Center
Running        Auto        License Logging Service
Running        Auto        Logical Disk Manager
Running        Auto        Messenger
Running        Auto        Microsoft Firewall
Running        Auto        Microsoft H.323 Gatekeeper
Running        Auto        Microsoft ISA Server Control
Running        Auto        Microsoft Scheduled Cache Content Download
Running        Auto        Microsoft Search
Running        Auto        Microsoft Web Proxy
Running        Auto        MSSQLSERVER
Running        Auto        MSSQLServerOLAPService
Running        Auto        Net Logon
Running        Auto        Network Associates Alert Manager
Running        Auto        Network Associates McShield
Running        Auto        Network Associates Task Manager
Running        Auto        Network News Transport Protocol (NNTP)
Running        Auto        Plug and Play
Running        Auto        Print Spooler
Running        Auto        Protected Storage
Running        Auto        Remote Procedure Call (RPC)
Running        Auto        Remote Procedure Call (RPC) Locator
Running        Auto        Remote Registry Service
Running        Auto        Remote Storage Engine
Running        Auto        Remote Storage File
Running        Auto        Remote Storage Media
Running        Auto        Removable Storage
Running        Auto        Routing and Remote Access
Running        Auto        RunAs Service
Running        Auto        Security Accounts Manager
Running        Auto        Server
Running        Auto        Simple Mail Transport Protocol (SMTP)
Running        Auto        SNMP Service
Running        Auto        SQLSERVERAGENT
Running        Auto        System Event Notification
Running        Auto        Task Scheduler
Running        Auto        TCP/IP NetBIOS Helper Service
Running        Auto        Terminal Services
Running        Auto        Windows Management Instrumentation
Running        Auto        Windows Time
Running        Auto        Workstation
Running        Auto        World Wide Web Publishing Service

**************************************************

Again thanks for all your help.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 34

Expert Comment

by:arbert
Comment Utility
Terminal Services doesn't require IIS to work.  IIS isn't required for any kind of authentication to SQL Server.  IIS can be used for replication (depending on how you set it up--you can use FTP or not).

Here are some good links that have some memory references:


http://www.sql-server-performance.com/misc_tips.asp

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&safe=off&th=4a3794cfcf05bf57&rnum=1

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&safe=off&th=8191777cfccc80b&rnum=9

I also have SBS.....I'll take it out a bit and play with it to see how much of it you can scale down on the install.....
0
 
LVL 1

Author Comment

by:Suburb-Man
Comment Utility
Dude give me some fin....noggin....D U D E!
(my son's greatest compliment. from NEMO)

Thanks

I'm happy to turn IIS off, but doesn't TS need it? So I should uninstall TS too?
How about ISA and RAS?

I got some IIS logs with SQL references in them:
My workstation is 10.10.10.100 and the SBS/SQL server is 10.10.10.128

C:\Documents and Settings\support\Local Settings\Temporary Internet Files\SBSERVER-IIS Logs C_WINNTSystem32LogFilesW3SVC1ex031115.log

#Software: Microsoft Internet Information Services 5.0
#Version: 1.0
#Date: 2003-11-15 06:15:04
#Fields: date time c-ip cs-username s-ip s-port cs-method cs-uri-stem cs-uri-query sc-status cs(User-Agent)
2003-11-15 06:15:04 10.10.10.100 - 10.10.10.128 80 OPTIONS / - 200 Microsoft-WebDAV-MiniRedir/5.1.2600
2003-11-15 06:15:04 10.10.10.100 - 10.10.10.128 80 PROPFIND /sql - 404 Microsoft-WebDAV-MiniRedir/5.1.2600

I don't know what is logging here, I connect to 10.10.10.128 with RemoteDesktop(TSClient)
, SQL Connector in MSA2002 and SQL enterprise manager. But port 80? hmm.
0
 
LVL 34

Accepted Solution

by:
arbert earned 500 total points
Comment Utility
Nope, TS doesn't use IIS.  I'm running TS here without IIS.  If you're not using this machine as a proxy server, shut off ISA.  If you're not using it for dial in, disable RAS as well.

0
 
LVL 1

Author Comment

by:Suburb-Man
Comment Utility
Arbert please post something in:
http://www.experts-exchange.com/Operating_Systems/Win2000/Q_20787881.html
maybe pointing back to this? for another 500pts.

SNMP handle count growth troubleshooting?
http://www.experts-exchange.com/Operating_Systems/Win2000/Q_20802243.html
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

763 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

6 Experts available now in Live!

Get 1:1 Help Now