SQL Server 2kSP3a Memory problems

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

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?
Suburb-ManAuthor Commented:
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.

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 :)  )

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Suburb-ManAuthor Commented:
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'

EXEC sp_configure

USE master
EXEC sp_configure 'recovery interval', '3'

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:30:03 AM


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.
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:




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.....
Suburb-ManAuthor Commented:
Dude give me some fin....noggin....D U D E!
(my son's greatest compliment. from NEMO)


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 and the SBS/SQL server is

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 - 80 OPTIONS / - 200 Microsoft-WebDAV-MiniRedir/5.1.2600
2003-11-15 06:15:04 - 80 PROPFIND /sql - 404 Microsoft-WebDAV-MiniRedir/5.1.2600

I don't know what is logging here, I connect to with RemoteDesktop(TSClient)
, SQL Connector in MSA2002 and SQL enterprise manager. But port 80? hmm.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Suburb-ManAuthor Commented:
Arbert please post something in:
maybe pointing back to this? for another 500pts.

SNMP handle count growth troubleshooting?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.