[SQL Server 2003] [Urgent] Performance issue

Question

[SQL Server 2003] [Urgent] Performance issue

sd 31430

SSC Rookie

Points: 46
More actions
October 13, 2011 at 7:40 am

#250077

Hello,
We are using SQL Server 2003 in our office.
And yes, it is clear to me, that there are newer SQL-Server Version
However...
Unfortunately, typical DQL SQL-Statements run very slowly.
SQL Statements are executed, though you could get a coffee in the meantime.
As an example, a typical table contains about 600.000 rows
I asked google and Voila....Here I am.
Is there a way, to increase the performance of the entire database with dbcc e.g. shrink tempdb or similiar ?
What about compressing the work tables ?
The hardware can not be changed, at the moment.
Thank you, in advance...
Hopefully,
Stephan

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Can you confirm which version of SQL Server you are using? There's no 2003 version.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

I'm available for a consult. If this is urgent, there's no way this can be done over a forum or by someone without experience.

I've done imrovement by as much as 90% overall speed improvement. And reports go down from 24 H to only a few seconds.

This obviously take some time to get there.

Contact me for more details.

sd 31430 SSC Rookie Points: 46 More actions · Answer 3

SQL Server 2000 SP4 Standard Edition - Version 8.00.2055

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Tune the code, tune the queries, there's no 'go faster' switch in SQL (shrink however is a very nice 'go slower' switch)

I wrote a 2-part article over at Simple Talk (for SQL 2005+) that I'm sure someone will have the link for (I don't right now), otherwise hire a consultant as it's not an easy job if you have no idea where to start.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

GilaMonster (10/13/2011)
Tune the code, tune the queries, there's no 'go faster' switch in SQL (shrink however is a very nice 'go slower' switch)
I wrote a 2-part article over at Simple Talk (for SQL 2005+) that I'm sure someone will have the link for (I don't right now), otherwise hire a consultant as it's not an easy job if you have no idea where to start.

We're on the same page here. I usually give those links out by default. But that seems way over his head from the limited info we have from him.

Add the fact that this is urgent which eliminates the time for the (very long) learning curve.

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2

sd 31430 SSC Rookie Points: 46 More actions · Answer 6

Ninja's_RGR'us (10/13/2011)
I'm available for a consult. If this is urgent, there's no way this can be done over a forum or by someone without experience.
've done imrovement by as much as 90% overall speed improvement. And reports go down from 24 H to only a few seconds.
This obviously take some time to get there.
Contact me for more details.

We already run some time with this lack of performance.

I would like to learn how to improve the performance step by step and I would be happy, if some one could be so generous help me gaining this information. 😉

Thank you for assistance, again.

😉 And of course, it is not SQL Server 2003. One point for you.

Basically, SQL Server 8 isnt faster, either.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

sd 31430 (10/13/2011)
We already run some time with this lack of performance.
I would like to learn how to improve the performance step by step and I would be happy, if some one could be so generous help me gaining this information. 😉

cool. Shouldn't take you more than a couple of years to learn.

After my articles, go buy Grant Frichey's book SQL Server query performance tuning distilled and Kalen Delaney's inside SQL Server 2005 Query tuning and optimisation.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

GilaMonster (10/13/2011)
sd 31430 (10/13/2011)
We already run some time with this lack of performance.
I would like to learn how to improve the performance step by step and I would be happy, if some one could be so generous help me gaining this information. 😉
cool. Shouldn't take you more than a couple of years to learn.
After my articles, go buy Grant Frichey's book SQL Server query performance tuning distilled and Kalen Delaney's inside SQL Server 2005 Query tuning and optimisation.

I have 10 000+ hours in tuning and yet I still learn almost everyday.

I applaud the thirst for knowledge. But you're not going from no experience to black belt overnight.

What I can do is tell you what I do with before / after picture and why to boost your learning. But that'll never replace 10K hours + many books.

sd 31430 SSC Rookie Points: 46 More actions · Answer 9

@Gila:

Thank you.

I had a quick view about your excellent article.You are quite cool.

I guess, you are right. It appears to me there isn't really an easy way to tune sql server.

I may come back to your offer, later.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

sd 31430 (10/13/2011)
@Gila:
Thank you.
I had a quick view about your excellent article.You are quite cool.
I guess, you are right. It appears to me there isn't really an easy way to tune sql server.
I may come back to your offer, later.

Just to ballpark it for you, I can probably get 80%-90% within a week working a few hours / day (I need time to see results in between interventions).

Unless you have a 500 IQ and speed read, there's just no way you can be done by next friday doing this.