June 27, 2008 at 6:16 am
I have one large process that gives me the results of 700,000 sql server logical io pages sec
2000 pages sec physical writes
500 page splits sec
350 latchese
response time 30
and i see SOS_SCHEDULER_YEILD
What is the amount of data that 700,000 io is doing.
The CPU will sit at 100% for ten minutes. The process is doing a lot of data but i wanted to determine how much data this is.
Cheers
June 27, 2008 at 12:44 pm
I'm going to hazard a guess that the code is doing a lot of inserts and/or updates, because of the page splits. Beyond that, it's kind of hard to tell what it might be doing without a little data, like the script of the function.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 27, 2008 at 1:14 pm
What kind of function is it that's performing that many writes? The only thing it can be writing to is a table variable, which then spells trouble since they don't perform well if they're large......
----------------------------------------------------------------------------------
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?
June 28, 2008 at 5:40 am
I had the auto statisics set to true on......and the CPU would stay at 100% for 10 minutes...i turned it off and it runs in 2 minutes cpu remains 35% ...........
Wow that was interesting....
The report was looking at a lot of data 5 gig if i done my math right 700,000 * 8192 / 1024
June 28, 2008 at 8:28 am
TRACEY (6/28/2008)
Wow that was interesting....
Only to you. For the rest of us it is merely confusing because you haven't really told us anything and you haven't answered any of our questions.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 28, 2008 at 8:54 am
Oh the process is a report that went back for 5 years worth of data, it takes the data from one table and does inserts into temp tables which are not temp tables they are person name + table . So it gets all data it needs then builds all new tables then processes these results in these tables, then once it has computed all the columns it then goes back to the real tables and updates this information from the temp table and then delete the records from the temp tables.
Temp tables are not the # tables it is a true table.
Cheers
June 28, 2008 at 1:53 pm
Like everyone has said, we'd probably need a lot more detail than that to actually help...
... but, based on your general description immediately above, I have to say that you are doing things horribly wrong... there is absolutely no need to split out tables based on person's name... none what so ever. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 29, 2008 at 8:12 am
Totally agree but this is a vendor's software and thats how they do this...........not much i can do 🙂 So much overhead building temp tables userid.table each time a process runs...
Just do it directly............
June 29, 2008 at 11:27 am
You can't replace the vendor's sproc? Heh... I do it all the time especially when it sucks the life out of my servers... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 29, 2008 at 4:09 pm
Now there a challenge.
June 29, 2008 at 5:19 pm
It's a challenge only if you can't get people to listen... I couldn't get them to listen the first time either... then, I took a 24 hour process that would sometimes fail, made it work in 15 minutes, and had it do 50% more work. Now, they ask what else I can improve... My answer is simple... Everything 😉
If you want to catch fish, go where the fishing is good 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 30, 2008 at 4:51 am
I would love to improve the vendor package I support, but management won't let me unless I can convince the vendor to make the change. Which happens occasionally. Unfortunately, some of the vendor's development staff gets very defensive when I ask questions about the rationale behind certain processes. liek the time I asked why null was allowed in a column that should never, under any circumstances be null. Fortunately, none of the bad code causes extended processing times, so performance hasn't been an issue. Just strange results on occasion.
June 30, 2008 at 9:00 am
I will just point out 1 thing here (not much to add since we have essentially no details to offer advice on thus far) is that a logical IO doesn't mean that new data was hit. I can do a query that will do 1M logical IOs - but all on the same single page of data. You can't really calculate how much actual data was hit except in certain cases such as a full table scan.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 30, 2008 at 11:24 am
Ross McMicken (6/30/2008)
I would love to improve the vendor package I support, but management won't let me unless I can convince the vendor to make the change. Which happens occasionally. Unfortunately, some of the vendor's development staff gets very defensive when I ask questions about the rationale behind certain processes. liek the time I asked why null was allowed in a column that should never, under any circumstances be null. Fortunately, none of the bad code causes extended processing times, so performance hasn't been an issue. Just strange results on occasion.
Personally, I'd consider "strange results" worse than performance issues. I don't care how fast code is, if the data it produces is wrong. Correct data is the whole purpose of a database. Correct and slow is better than wrong and fast, every time.
Think of it as the Wild West. Would you rather be the guy who shoots first and misses, or the guy who shoots second and hits? (Assuming those are the only two options. Fast and correct is, of course, the best.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 1, 2008 at 12:41 am
The second mouse always get's the cheese from the trap... 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply