Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lebedev
Posting Yak Master
126 Posts |
 Posted - 2011-01-28 : 13:21:27
|
| Hello,We noticed that one query is running really slow on our production SQL Server 2008 database even though all indexes have been created following DTA recommendations and common sense.The query is shown below and takes 45 sec to run on our database. The execution plan is bad because it starts joining from the large tables COPY and PHYSICAL_COPY, which contain 4 million rows each.We tried updating statistics and rebuilding indexes - nothing worked.We also tried the following solutions:1. Running "DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;" sped up the query to run under 1 second and changed the execution plan to filter and join ZONE, ZONE_PROPERTY and MACHINE which have 10K rows each. I think the fact that this query can run fast means that we have all necessary indexes in place.The problem with this solution is that the query continued performing slowly for different values in NP_MACHINE.ID = 'a8b4b49d-eb65-4f87-9bea-fe19b63dd51d'. Moreover, it slowed down a different variation of this query that has "AND (ENTITY_GROUP.COMMIT_NUMBER > 80815311))" (it's commented out in the query below).2. Adding OPTION (HASH JOIN, MERGE JOIN) query hint sped up the original query and did not have the bad side-effects of the first solution.The drawback of this solution is that we need to change our application code to add the query hint. However, this would require and upgrade to our production app server, which is time-consuming.Could anybody recommend any optimization techniques that would help us optimize the query below for different NP_MACHINE.ID = 'a8b4b49d-eb65-4f87-9bea-fe19b63dd51d' values and the case when the query has commented out filter?Thanks,AlecSELECT DISTINCT TOP 200 COPY.ROOT_COMMIT_NUMBER AS COL0, COPY.ROOT_ID AS COL1 FROM PHYSICAL_COPY INNER JOIN COPY ON PHYSICAL_COPY.COPY_ID = COPY.ID INNER JOIN ZONE ON PHYSICAL_COPY.REPOSITORY_TYPE = ZONE.REPOSITORY_TYPE AND PHYSICAL_COPY.CANONICAL_LOCATION_CHECKSUM LIKE ZONE.CANONICAL_LOCATION_CHECKSUM + '%' AND PHYSICAL_COPY.CANONICAL_LOCATION LIKE ZONE.CANONICAL_LOCATION + '%' INNER JOIN ZONE_PROPERTY_VALUE ON ZONE.ID = ZONE_PROPERTY_VALUE.ZONE_ID INNER JOIN ENTITY_GROUP ON ZONE_PROPERTY_VALUE.VALUE = ENTITY_GROUP.ID INNER JOIN GROUP_USER ON ENTITY_GROUP.ID = GROUP_USER.GROUP_ID INNER JOIN NP_MACHINE ON GROUP_USER.USER_ID = NP_MACHINE.USER_ID WHERE (NP_MACHINE.ID = 'a8b4b49d-eb65-4f87-9bea-fe19b63dd51d') AND (ZONE_PROPERTY_VALUE.ZONE_PROPERTY_ID = '05a413c8-b85b-4791-a94c-31f23e018575') AND (ZONE.IS_MARKED_DELETED = 0) AND (ZONE_PROPERTY_VALUE.IS_MARKED_DELETED = 0) AND (ENTITY_GROUP.IS_MARKED_DELETED = 0) AND (((((((GROUP_USER.IS_MARKED_DELETED = 0) AND (COPY.ROOT_COMMIT_NUMBER <= 82170380)) AND (ZONE.COMMIT_NUMBER <= 82170380)) AND (ZONE_PROPERTY_VALUE.COMMIT_NUMBER <= 82170380)) --AND (ENTITY_GROUP.COMMIT_NUMBER > 80815311)) AND (ENTITY_GROUP.COMMIT_NUMBER <= 82170380))AND (GROUP_USER.COMMIT_NUMBER <= 80815311)))ORDER BY COPY.ROOT_COMMIT_NUMBER ASC--OPTION (HASH JOIN, MERGE JOIN) |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-01-28 : 14:35:38
|
Try to rearrange your table in record number order, and add "OPTION (FORCE ORDER)" at the end of query.Sometime it will make a huge performance gain.
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2011-01-28 : 16:39:24
|
| Thank you for the suggestion, Peso. We will try this out.As I mentioned in my original post we already have a query hint OPTION (HASH JOIN, MERGE JOIN) which makes the query and its minor alternative run fast. However, adding the hint will require application code change and application upgrade.We are looking for ways to optimize the query and its minor variation for different parameter values without having to change the query itself. Maybe set some database settings, maybe create certain statistics, etc.Does anybody have any suggestions?Thanks,Alec |
|
|
|
|
|
|
|
|
Poorly performing query