I got some more feedback (see yesterday's blog post on this) which I now incorporated into sp_indexinfo. See change log at bottom of the article for details. http://www.karaszi.com/SQLServer/util_sp_indexinfo.asp Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

(See my initial blog post for general information about this proc.) I just updated sp_indexinfo a bit: I added the schema name as a new column in the output of the first resultset. I added an optional second resultset with missing index information. This information is obviously drawn from the missing index dynamic management views. ...

I am. I find myself endlessly hunting for index information when working against the various SQL Servers I come in contact with. And, sure, the information is there. You just need to go and get it. This generally means that I start with sp_helpindex. Then some SELECT from sys.indexes. Then some more against sys.partitions and sys.allocation units ...

(See my part 1 article about non-trusted constraints in general: http://sqlblog.com/blogs/tibor_karaszi/archive/2008/01/12/non-trusted-constraints.aspx) The optimizer is a pretty smart little animal. It can even use constraints to eliminate some data access or part of a query. That assumes however that the constraint is trusted. For example, for ...

It isn't easy to remember all the details regarding object/schema qualifying object names. There are many variables involved, such as: Version of SQL Server Qualifying the owner when you call the proc (EXEC dbo.procname) Qualifying object references inside the proc code Qualifying object names if you aren't using procedures Whether the user ...

Prompted by one of those daily ''SQL Tip'' emails a few days ago that recommended the correlated subquery to solve the cumulative total problem, this 5 minute screencast demonstrates that the cursor solution indeed out-scales the set-based correlated subquery for this particular problem.

All of us have heard that if you just add memory to your system, it will work faster.  This is generally accepted and it makes alot of sense, but what is the math behind it? About 2 years ago, I heard the presenter of a webcast that I was viewing put these two elements in a perspective that I relay on to others when speaking about ...

Best Practices are good enough to follow, but simply do not read and digest. Try to implement them within your environment to keep up the performance. Similary there are many things involved within the SQL Server 2005 Analysis Services such as MDX queries, data mining etc. For any sort of performance analysis exercise you must identify the ...

Though the message specifies as an informational message only and no user action is required, you should take some action to avoid current behaviour of databases within your environment. Also check whether you have enabled the AUTO_CLOSE property for any of the databases on that SQL Server instance, on the down side it is not recommended in ...

PARALLELISM is a close friend of DBA where you get to see it as a common occurence in the multi-processor server environment and during such performance issues you can also investigate whether a parallel plan is in use. For instance If a particular query is slow when it is using a parallel plan, you can try forcing a non-parallel plan by using the ...