March 3, 2015 at 5:33 pm
Okay, so you're sitting in a job interview. Your family needs food. Your mortgage lender expects to be paid. The bills are piling up. You need this job. No pressure. You're sitting across the table from three seasoned DBA's who are tag-teaming you with SQL trivia questions. Inevitably, the vague interview question arises:
"Your users report slow queries. What do you do?"
What exactly are they asking for? This is like taking your car into a mechanic; he asks you what the problem is, and you respond, "It makes a weird noise." What does the noise sound like? What part of the car is it coming from? Do you hear it all the time, or only when you accelerate or put on the brakes? He's going to ask for more specific details, but SQL Server interviewers (or the users for that matter) are not so lenient...
Many causes for slow queries flood my mind:
- Slow server network connection
- Inadequate memory / page faults
- Poor statistics
- Missing indexes
- Index columns in wrong order or sorted improperly
- Indexes missing columns (covering index)
- Need to create indexed views
- SARGeable queries (i.e. calculations on left-hand side of WHERE clause comparison)
- Replacing cursors with set-based operations
- Table partitioning
- Poor table normalization / denormalization design
- Buffer pool for recent pages too small
- Wrong transaction isolation level
- Etc., etc., etc...
Many tools to investigate these queries flood my mind at the same time:
- SQL Server Profiler
- Extended Events (SQL2012+)
- PerfMon
- SET STATISTICS IO ON
- SET STATISTICS TIME ON
- Query Execution Plans
- SQL Server ERRORLOG file
- Procedure Cache
- Etc., etc., etc...
Many DMV's flood my mind... Can three simultaneous floods be called a tsunami?:
- sys.dm_exec_query_stats
- sys.dm_exec_sql_text
- sys.dm_exec_query_plan
- sys.dm_exec_requests
- sys.dm_os_wait_stats
- sys.dm_exec_cached_plans
- sys.dm_db_missing_index_details
- sys.dm_db_missing_index_columns
- Etc., etc., etc...
Let's face it, several of those 1,000-page manuals we've grown accustomed to address the subject of SQL performance alone.
However, remember, this is a job interview, not an hour-long training class. You have 60 seconds to give whatever these DBA's consider to be an intelligent answer. How would you answer the question, "Your users report slow queries. What do you do?" if you only have a minute or two to respond?
I welcome your opinions on the subject, and thanks for your input!
March 3, 2015 at 6:08 pm
There is no certain answer for this question.
same question like what is your approach if your application is running slow.
This is hypothetical question and you can Drove them anywhere and You have covered almost everything in your answer.
March 3, 2015 at 10:58 pm
Wow... I just checked back on my humble little post: 27 views, and no one willing to take a crack at the answer. Okay, you 27 don't get the job... 😉
Seriously, I bring this up because I have actually been asked this question three times in the last two weeks in real life job interviews. This isn't fiction. Refusing to answer will only ensure you're not going to get the job. You have to say something. However, again, with only a minute or two to speak (the whole interview is 30 minutes, and they have other questions), you can only answer what the most important aspect of slow query troubleshooting is. How would you reply to this question, if your career depended on it?
March 3, 2015 at 11:16 pm
As hcip.77 said there is no single correct response but this is still a very valid question, I would answer it as if it was worded slightly differently: "Your users, in your preferred/optimal environment, report slow queries. What do you do?"
Given this, you can use the opportunity to describe the preferred environment with the components you would put in place such as monitoring and alerting.
😎
March 3, 2015 at 11:46 pm
jwiseh (3/3/2015)
Wow... I just checked back on my humble little post: 27 views, and no one willing to take a crack at the answer. Okay, you 27 don't get the job... 😉Seriously, I bring this up because I have actually been asked this question three times in the last two weeks in real life job interviews. This isn't fiction. Refusing to answer will only ensure you're not going to get the job. You have to say something. However, again, with only a minute or two to speak (the whole interview is 30 minutes, and they have other questions), you can only answer what the most important aspect of slow query troubleshooting is. How would you reply to this question, if your career depended on it?
Heh... one could also say "WOW! You've been asked this question 3 times over a two week period and you still don't know? Don't you like your family?" 😉
Like you, I absolutely disagree with what some of the others have said. This is not one of those questions that has no certain answer. As a DBA, you'd better have a definite plan of attack on something like this and you'd better be able to explain what that plan is. And, no... it's not some stupid 60 second answer they're looking for. They're looking for details here! This IS the proverbial "money shot" for any DBA interview and you should plan on spending 3-5 minutes on it... maybe more even if the interview is only scheduled for 30 minutes.
Let's start this off and put some food in your family's mouths. What ARE the very first two things you should do if this happens besides crap your britches?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2015 at 12:44 am
As a DBA, you'd better have a definite plan of attack on something like this and you'd better be able to explain what that plan is. And, no... it's not some stupid 60 second answer they're looking for. They're looking for details here! This IS the proverbial "money shot" for any DBA interview and you should plan on spending 3-5 minutes on it... maybe more.
Let's start this off and put some food in your family's mouth. What ARE the very first two things you should do if this happens besides crap your britches?
That's my question. Being someone who's more of a developer than a DBA (which doesn't matter, I'm going to get asked DBA questions anyway, right?), in my original post, I mention that there are dozens of things that affect query performance, dozens of tools that can be used to check things out, etc. So which two are the most important? That's why I come to this forum, to get help from the opinion of experts such as yourself. 🙂
I did spend 3-5 minutes on it during the interviews, but more picking random topics from the lists above and citing examples of times I've seen them. I'll give one example: I'd talk about checking out the query design for SARGeable errors using the Query Execution Plan. I'd try to verbally describe queries like these:
-- 96% of the batch cost...
SELECT c.name AS 'IdentityColumn'
FROM sys.columns c
INNER JOIN sys.objects o ON o.object_id = c.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name + '.' + o.name = 'HumanResources.Department'
AND c.is_identity = 1
-- 4% of the batch cost...
SELECT c.name AS 'IdentityColumn'
FROM sys.columns c
INNER JOIN sys.objects o ON o.object_id = c.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.name = 'Department' AND s.name = 'HumanResources'
AND c.is_identity = 1;
GO
...since I know putting a calculation or concatenation on the left side of the WHERE clause comparison forces a table scan, even if there is an index, because the concatenation has to be applied to each row first.
Then, I would talk about other things, like checking indexes, to see if adding one more column can create a covering index, thus improving performance, and checking the column and sort order of the index and such.
Although I know about these and other issues that cause slow query performance, I don't think citing individual examples is what the interviewers are looking for.
March 4, 2015 at 12:54 am
Hello Jwiseh,
I think you yourself given answer in your first post..!!!
All the possible causes that can make slow query performance are mentioned by you.
However, I would like to add one more point (cause for slow query performance):
Query execution plan.
Your query execution plan is also one of the important factor in query slow performance.
March 4, 2015 at 1:02 am
Heh... whether you're interviewing for a Developer's position or a DBA's position, one of the most important things would be "communication". You and I have just failed that because, from what your first post said, I assumed (yeah, we know about that) that you were interviewing for a DBA position and you never said you were interviewing for a Developer's position.
Let's avoid furthering that initial mistake. Are you interviewing for a Developer's job or a DBA's job? If Developer's, then what kind of Developer? If for a DBA, then what kind of DBA?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2015 at 1:21 am
...since I know putting a calculation or concatenation on the left side of the WHERE clause comparison forces a table scan, even if there is an index, because the concatenation has to be applied to each row first.
BTW, technically, that's wrong. You got most of it right but it doesn't matter which side of the comparisons the calculation or concatenation is on. If you include a column name in a calculation, including but not limited to concatenation, then the query is non-SARGable to one extent or another sometimes causing a table scan, as you've said. Notice that I said "sometimes" because "it depends".
There is no question that if put the first column of an index in a calculation in the WHERE clause, that's going to force a scan. It might be an non-clustered index scan, though. The optimizer might figure that a non-clustered index would be cheaper to scan than a 150 column wide table (clustered index if done right). Still, that's considered non-SARGable but can still be very useful when you can't change the design of the table that required such a thing.
Also, if you have (for example) a 3 column index and the first two columns in the WHERE clause are SARGable and the 3rd column is wrapped in a calculation (ISNULL(column,0)=0 for example), then there IS a chance that the optimizer will do a seek based on the first two columns and then a short scan on the rows that meet the criteria of those first two columns. Again, not optimal but frequently very well good enough for single row lookups for a GUI. Of course, the best thing to do would be to design the column to not take NULLs so that all 3 columns in the query are SARGable.
Never take it for granted, though. Always check the execution plan and understand that such trickery to get the job done faster might be short lived.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2015 at 1:36 am
It's 3:30 AM here. I've gotta be up-and-at-em in a little over 3 hours. I'll be back but I need to know the answers to my questions about what kind of position you're applying for. The question you ask is still the "money shot" for any of those positions but the answer will be a bit different depending on the position being applied for.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2015 at 1:39 am
Jeff Moden (3/4/2015)
Heh... whether you're interviewing for a Developer's position or a DBA's position, one of the most important things would be "communication". You and I have just failed that because, from what your first post said, I assumed (yeah, we know about that) that you were interviewing for a DBA position and you never said you were interviewing for a Developer's position.Let's avoid furthering that initial mistake. Are you interviewing for a Developer's job or a DBA's job? If Developer's, then what kind of Developer? If for a DBA, then what kind of DBA?
When interviewing for smaller companies (two manufacturing-related, and one was a small bank), they tend to look for a developer & DBA wrapped into one person. They want you to be an expert in all of SQL Server (because they can't afford to pay two). So, you get questions on both sides of the fence. One of them was also grilling me on my C# application development skills as well.
Let's start this off and put some food in your family's mouths. What ARE the very first two things you should do if this happens besides crap your britches?
The suspense is killing me... What ARE the very first two things you should do?
March 4, 2015 at 2:20 am
jwiseh (3/4/2015)
Jeff Moden (3/4/2015)
Heh... whether you're interviewing for a Developer's position or a DBA's position, one of the most important things would be "communication". You and I have just failed that because, from what your first post said, I assumed (yeah, we know about that) that you were interviewing for a DBA position and you never said you were interviewing for a Developer's position.Let's avoid furthering that initial mistake. Are you interviewing for a Developer's job or a DBA's job? If Developer's, then what kind of Developer? If for a DBA, then what kind of DBA?
When interviewing for smaller companies (two manufacturing-related, and one was a small bank), they tend to look for a developer & DBA wrapped into one person. They want you to be an expert in all of SQL Server (because they can't afford to pay two). So, you get questions on both sides of the fence. One of them was also grilling me on my C# application development skills as well.
Let's start this off and put some food in your family's mouths. What ARE the very first two things you should do if this happens besides crap your britches?
The suspense is killing me... What ARE the very first two things you should do?
It IS a bit of a trick question I asked. 😉
If it's a combined position, as you say, and if it's "urgent" and the server is currently slow, then the first thing to do would be to say "I'm already on it" [font="Arial Black"]and be telling the truth[/font]. As Eirikur hinted at and unless it's your first day on the job, you should have some monitoring and alerts setup to tell you when the users are going to complain about slowness. You should also have a "continuous improvement" program where you have written some code to tell you what the worst queries are so that you can fix them before they become problems. What such a proc should do is very much like what is done when you right-click on the instance name in the Object Explorer window, click {Reports}{Standard Reports}, and then select any one of the "Performance - Top Queries" reports. That report will not only show you what the whole bad query is but also the part of the query that the optimizer thinks is the worst part of the query along with stats for reads, cpu, and counts.
On that first part, you need to be able to explain what kind of monitoring you would have setup and will usually depend on two things... 1) how many servers there are and 2) what the budget was/will be for such things. If you're in a shop like the one I'm in, I've only got a dozen servers and only a couple of them are heavy hitters. I can't justify the expense of a monitoring software because the folks in NetOps have already bought some but I don't have a license for. So I wrote my own and I keep a PerfMon running on my right monitor at all times for the "Bread'n'Butter" server.
The second thing to do is prove whether or not the problem lives on the server or not and maybe fix it. The PerfMon thing will help a lot there especially if you hang a picture of the baseline you took nearby. One picture IS worth a thousand lines of code. I've even got it setup so that it gives me an indication as to how many jobs are currently running and I can fire up a perfmon with one click after I log into a server with an "urgent" problem. It would also be good to take a baseline like what Erin Stellato runs. You can Google her stuff. The only reason I don't use hers is because I wrote my own. If it's an "urgency", then you have to break out the fire-fighting equipment. I wrote a proc that does a bunch of things including checking for blocking (like sp_who2 does but returns the blocking hierarchy, as well), what the cpus and the disks are doing, and what the code is that the blocking spid(s) was trying to run. Again, I wrote my own to have a better understanding of the inner workings but you could use Adam Machanic's "WhoIsActive" to help there.
I say "maybe" fix it because you don't want to just jump in and kill a SPID. The rollback could be worse than the finish. "Must Look EYE!" 😉
If it's "just" users complaining about slowness, then you have a bigger job if not slightly less urgent. That's where it also helps to run either those "Top Queries" reports on an hourly basis or the one you've written that writes to a table so you can check day and night the next morning. I think Adam's does that, as well. Those are the queries that you really need to fix using the tools that you've already identified. You should do that even if the users aren't complaining.
Then you can talk about the tools and techniques that you've already talked about in your first post. The whole shebang should take about 5 minutes unless they interrupt you.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2015 at 2:23 am
Hello Jwiseh,
First go with Query execution plan, that is your query should be executed in minimum time. You can make your query better to have good execution time of your query by avoiding unwanted object.
Second thing check the Memory resources (RAM) assigned to your instance. All the query executed first in buffer cache (RAM) so enough memory also play important role to execute your query faster.
Regards,
Jitendra.
March 4, 2015 at 2:52 am
Thanks, Jeff, this gives me something to focus on. Now all I have to do is cut out all the "uhhs" and "umms" from my answer (I sometimes talk like Gerald Ford when I get nervous), and I should be okay. Thanks again for your help!
And thank you to the other people who have contributed ideas as well.
March 4, 2015 at 8:54 am
jitendra.padhiyar (3/4/2015)
Hello Jwiseh,First go with Query execution plan, that is your query should be executed in minimum time. You can make your query better to have good execution time of your query by avoiding unwanted object.
Second thing check the Memory resources (RAM) assigned to your instance. All the query executed first in buffer cache (RAM) so enough memory also play important role to execute your query faster.
Regards,
Jitendra.
The execution plan is the proverbial tip of the ice-berg in an interview. You need to be able to explain what you'd be looking for and how you can't necessarily rely on things like "cost" to do the best thing. Then, like the OP started explaining, you need to be able to identify some of the things to look for in the actual query itself and how to determine if an index would help or not and, if it could, how you would figure out what the index should be on and whether of not there should be any INCLUDEs, etc, etc.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply