March 7, 2012 at 12:16 pm
I was asked to find out why a update is taking longer on a new (upgraded) server
Old server (windows 2003 std / SQL 2000 SP4 std) 4 CPU, 2 gb's RAM (database data file on a single drive / log on separate drive)
New server (windows 2008 R2 / SQL 2008 x64 Enterprise) 4 CPU 32 gb's RAM (database data file split across 8 drives / log on separate drive)
For the upgrade the database was rebuilt to a multi file database and the data put back in the new database for performance considerations.
The DB is 120 gb's in size.
The tables in use:
-- sp_spaceused 'PS_BN_SNAP_PLAN'--186.000
-- sp_spaceused 'PS_PAY_CHECK' -- 5.260.526
-- sp_spaceused 'PS_PAY_DEDUCTION'-- 37.841.300
-- sp_spaceused 'PS_BN_SNAP_JOB'-- 45.000
This is the query as it stands...I formatted it for easier reading from what I received it as.
The query takes over 30 minutes to complete on the new system...looked at the estimated execution plan shows index scans.
After reviewing the query I started to try to pull the main update query apart so I could format it as a straight inner join and use a derived table.
The initial execution plan showed index scans with parallelism.
In testing, I got three of the 4 tables down to index seeks but the parallelism remains and the performance was very slow.
I started over, removing my own indexes and allowed SSMS to recommend any indexes which it did.
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[PS_PAY_DEDUCTION] ([OFF_CYCLE],[PAY_END_DT],[DED_CLASS],[DED_CUR])
INCLUDE ([COMPANY],[PAYGROUP],[PAGE_NUM],[LINE_NUM],[SEPCHK],[BENEFIT_RCD_NBR],[PLAN_TYPE],[BENEFIT_PLAN])
One of the indexes I created matched the above...but I didn't have it broken out with the INCLUDE statement
Looking at this index I have no idea how it decided on the specific columns to include (in regards to the order and which are in the NC index vs the INCLUDE).
If there is a piece of info I left out let me know...
UPDATE PS_BN_SNAP_PLAN
SET PS_BN_SNAP_PLAN.PAY_END_DT =
(
SELECT MAX(D.PAY_END_DT)
FROM PS_PAY_CHECK C,
PS_PAY_DEDUCTION D,
PS_BN_SNAP_JOB J
WHERE D.COMPANY = C.COMPANYAND
D.PAYGROUP = C.PAYGROUPAND
D.PAY_END_DT = C.PAY_END_DTAND
D.OFF_CYCLE = C.OFF_CYCLEAND
D.SEPCHK = C.SEPCHKAND
D.PAGE_NUM = C.PAGE_NUMAND
D.LINE_NUM = C.LINE_NUMAND
D.PLAN_TYPE = PS_BN_SNAP_PLAN.PLAN_TYPEAND
D.BENEFIT_PLAN = PS_BN_SNAP_PLAN.BENEFIT_PLANAND
D.BENEFIT_RCD_NBR = PS_BN_SNAP_PLAN.BENEFIT_RCD_NBRAND
C.EMPLID = J.EMPLIDAND
C.COMPANY = J.COMPANYAND
C.PAYGROUP = J.PAYGROUPAND
J.EMPLID = PS_BN_SNAP_PLAN.EMPLIDAND
J.COBRA_EVENT_ID = PS_BN_SNAP_PLAN.COBRA_EVENT_IDAND
J.BENEFIT_RCD_NBR = PS_BN_SNAP_PLAN.BENEFIT_RCD_NBRAND
C.OFF_CYCLE = 'N'AND
C.PAY_END_DT BETWEEN PS_BN_SNAP_PLAN.COVERAGE_BEGIN_DT AND
getdate()AND
D.DED_CLASS IN ('A','B')AND
D.DED_CUR > 0
)
WHERE EXISTS
(
SELECT D1.PAY_END_DT
FROM PS_PAY_CHECK C1,
PS_PAY_DEDUCTION D1,
PS_BN_SNAP_JOB J1
WHERE D1.COMPANY = C1.COMPANYAND
D1.PAYGROUP = C1.PAYGROUPAND
D1.PAY_END_DT = C1.PAY_END_DTAND
D1.OFF_CYCLE = C1.OFF_CYCLEAND
D1.SEPCHK = C1.SEPCHKAND
D1.PAGE_NUM = C1.PAGE_NUMAND
D1.LINE_NUM = C1.LINE_NUMAND
D1.PLAN_TYPE = PS_BN_SNAP_PLAN.PLAN_TYPEAND
D1.BENEFIT_PLAN = PS_BN_SNAP_PLAN.BENEFIT_PLANAND
C1.EMPLID = J1.EMPLIDAND
C1.COMPANY = J1.COMPANYAND
C1.PAYGROUP = J1.PAYGROUPAND
J1.EMPLID = PS_BN_SNAP_PLAN.EMPLIDAND
J1.BENEFIT_RCD_NBR = PS_BN_SNAP_PLAN.BENEFIT_RCD_NBRAND
J1.COBRA_EVENT_ID = PS_BN_SNAP_PLAN.COBRA_EVENT_IDAND
D1.BENEFIT_RCD_NBR = PS_BN_SNAP_PLAN.BENEFIT_RCD_NBRAND
C1.OFF_CYCLE = 'N' AND
C1.PAY_END_DT BETWEEN PS_BN_SNAP_PLAN.COVERAGE_BEGIN_DT AND
getdate()AND
D1.DED_CLASS IN ('A','B')AND
D1.DED_CUR > 0
)AND
'NA' =
(
SELECT J2.PAY_SYSTEM_FLG
FROM PS_BN_SNAP_JOB J2
WHERE J2.EMPLID = PS_BN_SNAP_PLAN.EMPLIDAND
J2.BENEFIT_RCD_NBR = PS_BN_SNAP_PLAN.BENEFIT_RCD_NBRAND
J2.COBRA_EVENT_ID = PS_BN_SNAP_PLAN.COBRA_EVENT_ID
)AND
DEPENDENT_BENEF = ' 'AND
COVERAGE_ELECT = 'E'
March 7, 2012 at 1:01 pm
Please repost the execution plan as outlined in this article by Gail Shaw:
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 7, 2012 at 1:15 pm
Sorry about that...I hadn't posted a maintenance plan previously...that works much better 🙂
March 7, 2012 at 1:36 pm
Will you update the old style join from this:
FROM PS_PAY_CHECK C,
PS_PAY_DEDUCTION D,
PS_BN_SNAP_JOB J
To the new style where Inner Join blah on something = something?
Also this section:
WHERE EXISTS (
SELECT D1.PAY_END_DT
FROM PS_PAY_CHECK C1,
PS_PAY_DEDUCTION D1,
PS_BN_SNAP_JOB J1
Is the highest cost and returns 19.6 million records.
Also, for the equality conditions, do you have indexes covering the columns from each table on both sides of the equality?
WHERE D1.COMPANY = C1.COMPANY AND
D1.PAYGROUP = C1.PAYGROUP AND
D1.PAY_END_DT = C1.PAY_END_DT AND
D1.OFF_CYCLE = C1.OFF_CYCLE AND
D1.SEPCHK = C1.SEPCHK AND
D1.PAGE_NUM = C1.PAGE_NUM AND
D1.LINE_NUM = C1.LINE_NUM AND
D1.PLAN_TYPE = PS_BN_SNAP_PLAN.PLAN_TYPE AND
D1.BENEFIT_PLAN = PS_BN_SNAP_PLAN.BENEFIT_PLAN AND
C1.EMPLID = J1.EMPLID AND
C1.COMPANY = J1.COMPANY AND
C1.PAYGROUP = J1.PAYGROUP AND
J1.EMPLID = PS_BN_SNAP_PLAN.EMPLID AND
J1.BENEFIT_RCD_NBR = PS_BN_SNAP_PLAN.BENEFIT_RCD_NBR AND
J1.COBRA_EVENT_ID = PS_BN_SNAP_PLAN.COBRA_EVENT_ID AND
D1.BENEFIT_RCD_NBR = PS_BN_SNAP_PLAN.BENEFIT_RCD_NBR AND
C1.OFF_CYCLE = 'N' AND
C1.PAY_END_DT BETWEEN PS_BN_SNAP_PLAN.COVERAGE_BEGIN_DT AND getdate() AND
D1.DED_CLASS IN ('A','B') AND
D1.DED_CUR > 0
Lastly,
Have you considered the use of filtered indexes for c1.off_cycle and d1.ded_class?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 7, 2012 at 1:37 pm
can you post SQL2008 version information (sp/cu/@@version + "max server memory" setting for that instance) ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 7, 2012 at 2:26 pm
You'll have to check the query qualifies with your original one ( check it with selecting the data of both sets and use the except operator to check for non-matching rows )
UPDATE A -- don't use the table name over here because that may cause extra processing !
SET PAY_END_DT = MAX_PAY_END_DT
FROM PS_BN_SNAP_PLAN A
INNER JOIN (
SELECT D1.PLAN_TYPE
, D1.BENEFIT_PLAN
, J1.EMPLID
, J1.BENEFIT_RCD_NBR
, J1.COBRA_EVENT_ID
, D1.BENEFIT_RCD_NBR
, C1.PAY_END_DT
, MAX(D1.PAY_END_DT) AS max_PAY_END_DT
FROM PS_PAY_CHECK C1
INNER JOIN PS_PAY_DEDUCTION D1
ON D1.COMPANY = C1.COMPANY
AND D1.PAYGROUP = C1.PAYGROUP
AND D1.PAY_END_DT = C1.PAY_END_DT
AND D1.OFF_CYCLE = C1.OFF_CYCLE
AND D1.SEPCHK = C1.SEPCHK
AND D1.PAGE_NUM = C1.PAGE_NUM
AND D1.LINE_NUM = C1.LINE_NUM
AND D1.DED_CLASS IN ( 'A', 'B' )
AND D1.DED_CUR > 0
INNER JOIN PS_BN_SNAP_JOB J1
ON C1.EMPLID = J1.EMPLID
AND C1.COMPANY = J1.COMPANY
AND C1.PAYGROUP = J1.PAYGROUP
AND C1.OFF_CYCLE = 'N'
and C1.PAY_END_DT < GETDATE()
GROUP BY D1.PLAN_TYPE
, D1.BENEFIT_PLAN
, J1.EMPLID
, J1.BENEFIT_RCD_NBR
, J1.COBRA_EVENT_ID
, D1.BENEFIT_RCD_NBR
, C1.PAY_END_DT
) B
ON B.PLAN_TYPE = A.PLAN_TYPE
AND B.BENEFIT_PLAN = A.BENEFIT_PLAN
AND B.EMPLID = A.EMPLID
AND B.BENEFIT_RCD_NBR = A.BENEFIT_RCD_NBR
AND B.COBRA_EVENT_ID = A.COBRA_EVENT_ID
AND B.BENEFIT_RCD_NBR = A.BENEFIT_RCD_NBR
AND B.PAY_END_DT >= A.COVERAGE_BEGIN_DT
AND B.PAY_END_DT < GETDATE()
AND A.DEPENDENT_BENEF = ' '
AND A.COVERAGE_ELECT = 'E'
where ( A.PAY_END_DT < B.max_PAY_END_DT
OR A.PAY_END_DT is null -- only leave this as is when needed
)
AND EXISTS ( SELECT *
FROM PS_BN_SNAP_JOB J2
WHERE J2.EMPLID = A.EMPLID
AND J2.BENEFIT_RCD_NBR = A.BENEFIT_RCD_NBR
AND J2.COBRA_EVENT_ID = A.COBRA_EVENT_ID
AND J2.PAY_SYSTEM_FLG = 'NA' )
TEST IT -- test IT !!!!
If you can, please post this new execution plan.
btw I use SQL Sentry Plan explorer to get a better and quick interpretation of your SQLPlan 😀
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 7, 2012 at 2:31 pm
SQLRNNR (3/7/2012)
Will you update the old style join from this:To the new style where Inner Join blah on something = something?
Absolutely, this is the first time seeing this statement which is a hold over from a previous version, which is now getting upgraded...that was an obvious one for me and I did start doing that until I kind of hit the wall on tuning it...I just decided to post the original.
Also this section:
WHERE EXISTS (
SELECT D1.PAY_END_DT
FROM PS_PAY_CHECK C1,
PS_PAY_DEDUCTION D1,
PS_BN_SNAP_JOB J1
Is the highest cost and returns 19.6 million records.
I will review the logic on this part, thank you for pointing this out.
Also, for the equality conditions, do you have indexes covering the columns from each table on both sides of the equality?
WHERE D1.COMPANY = C1.COMPANY AND
D1.PAYGROUP = C1.PAYGROUP AND
D1.PAY_END_DT = C1.PAY_END_DT AND
D1.OFF_CYCLE = C1.OFF_CYCLE AND
D1.SEPCHK = C1.SEPCHK AND
D1.PAGE_NUM = C1.PAGE_NUM AND
D1.LINE_NUM = C1.LINE_NUM AND
D1.PLAN_TYPE = PS_BN_SNAP_PLAN.PLAN_TYPE AND
D1.BENEFIT_PLAN = PS_BN_SNAP_PLAN.BENEFIT_PLAN AND
C1.EMPLID = J1.EMPLID AND
C1.COMPANY = J1.COMPANY AND
C1.PAYGROUP = J1.PAYGROUP AND
J1.EMPLID = PS_BN_SNAP_PLAN.EMPLID AND
J1.BENEFIT_RCD_NBR = PS_BN_SNAP_PLAN.BENEFIT_RCD_NBR AND
J1.COBRA_EVENT_ID = PS_BN_SNAP_PLAN.COBRA_EVENT_ID AND
D1.BENEFIT_RCD_NBR = PS_BN_SNAP_PLAN.BENEFIT_RCD_NBR AND
C1.OFF_CYCLE = 'N' AND
C1.PAY_END_DT BETWEEN PS_BN_SNAP_PLAN.COVERAGE_BEGIN_DT AND getdate() AND
D1.DED_CLASS IN ('A','B') AND
D1.DED_CUR > 0
The initial execution plan showed me index scans, I started working on the original 4 tables in a straight select statement (minus any of the sub queries below) to tune those...which is where I got stuck.
I tried the index tuning advisor which really didn't give me anything, I tried to create my own indexes which worked on three of the four tables but didn't seem to help.
I couldn't figure out why the forth table continued to use an index scan when I had a covering index created...PS_BN_SNAP_JOB, I figured it was because the table only had 45000 rows...but again wasn't entirely sure.
Lastly,
Have you considered the use of filtered indexes for c1.off_cycle and d1.ded_class?
I am not that familiar with the fuction filtered indexes (I will be reading up on that after I post this response) could you give me your opinion(s)?
March 7, 2012 at 2:38 pm
ALZDBA (3/7/2012)
can you post SQL2008 version information (sp/cu/@@version + "max server memory" setting for that instance) ?
Version: 10.0.2573.0 (2008 Enterprise)
Memory: 32 gb's
CPU: 4
DB size: 120 gb's
Drive configuration: 8 database data files 1 log file all on separate drives.
Default instance on a Virtual server.
March 7, 2012 at 2:41 pm
btw Did you perform full maintenance after you upgraded this database to sql2008 ?
-- checkdb with content-re-eveluation
DBCC CHECKDB (0) WITH ALL_ERRORMSGS, DATA_PURITY; --http://support.microsoft.com/kb/923247 !!! and repair
+ including statistics update, rebuild all indexes, usage update
If this is a new install of sql2008, why didn't you upgrade to SP3 ?
( 10.0.5500SQL Server 2008 Service Pack 3 (SP3)October 6, 2011 http://www.microsoft.com/download/en/details.aspx?id=27594)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 7, 2012 at 2:45 pm
Kind of a side question...can someone give me a brief explination on how to determine what columns go into the index vs. the INCLUDE?
For instance, I created the exact index that was recommended...
I created the index first on my own based on what was being utilized in the query...without thinking about letting SSMS suggest an index...
CREATE INDEX [lee] ON [dbo].[PS_PAY_DEDUCTION] ([COMPANY], [PAYGROUP], [PAY_END_DT], [OFF_CYCLE], [SEPCHK], [PAGE_NUM], [LINE_NUM], [PLAN_TYPE], [BENEFIT_PLAN], [BENEFIT_RCD_NBR], [DED_CLASS], [DED_CUR])
Then when I got stuck...I pulled out everything I was writing and wanted to see what it would recommend...
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[PS_PAY_DEDUCTION] ([OFF_CYCLE],[PAY_END_DT],[DED_CLASS],[DED_CUR])
INCLUDE ([COMPANY],[PAYGROUP],[PAGE_NUM],[LINE_NUM],[SEPCHK],[BENEFIT_RCD_NBR],[PLAN_TYPE],[BENEFIT_PLAN])
It recommended the same columns but broke it out in the new format...
How can anyone know what to put in the main index vs the INCLUDE? Is there something you would review step by step?
Because some of the columns are in the join, and some are in the filter...
Sorry if that doesn't make sense but I would haven't known how to construct the statement on my own vs. the recommendation...
March 7, 2012 at 3:13 pm
Leeland (3/7/2012)
Lastly,
Have you considered the use of filtered indexes for c1.off_cycle and d1.ded_class?
I am not that familiar with the fuction filtered indexes (I will be reading up on that after I post this response) could you give me your opinion(s)?
Filtered indexes can be very handy in some cases. I have seen marked improvement in queries where a lot of rows were being returned that were later removed from the query due to conditions in the where clause. In one case, I saw a query improve from minutes down to < 1s.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 7, 2012 at 3:14 pm
During the upgrade I took the SQL 2000 database, restored it to the 2008 server, let it restore and upgrade.
Once complete I ran the following steps...
EXEC dbo.sp_dbcmptlevel @dbname = N'xxx', @new_cmptlevel = 100;
GO
DBCC UPDATEUSAGE (xxx) WITH COUNT_ROWS
GO
DBCC CHECKDB WITH DATA_PURITY
GO
REBUILD all indexes
GO
Clear old execution plans
GO
Then I have a custom script to pull each table into the new database after recreating all the objects.
If this is a new install of sql2008, why didn't you upgrade to SP3 ?
( 10.0.5500 SQL Server 2008 Service Pack 3 (SP3) October 6, 2011 http://www.microsoft.com/download/en/details.aspx?id=27594)
Good point, the server was built last summer for the beginning of the project upgrade...the project was put on hold shortly after and picked back up at the beginning of the year...so it was missed by me.
I will add that to my task list.
March 7, 2012 at 8:03 pm
Am I wrong or is this a PeopleSoft database?
March 7, 2012 at 8:07 pm
Columns in the index are in the where clause and are used to determine which rows to retrieve.
Columns in the include are used in the select clause and are the returned data, but aren't part of the filter.
This allows the index alone to satisfy the query rather than having to go and retrieve the actual record to get the columns you want returned.
March 8, 2012 at 12:58 am
just to add to Toby's great summarized reply :
For more information on the Included columns topic ...
Have a look at "Create index "Create Index http://msdn.microsoft.com/en-us/library/ms188783%28v=sql.100%29.aspx]Create Index
and Index with Included Columns http://msdn.microsoft.com/en-us/library/ms190806%28v=sql.100%29.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply