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

Poor performing query on Expand / Collapse
Author
Message
Posted Monday, June 17, 2013 12:16 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:40 AM
Points: 56, Visits: 442
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



Post #1464302
Posted Monday, June 17, 2013 12:23 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 23, 2014 8:04 AM
Points: 83, Visits: 174
Start by looking at the query plan. If possible, post the queries and plan in the thread.
Post #1464311
Posted Monday, June 17, 2013 12:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,062, Visits: 11,891
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/


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1464313
Posted Monday, June 17, 2013 12:30 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:40 AM
Points: 56, Visits: 442
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.
Post #1464317
Posted Monday, June 17, 2013 1:21 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:40 AM
Points: 56, Visits: 442
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')


  Post Attachments 
ExecPlans.zip (8 views, 149.31 KB)
Post #1464338
Posted Monday, June 17, 2013 1:39 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 23, 2014 8:04 AM
Points: 83, Visits: 174
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.

  Post Attachments 
plan.png (16 views, 15.04 KB)
Post #1464344
Posted Monday, June 17, 2013 1:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,062, Visits: 11,891
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1464355
Posted Monday, June 17, 2013 2:01 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 23, 2014 8:04 AM
Points: 83, Visits: 174
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.
Post #1464362
Posted Monday, June 17, 2013 2:21 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:40 AM
Points: 56, Visits: 442
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.

Post #1464369
Posted Monday, June 17, 2013 2:25 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:40 AM
Points: 56, Visits: 442
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.
Post #1464371
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse