faster queries in sql 2000

[cornercuttin]

i am using a database that is hosted using sql 2000 (i think), and it is on a box that is from the same year or a year later, so the hardware and software are about 6 or 7 years old.

one of the tables in the database is about 6.5 gigs in size, and it is extremely slow to perform queries on. this box holds 9 databases, and all are being hit at random times via network inserts, stored procedures and network requests.

it is running extremely slow. the table that i am interested in holds about 9 million records, and it only gets cleared out 1 time a year when it is moved to a backup.

it seems as though a simple count(*) statement takes around 5 to 8 minutes to execute. to me, this is way too long.

is there any tricks to speeding things up? im not an ms sql guy (experience in postgre and mysql), so i dont know if there is any kind of indexing or anything i can turn on to get things cached or anything.

anyone have any tips?

 

[mrdenny]

What indexes are setup on the table?

What does the execution plan show?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[cornercuttin]

hmm. i dont think there are any indexes set up. i didnt set up this database, so i dont know the eact sql used to create the table, but when i double click the table in the enterprise manager, it doesnt say anything about indexes.

also, what do you mean by execution plan? im not sure what to say here.


thanks for the response man.

 

[gmmastros]

In Enterprise manager, right click on the table, then click All Tasks -> Manage Indexes. This will show you what indexes are set up on the table.

To see the execution plan, load a query in to Query Analyzer. Press CTRL-K and then run the query. At the bottom of the window, you'll see an execution plan tab.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

Start with reading in Books online about indexes. MOre than likely your problem is that the database needs the correct indexes set.

The execution plan is something you can see in Query analyzer; it shows how the database will execute the query. YOu want to avoid doing table scans for instance. It can get complicated, so I suggest doing some reading about how to read an execution plan as well.

Questions about posting. See faq183-874

 

[cornercuttin]

ok. so i looked at the indexes. this table has 1 index, and it indexes 7 of the 60 or so columns in this table. it is non-clustered.

the type of query i want to run is a count(*) query. it has 4 rows in the "where" part of the query, and 3 of the 4 are indexed.

i performed one of the count queries, and the execution plan was as follows: select - 0%, compute scalar - 0%, stream aggregate - 0%, parallelism - 0%, filter - 0%, boorkmark lookup - 18%, and master.pk_sitedata(the index scan) - 82%.

i will look at creating an index tuple for the exact rows i need. i will need to get an ok from the dbadmin before i do this tho.

thank you so much for the help. i really appreciate it.

 

[mrdenny]

By indexing on the columns that you use in the where command you should greatly improve your performance.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLBill]

First thing....you say you 'think' it is SQL Server 2000. Run this code and let us know the results:

SELECT ServerProperty('ProductVersion'),
       ServerProperty('ProductLevel'),
       ServerProperty('Edition')

If it returns 8.00.xxxx, it is SQL Server 2000. If the ProductLevel returns RTM, SP1, or SP2...you need to get it upgraded to SP3a or SP4 now.

-SQLBill

Posting advice: FAQ481-4875