June 17, 2013 at 12:16 pm
Hi all, been pulling my hair out today. We have a set of HR databases that currently live on 3 different SQL servers. One is the test Server, one is the current live server, and one is soon to take the place of the live server.
We have a number of queries that the application runs, and discovered awful performance on the soon to be live server.
On the test server (sql server 2008r2 sql express SP2) the query returns in a few seconds, on the current live server (sql server 2008r2 standard edition SP1) the query returns in a few seconds, on the soon to be live server (sql server 2008r2 standard edition SP2) the same query takes 10 minutes!
The soon to be live server hosts multiple user databases (20 plus), has 12gb of RAM, 8 cores and is a virtual machine.
The live server has 2gb of RAM, 2 cores and is virtual, it only hosts the HR databases
the test server has 2gb of RAM 2 cores and is virtual (I will need to check this to verify) only hosts the HR dbs
Though the soon to be live server has multiple databases it is not under any significant load, and there is no memory pressure as far as I can see, we recently upped it from 8gb to 12gb thinking this was the cause but with no success. One thing I noticed that when the query is running, it does push the CPU up, with each CPU maxing out at 100% for a short period of time, then the cpu drops off on that core and moves onto the next.
The funny thing is, there are no waits associated with the query when I monitor it in activity monitor, normally I would expect to see pageiolatch, locks or something to give me a clue, but in this case nothing.
looking at sp_lock when the query runs there are a lot of sch-s locks, but they shouldn't be causing any problem should they?
Indexes have been rebuilt, and are tiny anyway, the tables are relatively small, none over 300mb I don't think (don't have the database in front of me at the moment).
The one query I have been focussing my efforts on uses a join with a couple of matching predicates, with 3 or 4 filter clauses, it is querying views that reference themselves. It is not the best db design, but the queries still return in no time on the test and live servers.
If you need any more info let me know, hopefully someone will have some ideas about this!
Thanks in advance
Rich
June 17, 2013 at 12:23 pm
Start by looking at the query plan. If possible, post the queries and plan in the thread.
June 17, 2013 at 12:26 pm
With no more details than this we don't have much chance of providing much help. The best we can do is take a shot in the dark. At the absolute very least you need to provide an actual execution plan.
Take a look at this article about posting performance problems. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 17, 2013 at 12:30 pm
Yes sorry, I am at home now so do not have the actual query or plan to hand. I will add it to the post when I log in later. interestingly the most expensive thing in the execution plan was 9% cost, and that was an index scan of an index that is 2mb.
June 17, 2013 at 1:21 pm
I have attached the execution plan from the slow performing server, and from one of the quicker performing servers. (the query plans for the 2 quicker performing servers live and test are exactly the same).
See the slightly modified query below:
SELECT "view_1_alias"."Title"
, "view_1_alias"."Surname"
, "view_1_alias"."Known As"
, "view_1_alias"."Gender"
, "view_1_alias"."Start Date"
, "view_1_alias"."Leaving Date"
, "view_1_alias"."Post Name"
, "view_1_alias"."Category"
, "view_1_alias"."Key Unit Name 1"
, "view_1_alias"."Cost To"
, "view_1_alias"."FTE"
, "view_2_alias"."Grade Name"
, "view_1_alias"."Payroll Name"
FROM "DB1"."Schema1"."VIEW_1" "view_1_alias"
INNER JOIN "DB1"."Schema2"."VIEW_2" "view_2_alias"
ON ((view_1_alias."Person Number"=view_2_alias."Person Number")
AND (view_1_alias."Appointment Number"=view_2_alias."Appointment Number"))
AND (view_1_alias."Career Number"=view_2_alias."Career Number")
WHERE (view_1_alias."Start Date"<{ts '2013-06-30 12:55:40'}
AND view_1_alias."Leaving Date" IS NULL
AND view_1_alias."Payroll Name"=N'Payroll'
OR view_1_alias."Start Date"<{ts '2013-06-30 12:55:40'}
AND view_1_alias."Leaving Date">={ts '2013-06-01 12:55:36'}
AND view_1_alias."Payroll Name"=N'Payroll')
June 17, 2013 at 1:39 pm
Are both the servers running on the same version of the SQL Server? Initial look at the plan shows that slow execution is taking lot of time to compile a plan and hence using lots and lots CPU. Look at the attached file.
June 17, 2013 at 1:53 pm
Also there appears some differences in the ddl. At the very least some of the indexes are different between the two plans.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 17, 2013 at 2:01 pm
Couple more things to check:
1. Statistics are updated or not. Update them with sp_Updatestats
2. How much data is there in this server compared to other server?
3. The SQL Server does not seem to re-use the plan, every time the query runs. You may need to look into that. One of the solutions for this is to turn Forced parameterization ON.
June 17, 2013 at 2:22 pm
They are running sql server 2008r2 standard edition, though the quicker performer is at sp1, the slower at sp2.
Yes it takes a very long time to compile, once I had run it in it once, I could rerun it quickly, but it doesn't seem to stay in the cache (need to verify this tomorrow), and this behaviour is different to the other server, takes no time for the initial compile (certainly not 10 minutes anyway).
The application seems to run these queries rather than them being wrapped in stored procedures, but the execution syntax should be the same each time.
I did update the stats earlier as I noticed the estimated rows was different to actual.
The DDL is pretty much the same (though there are a few extra views in live not relevant to the query), i ran sql compare to verify this earlier today. Indexes should be the same, if anything the slower of the two servers has less fragmentation as I rebuild the indexes today.
June 17, 2013 at 2:25 pm
The amount of data in the databases should be very similar, the database on the slower of the servers was last updated by an automatic job early this morning before business hours. so any difference in quantity of rows should be minimal, not a lot of data is loaded in the day.
June 18, 2013 at 5:06 am
mayurkb I switched parametrization to forced on the offending database, and now when I run the query (after it's initial 10 minute run into the cache), even if I change it slightly by adding/removing spaces it returns in seconds.
This is definitely a problem with the compilation of the query, but what would be causing this extra long compilation time, does compilation utilise CPU heavily? there are 8 cores on the server, but it is a virtual server, see any problems here?
The query takes seconds to compile on the test and current live servers.
Thanks for all your help in this guys, getting me on the right track i think.
Rich
June 18, 2013 at 7:11 am
Glad I could help.
We were also having some CPU issues with our virtual servers running on vSphere. We did not see those issues while running on hyperv.
Also, keep in mind this is a short term solution. For long term solution, you will have to tune your query by fixing query, views and indexes.
June 18, 2013 at 1:14 pm
update on this, restored the backups to another sql server with exactly the same OS and SQL server version and SP, and the query returns in seconds, even on the intial compile. So that's 3 servers handling the queries with ease, but the super awesome production server can't deal with it!
I can only think it's something in the configuration somewhere, Need to find why it is using a different execution plan on the slowly running server.
June 20, 2013 at 4:37 am
FYI, solved this by adding 7 extra tempdb files to match the 8 cores, then restarted the sql server. Now the query plans match exactly, and the query returns in no time.
July 9, 2013 at 1:16 pm
just to let you know the tempdbs didn't sort it, the restart sorted it temporarily but then the issue returned. Eventually tracked it down to trace flag T8780 being turned on. This trace flag increases the timeime the optimiser can search for the best plan before a timeout occurs. Because the query was so complex this was taking 10minutes and STILL timing out.
I turned the trace flag off and the query now runs in seconds, all be it with a sub optimal plan.
Just need to figure out what is turning the trace flag on.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply