keplan
asked on
SQL Server Performance
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
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
hi
maybe its just me but i don't see any attachments.
maybe its just me but i don't see any attachments.
I think keplan forget to upload the attachment I don't find too !
ASKER
ASKER
I've attached a screenshot of the PerfMonitor at the time the system is slow.
Please give us your input.
Please give us your input.
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?
----------
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?
----------
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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 ~