SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


help me in improving my server performance


help me in improving my server performance

Author
Message
smilewithrams-907484
smilewithrams-907484
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 49
Hi evryone,

i need your graat help by suggesting the solution to my problem. Im using my server for ETL process. we are expecting 30 - 35% increase in datavolume by the next month.

our ETL cycle starts from 10.30 and ends between 4.30 and 5.30, sla time is 6.30 (should not cross that). but wht worrying me is increase in data volume my increase this ETL load and report time. How can i improve my server performance.

we have implemented horizontal partioning of some facts and aggregates using partitioned views. performing insertions into underlying tables than inserting into views. using No lack table hint in select queries, updating statistics daily nad reindexing weekly

server :hp prolient 64 bit
sql server 2005 sp2
memory config :AWE enabled, total 16gb and available :1.12gb
virtual : 23.38 Gb available 8.55 Gb
pagefile 7.99 Gb
pagefile : c:\pagefile.sys

Database : divided onto files and filegroups mdf and 6ndf files.
all these files are stored on M:drive {san}

Review this partioning, indexes and server config and our ETL stratergy and suggest me the ebneficial measures
Nicholas Cain
Nicholas Cain
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3102 Visits: 6200
AWE should not be enabled on 64bit systems.

Does the server have 16GB of memory total? If there is only 1.2GB available you are choking the OS, that can seriously impact things that the ETL process can require.

Do you have the "Lock Pages in Memory" setting on for the service account that you are using?

You mention the virtual memory, you should not be paging at all, so this should be irrelevant, if you are seeing paging it is because you either have only 1.2GB for the OS, or it's paging out SQL processes in order to run OS processes.


Make a start with those things and if you get no improvements then you'll need to provide schemas, scripts and data examples.



Shamless self promotion - read my blog http://sirsql.net
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86879 Visits: 45263
Couple things...

Nolock is not a good thing to add everywhere. Are you aware of the problems it may cause and do you feel that the risks of bad data are acceptable?
What's SQL's max memory set to? From the sound of things, it should perhaps be lowered.
Are the log files also on M? If so, why?
What's the RAID level of the SAN drives and are they dedicated?

Is it 64 bit SQL? Enterprise edition? Standard edition? If enterprise, have you considered partitioned tables, rather than partitioned views?

As for advice, profile the ETL process, find the slowest portion and then look at optimising that. Looking at the entire thing in one go is not a good idea. You need to identify the pain points and fix those, then find the next most problematic point and fix that, etc, etc

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


ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7841 Visits: 7145
In addition to the other suggestions, make sure that:

1) Join columns are indexed

2) Indexes have the proper freespace and are reorganized / rebuilt as often as needed to keep them functioning well

3) Queries do not do functions on table columns unless absolutely necessary, esp. on indexed columns. For example, instead of:
WHERE YEAR(datecolumn) = 2008 AND MONTH(columnname) = 2
write:
WHERE datecolumn >= '20080201' AND datecolumn < '20080301'

4) Tempdb has at least one data file per processor core

Since you have only one SAN drive, you cannot be using RAID 1 for logs and RAID 5/10 for data. This will affect your performance as well.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85450 Visits: 41078
Stop using SSIS for ETL. Use BULK INSERT and some good T-SQL programming, instead.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Andrew Gothard-467944
Andrew Gothard-467944
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 2357
Don't use Table or View as your data access method when loading or transferring data. This apparently uses a cursor behind the scenes, and as a result kills your ETL. Or at least means it runs forever. Rather than use table or view, on say vwMyETLSource you are actually better off using SQL Command for data access method with SELECT * FROM vwMyETLSource.


Goes against the grain I know
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search