SQLServerCentral.com / Question of the Day (QOD) / Testing Center / Question of the Day for 07 May 2007 / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 31 Mar 2015 12:56:53 GMT20RE: Question of the Day for 07 May 2007http://www.sqlservercentral.com/Forums/Topic360104-120-1.aspxI answered this one incorrectly as well. I got stats back after restarting my SQL server. While there was little information, there was still something returned.Thu, 02 Jan 2014 15:53:55 GMTKWymoreRE: Question of the Day for 07 May 2007http://www.sqlservercentral.com/Forums/Topic360104-120-1.aspxHello!I am confused as well. Here's what I did (on a SQL 2008 box):1. Ran the query mentioned2. Restarted the services (both SQL Server Agent & SQL Server)3. Re-ran the query once the server was back up - I got the results same as in step #1Tue, 03 Aug 2010 12:15:12 GMTNakul VachhrajaniRE: Question of the Day for 07 May 2007http://www.sqlservercentral.com/Forums/Topic360104-120-1.aspx<P>Steve: Once more (and than I'll drop this). BOL is confusingly worded. The counters are *NOT* reset. You don't need to reword the question (but adding a bit to the explanation in the answer wouldn't hurt).</P><P>Cycle a server and query the view if you don't believe me.</P>Mon, 07 May 2007 11:22:00 GMTHugo KornelisRE: Question of the Day for 07 May 2007http://www.sqlservercentral.com/Forums/Topic360104-120-1.aspxAh, it wasn't to trick you, but educate you that these are restart-restart counters. I think you could argue that they are set to zero, or nothing. It's just that you don't get any info if you query the view.I'll reword the answer and explanation to say they're reset.Plus if the answer is obvious and can be "guessed" then you might not bother to pay attention or look up the info.Mon, 07 May 2007 08:38:00 GMTSteve Jones - SSC EditorRE: Question of the Day for 07 May 2007http://www.sqlservercentral.com/Forums/Topic360104-120-1.aspxTDuffy: See my previous reply to Tao. Query this view on a just recycled server, and you get (almost) nothing.Mon, 07 May 2007 07:45:00 GMTHugo KornelisRE: Question of the Day for 07 May 2007http://www.sqlservercentral.com/Forums/Topic360104-120-1.aspxTricky question - I got caught myself, assuming that counters set to zero are not "nothing".Mon, 07 May 2007 07:44:00 GMTMike CRE: Question of the Day for 07 May 2007http://www.sqlservercentral.com/Forums/Topic360104-120-1.aspxCounters set to 0 are still providing info on how the indexes are used. The correct answer is A. Perhaps these questions should educate rather than try to trick.Mon, 07 May 2007 07:39:00 GMTTDuffyRE: Question of the Day for 07 May 2007http://www.sqlservercentral.com/Forums/Topic360104-120-1.aspx<P>I was torn between (A) and (D). I chose (A) because the question said that the user began using the server. My mistake was to assume the query "<FONT color=#00008b>sys.dm_db_index_usage_stats DMV" was NOT the first thing the user did. I assumed that they had actually did other things before this query was ran.</FONT></P><P>Oh well.</P>Mon, 07 May 2007 06:30:00 GMTxcmuchipRE: Question of the Day for 07 May 2007http://www.sqlservercentral.com/Forums/Topic360104-120-1.aspx<P>Tao: BOL is indeed misleading. You can try it if you have a test server at your disposal - run some queries, check that various indexes used show up in sys.dm_db_index_usage_stats, restart the server and then query sys.dm_db_index_usage_stats again.</P><P>&lt;nitpicking&gt;</P><P>You don't really get "no" information. You'll at least get a list of column names &lt;g&gt;. And you'll get an overview of what indexes were used during system startup. On my test database, startup apparently involved one scan of sysendpts and one scan of sysobjvalues.</P><P>If the server has some huge startup procedures, querying sys.dm_db-index_usage_stats right after a server restart <EM>might</EM> even be useful...</P><P>&lt;/nitpicking&gt;</P><P>Nitpicking aside, this is a great question. Many people still don't know about this view. And judging by the distribution of answers, many people don't know that it's fllushed on restart either.</P><P>Edit: One more nitpick - this view is not available on versions prior to SQL Server 2005.</P>Mon, 07 May 2007 01:32:00 GMTHugo KornelisRE: Question of the Day for 07 May 2007http://www.sqlservercentral.com/Forums/Topic360104-120-1.aspx<P>The "Right" answer is apparently a little misleading here... According to BOL, there would be entries in the DMV, they would just have all counters set to 0.</P><P><A href="http://msdn2.microsoft.com/en-us/library/ms188755.aspx">http://msdn2.microsoft.com/en-us/library/ms188755.aspx</A></P><P>Effectively, this would mean that the DMV DOES tell you something - it gives you a list of all the indexes used since the database itself was last shut down or detached.</P><P>But then maybe it's just unclear in the BOL article, and the rows really are all removed every time the service is started. Does anyone know? (I don't have 2005 handy to test...)</P><P>Thanks,</P><P>Tao</P><P> </P>Mon, 07 May 2007 00:22:00 GMTTao KlerksQuestion of the Day for 07 May 2007http://www.sqlservercentral.com/Forums/Topic360104-120-1.aspxComments posted to this topic are about the Question of the Day for 07 May 2007 posted at http://www.sqlservercentral.com/testcenter/qod.asp?QuestionID=1028.Fri, 20 Apr 2007 18:05:00 GMTSite Owners