Louis Davidson

Louis is the editor of this Simple-Talk website. Prior to that, has was a corporate database developer and data architect for a non-profit organization for 25 years! Louis has been a Microsoft MVP since 2004, and is the author of a series of SQL Server Database Design books, most recently Pro SQL Server Relational Database Design and Implementation.

Follow Louis Davidson via
26 August 2007
26 August 2007

sys.dm_db_index_operational_stats

0
1
  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 … Read more
0
1
06 August 2007
06 August 2007

sys.dm_exec_query_optimizer_info

0
0
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. Type: View Data: Accumulating reset at restart Columns: counter – Name of the query occurrence – Number of times the counter was recorded to … Read more
0
0
27 July 2007
27 July 2007

sys.dm_io_virtual_file_stats

0
1
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 … Read more
0
1
22 July 2007
22 July 2007

sys.dm_db_index_usage_stats

0
1
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 … Read more
0
1
14 July 2007
14 July 2007

sys.dm_db_file_space_usage

0
0
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 … Read more
0
0
11 July 2007
11 July 2007

sys.dm_db_partition_stats

0
1
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 object/partition drop … Read more
0
1
10 July 2007
10 July 2007

Blogging the DMV’s

0
0
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 … Read more
0
0
07 May 2007
07 May 2007

Using application locks to implement a critical section in T-SQL code

0
3
This is being added to the addendum (located here) for my “Pro SQL Server 2005 Database Design and Optimization” book and would have appeared in the pessimistic locking section on page 478 in Chapter 9: Coding for Integrity and Concurrency; Pessimistic Locking. You can download the entire addendum here: Addendum. The problem of the critical … Read more
0
3
03 April 2007
03 April 2007

SQL Server Search Macro (plus build your own directed search!)

0
0
Microsoft has built a little site for searching the books online on the web (which gives you links that are more readily shared with others): http://search.live.com/macros/sql_server_user_education/booksonline That is nice, but what is nicer is that you can create your own search macros and save them.. Just click the “create your own search engine” and go … Read more
0
0
26 February 2007
26 February 2007

Ten Common Database Design Mistakes

0
1883
If database design is done right, then the development, deployment and subsequent performance in production will give little trouble. A well-designed database 'just works'. There are a small number of mistakes in database design that causes subsequent misery to developers, managewrs, and DBAs alike. Here are the ten worst mistakes … Read more
0
1883