Performence Problem

Question

Performence Problem

mohan.pariveda 18256

SSChasing Mays

Points: 657
More actions
April 24, 2011 at 8:03 am

#237039

Hi All,
I am new to the SQL Server tuning and facing a critical performence problem.
My environment : Windows 2003 R2 (32 bit OS), SQL 2005 ENT, 8 GB RAM , 8 Processors
AWE not enabled.
My Database size is 1.9 TB. (3 tables having 700 gb data in it)
Queries taking lot of time to finish...
TempDB is having 8 data files and having enough space in it.
Can any one please help me in improving the server performence...
🙁

Viewing 15 posts - 1 through 15 (of 15 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Performance tuning is a wide area. There are several books available...

Based on what you've posted so far, it's almost impossible to tell what'S wrong: from hardware issues to bad configured SQL Server to missing indexes to "suboptimal" written queries... the list goes on.

If it's a production system I recommend to get a tuning expert in for a few days. You'll learn a lot more from an experienced person showing you how to perform your system than you could learn just from a few posts here.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

okan.okay Newbie Points: 3 More actions · Answer 2

Check if tracing is on... It usually makes the system crawl...

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

That's a big area...

If it's critical and you have no idea where to start, consider getting someone in to help. It'll be far more effective in the long run.

Otherwise, this series may give you a place to start...

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/

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

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

That said, one glaring misconfiguration is a 32-bit OS and no AWE. That means SQL has at most 2GB of memory, for a DB nearly 2 TB in size. 8GB on the server's likely not enough either (my new desktop has 12 GB and I know several people with laptops that have 16GB of memory in them)

I would recommend that you consider moving to a 64-bit OS and SQL for a DB of that size, and adding significantly more memory as soon as possible.

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

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 5

If dont have enough resources and you cant buy a license of new OS then you should change these settings and requirement

1-Enable PAE on your OS level

2-Enable AWE on your SQL Server Level and give at least 6 GB to SQL Server

3-Partition your Tables into multiple file groups with partition scheme

4-Check Missing Indexes ?

5-Maintenance done ?

6-Is this Server only Database or other services are running like IIS or else ?

Regards,

Syed Jahanzaib Bin Hassan

MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog

http://www.aureus-salah.com

My Blog
www.aureus-salah.com

Grant Fritchey SSC Guru Points: 398713 More actions · Answer 6

okan.okay (4/24/2011)
Check if tracing is on... It usually makes the system crawl...

Based on what. A server-side trace is one of the best ways to collect performance metrics. As long as the trace is collecting appropriate events and columns, it puts a very minimal load on the system, well below 1%. If it's misconfigured, yeah, it can cause problems, but you can say that about anything.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398713 More actions · Answer 7

mohan.pariveda 18256 (4/24/2011)
Hi All,
I am new to the SQL Server tuning and facing a critical performence problem.
My environment : Windows 2003 R2 (32 bit OS), SQL 2005 ENT, 8 GB RAM , 8 Processors
AWE not enabled.
My Database size is 1.9 TB. (3 tables having 700 gb data in it)
Queries taking lot of time to finish...
TempDB is having 8 data files and having enough space in it.
Can any one please help me in improving the server performence...
🙁

If you already know which queries are running slow, get their execution plans and figure out what's causing the problem.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

mohan.pariveda 18256 (4/24/2011)
Hi All,
I am new to the SQL Server tuning and facing a critical performence problem.
My environment : Windows 2003 R2 (32 bit OS), SQL 2005 ENT, 8 GB RAM , 8 Processors
AWE not enabled.
My Database size is 1.9 TB. (3 tables having 700 gb data in it)
Queries taking lot of time to finish...
TempDB is having 8 data files and having enough space in it.
Can any one please help me in improving the server performence...
🙁

I CAN help you, but this is WAY beyond a forum issue. And even my magic probably won't give you acceptable performance on a 2TB database on such old and VASTLY underpowered hardware and OS. Even if you had SSDs as your storage you could have performance problems. With proper indexing, good schema, good code, simple OLTP workload without many concurrent users we could get some semblance of performance from your system.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

mohan.pariveda 18256 SSChasing Mays Points: 657 More actions · Answer 9

Thanks alot for the reply...!!

My current environment:

SQL (Standard edition- 9.00.3042.00), WINDOW NT 5.2 (3790), 8 GB RAM, 8 Processors

My database are alredy devided into multiple(9) filegroups on different disk drives and data files are on different file groups.

PAE switch is enabled in boot.ini file.

SQL Server is using all 8 processors & AWE is not enabled yet (it has to be enabled).

I have added multiple files to the TEMPDB, equal to the #processors.

One Database is having - 1.9TB size (3 tables are having arround 700GB of size) and Total DB size on the server arround 3 TB.

How can i improve the performence ?

If the table partitioning is your solution, please let me know how can i partition the existing 700GB table?

Please share your solutions which are very pretious to me.....

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

Errr... did you read any of the replies?

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

LutzM SSC Guru Points: 107049 More actions · Answer 11

Also, please make sure you're posting consistent information:

your first post: Windows 2003 R2 (32 bit OS), SQL 2005 ENT

your latest post: SQL (Standard edition- 9.00.3042.00), WINDOW NT 5.2 (3790)

Like I stated before: it is strongly recommended to get a consultant in for a few days...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sujitkmishra Mr or Mrs. 500 Points: 504 More actions · Answer 12

In your case, memory setting need to be revisited as stated by experts in the forum. even if you enable AWE 8 GB memory looks lesser for the size of database you have.

Whatever may be the cause of performance issue, it must manifest in Disk/Memory/Cpu or Network. Consider capturing the details using SQLDiag and post your analysis it in the forum.

bobnysoftz SSC Veteran Points: 294 More actions · Answer 13

Download the PAL and run on your machine. This will highlight any issues on your machine. Also check if u have enough disk space, memory and if there are any long running queries

Rick.Cornell.SME SSCrazy Eights Points: 8597 More actions · Answer 14

Rick.Cornell.SME

SSCrazy Eights

Points: 8597

April 26, 2011 at 12:13 pm

#1316097

You also might consider Partioning your tables too?