SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Need Help With Dream job Interview
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

viperbyte
Posting Yak Master

USA
132 Posts

Posted - 11/28/2012 :  07:59:21  Show Profile  Reply with Quote
Good morning everyone. I'm going to my first ever interview for a DBA position in a few hours. I'm lucky that the employer is considering me for this position since I don't have any official DBA experience, don't have a bachelor's degree at all and obviously not currently employed as a DBA. I'm not even employed. Although on my resume I mention my current computer programming freelancing(translates to starving) as my current occupation. I studied on a fast track school for the MSSQL DBA position in 2004 and was one of the best students. I'm sure that after a couple of weeks on the job that I will have had enough time to cram to be able to begin to feel a little comfortable and competent. I would like to minimize the unproductive time as I get up to speed and will cram like crazy. This is all if I should land the job of course. My biggest concern is how to handle the interview to convey that I will be able to do the job. Can you folks pleeeeaaassse suggest to me what I can say to come across as someone that will be able to address their current problem at least? Their current problem and the reason they are hiring a DBA is that their web app is really slow. They feel that their database needs proper indexing. One of my first thoughts is to somehow let them know that I can show them some kind of stats that shows how a particular query is performing. Then show them the results after work has been done. How do I express this? I will be showing them before and after "whats"? Besides that, what will I say will be my approach? Super please help me. This is the job that I have always wanted and I just about gave up on this. If I had known this opportunity was going to show up I would have kept sharper on all this. Super please.

ahmeds08
Aged Yak Warrior

India
656 Posts

Posted - 11/28/2012 :  08:21:17  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
to check the database performance there are lot of things to be analyzed first:
1.Hardware currently used
2.database design
3.proper indexing
4.SQL code written currently.

run the exisiting queries and check their execution plans.
and someone else can still give you more points on this forum.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 11/28/2012 :  10:00:26  Show Profile  Visit robvolk's Homepage  Reply with Quote
If they let you during the interview, you can run this diagnostic query:
SELECT object_schema_name(s.object_id) + '.' + OBJECT_NAME(s.object_id) + '.' + s.name table_stats
,p.rows, STATS_DATE(s.object_id,s.stats_id) stats_date, 
CONVERT(INT, INDEXPROPERTY(p.object_id, s.name, 'rowmodcnt80')) modified_rows,
'UPDATE STATISTICS ' + QUOTENAME(object_schema_name(s.object_id)) + '.' + 
QUOTENAME(OBJECT_NAME(s.object_id)) + QUOTENAME(QUOTENAME(s.name),'(') + ' -- ' + 
CAST(p.rows AS VARCHAR(20)) SQL
FROM sys.stats s
INNER JOIN sys.partitions p ON s.object_id=p.object_id AND p.index_id<2
WHERE s.object_id>100	-- ignore system objects
AND p.rows>100		-- change rowcount if needed
AND STATS_DATE(s.object_id,s.stats_id)<GETDATE()-10 -- last updated 10 or more days ago, change if needed
ORDER BY stats_date, p.rows ASC, SQL
It will highlight statistics that are out of date and show how old they are, how many rows were modified since last update, and provide a SQL statement to update them (copy and paste that column). It may simply be that their statistics are out of date and not allowing the optimizer to choose effective plans. This is a quick hit and if successful may impress them enough that you're capable.

I agree wholeheartedly with Ahmed's list, there's a lot more to tuning that just updating stats, but that's a simple thing to help get your foot in the door. If it doesn't help then you'll have to look at those other things.

If they are running older ASP code (not ASP.Net), it can easily be badly written and will perform badly no matter what you do on the database side. Look for ADO.Recordset.MoveNext, especially in a loop, and even more especially if they are doing lots of Response.Write in those loops. They'll want to use GetRows or GetString methods to process a Recordset quickly.

Good luck! Hope this helps.
Go to Top of Page

viperbyte
Posting Yak Master

USA
132 Posts

Posted - 11/28/2012 :  10:17:18  Show Profile  Reply with Quote
Thanks for the replies. Can you folks please them coming? I will super appreciate it very much. Robvolk I ran the statement on my Sql Server Express instance: the Results pane show three rows and the modified_rows column has the value 0 for each of the rows. In the Messages pane a message says "(3 row(s) affected)". What was modified? Can you or someone explain that to me please?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 11/28/2012 :  10:30:11  Show Profile  Visit robvolk's Homepage  Reply with Quote
Nothing was modified, the results show you have 3 statistics that meet the criteria (haven't been updated in at least 10 days and tables of at least 100 rows). The modified_rows value means that 0 rows have been modified since the statistics were last updated. Since there's no modified data the statistics are still valid. (Be advised that this value may not always be accurate or useful, see Books Online under "sysindexes" for more information).

You'd want to look for results that show a large number of modified rows compared to the total rows, or a smaller number of rows that are more out-of-date. Again this is just a quick fix and may not resolve deeper problems. Keeping statistics up-to-date is the first step (of many) in tuning performance.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000