SQL Admin required for sub-contract

[Peon]

Hi

As the topic says. I have a client that has an old SQL database I need to be worked on. I have no idea about SQL and how to optimize it.

Regards,
Peon

 

[bin3]

You will need to find out more about the db I reckon: Big difference between maintaining a small db, probably ported from Access to MSSQL with a few tables vs. a clustered reporting services solution with triggers, views, stored-procs, etc, etc.

The one any DBA / person with some db experience can probably maintain, and if necessary, port to a different platform.

For the other, you need somebody that eats, drinks and sleeps SQL ...

 

[Peon]

Im looking for the latter. The DB is crap slow in operation.

 

[ToxicBunny]

How big are we talking?

What kind of reporting infrastructure does it supply?

 

[Peon]

Retail servicing 6 clients.

 

[ToxicBunny]

retail?

I can almost guarantee that I know why the stuff is dog slow... there is probably little to no "archiving" of inactive data.. which is making basic reads dog slow as well as writes.

 

[stricken]

retail?

I can almost guarantee that I know why the stuff is dog slow... there is probably little to no "archiving" of inactive data.. which is making basic reads dog slow as well as writes.

+1.

SELECT * FROM 5Gb TABLE WHERE id=5 AND name=""

 

[zippy]

retail?

I can almost guarantee that I know why the stuff is dog slow... there is probably little to no "archiving" of inactive data.. which is making basic reads dog slow as well as writes.

I can't see how this database can be "dog slow" because of no archiving. It doesnt sound like a big operation. 6 clients....

Probably just badly designed or tables where added with no thought to performance, or on crappy hardware.

I work on a db with large tables. Some contain more than 20 million rows. Use partitioning, parrallel queries etc. Granted, it isn't SQL Server, but I would have thought SQL server can handle large tables if properly designed and indexed.

 

[InterGalacticSpook]

Yo Peon,
You can do a little optimization yourself before you get into the meat and potatoes of SQL admin and dev.
Q: Is the SQL dB, logs and OS on one drive ?
A: Move dB and Logs onto their own drives, spread the IO

Q: Are the search fields indexed
A: start looking and the queries .i.e where and order by clauses and index on those columns

Q: Do you have rogue jobs
A: check the job activity monitor

Is SQL residing on a desktop or server and what other roles does the box have ...
Get to know the profiler and the admin utils

anyway dude, play nice, these are things you can do get a boost,

whoop whoop,
Igs

PS: I dont mind driving to Hartees for extra work

 

[Jabberwocky]

^^^
What he said


edit: Backup first!

check job activity
Don`t forget to prioritise sql to use multiple cpu`s (it has an option for that)
And check the version they are using, personal edition sucks a little

 

[bin3]

... Also: If it's MSSQL and you are lazy ...

Run a SQL trace for a while, work through the slow areas.
Save the trace and run index analyser on it.

Apply the indexes and get 200% performance increase ...

[Or, maybe not: but it's one way to quickly pick up if you need to make major changes, normalise better, etc, etc: or if it's just a matter of some basic new indexes on the correct fields]

[I have worked on MSSQL quite a few years ago, but even then MSSQL 95 / 97 / 2000+ could handle xx GB databases without major issues. I preferred Oracle when going over about 100GB, but even there, MSSQL with some love and care can job quite well]

 

[ToxicBunny]

All very good suggestions and I would suggest going through some of them on your own Peon.., but depending on the definition of 6 clients..

Is it 6 clients in the same shop, or 6 different shops? (that in itself is a huge distinction)....

As for it being dog slow due to table sizes, I spose it depends on dog slow... a frequent query that takes anything over 1second in my world is dog slow.... purely because I work very hard at keeping my query performances optimal, and i work in a hugely transactional world where transactional performance is key to absolutely EVERYTHING

 

[ToxicBunny]

I can't see how this database can be "dog slow" because of no archiving. It doesnt sound like a big operation. 6 clients....

Probably just badly designed or tables where added with no thought to performance, or on crappy hardware.

I work on a db with large tables. Some contain more than 20 million rows. Use partitioning, parrallel queries etc. Granted, it isn't SQL Server, but I would have thought SQL server can handle large tables if properly designed and indexed.

SQL can very easily handle 20million rows or more.. but like any relational DB, if you're using it for transactional queries then 20m rows will hurt beyond what is acceptable performance.

Yo Peon,
You can do a little optimization yourself before you get into the meat and potatoes of SQL admin and dev.
Q: Is the SQL dB, logs and OS on one drive ?
A: Move dB and Logs onto their own drives, spread the IO

Q: Are the search fields indexed
A: start looking and the queries .i.e where and order by clauses and index on those columns

Q: Do you have rogue jobs
A: check the job activity monitor

Is SQL residing on a desktop or server and what other roles does the box have ...
Get to know the profiler and the admin utils

anyway dude, play nice, these are things you can do get a boost,

whoop whoop,
Igs

PS: I dont mind driving to Hartees for extra work

All very good suggestions, and if you're in the area then easier to do the work than remotely, esp since it seems as if Peon would rather get someone who knows SQL in rather than trying to figure the stuff out for himself...

^^^
What he said


edit: Backup first!

check job activity
Don`t forget to prioritise sql to use multiple cpu`s (it has an option for that)
And check the version they are using, personal edition sucks a little

I am going to go on a flyer here and I guess that if its MS SQL that they're running Express Edition...

... Also: If it's MSSQL and you are lazy ...

Run a SQL trace for a while, work through the slow areas.
Save the trace and run index analyser on it.

Apply the indexes and get 200% performance increase ...

[Or, maybe not: but it's one way to quickly pick up if you need to make major changes, normalise better, etc, etc: or if it's just a matter of some basic new indexes on the correct fields]

[I have worked on MSSQL quite a few years ago, but even then MSSQL 95 / 97 / 2000+ could handle xx GB databases without major issues. I preferred Oracle when going over about 100GB, but even there, MSSQL with some love and care can job quite well]

Doing this is definitely a way of seeing if you need indexes, but be very careful when just randomly applying them, always pay attention to what the software is suggesting.

 

[Peon]

The thing is, I'm not keen on "learning" on a production server. I don't trust the backups either. Besides, I'm hoping to learn from the guru some things about SQL.

BOFH: Its 6 client PC's connecting to the server.

For example: Clicking on the "Logon" button in the app takes about 3 minutes to get a response. Not exaggerating. Its a good 2-3 minutes trying to logon. Requesting data can take about 3 seconds and is prone to hanging or stalling.

Thanks for the replies fellas. Will keep you all posted.

 

[Jabberwocky]

The thing is, I'm not keen on "learning" on a production server. I don't trust the backups either. Besides, I'm hoping to learn from the guru some things about SQL.

BOFH: Its 6 client PC's connecting to the server.

For example: Clicking on the "Logon" button in the app takes about 3 minutes to get a response. Not exaggerating. Its a good 2-3 minutes trying to logon. Requesting data can take about 3 seconds and is prone to hanging or stalling.

Thanks for the replies fellas. Will keep you all posted.

Taking another guess here... Is it perhaps an upscaled access 2003 database? have they since upgraded to 2007/10? do they run the front-end through access (ade/mde)?
Try getting the access 2003 runtime redistributable and open the front-end with that, see if it works faster. I`ve seen a similar problem.. where access 2007 wouldn`t play nice.

Kinda shoddy, if that is the case you can expect the data types to be wrong as well. (eg. int where tinyint would suffice)

 

[ToxicBunny]

Oh.. 6 clients connecting to a DB and its taking that long?

Thats absolute madness, there is something really piss poor about that DB.... and yeah, in that case there is a good chance its an "upscaled" access DB.

 

[Peon]

SQL Server 2000. DB and log files.