Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query CPU Performance


Query CPU Performance

Author
Message
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
Artoo22 (10/30/2012)
matt.gyton (10/30/2012)
Just a bit more info for you....

The database was being transferred from a SQL 2005 server to a SQL 2008 R2 server. I have now determined, after trying it out on several servers, that it appears to only be a problem when the database is restored onto the SQL 2008 R2 servers.

Don't know if that might give any clues?

Have you rebuilt indexes or updated stats?


Matt did say in his first post that he had rebuilt/the indexes and run an update stats.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
matt.gyton
matt.gyton
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 295
Indeed I have...it's a bit of a mystery at the moment, the sort is just killing the CPU. I have also checked the indexes are set up the same on both servers etc. and all appears to be in order. When I restored a copy on to another SQL 2005 server it worked fine first time without any index rebuilds or stats updates etc.
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
Are the boxes the same in terms of Memory, CPU's and disks?

_________________________________________________________________________
SSC Guide to Posting and Best Practices
matt.gyton
matt.gyton
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 295
Jason-299789 (10/30/2012)
Are the boxes the same in terms of Memory, CPU's and disks?

The new server is a virtual server whereas the old one is physical. In terms of hardware though the new box is much more powerful, with faster CPUs, more than twice the memory, high speed discs in the RAID arrays etc....essentially everything on it should be better, which is why this is even more surprising! The new server is also running Windows Server 2008 R2, whereas the old one is Server 2003.

Most queries are running very fast, this is the only one I've come accross so far which is causing this problem.

Appreciate all of your time guys...!
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
Thanks matt,

I take it that the SQL server is a 64bit box and doesnt have any throttling going on in terms of Max Degree of Parallelism, max memory usage.

How many processors does the box have?

Also that the Temp Db, source DB and logs are located on different physical disks rather than being on the same disk.

I personally cant see anything wrong with the query and if it works ok on other boxes, then it really can only be hardware configuration that is a problem.

Out of curiosity have you tried running the query with the Option(MAXDOP 1) to see if that helps, as i noticed a lot of parallelism going on in the plans and sometimes it can impact the query signficantly.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
matt.gyton
matt.gyton
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 295
Jason-299789 (10/30/2012)
Thanks matt,

I take it that the SQL server is a 64bit box and doesnt have any throttling going on in terms of Max Degree of Parallelism, max memory usage.

How many processors does the box have?

Also that the Temp Db, source DB and logs are located on different physical disks rather than being on the same disk.

I personally cant see anything wrong with the query and if it works ok on other boxes, then it really can only be hardware configuration that is a problem.

Out of curiosity have you tried running the query with the Option(MAXDOP 1) to see if that helps, as i noticed a lot of parallelism going on in the plans and sometimes it can impact the query signficantly.


Yes it's 64bit, has 32gb of memory (although only 20gb allocated to this instance) and the MAXDOP is set to 0/unlimited (same setting as the old server). The box has 2 virtual CPUs, which is the same as a different SQL 2005 box it ran fine on, so I don't think there is an issue there.

The only reason why I don't think it can be hardware related is because of how the query runs fine on SQL 2005 servers but not on any of our SQL 2008 R2 servers. This is true of both virtual and physical servers with varying amounts of resources, so it must be something to do with 2008 R2 I think....struggling to think what though!
arnipetursson
arnipetursson
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 1019
What is the memory configuration of the 2008 server.
In general, are there differences in the hardware and in the way the instances are configured.
I would look at memory and tempdb configuration (how big and how many data files) first.
arnipetursson
arnipetursson
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 1019
How do other memory intensive queries (large sorts) perform on the virtual server?
Is it possible that the physical host is overcommitted in terms of memory?

What may look to you as CPU churning through memory on your VM would actually be paging on the physical host.
Sean Pearce
Sean Pearce
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1170 Visits: 3432
matt.gyton (10/30/2012)
Indeed I have...it's a bit of a mystery at the moment, the sort is just killing the CPU. I have also checked the indexes are set up the same on both servers etc. and all appears to be in order. When I restored a copy on to another SQL 2005 server it worked fine first time without any index rebuilds or stats updates etc.

Did you rebuild ALL stats or run a normal maintenance procedure?



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
Sorry for the late response Matt, I cant see much wrong with the server set up in terms of memory etc, I'm not a Virutal server expert but I have seen a issues when the VS isnt configured properly, or when other VS's are under significant load on the same server.

I would try running the query to run with the OPTION(MAXDOP 1) to see if that helps the performance and brings it down to a more acceptable level, as its possible there could be too many parallel threads being run which causes a significant amount of internal blocking with CX_PACKET and PAGELATCH waits.

Also look at the DMV sys.dm_os_wait_stats that occur when the query runs and run the standard performance monitoring on the server, as well as the number of memory swaps between the physical and pagesys that are occuring to see if that is a possible cause, as I wouldnt expect to see that much activity if the server has 32GB of memory.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search