MS-SQL Server is sometimes a dog sometimes not

[Guest_imported]

I'm running MS-SQL Server for a large data analysis project. The largest database is 90 gigs with 20 gigs remaining growth space. The tables contain static data - and we are running large queries against the data.

All of this is housed in a 8 processor Compaq with a terabyte of storage, and run under Windows 2000 Advanced Server.

Here is my problem - Sometimes I can run a simple query against 5 million records in about 2 minutes. Today I am running a simple query against 500,000 records and the query has been running for 2 hours - WHICH BY MY CALCULATIONS IS 360 TIMES SLOWER THAN NORMAL. What's the deal with SQL Server? I want it to sit down and shut up and not try to actively manage my data - just wait until I submit a query and run in as quickly as possible.

I have turned "auto start" off, I have turned "truncate logs at checkpoint" on. Is there anything else I should do to improve performance for what is essentially a data warehousing application? What does SQL Server do behind the scenes that could be bringing performance to it's knees?

 

[dbtech]

You need to investigate what causes the problem, u never mentioned your sql server version.

Some things u can try
1)U can try to set the database to read only, and turn it off when u do the updates to the database.
2)Use Query analyser to see the execution plan
3)Use Profiler to trace the query and locking.....
4)Use SET STATISTICS IO|ON|OFF to see the number of reads,scans ......
5)Use NT performance monitor
6)Search for KB, for multiprocessor related query behavior
7)check this white paper from microsoft

http://www.microsoft.com/sql/techinfo/development/70/qryproc.exe

good luck. It takes time and effort to tune a query, just having a big machine doesn't mean everything runs fast.

dbtech

 

[greinem]

are you querying against indexed fields?

 

[tlbroadbent]

Check the following articles.

http://support.microsoft.com/support/kb/articles/Q224/5/87.ASP

http://www.microsoft.com/technet/treeview/default.asp?url=/TechNet/prodtechnol/sql/plan/indexing.asp

http://www.swynk.com/friends/boyle/speed_select.asp

http://www.sql-server-performance.com/indexes_not_equal.asp

Find other performance articles at

http://www.sql-server-performance.com/articles.asp.

Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin