SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Plans migrating from 2005 to 2008R2


Query Plans migrating from 2005 to 2008R2

Author
Message
KTD
KTD
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 373
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.
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5224 Visits: 875
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
KTD
KTD
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 373
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.
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5224 Visits: 875
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
KTD
KTD
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 373
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.
KTD
KTD
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 373
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.
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5224 Visits: 875
We also need the index - and of course, I forgot to mention: the query!

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
KTD
KTD
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 373
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.
KTD
KTD
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 373
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.
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5224 Visits: 875
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search