August 2007 Blog Posts
When SQL Server receives a new query, it attempts determine the best possible plan for resolving that query. It considers a number of different factors as it analyzes the query and maps out a way in which to retrieve the information requested.
Whether or not the query optimizer deems an index to be useful in resolving a query largely depends on the information contained in the statistics for that index.
If the statistics are outdated and do not accurately represent the distribution of values within the table, the query optimizer may not produce an optimal plan for resolving the query. Misleading statistics may result in...
As a consultant, I regularly need to determine when a stored procedure was last altered. Without having implemented a series of DDL triggers, how can this be accomplished?
In Microsoft SQL Server, you can easily retrieve this information from the sys.procedures catalog view. The following query demonstrates this.
SELECT [name] ,modify_date ,create_date ,*FROM sys.procedures
Of course you can take it a step further by limiting the results to a period of time where you know that no changes should have been made. For example, the following...
As database professionals, we may responsible for dozens, if not scores, of SQL Servers throughout our department or enterprise. Now that Microsoft has announced that Notification Services will not ship as part of the SQL Server 2008 product, how can you readily identify which of the servers in your charge have SSNS instances installed?
Fortunately for us it's rather easy. SSNS 2005 registers each installed instance in the msdb system database. The following query returns a list of every SSNS instance for the SQL Server instance.
SELECT
*
FROM
msdb.NS90.NSInstanceInfo
To retrieve a list of all SSNS applications for the...
For the past three years of so, I've been blogging about my experiences with Microsoft SQL Server and the lessons I've learned along the way. During that time, I posted some pretty good tutorials, if I do say so myself. In particular, I regularly commented on an often underutilized facet of SQL Server called Notification Services.
For those not very familiar with the topic, Notification Services was initially released as add-on to SQL Server 2000. It's a highly scalable development framework and hosting platform for notification applications. It's based on technologies that most of us already know and love -...