February 8, 2011 at 8:01 am
Hi experts,
Please advise on where to sort out the issue I'm having. A few of our users reported that they were experiencing major slowdowns when they tried to load data into several SQL tables in different databases.
As this seems to be a "global" problem I assume that there's nothing to do with the queries, table indexes, etc but rather I should focus on hardware bottlenecks OR start a profiler trace at the time when the insert operations start to catch blocking. Would that make sense?
Please share your ideas. I appreciated your inputs.
__________________________
Allzu viel ist ungesund...
February 8, 2011 at 8:06 am
how many rows are you inserting? could be page splitting, triggers, etc
February 8, 2011 at 8:08 am
If you're looking to focus on system metrics, then why would you set up a trace? That's for tactical measures of the performance of individual queries. If you want to see what's occurring on the system, cpu, i/o, memory, you should be focusing on Performance Monitor counters.
"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
February 8, 2011 at 8:23 am
Thanks for both of you for taking the time to respond.
I would say thousands of rows. After all, page split occurs when there's a memory bottleneck among other things, right?
Yeah, I'm aware of that but for now I'm just thinking of reasons that might cause the problem and I believe that blocking could also be an issue along with the poor hardware performance. Please correct me if I'm wrong.
__________________________
Allzu viel ist ungesund...
February 8, 2011 at 9:00 am
It could be resource contention. I'd check the wait states to see what's causing things to slow down. Also you can monitor calls as they're running slow using sys.dm_exec_requests and see what they're waiting on as they sit there. That will at least point you in the right direction.
"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
February 8, 2011 at 9:24 am
Thank you - I'll look into that. In the meantime I figured that the memory cosumption of sql is extremely high and eats up all of the available memory from the OS. Looks like it's a known issue in case of 64 bit systems and some say setting a maximum memory value for sql might help to increase performance (currently it is unlimited).
Would the above apply in my case? If so, why is it recommended to restrict the memory cosumption of Sql Server? And why would that help to speed up queries? Thank you in advance.
__________________________
Allzu viel ist ungesund...
February 8, 2011 at 12:22 pm
Bungholio (2/8/2011)
Thank you - I'll look into that. In the meantime I figured that the memory cosumption of sql is extremely high and eats up all of the available memory from the OS. Looks like it's a known issue in case of 64 bit systems and some say setting a maximum memory value for sql might help to increase performance (currently it is unlimited).Would the above apply in my case? If so, why is it recommended to restrict the memory cosumption of Sql Server? And why would that help to speed up queries? Thank you in advance.
You want to put an upper limit on SQL Server just so that the OS has some memory too. Otherwise you get into a situation where SQL Server will take it all and then have to deallocate it for the OS. It's not the end of the world, but it will affect overall performance. Limiting it, allowing the OS to have memory that SQL Server can't touch, just makes it run a bit better.
As to whether or not you'll see performance enhancments in queries... doubtful. Possible, but unlikely. Unless you were regularly and often hitting a situation where queries are being slowed down because of memory allocation... it's unlikely.
"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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply