Browse by Tags

• Best Practices
• Catalog Objects
• Concurrency
• Constraints
• Database Design
• Denali
• Design
• Development
• Dynamic Management Objects
• MVP
• PASS
• Performance
• PreCon
• Security
• Speaking
• SQL Saturday
• SQL Server
• SQL Techniques
• SQL Tools
• SQLPASS
• System Functions
• Tips
• Triggers
• Utility
• Why We Write
• Writing

• 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 Read More... Posted Monday, August 06, 2007 8:40 PM by drsql | 1 Comments Filed under: SQL Server, T-SQL, Dynamic Management Objects

• 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, Read More... Posted Friday, July 27, 2007 9:26 PM by drsql | 12 Comments Filed under: SQL Server, T-SQL, Dynamic Management Objects

• 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 ), Read More... Posted Sunday, July 22, 2007 10:45 PM by drsql | 9 Comments Filed under: SQL Server, T-SQL, Dynamic Management Objects

• 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 Read More... Posted Saturday, July 14, 2007 3:35 PM by drsql | 4 Comments Filed under: SQL Server, T-SQL, Dynamic Management Objects

• 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: Read More... Posted Wednesday, July 11, 2007 9:41 PM by drsql | 3 Comments Filed under: SQL Server, T-SQL, Dynamic Management Objects

• 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: Read More... Posted Tuesday, July 10, 2007 12:14 AM by drsql | 4 Comments Filed under: SQL Server, T-SQL, Dynamic Management Objects

• 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 Read More... Posted Thursday, February 08, 2007 9:59 PM by drsql | 2 Comments Filed under: SQL Server, T-SQL

• 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 Read More... Posted Wednesday, December 13, 2006 7:25 PM by drsql | 4 Comments Filed under: SQL Server, T-SQL, Concurrency

• The joys of a calendar table (ytd, month, and quarter summations)

  There was a question on the forums the other day that asked how to get a total for the current month, the current quarter, and year to date. It is actually a pretty easy task when using a calendar table. I have one you can use here (I fixed an error in Read More... Posted Saturday, December 09, 2006 8:13 PM by drsql | 1 Comments Filed under: SQL Server, T-SQL, SQL Techniques

• There is still stuff I hadn't noticed (OBJECT_ID has a parameter for type of object)

  I was building a script to build a table I had built in my dev environment including inserts so I did a bit of hunting around and came upon Vyas's script ( http://vyaskn.tripod.com/ ) that is used to generate inserts from a table (script here: http://vyaskn.tripod.com/code/generate_inserts_2005.txt Read More... Posted Sunday, December 03, 2006 4:42 PM by drsql | 0 Comments Filed under: SQL Server, T-SQL, System Functions

• Constructive versus Deconstructive

  I have been struggling to find a way to describe the fundamentals of first normal form for my PASS presentation, and this came to mind last night. SQL works in a very constructive way, meaning that if you have base values (commonly referred to as atomic, Read More... Posted Wednesday, October 18, 2006 7:56 PM by drsql | 0 Comments Filed under: T-SQL, Database Design

• Another reason to hate quoted identifiers...

  I was working with the Redgate compare tool today (something I am a big fan of I might add,) using it to take a snapshot of my database so I could drop all code and do a collation change to a database (an article I updated because I discovered a new wrinkle Read More... Posted Sunday, October 01, 2006 8:06 PM by drsql | 0 Comments Filed under: SQL Server, T-SQL

• Easier access to metadata coming soon to a SQL Server near you

  This is not a scoop or anything, but it is quite newsworthy. It is also a good way to kick off my sqlblog er...blog: Kalen Delaney - Metadata Power Simon Sabin - object_name() to be extended in SP2 A very common request is to have some of the system functions Read More... Posted Monday, September 25, 2006 7:35 PM by drsql | 0 Comments Filed under: SQL Server, T-SQL, System Functions