THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Browse by Tags

All Tags » SQL Server » T-SQL   (RSS)
Showing page 5 of 6 (54 total posts)
  • Changing the owner of a database

    Tonight, as I was creating my sample database for my chapter on implementing the database, I learned something new, that existed in 2005.  I had always used sp_changedbowner to change the owner of a database, but I was reading in another section about ALTER AUTHORIZATION earlier to change the owner of an object in a schema, so I said to my ...
    Posted to Louis Davidson (Weblog) by drsql on December 5, 2007
  • sys.dm_db_index_operational_stats

      This object provides very useful stats on how many times an index has been used, locked, waited on, etc. An index in this context can mean several things: a clustered index, heap, index, or a partition of either of these. The grain of the function is down to the partition level, so if you are working with a table that is partitioned into ...
    Posted to Louis Davidson (Weblog) by drsql on August 26, 2007
  • sys.dm_exec_query_optimizer_info

    Information about how queries have been optimized since the server has been restarted. Note that counters are only affected when there is some sort of optimization event, not on every query. (reference: http://sqlserver-qa.net/blogs/perftune/archive/2007/05/11/get-statistics-on-query-plan-optimizer-execution.aspx ) Type: View Data: Accumulating ...
    Posted to Louis Davidson (Weblog) by drsql on August 6, 2007
  • sys.dm_io_virtual_file_stats

    Excellent dmv that shows, for each file that SQL Server uses for the databases, stats on how frequently the file has been used by the database. This is one of the primary dynamic management views I use almost daily (well, not usually when I am on vacation, but even sometimes then!)  The file can be any file used in the database, including the ...
    Posted to Louis Davidson (Weblog) by drsql on July 27, 2007
  • sys.dm_db_index_usage_stats

    This object gives statistics on how an index has been used to resolve queries. Most importantly it tells you the number of times a query was used to find a single row (user_seeks), a range of values, or to resolve a non-unique query (user_scans ), if it has been used to resolve a bookmark lookup (user_lookups) and how many changes to the index ...
    Posted to Louis Davidson (Weblog) by drsql on July 22, 2007
  • sys.dm_db_file_space_usage

    Give space usage of objects in tempdb (most likely this will be extended to more than just tempdb in a future edition of SQL Server.) Can be used to see how and why space is being used in Tempdb, on a file by file basis. Type: View Data: temporal, reflects the current state of the file usage Columns: database_id – identifies the database (relates ...
    Posted to Louis Davidson (Weblog) by drsql on July 14, 2007
  • sys.dm_db_partition_stats

    For the current database, gives you space oriented statistics for each partition of indexes (even if you only have one partion), like row count, page counts, etc. Resembles the sysindexes in previous versions of SQL Server, with more information. Type: view Data: accumulating, refreshed at server restart Scope: Reset on server restart (or ...
    Posted to Louis Davidson (Weblog) by drsql on July 11, 2007
  • Blogging the DMV's

    Well, I am working on a project that is going to be a book on dynamic management views (and functions, but DMF is an interesting acronym, and objects, as in DMO, has a well known other meaning.  What I will post will be one object in the following format: Name of object Type: Function or View Parameters: If it is a function Scope: Really ...
    Posted to Louis Davidson (Weblog) by drsql on July 10, 2007
  • Getting the difference between two sets of like data

    A question came up on the forums the other day that a person wanted to compare two tables of data.  Doing this is pretty easy using several techniques:   Using EXCEPT Using NOT EXISTS Using a FULL OUTER JOIN First two sample tables which represent two sets of data that you would get from a statement, from clause, etc.  ...
    Posted to Louis Davidson (Weblog) by drsql on February 8, 2007
  • Does XLOCK always prevent reads by others?

    I was going back through my files of draft blogs for the past 6 months (particularly looking for things that are wrong in my book,) and I found this one that covers something that I found really fascinating.)  In this thread, a person was attempting to create a pessimistic lock by applying an XLOCK in a hint.  Seemed reasonable to me, ...
    Posted to Louis Davidson (Weblog) by drsql on December 13, 2006
Privacy Statement