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 ««1234»»»

Query CPU Performance Expand / Collapse
Author
Message
Posted Tuesday, October 30, 2012 9:12 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1378842
Posted Tuesday, October 30, 2012 9:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 1, 2014 2:51 AM
Points: 72, Visits: 227
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.
Post #1378845
Posted Tuesday, October 30, 2012 9:25 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
Are the boxes the same in terms of Memory, CPU's and disks?


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1378849
Posted Tuesday, October 30, 2012 9:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 1, 2014 2:51 AM
Points: 72, Visits: 227
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...!
Post #1378856
Posted Tuesday, October 30, 2012 9:50 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1378867
Posted Tuesday, October 30, 2012 10:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 1, 2014 2:51 AM
Points: 72, Visits: 227
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!



Post #1378873
Posted Tuesday, October 30, 2012 10:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:45 AM
Points: 275, Visits: 863
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.

Post #1378878
Posted Tuesday, October 30, 2012 11:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:45 AM
Points: 275, Visits: 863
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.



Post #1378899
Posted Tuesday, October 30, 2012 2:17 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:43 AM
Points: 875, Visits: 2,800
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
Post #1379017
Posted Wednesday, October 31, 2012 1:49 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1379176
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse