May 26, 2011 at 9:39 am
GilaMonster (5/26/2011)
One very uncomfortable PM sent...From the posts the person has made, they are totally out of their depth. 6TB DBs, major perf problems and barely a clue.
How do people end up in that situation? It must entail at least a little but of fabulation.
"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
May 26, 2011 at 10:43 am
Gotta love a query that, when executed, complains about a non-existent table but then, when you look at your code, you're not even calling this non-existent table.
I'm not sure how it happened. I ran the query this morning, no problems. I went to re-run it, it said "X table doesn't exist" and I didn't change the query at all. 10 seconds later, it's re-running fine, with no errors.
Someone's been messing with my database... And if I find out it was you, Goldilocks, boy are you in trouble!
May 26, 2011 at 10:43 am
Grant Fritchey (5/26/2011)
GilaMonster (5/26/2011)
One very uncomfortable PM sent...From the posts the person has made, they are totally out of their depth. 6TB DBs, major perf problems and barely a clue.
How do people end up in that situation? It must entail at least a little but of fabulation.
Unless this is a case of a "we don't have a DBA, you know more than the rest of us about this stuff, fix it and have fun."
May 26, 2011 at 10:45 am
Brandie Tarvin (5/26/2011)
Gotta love a query that, when executed, complains about a non-existent table but then, when you look at your code, you're not even calling this non-existent table.I'm not sure how it happened. I ran the query this morning, no problems. I went to re-run it, it said "X table doesn't exist" and I didn't change the query at all. 10 seconds later, it's re-running fine, with no errors.
Someone's been messing with my database... And if I find out it was you, Goldilocks, boy are you in trouble!
Awesome. My code to query parallel universes is propigating!
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 26, 2011 at 10:50 am
Brandie Tarvin (5/26/2011)
Gotta love a query that, when executed, complains about a non-existent table but then, when you look at your code, you're not even calling this non-existent table.I'm not sure how it happened. I ran the query this morning, no problems. I went to re-run it, it said "X table doesn't exist" and I didn't change the query at all. 10 seconds later, it's re-running fine, with no errors.
Someone's been messing with my database... And if I find out it was you, Goldilocks, boy are you in trouble!
I had an Oracle database doing the exact same thing last month and it turned out to be a bug in the optimizer (yet another one) when evaluating materialized views for query rewrite. I suspect it could be a somehow similar case.
-- Gianluca Sartori
May 26, 2011 at 10:52 am
SQLRNNR (5/26/2011)
Gianluca Sartori (5/25/2011)
No way, the Summit is too far and too expensive for me.Especially now that I'm moving into freelance consulting.
Somehow I missed this post yesterday but wondered if that might be the case based on your email address.
Ah! I bought that domain two years ago. I've been preparing the leap for a long time and now I (hopefully) found the chance to try.
-- Gianluca Sartori
May 26, 2011 at 10:56 am
Gianluca Sartori (5/26/2011)
SQLRNNR (5/26/2011)
Gianluca Sartori (5/25/2011)
No way, the Summit is too far and too expensive for me.Especially now that I'm moving into freelance consulting.
Somehow I missed this post yesterday but wondered if that might be the case based on your email address.
Ah! I bought that domain two years ago. I've been preparing the leap for a long time and now I (hopefully) found the chance to try.
Good luck with this endeavour
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 26, 2011 at 10:57 am
Gianluca Sartori (5/26/2011)
SQLRNNR (5/26/2011)
Gianluca Sartori (5/25/2011)
No way, the Summit is too far and too expensive for me.Especially now that I'm moving into freelance consulting.
Somehow I missed this post yesterday but wondered if that might be the case based on your email address.
Ah! I bought that domain two years ago. I've been preparing the leap for a long time and now I (hopefully) found the chance to try.
Good luck with this endeavour
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 26, 2011 at 11:03 am
Thanks. I will need tons of luck.
-- Gianluca Sartori
May 26, 2011 at 11:53 am
Brandie Tarvin (5/26/2011)
Gotta love a query that, when executed, complains about a non-existent table but then, when you look at your code, you're not even calling this non-existent table.I'm not sure how it happened. I ran the query this morning, no problems. I went to re-run it, it said "X table doesn't exist" and I didn't change the query at all. 10 seconds later, it's re-running fine, with no errors.
Someone's been messing with my database... And if I find out it was you, Goldilocks, boy are you in trouble!
A trigger maybe?
"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
May 26, 2011 at 11:57 am
Grant Fritchey (5/26/2011)
Brandie Tarvin (5/26/2011)
Gotta love a query that, when executed, complains about a non-existent table but then, when you look at your code, you're not even calling this non-existent table.I'm not sure how it happened. I ran the query this morning, no problems. I went to re-run it, it said "X table doesn't exist" and I didn't change the query at all. 10 seconds later, it's re-running fine, with no errors.
Someone's been messing with my database... And if I find out it was you, Goldilocks, boy are you in trouble!
A trigger maybe?
Actually, someone else is running the never-ending query on the same instance\db. Basically, SQL Server is so confused, it can't see any of the tables right now.
Good thing this is just a Dev instance.
May 26, 2011 at 12:20 pm
Steve Jones - SSC Editor (5/26/2011)
I'll say that most of the better applications were self-written. It's hard for someone else to do a good job listing everything you do.
A DBA that can write? That's pretty Exceptional right there! I assume the winner was the one that spelt their name right 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 26, 2011 at 12:43 pm
Brandie Tarvin (5/26/2011)
Grant Fritchey (5/26/2011)
Brandie Tarvin (5/26/2011)
Gotta love a query that, when executed, complains about a non-existent table but then, when you look at your code, you're not even calling this non-existent table.I'm not sure how it happened. I ran the query this morning, no problems. I went to re-run it, it said "X table doesn't exist" and I didn't change the query at all. 10 seconds later, it's re-running fine, with no errors.
Someone's been messing with my database... And if I find out it was you, Goldilocks, boy are you in trouble!
A trigger maybe?
Actually, someone else is running the never-ending query on the same instance\db. Basically, SQL Server is so confused, it can't see any of the tables right now.
Good thing this is just a Dev instance.
Almost sounds like global temporary tables being created and dropped.
May 26, 2011 at 1:32 pm
Brandie Tarvin (5/26/2011)
Grant Fritchey (5/26/2011)
Brandie Tarvin (5/26/2011)
Gotta love a query that, when executed, complains about a non-existent table but then, when you look at your code, you're not even calling this non-existent table.I'm not sure how it happened. I ran the query this morning, no problems. I went to re-run it, it said "X table doesn't exist" and I didn't change the query at all. 10 seconds later, it's re-running fine, with no errors.
Someone's been messing with my database... And if I find out it was you, Goldilocks, boy are you in trouble!
A trigger maybe?
Actually, someone else is running the never-ending query on the same instance\db. Basically, SQL Server is so confused, it can't see any of the tables right now.
Good thing this is just a Dev instance.
Yikes. That's messed up.
"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
May 26, 2011 at 2:22 pm
GilaMonster (5/26/2011)
Jim Murphy (5/25/2011)
I guess, I would love to see a precon (not necessarily this one, but here's an idea for the future), where we walk in and have a business complaint. Maybe two or three complaints that may cover 'the website is giving a timeout error 3 or 4 times a day' or something like that. Maybe throw in a possible deadlock or something. If you had a database and jobs or powershell to apply a load of poor queries and other OLTP incompatible lock activity to simulate the real world, I would love to see you go step-by-step, where you would start to isolate the largest problem first, then the next then the next. Waitstats, perfmon, dm's, trace, query analysis and statistics io/time, and the like. Disk bottleneck and too many recompiles, or other common issues. Query analysis/indexes.I put in a regular session where, should it be accepted, I intend to do just that (on a smaller scale). Start with a poorly performing website and, in front of the audience, trace, analyse and fix.
http://sqlinthewild.co.za/index.php/2011/05/10/pass-summit-abstracts/ "Performance improvements in 60 min or less. Guaranteed"
Hahaha! That is awesome. So THIS is the abstract that was written in verse? Partially. That was great, and I'd love to watch it. I guess I'm talking about an idea for a 6th session, something next year and preferedly a precon. Here are a few ideas that I'd love to see the essence of:
I guess I was looking at something not only plan related but dealing with other bottlenecks. Course, 9 times out to 10, that goes back to some crappy TSQL. So I understand the focus on plans.
I just think it would be nice to start from looking at some system metrics, noting high CPU and disk IO, isolating CPU down to a poor query which is using bad TSQL practices and apply a little rewrite, then on to disk and isolate that down to one major report and the plan revealing a missing index or two. Solve one at a time. Or maybe the disk issue is data file issues (file location, SAN config, partition/sector alignment, etc.) rather than a poor query directly.
Then look for more obscure issues once these are resolved. Perhaps too many compiles/s, find the query using sprinkled temp table mania and rework to the query a bit to reduce them. Monitor again, find IO Stalls taking too long or whatever else YOU think is the next biggest deal that needs to be addressed.
So I guess I'm looking for a larger scope with the problems being more than query plans in some cases, although plans reveal the biggest issues in most cases. Like in real life.
Or perhaps the problem is a missing index, but you find there is already 10 indexes on the table from someone before you. Isolate the unused, work through the decision process for consolidating some indexes, test before and after, etc. I guess this is why I'm thinking about a 2 day precon for something like this. A semi-total system analysis and what you would look at first, then next. Like what we do for a few weeks for a client, just narrowed down to a 2 day experience with fewer issues found/solved.
I guess what I'm saying is, different sessions focus on different narrow performance issues (one session is only query plans, another is 5 bad TSQL practices, another is misconfigured SAN/disk partition/sector alignment, still another is parallelism causing the CPU cache to be constantly hydrated/dehydrated and on and on and on. I guess Paul White should be a 3rd partner along with Wes for the disk issues. But I'd love to see multiple issues, where you'd start, and what you'd tackle first and how.
I guess I'm in fantasy land since this scope is too big. I know, what if this was a 300/400 session that didn't walk the full resolution, just the overall diagnosis of each yielding a 'statement of resolution' and Blog link for more on the resolution. ('The problem in this case, is that Parallelism is at the default setting of zero and the threshold is too low. To find the proper 'it depends' setting for your environment, go to this blog'.) There we go; that's what I'm looking for. Maybe a series of these, finding varying issues (part 1 focuses on CPU issues, part 2 for disk/IO part 3 is ...). Each part with a query being a problem, and the system config being another problem.
In real live, do you really go for a trace and plan analysis first? I usually start with perfmon or similar to identify the system stress areas so I can get a better idea if I should tune to relieve disk io (indexes in many cases) or tune to relieve CPU, or recommend a RAM upgrade since there is only a 1GB buffer cache causing/exacerbating increased disk io, or reduce their parallelism to something other than 0, or.... I know, all of it needs to be done, but I'd love to see your decision points and reasons, along with your 'how'. Maybe I have been approaching my analysis/tuning wrong all of these years and so I would love a larger scope to either validate my methods, or correct them, finding a larger kill quicker then I do now. I know, sounds like a 2 week precon or something from SQLSkills.com.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
Viewing 15 posts - 26,671 through 26,685 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply