Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Question of the Day for 07 May 2007 Expand / Collapse
Author
Message
Posted Friday, April 20, 2007 6:05 PM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
Comments 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.
Post #360104
Posted Monday, May 7, 2007 12:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:38 PM
Points: 1,385, Visits: 1,249

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.

http://msdn2.microsoft.com/en-us/library/ms188755.aspx

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.

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...)

Thanks,

Tao

 



http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
Post #363580
Posted Monday, May 7, 2007 1:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:18 PM
Points: 6,056, Visits: 8,342

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.

<nitpicking>

You don't really get "no" information. You'll at least get a list of column names <g>. 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.

If the server has some huge startup procedures, querying sys.dm_db-index_usage_stats right after a server restart might even be useful...

</nitpicking>

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.

Edit: One more nitpick - this view is not available on versions prior to SQL Server 2005.




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #363588
Posted Monday, May 7, 2007 6:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 16, 2008 12:06 PM
Points: 12, Visits: 4

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 "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.

Oh well.




Post #363620
Posted Monday, May 7, 2007 7:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2008 12:02 PM
Points: 1,318, Visits: 57
Counters 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.


Post #363644
Posted Monday, May 7, 2007 7:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
Tricky question - I got caught myself, assuming that counters set to zero are not "nothing".
Post #363648
Posted Monday, May 7, 2007 7:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:18 PM
Points: 6,056, Visits: 8,342
TDuffy: See my previous reply to Tao. Query this view on a just recycled server, and you get (almost) nothing.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #363649
Posted Monday, May 7, 2007 8:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:34 PM
Points: 31,204, Visits: 15,649
Ah, 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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #363675
Posted Monday, May 7, 2007 11:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:18 PM
Points: 6,056, Visits: 8,342

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).

Cycle a server and query the view if you don't believe me.




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #363760
Posted Tuesday, August 3, 2010 12:15 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 4:19 AM
Points: 1,437, Visits: 1,847
Hello!

I am confused as well. Here's what I did (on a SQL 2008 box):
1. Ran the query mentioned
2. 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 #1


Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #963039
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse