Performance problem but memory available...

Hi,
I've some performance problems on a server.
I monitor these statistics:
Pages/sec
page faults/sec
available MBytes
the result is strange during small activities on the server: (min, avg, max)
Pages/sec = 0, 30, 217
page faults/sec = 18, 350, 4000
available MBytes = 290Mb, 300,Mb 308Mb
My instance of SQL Server used 545Mb (700Mb in VM Size) and my server has
1280Mb installed.
Opening the property page of SQL server to manage the memory could take 2 to
3 minutes before I can see anything!!!
I've the same problem with some client applications, a simple query can take
a long time, and another time (5minutes) the same query takes only 5 seconds
after a reboot.
If I restart my server, then all works fine, but when my SQL Server instance
start to consume more memory, then the performance slow down. I don't
understand why, because I've some memory available.
What can I monitor to identify the problem?
Thanks for your guides.
Jérôme.
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.581 / Virus Database: 368 - Release Date: 2004-02-09What is the processor utilization?... Sounds like you've got something
eating it all up!
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jéjé" <willgart@._A_hAotmail_A_.com> wrote in message
news:utqpL3V9DHA.1636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've some performance problems on a server.
> I monitor these statistics:
> Pages/sec
> page faults/sec
> available MBytes
> the result is strange during small activities on the server: (min, avg,
max)
> Pages/sec = 0, 30, 217
> page faults/sec = 18, 350, 4000
> available MBytes = 290Mb, 300,Mb 308Mb
> My instance of SQL Server used 545Mb (700Mb in VM Size) and my server has
> 1280Mb installed.
> Opening the property page of SQL server to manage the memory could take 2
to
> 3 minutes before I can see anything!!!
> I've the same problem with some client applications, a simple query can
take
> a long time, and another time (5minutes) the same query takes only 5
seconds
> after a reboot.
> If I restart my server, then all works fine, but when my SQL Server
instance
> start to consume more memory, then the performance slow down. I don't
> understand why, because I've some memory available.
> What can I monitor to identify the problem?
> Thanks for your guides.
> Jérôme.
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.581 / Virus Database: 368 - Release Date: 2004-02-09
>

Performance probelm with large table

I have a web page to allow user to create Sales Order. For this i have 2 tables, one for the informations (Sales Order) about the order and the other table (Sales Order LineItems ) contains the details of the order. For exemple for one order if i order 2 CD i will have 2 rows for this order in the Sales Order LineItems table.

When i've created the page i've made some test by creating 100 orders. Everything was working fine. Now yesterday i've tried to create 100 000 orders and 400 000 details rows. Since i 've created all thoses order the performance are not good at all. When i tried to open an order on my web page it can take up to 20sec juste to load the page. This make no sense at all since all my queries return only information for one order.
The problem is probably with SQL Server since when i've tried to open my webpage if i tried to launch another query everything is really slow. All my columns have Indexable properties set to yes.

Any idea ? I'm using ADO.NET and most of my Queries are inside StoredProc.

Thanks!

What is your table structure like?

Indexes?

|||

Can u share with us your table structure and if possible query u are trying to execute.

Basic Performance suggestion:

1. Use of index (smart use) No overuse

2. Keep ur statistics up to date.As in ur case u added so many rows it might have invalidated statistics on table.

3. Look for fragmentation in the tables. use dbcc showcontig to find out if any.

4. avoid using select * use column name in select

5. Use show plan to find out index(If u have any on the tables) is being used by SQL Server or not

6. Look for scans (Table scan or index scan) ..any case seek is better

so many others ..