October 7, 2010 at 4:08 pm
I am planning and testing an upgrade infrastructure of a OLTP database that is running on 2000 and moving to 2008. The problem that I'm having is that there is one query that has degraded in performance tremendously and I cant figure it out. I've Used SP DTA Perfmon took out some of the hints and it did perform better but still not up to the performance of 2000. The only thing that I'm seeing that could be an issue is that the 2 biggest tables have 55mil and 88mil rows.
My question is could tables that are that big and have NOT been partitioned be the issue going from 2000 to 2008? Thanks.
Any feedback would be greatly appreciated.
October 7, 2010 at 4:45 pm
have the statistics been updated since the upgrade?
---------------------------------------------------------------------
October 8, 2010 at 4:17 am
Check the indexes and as well as fragmentation level too
October 8, 2010 at 4:27 am
Pradyothana Shastry (10/8/2010)
Check the indexes and as well as fragmentation level too
would an upgrade change the fragmentation level?
October 8, 2010 at 6:22 am
steveb. (10/8/2010)
Pradyothana Shastry (10/8/2010)
Check the indexes and as well as fragmentation level toowould an upgrade change the fragmentation level?
Assuming either a restore or attach style upgrade.... nope.
There are some edge cases where marginal TSQL code that ran OK in 2000 no longer runs well in 2005/2008 because the optimizer is a bit more stringent. After you update all the statistics, preferably with a full scan, if the performance doesn't improve, can you post the query and the execution plan here?
By the way, for what it's worth, the DTA is nigh on to useless if your queries are at all complicated.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 8, 2010 at 6:30 am
On an upgrade, you should be rebuilding the indexes... I am pretty sure this is MS recommended or in BOL
October 8, 2010 at 7:22 am
I certainly saw that when I upgraded from 2000 to 2005. There were a series of report queries that began to perform horribly in 05 that worked well in 00. When I read the queries I asked how they ever possibly worked at all. There were definitely some loopholes that got closed in 2005 and later and you will probably have to re-design the query if reindexing doesn't solve the problem.
Not that this will help this situation (it won't) but you should also do a DBCC CHECKDB WITH DATA_PURITY and change the page verify to CHECKSUM. Just a standard "should do" on an upgrade from 2000.
October 8, 2010 at 7:54 am
while we are there dbcc updateusage(0) as well
---------------------------------------------------------------------
October 8, 2010 at 5:43 pm
be sure that the correct datatypes are used , not relying on implicit conversions
October 9, 2010 at 4:25 pm
See "SQL Server 2008 Upgrade Technical Reference Guide":
Here are the steps:
1. Change db compatibility level to 100 on all upgraded databases
2.(ONLY for database upgrades from SQL Server 2000 to SQL Server 2005/2008)
Execute DBCC CHECKDB WITH DATA_PURITY to check the database for column values that are not valid or are out of range.
After you have successfully run DBCC CHECKDB WITH DATA_PURITY against an upgraded database,
you do not need to specify the DATA_PURITY option again because SQL Server will automatically maintain "data purity."
This is the only DBCC CHECKDB check that you need to run as a post-upgrade task.
3.Run DBCC UPDATEUSAGE to correct any incorrect page or row counts.
USE dbName;
go
DBCC UPDATEUSAGE (dbName);
GO
4.Update statistics by using the sp_updatestats stored procedure to ensure all statistics are up-to-date:
USE dbName;
go
EXEC sp_updatestats;
go
(UPDATE STATISTICS [tblname] WITH FULLSCAN may be advisable for a few select tables, especially the larger ones)
5. Run this on all upgraded databases:
alter database [your-db-name] set PAGE_VERIFY CHECKSUM
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 9, 2010 at 8:15 pm
reggie burns-317942 (10/7/2010)
I am planning and testing an upgrade infrastructure of a OLTP database that is running on 2000 and moving to 2008. The problem that I'm having is that there is one query that has degraded in performance tremendously and I cant figure it out. I've Used SP DTA Perfmon took out some of the hints and it did perform better but still not up to the performance of 2000. The only thing that I'm seeing that could be an issue is that the 2 biggest tables have 55mil and 88mil rows.My question is could tables that are that big and have NOT been partitioned be the issue going from 2000 to 2008? Thanks.
Any feedback would be greatly appreciated.
All the others have posted some good suggestions but I've recently been bitten by an "enhancement" in 2008 and Jo Pattyn alluded to it above. SQL Server 2008 is a whole lot less forgiving about mismatched datatypes in a query. In 2000 and 2005, such mismatches were frequently handled very nicely in that the optimizer would make the correct choice on how to handle the mismatch. In 2008, the optimizer is a whole lot more picky about it. Check the joins on the query in question and make sure that the predicates being joined are the exact same datatype or you could end up with an "accidental cross join" behind the scenes that will produce millions and even billions of internal rows that just aren't necessary.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 11, 2010 at 8:45 am
Thanks for all the input.
After running update statics on 5 of the must active tables the queries are running much better, as if it was still in the SQL 2000.
One more question I wanted to know since I haven't performed this test. Is it as simple and straight forward to assume that if I need to move my 2000 cluster DBs to a new SAN environment and using 2008 cluster I would
1. Detached DBs
2. Copy to new 2008 SAN environment
3. Reattach DBs in new 2008 SAN environment
4. Change compability level to 100
5. Run checkdb with data-purity for errors, Run updateusage, Run sp_updatestats
Or
1. Backup production DB with log or diff
2. Restore on new 2008 environment
3. continue from step 4-5
I have limited downtime for my upgrade and I wanted to see if any suggestions of which would be the best option or the quickest option.
Thanks for everyones feedback.
October 11, 2010 at 3:43 pm
reggie burns-317942 (10/11/2010)
Thanks for all the input.
After running update statics on 5 of the must active tables the queries are running much better, as if it was still in the SQL 2000.
One more question I wanted to know since I haven't performed this test. Is it as simple and straight forward to assume that if I need to move my 2000 cluster DBs to a new SAN environment and using 2008 cluster I would
1. Detached DBs
2. Copy to new 2008 SAN environment
3. Reattach DBs in new 2008 SAN environment
4. Change compability level to 100
5. Run checkdb with data-purity for errors, Run updateusage, Run sp_updatestats
Or
1. Backup production DB with log or diff
2. Restore on new 2008 environment
3. continue from step 4-5
I have limited downtime for my upgrade and I wanted to see if any suggestions of which would be the best option or the quickest option.
Thanks for everyones feedback.
Both options are equivalent in my view, and it all comes down to personal preference.
If you do go with the backup/restore option, make sure you take a final backup of the tail of the log like this:
BACKUP LOG [dbname] TO DISK = '...' WITH NORECOVERY;
This will be the final backup to restore on the target server.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 12, 2010 at 5:43 am
reggie burns-317942 (10/11/2010)
Thanks for all the input.
After running update statics on 5 of the must active tables the queries are running much better, as if it was still in the SQL 2000.
One more question I wanted to know since I haven't performed this test. Is it as simple and straight forward to assume that if I need to move my 2000 cluster DBs to a new SAN environment and using 2008 cluster I would
1. Detached DBs
2. Copy to new 2008 SAN environment
3. Reattach DBs in new 2008 SAN environment
4. Change compability level to 100
5. Run checkdb with data-purity for errors, Run updateusage, Run sp_updatestats
Or
1. Backup production DB with log or diff
2. Restore on new 2008 environment
3. continue from step 4-5
I have limited downtime for my upgrade and I wanted to see if any suggestions of which would be the best option or the quickest option.
Thanks for everyones feedback.
Personally, we've always used the backup & restore method because it does nothing at all to the old system. In the event you need to go back to it (has never happened yet, knock wood), you can, almost instantly. It's just a tad safer than the dettach/attach approach.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 13, 2010 at 9:21 am
OK After further testing I still have one query that still has increased its performance with the upgrade. I've studied the Exec plan and see that there is a scan of about 50mil records where there probably should be a seek. the plan showed a 95% cost on this seek.
Tables used are
1. Tickets=55mil
2. Seats=85mil
Events=200thou
Section=35thou
All are seeks except for Tickets is a scan and the programmer uses a inner loop join.
Another question why use a inner loop join vs a inner join?
Here is the query
update Ticket with(rowlock,updlock) set TicketState = 2, MasterTicketID = #PackageTicket.MasterTicketID, TimePurchased = '2010-10-13 09:47:18.57', PaymentInfoID = 10872192, PaymentInfoIDOrig = 10872192, TicketConvFee =
CASE WHEN SubPriceDetail.ConvFeeBoxOffice IS NOT NULL THEN SubPriceDetail.ConvFeeBoxOffice
WHEN MasterPriceDetail.ConvFeeBoxOffice IS NOT NULL THEN MasterPriceDetail.ConvFeeBoxOffice
ELSE Event.ConvenienceFee END , TicketEmbFee = CASE WHEN SubPriceDetail.EmbFeeBoxOffice IS NOT NULL THEN SubPriceDetail.EmbFeeBoxOffice ELSE MasterPriceDetail.EmbFeeBoxOffice END , TicketCost = CASE WHEN SubPriceDetail.PriceBox IS NOT NULL THEN SubPriceDetail.PriceBox ELSE MasterPriceDetail.PriceBox END , TicketCommission = ISNULL(SubPriceDetail.CommissionBoxOffice, MasterPriceDetail.CommissionBoxOffice)
,Ticket.LinearBarcode = case
when datalength(Ticket.LinearBarcode) >= datalength(isnull(MasterPriceDetail.ClientProductCode, MasterPrice.ClientProductCode))
then isnull(MasterPriceDetail.ClientProductCode, MasterPrice.ClientProductCode) +
substring(Ticket.LinearBarcode,datalength(isnull(MasterPriceDetail.ClientProductCode, MasterPrice.ClientProductCode))+1,100)
else Ticket.LinearBarcode end
FROM Ticket with(rowlock,updlock)
INNER JOIN Event with (nolock) ON Ticket.EventID = Event.EventID INNER JOIN Seats with (nolock) ON Ticket.SeatID = Seats.SeatID INNER JOIN Sections with (nolock) ON Sections.SectionID = Seats.SectionID LEFT OUTER JOIN #PackageTicket with(nolock) on Ticket.TicketID = #PackageTicket.SubTicketID
INNER LOOP JOIN Ticket as MasterTicket with (nolock) ON MasterTicket.TicketID = isnull(#PackageTicket.MasterTicketID, Ticket.TicketID)
JOIN Event as MasterEvent with (nolock) ON MasterEvent.EventID = MasterTicket.EventID
JOIN Seats as MasterSeats with (nolock) ON MasterSeats.SeatID = MasterTicket.SeatID
JOIN Sections as MasterSections with (nolock) on MasterSections.SectionID = MasterSeats.SectionID
LEFT OUTER JOIN Performance with (nolock) ON Performance.EventID = Ticket.EventID
and Performance.PerformanceTime = '1990-01-01 00:00:00.000'
JOIN Price as MasterPrice with (nolock) ON MasterPrice.EventID = MasterEvent.EventID
AND MasterPrice.PriceName = case when MasterEvent.PerformanceEvent = 1 and Performance.PriceName is not null then Performance.PriceName else MasterEvent.PriceName end
AND MasterPrice.EffectiveTime =
(
select max(EffectiveTime)
from Price with (nolock)
where EventID = MasterEvent.EventID
and PriceName = case when Event.PerformanceEvent = 1 and Performance.PriceName is not null then Performance.PriceName else MasterEvent.PriceName end
and EffectiveTime <= current_timestamp
)
LEFT OUTER JOIN PriceDetail MasterPriceDetail with (nolock) ON MasterPriceDetail.PriceID = MasterPrice.PriceID AND MasterPriceDetail.TierGroup = MasterSections.TierGroup
and MasterPriceDetail.MemberLevel = '' and MasterPriceDetail.TierOrder = 0
LEFT OUTER JOIN Price SubPrice with (nolock) ON SubPrice.EventID = Event.EventID
AND SubPrice.PriceName = case when Event.PerformanceEvent = 1 and Performance.PriceName is not null then Performance.PriceName else Event.PriceName end
AND SubPrice.EffectiveTime =
(
select max(EffectiveTime)
from Price with (nolock)
where EventID = Event.EventID
and PriceName = case when Event.PerformanceEvent = 1
and Performance.PriceName is not null then Performance.PriceName else Event.PriceName end
and EffectiveTime <= current_timestamp
)
INNER JOIN PriceDetail as SubPriceDetail with (nolock) ON SubPriceDetail.PriceID = SubPrice.PriceID AND SubPriceDetail.TierGroup = Sections.TierGroup
and SubPriceDetail.MemberLevel = '' and SubPriceDetail.TierOrder = 0
WHERE Ticket.TicketID IN (0,31673862,31673863,31673864,31673865,31673866,31673867,31673868,31673869,31673870,31673871,31673872,31673873,31673874,31673879,31673880,31673881,31673882,31673883,31673887,31673888,31673889,3167389
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply