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)) SQLFROM sys.stats sINNER JOIN sys.partitions p ON s.object_id=p.object_id AND p.index_id<2WHERE s.object_id>100 -- ignore system objectsAND p.rows>100 -- change rowcount if neededAND STATS_DATE(s.object_id,s.stats_id)<GETDATE()-10 -- last updated 10 or more days ago, change if neededORDER BY stats_date, p.rows ASC, SQLIt 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. 
Need Help With Dream job Interview
Posted - 2012-11-28 : 07:59:21
