SQL Server Performance Help

[SteveC]

Hi all,

Any SQL Database Admins round here? Some internet/intranet software we've written at my work is built around an SQL database - it's a kind of Knowledge Management tool. Anyway, one of the queries (a search) asks for data to be returned from a table currently containing over 37 million records. Well, this takes ages - it takes SQL 40 seconds just to count them! Getting on for 5 minutes or so to produce all the results.

I've been experimenting with load balancing and clustering the last few days and am quite impressed, but although this will be great for web server performance and database resilience, it's not going to help the database performance - all the load balancing stuff MS offer is basically geared towards sharing queries out, not sharing the work each query does. i.e. it's geared towards loads of small queries, not huge ones, or ones that take ages to respond.

So, what I'm asking is, how do I get SQL to perform faster? When doing the search function, the application sits there waiting for data to come back from the SQL Server, but the SQL Server's CPU is never going over 20-30% - how can I make SQL use all the available resources? The only option we've found is to change the worker threads (Which is great for lots of queries) and Boost NT Priority which hardly made a difference.

I'd understand it if the CPUs hit 100% - chuck a more powerful system at it.

The servers (IIS and SQL) are Dual Xeon 550Mhz (half meg cache), 1Gig RAM, RAID 5 - 5x18Gb UW SCSI, Running Win2K, SQL is version 2000.

Any ideas would be great!

[Sasq]

I don't know the data or the exact flavour of sql you are using, but index some of that table which columns are indexed depends on the data. but it will help speed dramitically

Dan

[cjolley]

Through away that toy & get Oracle.
That will solve your problem*.
chuck

*or, at least, then I could fix it for you

PPS It does sound like it's missing an index.

[Agent31]

Indexing the tables would be your best bet, though 37 million records is pretty big by any standard. You say the CPU isn't grindng away at 100%, but are the disks thrashing? Be sure that you've got indexing turned on!

I can tell you from experience that running a query that size via a web db connection is going to time out! If you get to the point where you're building your DB connection DLLs, be sure to set them to cache the results in IIS else you'll find your beautfully load-balanced servers all occupied running queries all the time!

[SteveC]

Sorry guys - been so busy with this all day I forgot to reply

Anyway, All scans were Index based - no full table scans.
And the RAID array's working nice and fast - no thrashing.

Also been using the Query Analyser the last few days and we know what's taking the bulk of the time, and we sorted it today - have a guess what it was:

[spoiler]The main search query was LOWER-ing the records in the table before comparing with the search string - LOWERing 527,000 records was taking about over 30 seconds! We worked our way round that and now that one query is near enough instant.[/spoiler]

Anyway, we still have some performance issues, but we <I>believe</I> it's our coding in the search DLL - we're comparing two tables independantly instead of getting SQL Server to do it. Early results tonight look good


Even so - any tips on optimizing caching? Although that won't help in this case as the search is real time on the data stored.

[Agent31]

sorry mate, my knowledge runs out with the SQL coding, or at most the IIS caching... good luck!