Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Surfer513
Starting Member
29 Posts |
 Posted - 2011-04-18 : 17:52:16
|
| I have a question and some troubleshooting theories. First off, there is an IS package running that is insert data into a table at a very slow rate. And the memory utilization for the machine is stagnant at less than 10%.What are the main culprits with this type of performance? Should I just run sp_configure with server memory to up that? Typically what are the gains when that is doubled? Tripled? Quadrupled?Any other things I should look out for? I'm blaming this primarily on memory, but please let me know if there are other possibilities that could be causing this. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Jahanzaib
Posting Yak Master
115 Posts |
Posted - 2011-04-18 : 18:11:34
|
| Execute your package and check this query select st.text,sp.* from sys.sysprocesses spcross apply sys.dm_exec_sql_text(sp.sql_handle) stSQL Server VersionTotal RAMTotal CPU physical or logicalSQL Server Total RAMOS version and 32bit or 64bitHow much indexes on a table which is going to insert data,how much data in it , clustered index was created on a this table Data fetching from txt file or csv file which is going to insert in a tableRegards,Syed Jahanzaib Bin HassanMCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBAMy Blogwww.aureus-salah.com |
 |
|
|
Surfer513
Starting Member
29 Posts |
Posted - 2011-04-18 : 19:14:42
|
quote:
Originally posted by tkizer
Why are you blaming this on memory? What evidence is there that it's a memory problem? Define slow rate. What method are you using to do the insert? What is SQL Server configured to use in regards to memory and how much do you have on the server?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
The reason I was first looking to memory was the extremely low utilization percentage (constantly below 10%). Even if it was a cumbersome execution plan, wouldn't you assume it would be more memory consumption?? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Surfer513
Starting Member
29 Posts |
Posted - 2011-04-18 : 20:40:48
|
| I'll have to double check on how many indexes are on the table with sp_helpindex. Would it be benefitial to have more than one??And SQL Server being a memory hog is what I was counting on, and that's why it baffles me to be running with less than 10% utilization. I'll also have to see what the max server memory is with sp_configure on the server. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-04-18 : 22:13:42
|
| The point of us asking how many indexes are on that table is that multiple indexes can hurt insert/update/delete performance. If you only have one, well then it's not a problem unless you are using a query for your insert. You never did answer how you are doing the insert, and we do need that to help.Yes please do check sp_configure. The default is unlimited and should always be lowered to allow the OS and other processes some memory. Typically 2+GB are left for the other stuff. On our 48GB databases servers, we have SQL configured to use 40GB of it.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
|
|
Integration Package Running Slowly