Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-02-18 : 08:33:47
|
| John writes "I have a database of 10GB size, with 3 large tables. The largest table has 885,950 rows and 3.5 GB size. I have a development server (Windows 2003 and SQL Server 2000) with 1GB RAM. Even a simple query on this big table requires 10 minutes to complete. After that another query takes a longer time. It is observed on Windows Task Manager that sqlserver service occupies about 700MB. If you stop this service and restart it, the memory occupied by this service is just 200 MB. Then the queries will be executed faster.How this memory unloading of the sqlserver service can be better handled?The same database is running rather smoothly on a Windows XP machine with just 512MB RAM. What is the problem with the server?Adding more memory may save us a lot. But what about partitioning the larger table?" |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-02-18 : 11:51:36
|
| And what about indexing tha table(s), and checking the execution plans to see what is taking so long?rockmoose |
 |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-02-18 : 12:28:14
|
| There's really a lot of things that could be causing your query to run slowly. I would do what rockmoose suggests and if that looks ok, then you may wish to consider other factors such as how SQL Server is using memory. Is it setup to use memory dynamically or did you define some type of ceiling? When comparing performance of the server with the XP machine you need to look at hardware differences closely. What is the disk configuration on the server? Are you using any RAID configurations? What is the L2 cache on both machines? You can also run perfmon and check the following counters.Processor: % Processor TimeSystem: Processor Queue LengthMemory: Pages/secSQL Server: Memory Manager: Total Server Memory(KB)SQL Server: Memory Manager: Target Server Memory(KB)SQL Server: Buffer Manager: Buffer Cache Hit RatioPhysical Disk: Current Disk Queue Length TotalPhysical Disk: % Disk Time TotalPhysical Disk: Access Methods: Page Splits/secThis will give you an idea of your server's health. There are a few other counters to consider, but this is a good start.Dave |
|
|
|
|
|
|
Server Memory