performance

Hi all,
I have strange problem.
I have query (where I have to use cursors, functions, subqueries, variables
of type table) and it runs aprox 7 seconds on new built and filled database.
New built and filled database means :
- create tables
- load data
- create constrains, indexes, views, procedures, functions
After restart of SQL server query runs longer (aprox 11 seconds) and it
stays on 11 seconds all the time.
Also it is very strange that on server with more memory, SCSI disks it runs
slower than on my local workstation.
Any ideas ?
Regards,
janHello Jan,
It is possible that your pages have been flushed from the buffer pool on the
server, if there are lots of other queries going on. A way to test the actual
performance is to flush the cache on both machines before running the query.
CAUTION: This will cause a performance degradation to users on both machines
as SQL Server will need to get all flushed pages from disk.
Use:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Then run your query.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Jan Hruz" wrote:
> Hi all,
> I have strange problem.
> I have query (where I have to use cursors, functions, subqueries, variables
> of type table) and it runs aprox 7 seconds on new built and filled database.
> New built and filled database means :
> - create tables
> - load data
> - create constrains, indexes, views, procedures, functions
> After restart of SQL server query runs longer (aprox 11 seconds) and it
> stays on 11 seconds all the time.
> Also it is very strange that on server with more memory, SCSI disks it runs
> slower than on my local workstation.
> Any ideas ?
> Regards,
> jan
>
>