MS SQL Server - I need justification.

[uberlad]

I turn to you guys, as the officially recognised source of all knowledge, for some justification at work.

We keep all our customer data in a database running on MS SQL server 7. It isn't huge, about 1.5GB and the largest table has about 600k rows.

We've been getting more and more timeouts recently when trying to do anything clever that involves this table.
I'm suggesting that we throw some money at the problem (because my dept. never gets the new shiny PCs) but need justification for upgrading the PII 400 with 512Mb RAM (only recently up from 256Mb!) to something that doesn't suck.

Am I right in saying that these timeouts are basically caused by the server not being able to suck up and work out all the data in the 30s or so that it has given itself to do so? Presumably, then, upgrading the machine will allow it to get and process the data much faster - reducing timeouts.

I also plan on trying to get actual training (gasp) in using and programming the damn thing as I'm sure that the code we're feeding it is terribly inneficient.

Can anyone point me towards any kind of resource that will help me either justify the new, streamline the old, or ideally both?

Thanks guys.

Uberlad

[Sasq]

you know, I actually have an MCDBA on MS Sql7. sadly last time I used it was getting the ticket - Strictly MySql these days. (Its free )

There should be a log regarding those timeouts. - It really depends on how busy the server is (and how badly written those queries are). There are many variables as to why its slow.
The machine itself, table structre - is it to split, not split enough, queries involved, processing data that you don't need to process etc etc.

1st Get the training on sql, second work out where the problem is. A newer machine might *solve* the problems, but it could very well just be a patch.

Dan

[uberlad]

Just what I expected anyone with an ounce of knowledge to say, thanks

I joined the department just after they got their training, and I don't think it covered optimisation of either requests or database setup.

Oh, and we aren't allowed to define any table joins on the server and have to ensure referential integrity of our own backs. It is not a good database, and the system at the front end is not much better.

I have a feeling that the setup that we have to have does somewhat cripple potential performance of the system. I shall be professional and not name the SOBs who have got many £'s from us over the last five years.

Uberlad

[Sasq]

Well, I'm now freelance programming.. I can give you a quote

But you can always query using joins. Not knowing the data and the schema I couldn't actually say what the best way around your problems are.

Dan

[uberlad]

I just get the nagging feeling that there is a world of internal optimisation that the server could be doing that we don't allow. Hopefuly HR will decide that there is a trainging budget this year and that we can hve some of it (you've gotta love the tech recession)

Trust me, you don't want to know anything about the data, or the schema. They're a mess and I'm reasonably sure that to understand them is to go mad.

Can you recommend any online (free) resources that I could read to at least know how ignorant I am?

Even knowing that would be start

Uberlad

[Sasq]

learn standard sql. you can get (or could get ) a sample sql7 (think I have a disk here) thats good for 120 days. (I'm sure you could find other ways as well)

As for online I'm not sure, I bought a monster SQL book that I use as reference.

Best way is to install SQL (mysql is good for this purpose, as it doesn't do stored commands, and doesn't deal with nested queries - well ver 3.x.x doesn't) load up something like phpMyAdmin and just play.

Work with real Data where you can, before I impliment anything on the forums here, I have a test MURC on my server that I test all DB mods on first. - it lets me see how long queries take, if there are too many queries to achive a set goal etc.

Practice and play. set your self tasks involving Joins (and damn they still screew with my head)

Dan

[uberlad]

I can do the basic SQL, but the DB bites me on the ass by timing out every time I try to do anything too clever with it. I'm normally reduced to pulling put much more data than I need and having my webserver apps do processing on it.

And of course I have generate massive cache tables to give me small enough data sets to do clever stuff on the last months data.

Whoops - this is turning less into an informed request for assistance with SQL server and more into a big rant about why sections of my job suck (money spent badly in the past).

Thanks for the suggestions.

Uberlad