| Author |
Topic |
|
NotASQLDBA
Starting Member
5 Posts |
 Posted - 2004-09-29 : 18:19:35
|
| I have a dual Xeon system with 6Gigs of ram.. I have setup the /3GB /PAE switches and the AWE_Enbaled 1 switch. Yet task manager reports that SQLSRVR.EXE is only using 98 megs of ram. We migrated off of a Compaq DL380 that had 6 gigs of ram and it was faster than this one. If I look at CPU it is only running around 10% yet the server doesn't seem to process its work requests any faster and actually slower than before. Any ideas? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-09-29 : 21:11:28
|
| sql server will just take the memory it needs.Sounds like you may have disk io problems.98MB? That sounds low to do anything. Try it without the switches maybe. Which edition of sql server?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
pyeoh
Starting Member
18 Posts |
Posted - 2004-09-29 : 23:03:54
|
| I wouldn't rely on Task Manager. I believe it reports only the physical memory in use by the process. You might like to try out TaskInfo for more detailed process stats.Re performance, any changes in the disk subsystem? I would think this is more important than CPU and RAM, especially if the database is larger than your physical RAM.Peter Yeohhttp://www.yohz.comNeed smaller SQL2K backups? Use MiniSQLBackup Lite, free! |
|
|
|
NotASQLDBA
Starting Member
5 Posts |
Posted - 2004-09-30 : 10:28:55
|
| The server is on a 4 terabyte 2Gb SAN storage unit. The performance graph for the SAN shows minimal IO passing through the port.One thing of note is that I did see a high number under full scans/sec of about 8-30. Isn't that supposed to be in the 1-2 range?? Also there are an average of 15 soft page faults/sec. This server is incidentally in an active/passive cluster. We have another server almost identical that is standalone. It is also on the SAN and task manager says it is using 2.7 gigs of ram. And this server flys.. |
|
|
|
pyeoh
Starting Member
18 Posts |
Posted - 2004-09-30 : 21:46:54
|
| Usually, a server 'flys' if the data it needs to read are already in memory, and 'crawls' if it needs to read data from disks.Could there be another process contending with SQL Server for memory? You could try running DBCC MEMUSAGE(names, 50), use your applications as normal, then run DBCC MEMUSAGE periodically (especially when you feel the database is slow) to check if its data buffers are filled, or being cleared out somewhere.Peter Yeohhttp://www.yohz.comNeed smaller SQL2K backups? Use MiniSQLBackup Lite, free! |
|
|
|
NotASQLDBA
Starting Member
5 Posts |
Posted - 2004-10-01 : 02:17:18
|
| Does this look good..??Buffer Cache Top 20dbid objectid indexid Buffers Dirty ------ ----------- ------- ----------- ----------- 11 1733581214 0 136772 14811 228195863 2 94803 271611 148195578 35 67136 8711 148195578 0 23105 52311 148195578 2 17543 93011 228195863 0 17336 2217 478624748 0 16143 07 1886629764 0 15571 011 148195578 255 11501 29011 148195578 4 11342 87111 674101442 0 11082 011 502292849 0 10086 717 478624748 2 9103 07 1915153868 2 7945 011 1733581214 16 7931 17611 2055678371 0 7890 011 502292849 8 7640 65011 502292849 5 6761 70011 148195578 1 6279 447 1915153868 0 5371 0(20 row(s) affected)Total Buffer Pool Page Count : 553559Total Buffer Pool Dirty Count : 14856 |
|
|
|
pyeoh
Starting Member
18 Posts |
Posted - 2004-10-01 : 06:06:13
|
| Sure does. SQL Server's using 4+ Gig for it's data buffers(553559 * 8k). When you took these stats, was the server slow?Peter Yeohhttp://www.yohz.comNeed smaller SQL2K backups? Use MiniSQLBackup Lite, free! |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-10-01 : 11:04:59
|
| Going to interject a stupid question here. What versions of Windows and SQL Server are you running?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
NotASQLDBA
Starting Member
5 Posts |
Posted - 2004-10-05 : 10:15:08
|
| Yes.. Even at the time I took those readings the server continues to be slower than the old one. The server is running Windows 2000 Advanced and SQL 2000 Enterprise.. |
|
|
|
pyeoh
Starting Member
18 Posts |
Posted - 2004-10-05 : 11:28:51
|
| That bad, huh? I would try identifying a couple of queries that are slow running on this server, but running fast on the other server, and analyze the differences in detail, using the output from the execution plans, profiler, SET STATISTICS IO ON, SET STATISTICS TIME ON ... There has to be an element/elements somewhere that is making the difference. Could be hardware, could be software. Sorry I can't be of more help.Peter Yeohhttp://www.yohz.comNeed smaller SQL2K backups? Use MiniSQLBackup Lite, free! |
|
|
|
NotASQLDBA
Starting Member
5 Posts |
Posted - 2004-10-05 : 16:50:05
|
| Yeah.. we have a couple of theories.. It's on a SAN with 64 73 Gig drives in a Raid 5 and it should be a 0+1.The hyperthreading with with windows 2000 advanced is not that good.the Xeon processor was a bad choice.. So the question is, if you had the option to build a new head unit, what would you use?? P4, Xeon, AMD64?? 4Gigs, 6Gigs, 8gigs?? SAN or no SAN. Keep in mind that this database is 100 GIG and gets 1+ million row changes a day. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-10-05 : 17:38:16
|
| Hvae you run profiler and logged it to a table to see what queries are taking a long time?Have you looked at your Avg Disk Queue length on the drives where your SQL stuff goes to?Tell us more about your disk config. Do you have your transaction logs and data separated out on two different LUNs or RAID Groups?One million rows per day sounds like you might be a bit write heavy. The RAID 5 setup would be great for a read heavy system, but you'll take some serious hits on writes.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
Im lost