Welcome to Sign in | Help

September 2006 - Posts

Did you know that you can contribute to MSDN documentation ?


This is a must see white paper written by Robert Dorr !

I won't say more but if you are a serious dba then you've got to read this:

SQL Server I/O Basics, Chapter 2

Script Repository: SQL Server 2005

If you follow the above link you will find scripts that will help you manage your SQL Server. To give you an idea of what you can get from this site I included the list of topics:

  • Buffer Cache
  • CPU and Optimization
  • Indexes and Indexing
  • Input/Output
  • Performance (General)
  • Processor Cache
  • SQL Text
  • Tempdb
  • Transactions and Logging
  • Waitstats

The comple list follows:

  • Buffer Cache
  • CPU and Optimization
  • Indexes and Indexing
  • Input/Output
  • Performance (General)
  • Processor Cache
  • SQL Text
  • Tempdb
  • Transactions and Logging
  • Waitstats
  • If you are a developer then you should take a look here:

    MSF for Agile Software Development Process Guidance

    MSF for CMMI® Process Improvement

    and the how to:

    Extracting Files and Password Needed Prompts

    By the way, do you know that a new MSF book is available ?

    Microsoft Solutions Framework Essentials

    According to Windows Server Division WebLog the Service Pack 2 for Windows 2003 will improve SQL Server 2005 performance by introducing some changes in Winsock API. However you won’t notice the change unless you have a server with 8 or more CPUs and multiple NICs.



    If you try to code a DDL managed trigger there is a small chance to have problems with the parameters of SqlTrigger attribute: Name, Target and Event.
    Here’s an example that works :

    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Data.SqlTypes
    Imports Microsoft.SqlServer.Server
    Partial Public Class Triggers
    <SqlTrigger(Event:="FOR DROP_TABLE", Name:="TriggerSample", Target:="DATABASE")>_
    Public Shared Sub TriggerSample()
    Dim triggContext As SqlTriggerContext
    triggContext = SqlContext.TriggerContext

    Select Case triggContext.TriggerAction
    Case TriggerAction.DropTable
    SqlContext.Pipe.Send("Table dropped! Here's the EventData:")
    End Select
    End Sub
    End Class

    Let's take a look at the following example from Books Online:

    DECLARE @price money

    SET @price=2500.00
    SELECT CatalogDescription.query('
    declare namespace pd="

               ProductID="{ sql:column("Production.Product.ProductID") }"
               ProductModelID= "{ sql:column("Production.Product.ProductModelID") }"
               ProductModelName="{/pd:ProductDescription[1]/@ProductModelName }"
               ListPrice="{ sql:column("Production.Product.ListPrice") }"
               DiscountPrice="{ sql:variable("@price") }"
    FROM Production.Product
    JOIN Production.ProductModel
    ON Production.Product.ProductModelID = Production.ProductModel.ProductModelID
    WHERE ProductID=771

    -- Output

    <Product ProductID="771" ProductModelID="19" ProductModelName="Mountain 100" ListPrice="3399.99" DiscountPrice="2500" />

    What do we have here:

    1. The xml result is constructed using the query() method and XQuery language.
    2. The value for ProductID is obtained from a non-XML column using the sql:column() function to bind this value in the XML.
    3. The same method is used for the value of ListPrice from a non-XML column of another table.
    4. The value of DiscountPrice is taken from a Transact-SQL variable using the sql:variable() function.
    5. Last the value of ProductModelName is taken from an xml type column.

    If you intent to take the 70-442 you should be familiar with all the above methods and functions: query(), sql:column(), sql:variable().

    If you want the complete answer you should take a look at the following post What are the different cached objects in the plan cache? from the SQL Programmability & API Development Team Blog.


     The following caches are included in procedure cache :

    1. Compiled Plans
    2. Execution plans 
    3. Algebrizer tree
    4. Extended Procs 
    5. Inactive Cursors


    If you are interested in taking the 70-431 exam you may find interesting this webcast: MSDN Webcast: Preparing for Exam 70-431 SQL Server 2005 Implementation and Maintenance (Level 200).


    In this presentation, we help prepare you for the Microsoft Certified Technology Specialist Exam 70-431 Microsoft SQL Server 2005 Implementation and Maintenance. We direct you to freely available information about what Exam 70-431 covers and point to material that might help you pass the test. In addition, we drill down on three selected topics that are relevant to the exam: how to perform log backups and restorations for a database, an overview of using SQL Server Profiler, and working with the Database Engine Tuning Advisor.

    Presenter: Rick Dobson, Author/Trainer/Mentor, CAB, Inc.

    Rick Dobson is the author of the PrepLogic guide and sample exam questions for the Microsoft Certified Technology Specialist Exam 70-431 Microsoft SQL Server 2005–Implementation and Maintenance.



    If high-availability is one of your interests then you should definitely take a look at this white paper: SQL Server 2005 Failover Clustering White Paper


    You can find complimentary information to clustering topics from Books Online such as planning, implementing, and administering of a failover cluster as well as differences between SQL Server 2000 and SQL Server 2005 clustering.

    Thursday, September 7, 2006 at 9:30am ) Pacific Time, Paul A. Mestemaker II, Program Manager in Microsoft SQL Server team will deliver the following webcast TechNet Webcast: Using the SQL Server Upgrade Advisor and New SQL Server 2005 Best Practices Analyzer Tools (Level 200).
    To get more information use the following links: