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

Query Plans migrating from 2005 to 2008R2 Expand / Collapse
Author
Message
Posted Wednesday, September 4, 2013 8:50 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 7:38 AM
Points: 28, Visits: 323
I'm migrating from a 2005 server to a 2008R2 Server on Windows 2012. I restore the backup to test on. I update all indexes and statistics. Update Usage. I run a simple test query that the current production server handles easily. However the new server creates massive IO and doesn't make use of some indexes that it should be using. I get very high disk latencies at the perfmon counters but the SAN team doesn't see the latencies at the SAN level. The new servers hardware and disk are much more robust than the old server not to mention the old server has a load on it when the test is run. The SAN is a VNX5700 with separate pools for Log, TempDB and DATA. TempDB has 20 spindles under it and DATA has 75. All 15K SAS.

I wouldn't expect the 2008 optimizer to do such a bad job.
However besides the Execution Plan being different I have looked at the SAN, HBA card, Powerpath, sent NARS and Sp-collects to EMC, moved the TempDB files to the data LUNS, made sure all drivers were up to date, checked the fiber network, changed queue depth settings on the HBA. So we see almost 8GB during this query passing through the HBA. Nothing even close to that on current prod so it looks like SQL just doing a bad job not using the indexes. Creating massive amounts of IO.


[url=http://www.evernote.com/shard/s272/sh/2ce774ce-2bd8-4918-9536-58b9f6a2729b/3ff13c71a7a4b614e52f0793bc740d93][/url]


I don't always test my SQL scripts, but when I do, I test in Production.
Post #1491350
Posted Wednesday, September 4, 2013 3:36 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 817, Visits: 743
It would certainly help to see the query, as well as the CREATE TABLE and CREATE INDEX statements for the table.

There is little reason to look at the hardware, when there are so apparent difference in the query plan. The plan you get on SQL 2008 scans the entire table, which I assume is some 8 GB in size.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1491555
Posted Wednesday, September 4, 2013 3:45 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 7:38 AM
Points: 28, Visits: 323
Actually the table is over 100GB in size just data not counting indexes. This is a very large database (5TB) and the servers are very powerful with over 128GB ram ect. The query selects top 10000 records ordered by recordate. It is slow even with top 20 on the new server. It was intended as a test of tempdb for sure but certainly shouldn't have caused these types of issues.

I was a little wary of posting too much information due to security around here, let me check with my boss and if it is ok to post the information you suggested.

BTW thanks for your response and I appropriate any help very much!


I don't always test my SQL scripts, but when I do, I test in Production.
Post #1491558
Posted Wednesday, September 4, 2013 3:49 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 817, Visits: 743
The problem is that without knowing the tables and indexes, we cannot say much. Judging from the plan on SQL 2005 alone, there is room for improvements.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1491561
Posted Wednesday, September 4, 2013 3:54 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 7:38 AM
Points: 28, Visits: 323
Absolutely understand and he said I can just now. So I will get all that together. There certainly wouldn't be a covering index for the query because we would never select * like I have done for this test so this one is going to do a look-up for sure for the non covered columns.


I don't always test my SQL scripts, but when I do, I test in Production.
Post #1491564
Posted Wednesday, September 4, 2013 4:02 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 7:38 AM
Points: 28, Visits: 323
Table.




CREATE TABLE [dbo].[Claims](
[ClaimID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[SenderID] [int] NOT NULL,
[UserID] [int] NOT NULL,
[FileID] [int] NOT NULL,
[StrProviderID] [varchar](50) NOT NULL,
[ProviderID] [int] NOT NULL,
[PayToProviderID] [int] NULL,
[PayerID] [int] NOT NULL,
[AccountNumber] [varchar](30) NOT NULL,
[PayerClaimNumber] [varchar](30) NOT NULL,
[Type] [char](1) NOT NULL,
[GroupNumber] [varchar](30) NOT NULL,
[ClaimFilingIndicator] [char](2) NOT NULL,
[TotalClaimAmount] [money] NOT NULL,
[PayerPaidAmount] [money] NOT NULL,
[PatientPaidAmount] [money] NOT NULL,
[NonCoveredCharges] [money] NULL,
[CreationDate] [datetime] NULL,
[StatementDateFrom] [datetime] NULL,
[StatementDateTo] [datetime] NULL,
[AddressedDate] [datetime] NULL,
[StatusID] [int] NOT NULL,
[ClaimStatus] [varchar](3) NULL,
[RoutedDate] [datetime] NULL,
[CHNumber] [varchar](20) NOT NULL,
[PlatformID] [varchar](5) NOT NULL,
[ItemID] [int] NULL,
[SubscriberNumber] [varchar](80) NULL,
[SubscriberName] [varchar](105) NULL,
[PatientNumber] [varchar](80) NULL,
[PatientName] [varchar](105) NULL,
[StatusCategoryCode1] [varchar](3) NULL,
[StatusCode1] [varchar](3) NULL,
[StatusEntityIdentifier1] [varchar](3) NULL,
[StatusCategoryCode2] [varchar](3) NULL,
[StatusCode2] [varchar](3) NULL,
[StatusEntityIdentifier2] [varchar](3) NULL,
[StatusCategoryCode3] [varchar](3) NULL,
[StatusCode3] [varchar](3) NULL,
[StatusEntityIdentifier3] [varchar](3) NULL,
[StatusEffectiveDate] [datetime] NULL,
[StatusPaidAmount] [money] NULL,
[ProviderSecondaryIDQual] [varchar](2) NULL,
[ProviderSecondaryID] [varchar](30) NULL,
[GroupControlVersion] [varchar](30) NULL,
[RecordDate] [datetime] NOT NULL,
[PayerClaimTypeID] [int] NULL,
[FrequencyTypeCode] [char](1) NULL,
[PrincipalDiagnosisCode] [varchar](30) NOT NULL,
[AdmittingDiagnosisCode] [varchar](30) NOT NULL,
[ReasonForVisitDiagnosisCode] [varchar](30) NOT NULL,
[DiagnosisCode1] [varchar](30) NOT NULL,
[DiagnosisCode2] [varchar](30) NOT NULL,
[DiagnosisCode3] [varchar](30) NOT NULL,
[DiagnosisCode4] [varchar](30) NOT NULL,
[DiagnosisCode5] [varchar](30) NOT NULL,
[DiagnosisCode6] [varchar](30) NOT NULL,
[DiagnosisCode7] [varchar](30) NOT NULL,
[DiagnosisECode] [varchar](30) NOT NULL,
[LastStatusChanged] [datetime] NULL,
[IsClaimOrEncounter] [char](1) NULL,
[ContractualObligations] [money] NULL,
[CorrectionandReversals] [money] NULL,
[Otheradjustments] [money] NULL,
[PayorInitiatedReductions] [money] NULL,
[PatientResponsibility] [money] NULL,
[PaymentDate] [datetime] NULL,
[CheckOrEFTNumber] [varchar](30) NULL,
[Paid] [bit] NULL,
[ReferralNumber] [varchar](30) NULL,
[PriorAuthorizationNumber] [varchar](30) NULL,
[RoutingToPayer] [char](1) NOT NULL,
[NewPaidAmount] [money] NULL,
[PatientCollectedAmount] [money] NULL,
[IsProduction] [char](1) NULL,
[IsPoBox] [char](1) NULL,
[ProviderActionID] [char](1) NULL,
[RenderingIndOrOrgID] [int] NULL,
CONSTRAINT [PK_ClaimsAllParts] PRIMARY KEY CLUSTERED
(
[ClaimID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

SET ANSI_PADDING OFF
GO


I don't always test my SQL scripts, but when I do, I test in Production.
Post #1491565
Posted Wednesday, September 4, 2013 4:05 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 817, Visits: 743
We also need the index - and of course, I forgot to mention: the query!

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1491567
Posted Wednesday, September 4, 2013 4:06 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 7:38 AM
Points: 28, Visits: 323
CREATE NONCLUSTERED INDEX [IX_Claims_RecordDate_INC_ClaimID_UserID] ON [dbo].[Claims]
(
[RecordDate] ASC
)
INCLUDE ( [ClaimID],
[UserID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO



I don't always test my SQL scripts, but when I do, I test in Production.
Post #1491568
Posted Wednesday, September 4, 2013 4:07 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 7:38 AM
Points: 28, Visits: 323
the query. Understand this was for a test of the server but I got more than I bargained for.

select top 10000 * from claims with (nolock) where claimid > 100781325 order by recorddate asc


I don't always test my SQL scripts, but when I do, I test in Production.
Post #1491569
Posted Wednesday, September 4, 2013 4:16 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 817, Visits: 743
A fairly nasty query, I would say, that leaves the optimizer between a rock and a hard place.

SQL Server can retrieve orders by recorddate, until it has found 10000 rows that fits criteria on ClaimID. Or it can retrieve all rows through the clustered index that fits the index and sort those rows.

Since I don't know the statistics, I can't say which plan is the best. What I bet that ClaimID and RecordDate are correlated - something that the optimizer don't know and don't assume.

Since your aim is to test the hardware, drop the issue about the different plans, but add an index hint so that you get the same plans on both platforms.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1491573
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse