Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Deletes takes a long time, but CPU, Disk and RAM untroubled...why ? Expand / Collapse
Author
Message
Posted Tuesday, September 24, 2013 3:25 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:09 AM
Points: 331, Visits: 240
Hi all,

Running SQL 2012 BI on a spanky new server with 2690 x 2 CPU, fast SSD, ramdisk for tempdb and 128GB RAM. I have a problem finding a performance bottleneck, in essence.

Deleting records on a where condition is a the simplest example, but some select queries exhibit this behaviour too, for part of their runtime. Back to the delete example!

If CPU idles at 3 or 4% whilst the delete is running.
And RAM use is 13GB of 128GB
And Disk is idling at a few K per second

Why does this delete operation take minutes ? Why wouldn't SQL use more resources to get the job done faster ?

Regards, Greg.
Post #1497728
Posted Tuesday, September 24, 2013 3:41 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:13 AM
Points: 4,432, Visits: 4,167
Deleting records on a where condition is a the simplest example, but some select queries exhibit this behaviour too, for part of their runtime.


What kind of behaviour are you talking about exactly? How many records do you select/delete?

Lots of things can cause slow performance.
Without having more detailed informations we can only guess. Can be missing indexes, too many indexes, locking and blocking issues, triggers getting activates etc.

Oh, and btw do you really have 2690 x 2 CPU's ?


Markus Bohse
Post #1497733
Posted Tuesday, September 24, 2013 4:14 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:09 AM
Points: 331, Visits: 240
Hi Markus,

Yes, I see what you mean, but...

I'm running these query tests standalone so no other users in the way.

1) There are no triggers on the table
2) It's just 1 table
3) How could it be being blocked, I wonder ?

I suppose (regardless of the details of the query) I have a conceptual problem to get my head around - which is - why doesn't SQL use more of the resources on the server to do the job faster ?

All I can think of that might slow it down is some kind of locking.blocking - but I can't think where this might be coming from, when the behaviour is noted in a simple delete statement.



Post #1497751
Posted Tuesday, September 24, 2013 4:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
greg.bull (9/24/2013)
Why does this delete operation take minutes ? Why wouldn't SQL use more resources to get the job done faster ?
Can you post exec plan along with sql and table defintion .


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1497752
Posted Tuesday, September 24, 2013 6:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:20 AM
Points: 86, Visits: 515
What are the data types of the tsble, are there any large data types ie VARCHAR(MAX),TEXT etc.

What is the data size of the whole table.

Is it waiting to get exclusive access to the table

SELECT * from sys.sysprocesses where blocked <> 0.
Post #1497806
Posted Tuesday, September 24, 2013 8:37 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:02 PM
Points: 15,501, Visits: 27,887
Check sys.dm_exec_requests while the query is running to understand what is causing it to wait. That's the best method. Anything else is just guessing.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1497907
Posted Tuesday, September 24, 2013 9:43 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 4,319, Visits: 6,112
1) sp_whoisactive (sqlblog.com I think version 11.11 is latest)

2) track_waitstats_2005 (found in the sql server 2005 waits and queues white paper IIRC) to do a wait stats analysis

3) run a file IO stall analysis

Download my Tune Like a Guru presentation here: http://sqlsaturday.com/81/schedule.aspx. I have both file IO and waitstats stuff there. Every client I ever help out is taught to run those three things IMMEDIATELY and WITHOUT THOUGHT as the first response to a performance issue. One or more of them will point you in the right direction the vast majority of the time. I note they won't help you FIX a problem if you find it though!



Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1497950
Posted Tuesday, September 24, 2013 12:58 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
greg.bull (9/24/2013)

...
If CPU idles at 3 or 4% whilst the delete is running.
And RAM use is 13GB of 128GB
And Disk is idling at a few K per second
...


How many cores are there?


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1498020
Posted Tuesday, September 24, 2013 1:05 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:09 AM
Points: 331, Visits: 240
16 hyper threaded up to 32
Post #1498022
Posted Wednesday, September 25, 2013 5:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:09 AM
Points: 331, Visits: 240
All I can think of here then is that some tasks (ie, a delete, or an insert exec) cannot be parallelised, regardless of server settings. So, in effect the bottleneck I'm seeing is CPU. Just a hunch really as 32 cores / 3 or 4% utilistation is about 1 core...

Thanks to everyone who contributed.

Regards, Greg.
Post #1498255
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse