January 23, 2008 at 5:11 am
Hello everybody,
we have an application for tracking website events. Every day it starts filling up a new table which has about 28 million rows at the end of the day.
On our old SQL Server 2000 system no table scans occur. On the new system we see table scans after inserting about 2,2 million rows and the cpu load rises to 100%.
Because of this I see a lot of resource_semaphore waits.
Doing an UPDATE STATISTICS FULLSCAN gives a little relief, but this doesn't last long.
There is a high degree of fragmentation on the indexes.
I'm scanned almost the entire web for an solution or an explanation and would appreciate any hint.
Dirk Schnell
January 23, 2008 at 6:04 am
Not knowing the structure or the code, it's hard to make suggestions. If you haven't already, check out this white paper from Microsoft. They outline a number of things you can do to ensure either better statistics, or better use of the statistics. Not much help I realize.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 23, 2008 at 8:04 am
dsc (1/23/2008)
There is a high degree of fragmentation on the indexes.
Rebuilding your indexes is an obvious suggestion.
Can you post the table structure, indexes and the code that's causing the table scans please. Otherwise we're just guessing at the cause.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2008 at 12:26 pm
as said, it's tricky to make recommendations in this situation, however, SQL2005 is much fusier with optimiser and plan reuse than sql2000 was, this is a common happening and my observation would be that in almost all cases it is due to inadequate indexing on the table. If your table is a technically a heap ( inserts only ) , which most tables like this are then the pk/clustered index should be on an ascending key and the actual table should not fragment, likely secondary indexes will fragment, you may be able to offset this a little with setting a fill factor. You have the option of on-line index rebuilds in sql 2005, you might try that. capture the query plans and stats for your queries during the day as an aide to understanding what is happening.
you could try partitioning, i could sort of question the value of a table that generates 28 million rows a day - is the data actually used - could you use a slighlty different setup, maybe 4 tables x 7 million rows would be better. Please don't tell me you are also updating this table.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 23, 2008 at 12:44 pm
also - while I would definitely take this information with a grain of salt - you might care to peruse
select * sys.dm_db_missing_indexes
To see if SQL 2005 thinks it would want other indexes than the ones you have in play. There have been some changes which make 2005 prefer other indexes....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 23, 2008 at 1:14 pm
well ... let's hope you are on SP2 with at least CU4.
- install MS performance dashboard (download free at MS):cool:
download it an install at as well client as server. It has some nice reports concering consumption ans missing indexes.
- did you perform all maintenace after upgrade and after settling dblevel 90 ?
* dbcc dbreindex -all objects-
(or it's online alter index alternative (EE only))
* sp_updatestatistics
* dbcc updateusage (0) with count_rows.
*-- checkdb with content-re-eveluation
DBCC CHECKDB (0) WITH ALL_ERRORMSGS, DATA_PURITY;
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
January 25, 2008 at 1:46 am
Thx for all your comments.
Further investigation showed that logical fragmentation of the indexes is much higher on SQL Server 2005 than in 2000.
After some thousand inserts it is at a level of 96% on 2005, 2000 shows only 50% after 26 mill. inserts.
The sudden server death is due to the affinity of 2005 query optimizer to use table scans at a fragmentation level of 99,8%. 🙂 Well I do understand the reasons, but not why this heavy fragmentation takes place.
I wonder if anybody else facing a situation like this.
So long.
January 25, 2008 at 5:59 am
The optimiser will pick a table/clustered index scan if it feels that its more efficient that multiple seeks (possibly with key lookups). It's not going to decide to scan just because there's high fragmentation.
Can you maybe find one of the more problematic queries, post it, the table and index structure and the execution plan (as a .sqlplan file) please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 25, 2008 at 10:18 pm
I'm not sure on how much difference it would make, but SQL2005 fragmentation stats are more accurate than SQL2000, and by accurate I mean higher .... http://msdn2.microsoft.com/en-us/library/ms189858.aspx.
Unless I've missed it, you didn't say what kind of query is producing these table scans - single table on an indexed column? Posting the good and bad plans might help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply