Solved

SQL Server Performance

Posted on 2013-02-05
10
146 Views
Last Modified: 2016-02-18
Hi,

I'm building a Datawarehouse project with SSIS and SSAS.
I've issue with my packages nd query running really slow as I'm quering 2.5 billions of rows with clustered index on the table.

I've attached performance monitor screenshot at the time of my CUBE is processing data.

Can anyone tell me the issue on the server perfromance by looking at Performance monitor.
I dont have much experience on SQL performanc monitoring and measering.

Thanks in advance
0
Comment
Question by:keplan
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 4

Expert Comment

by:jjjosef
ID: 38858366
You should check the performance of the SQL server and try to improve your server performance :-

Check theCPU usage while running SQL SERVER
Check memory usage by particular query is running
Defragment you database
If in SQL server any object or file  is taking large space switch it into a new filegroup

Partitioning of the table &  the database if require

These operations  would be helpful in order to improve your SQL Server Performance
Also refer the following article for SQL server performance issue :-

Five Things That Fix Bad SQL Server Performance

~ Edit by Modulus_Twelve - This post contains unattributed content from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=181985 ~
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 38858567
hi

maybe its just me but i don't see any attachments.
0
 
LVL 4

Expert Comment

by:jjjosef
ID: 38858600
I think keplan forget to upload the attachment I don't find too !
0
 

Author Comment

by:keplan
ID: 38866575
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:keplan
ID: 38870303
I've attached a screenshot of the PerfMonitor at the time  the system is slow.
Please give us your input.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38874594
keplan,
as per the  attached picture - your server is chocking
----------------------------------------------------------------------------------------

< query running really slow as I'm quering 2.5 billions of rows with clustered index on the table.
>
please clarify  "really slow"
did it run fast before?


----
are you using "pump" from table or using t-sql in your SSIS?
if query: is it AD-HOC or stor proc (it would be better vs. Ad-Hoc )

 how fast does it run without SSIS?

you probably need to optimize this t-sql (add indexes; run DB maints: such as update start;reindex)


also you may need to add more memory \CPU to Sql server and SSAS box(s)

BTW: sql server and SSAS on the same box or different servers? if "different" - are they in the same DC or remote?

----------
0
 

Author Comment

by:keplan
ID: 38877984
Hi EugeneZ

I'm reading a single table with 2.3 billions record, I've one clustered index on the table.
Does it best practice to add more index to this table? The Cluster index is a composite key.

and also Table has been partition into weeks

It has not run faster, my requirment to run this faster

I used TSQL script not a Stored proc. Does it make any difference to ths process.



All the component, SSAS and SSIS and SQL server running on the same server.
But, I'm deploying to another SSAS server.

Yes, it is same AD.

Please give me some addtional help, thank you.
Could you please let me know how to optimize the query or DB or Table? I nevere done this, so keen to learn.
0
 
LVL 42

Accepted Solution

by:
EugeneZ earned 500 total points
ID: 38878512
procs  are faster  than AD-hoc version of the same code ...

are you using some "where"  criteria in you query? if 'yes' - do you have index for this?
how many columns are included? do you need all?

it is no easy to optimize without seeing table structure and query it self ...

you many need to load the data in smaller batches maybe using "weekly partitions"


--

you need to review indexing

check the post for ideas:

stackoverflow.com/questions/337744/whats-your-approach-for-optimizing-large-tables-1m-rows-on-sql-server


Planning Guidelines for Partitioned Tables and Indexes
http://msdn.microsoft.com/en-us/library/ms180767(v=sql.105).aspx
0
 

Author Comment

by:keplan
ID: 39758952
I've requested that this question be closed as follows:

Accepted answer: 167 points for EugeneZ's comment #a38878512
Assisted answer: 167 points for jjjosef's comment #a38858366
Assisted answer: 166 points for EugeneZ's comment #a38874594
Assisted answer: 0 points for keplan's comment #a38877984

for the following reason:

d
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
What should be a storage size for SQL in day1, day2 and day 3 7 78
SQL 2000:  Truncate Transaction log 10 21
SQL 2008 Conversion failed 7 20
Sql query 107 22
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now