10 logical connections is a hard limit (see http://blogs.msdn.com/angelsb/archive/2005/01/13/352718.aspx and http://blogs.msdn.com/angelsb/archive/2004/09/07/226597.aspx for more details). If you go over this limit, it gets expensive from a performance standpoint. Also see ...

I asked for some names of people who you would like to see interviewed here at Sqlblog and Erland Sommarskog's name popped up a couple of times. I contacted Erland and he was kind enough to take time out from his busy schedule to answer these questions.  So, here are the questions. What are the most important things a person can do ...

Ok, I admit it.  Sometimes the least important things are the most fun.  As I try to get my blog back up and kicking again after a few months of holiday fun coupled with some dreary personal life things (a death in the family and lots of sickness/busyness, mostly,) I felt the need to write about another little time saving feature that ...

In 2005, rebuilding a table that was a heap (no clustered index) wasn't easy.  You could copy it to a different table, or you could add a clustered index and then drop it. In 2008, this is a far easier thing to do.  They have added to the ALTER TABLE command a method to rebuild the table, which is the same as rebuilding the clustered ...

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

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

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

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

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

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