Query CPU Performance
-
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!
-
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.
-
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.
-
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?
-
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 -
Morning all...
In general large sorts run ok on the server - I just ran a select statement sorted by 3 columns which returned ~800,000 rows in about 20 seconds - it is just this one query that appears to be a real problem so far, although there may be others.
The physical VM box is part of a new Citrix farm which is currently not under much load, so it should not be a problem in terms of hardware.
When I rebuilt the stats and indexes etc. I simply created a new Maintenance Plan to do them for me and ran them overnight. I'm not sure these are actually an issue though as when I restore the database onto a SQL 2005 server it runs fine straight away without any stats updates, index rebuilds etc.
Just tried changing the MAXDOP to 1 and unfortunately it just ran twice as slowly. I can't seem to get much useful information out of sys.dm_os_wait_stats (mainly because I've not used it before so I don't know what I'm looking at!)
-
hmm, ok so we know its not a problem with Parallelism, which is a good thing.
I take it you have rights to remote onto the server, if can you set up a set Perfmon on the server and add
From OS Disk & Misc counters : Phyisical Disk (per disk) : Avg Disk Sec/Read, and Avg Disk Sec/Write
From Network Interface : Bytes Total/Sec
What these should do is show if you have any problems with the Disks or Network IO being the bottleneck.
then run the query and let us know what the results are.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
I added those counters in to my usual perfmon collector set and ran the query again....here are the average values:
Physical Disk: Avg Disk sec/Read: 0.001
Physical Disk: Avg Disk sec/Write: 0.001
Network Interface : Bytes Total/Sec: ~4,800 (unaffected by query)
Others:
Processor: Processor Queue Length: 10.130
Processor: % Processor Time: 93.383
Physical Disk: Average Disk Queue Length: 0.014
Physical Disk: % Disk Time: 0.203
Memory: Available Bytes: 8gb+
Memory: Pages/sec: 0.707
As far as I can see the only counters that appear to be an issue relate to the CPU...
-
It looks like the problem isnt with the Disks as they are performing very well, with low disk ques and fast responses.
It may be worth checking with the guys responsible for the Citrix farm and see if they can check the server thats running this VS, to see if there are any issues, as unfortunately I'm out of ideas now.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Thanks Jason, appreciate all your time on this. I will indeed speak to the server admins regarding the Citrix farm....I also have the software suppliers on the case now so hopefully they will come up with something!!
I will let you know what the issue was when (if!) I manage to get it resolved!
Cheers!
-
no problem it will be nice to know what the problem was just for future reference.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Just heard from the supplier who says they have replicated the problem at their end moving from a SQL 2005 server to 2008R2.
They have said it appears to be an inherant problem with 2005/2008R2 compatability, and that something needs to be applied to the 2005 server before the database is copied (not entirely sure what yet but I believe it may be a hotfix or service pack, as it currently only has SP2). If that doesn't work they say scripting the data to the new server should resolve the issue too.
Progress 🙂
-
thanks for the update matt,
thats curious, im sure we have several SQL2005 databases running on R2 without any issues, so I'm curious to know what they're going to do on the 2005 box to change this.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Yeah I do too, although perhaps they were transferred from 2005 servers with later service packs on or something? Plus I still haven't found any other queries which run that slowly, so I assume it must be quite a specific problem. That query references a couple of other views too so the problem could maybe lie within one of those.
I'll let you know exactly what it was if I can find out!
-
1) Funny that you call this fairly simple. That is a beast of a query, with MANY joins, MANY CPU-burning operations (CASES, hash joins, aggregates,etc).
2) There is a CONVERT_IMPLICIT in there that will FUBAR the optimizer and burn CPU ticks
3) I don't have time to dig into the query plans, but they seem different. Less hashing in OLD server one? Maybe try forcing LOOP JOINS judiciously (or brute for all of them) with hints?
4) When you updated stats, did you do so with FULL SCAN? That is MANDATORY when upgrading a database on new version.
5) If you really want to get this spiffied up, hire a professional performance tuning consultant for an hour or two. I have seen people hunt and peck for days/weeks with something this complex when getting someone into their system for even a few minutes could pay huge dividends. You might pick up some tricks from him/her also!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply