September 4, 2013 at 8:50 am
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.
I don't always test my SQL scripts, but when I do, I test in Production.
September 4, 2013 at 3:36 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 4, 2013 at 3:45 pm
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.
September 4, 2013 at 3:49 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 4, 2013 at 3:54 pm
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.
September 4, 2013 at 4:02 pm
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.
September 4, 2013 at 4:05 pm
We also need the index - and of course, I forgot to mention: the query!
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 4, 2013 at 4:06 pm
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.
September 4, 2013 at 4:07 pm
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.
September 4, 2013 at 4:16 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 4, 2013 at 6:39 pm
Yes sir, the Current production server does it in about 1 min. The new one takes from 4 to 7 mins. I did use the hint which I need to do again and go back and do again and look at the plan because I cannot remember the details, but it still took much longer. I have also tried limiting the columns. I see disk latencies up to 2000 ms during this so it make us wary of migrating until we are sure. That is why we have double checked all the hardware. I just don't get why this server is choosing such a slow plan. I also tried using a plan guide but for some reason I couldn't get it to use the guide. I couldn't get much info about why but I did the profiler to see if it was used or not.
A bit of interesting info my reporting server which is a replicated copy of many of the tables can do a loop 600 times of this query using a random claimid seeded in 60 seconds. If your wondering how that's possible the server has a PCI SSD card that uses some software called Velobit for disk read caching. It uses the same plan as the Production Server and is 2008R2
I don't always test my SQL scripts, but when I do, I test in Production.
September 4, 2013 at 7:34 pm
Plan using the hint still slow and very high latencies. So I guess I have achieved my objective to test the new hardware.
I don't always test my SQL scripts, but when I do, I test in Production.
September 5, 2013 at 1:24 am
You still have a different plan than on SQL 2005.
The plans posted to evernote are difficult to read. The best is if you can attach them as .sqlplan files.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 5, 2013 at 6:48 am
Here they are
I don't always test my SQL scripts, but when I do, I test in Production.
September 5, 2013 at 3:52 pm
Thanks for the plans!
I note that the table is partitioned. What is the partitioning column? ClaimID?
It appears that you have mixed up the plans a bit. The one you call NewServerPlan.sqlplan is in fact from you old server. (I can see the version number in the XML plan.)
There is something fishy with the plan from the new server. It first sorts the data from the Index Scan on ClaimId to make the Key Lookup faster, and then it performs a TOP N Sort on the fat result set - it should be able to run the TOP before the Key Lookup.
From a more practical perspective: is this a critical query for you, or just something you found vile enough to test the hardware of the new server?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply