Wednesday, May 28, 2008

SQL Server DDL Logout Trigger?

While SQL Server 2005/2008 have a DDL Login Trigger Event, they don't have a Logout event.  This was brought up on the SQL Forums in a post in the Database Engine Forum, and I found it odd that SQL lacked the opposite event.  Oracle has Logout Triggers, so it seemed odd that SQL would be lacking this for comparisons.  An interesting solution was posted by Jim McLeod that uses the Service Broker to accomplish the same type of action.  He also posted his code on his blog.

I would tend to agree with the comments on the post that the original purpose for this request isn't really a SQL Server issue, but more of an application issue, and delegating the cleanup of a connection to SQL like this is misplaced.  The connection should be in an explicit transaction that rolls back if the user disconnects.  To me this seems like the best scenario for the cleanup of what the connection has done.  However, Jim's solution was interesting enough to warrant blogging about.

Thursday, May 22, 2008

Follow Up: VMware Forums Orlando

I had a number of questions asked this afternoon both during and after I presented on running SQL Server virtualized with VMware.  In order to make this easily distributable, and cover all the topics completely, I decided to use my blog as a central point to provide follow up.

  1. If you want a copy of the slide deck from the presentation, you can download it from here.

  2. A question was raised regarding the use of multiple files for a database to match the number of processors on the SQL Server. This is a contested subject, and various experts will argue different sides of it. There is a posting in the PSS Blog from Microsoft Full Timer Robert Dorr regarding this subject:

    http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx

    This posting discusses this question for both user databases as well as tempdb. It is the most recently set of information available at this point in time.

  3. Another question involved running SQL Server on x86 versus x64. Microsoft has a whitepaper that covers the advantages of a 64 bit environment. From a personal implementation perspective, the first migration from SQL 2000 to SQL 2005 I made in VMware was an x86 to x64 migration, and I have only gone to 64 bit since.  The performance difference is considerable, and the ability to scale RAM beyond 4GB without PAE and AWE, while gaining an extension of the VAS, makes it worth doing alone.  If your ESX host hardware is 64 bit then you should definitely consider this migration.

    Since this requires that you reinstall the OS as 64 bit as well, this is where I built the server disconnected from the network. To do this, you have to schedule a brief outage of your existing production server to allow you to name the new server the same name and join it to your domain. Once this is done, you can take the new server off the network, and start the SQL 2000 server again. Then you simply work on the new server using the Virtual Infrastructure Client, and install and configure SQL as needed. Transfer your Logins to the new server using the appropriate scripting code from the following link:

    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=TransferLogins&referringTitle=Home

    Once the new server is ready, to migrate over, shut down the SQL 2000 server.  Detach the database storage LUN from the old server and attach it to the new server and power on the SQL 2005 server.  Attach the individual databases to the SQL Instance.  From there follow standard upgrade procedures, rebuilding indexes and updating statistics, and test the applications out.

  4. SQLIO, the tool used in providing the disk subsystem benchmark results is available for download from Microsoft.  Included with it is a document that details how to use it.  Ideally, you will be able to run this on a system prior to installing SQL Server on it, and prior to it going into production.

  5. Prior to migrating or planning to migrate into VMware, it is important to know where you are starting performance wise.  In an ideal world, you would be monitoring SQL Server consistently either with perfmon, or one of the commercially available tools like Spotlight on SQL, or SQL Diagnostic Manager.  (My graphs were created from a .NET application I wrote so they won't look like either of these.)  If you decide to go the PerfMon route, the counters you need to collect can be found on Kevin Kline's Screencasts.

If there are further questions, please feel free to contact me through my blog profile and I will answer them.

Wednesday, May 21, 2008

SQL Server Management Studio Tools Pack 1.0

SSMS Tools PACK is an Add-In (Add-On) for Microsoft SQL Server Management Studio and Microsoft SQL Server Management Studio Express. Mladen Prajdić developed this tool set to solve some of his own problems with using SSMS, and has made them available to the SQL Community.I am impressed with it overall. The features it offers as listed on the website are:

  • Uppercase/Lowercase keywords.
  • Run one script on multiple databases.
  • Copy execution plan bitmaps to clipboard.
  • Search Results in Grid Mode and Execution Plans.
  • Query Execution History (Soft Source Control).
  • Text document Regions and Debug sections.
  • Running custom scripts from Object explorer's Context menu.
  • CRUD (Create, Read, Update, Delete) stored procedure generation.
  • New query template.
  • Generate Insert statements for a single table, the whole database or current result sets in grids.

I really like the first one and the last one in this list. I hope you will give it a look.

Friday, May 9, 2008

Using a CLR TVF to SELECT from the SQL Error Log

One of the things I have always wanted to do, but have never been able to easily is to select from the ErrorLogs as if they were tables. To do this in the past has always required creating a temp table, inserting the results of the xp_readerrorlog or sp_readerrorlog into it, and then selecting out of that table. By using a CLR TVF with EXTERNAL_ACCESS, you can easily be able to select from the ErrorLogs as if they were actual tables in a simple statement.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
 
/*=========================================================================================

  File:      fn_ReadErrorLog.cs
  Summary:   Table-valued function to retrieve the SQL Server Error Log for the
             current instance as a table.
  Parameter: LogNumber - The Log Number in the ErrorLogs to Read.
             Specify NULL or 0 to refer to active log
  Date:      May 9, 2008

---------------------------------------------------------------------
 
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

============================================================================================ */

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(Name="fn_ReadErrorLog", 
        FillRowMethodName = "FillRowErrorLogReader", 
        DataAccess = DataAccessKind.Read, 
        TableDefinition = "LogDate datetime, ProcessInfo nvarchar(100), Text nvarchar(4000)")]
    public static IEnumerable InitMethodErrorLogReader(int LogNumber)
    {
        List<string[]> logitems = new List<string[]>();
 
        string servername;
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "select serverproperty('ServerName')";
                servername = (string)cmd.ExecuteScalar();
            }
            conn.Close();
        }
 
        using (SqlConnection connection = new SqlConnection("Server=" + servername + ";Integrated Security=true"))
        {
            connection.Open();
            SqlCommand sqlCommand = connection.CreateCommand();
            sqlCommand.CommandText = "exec xp_readerrorlog " +LogNumber.ToString();
            SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

            while (sqlDataReader.Read())
            {
                string[] item = new string[3];
                item.SetValue(sqlDataReader["LogDate"].ToString(), 0);
                item.SetValue(sqlDataReader["ProcessInfo"].ToString(), 1);
                item.SetValue(sqlDataReader["Text"].ToString(), 2);
                logitems.Add(item);
            }
        }
        return logitems;
    }

    public static void FillRowErrorLogReader(object obj, out SqlDateTime logDate,
        out SqlString processInfo, out SqlString text)
    {
        string[] item = (string[])obj;
        logDate = (SqlDateTime)DateTime.Parse(item[0].ToString());
        processInfo = (SqlString)item[1].ToString();
        text = (SqlString)item[2].ToString();
    }
};


This Example will also be posted on SQLCLR.net in the coming days.

Monday, May 5, 2008

Monitoring the SQL Plan Cache.

Something that isn't often monitored by DBA's but should be is the contents of the Procedure Cache. In SQL 2005, this can be done through the DMV's, and a lot of data is available for a DBA to analyze by querying a single DMV:
sys.dm_exec_cached_plans





For each row returned, there is a plan_handle that will allow you to view the specific query that the plan exists for by using a CROSS APPLY of the following system function:



sys.dm_exec_sql_text()



We can see some important information about the queries that are or are not being properly cached in the Procedure Cache by the database engine.  Microsoft has provided some scripts on the Scripts site that allow you to look at how efficiently your queries are functioning. 



http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/proc/default.mspx?mfr=true



A problem which will be addressed in a later posting is cache bloat caused by the execution of adhoc, non-parameterized queries.

Sunday, May 4, 2008

Things to know about the SQL 2008 Date/Time Datatype

I was researching a question made on the SQL Forums regarding the return results of the SQL 2008 Date/Time datatype. If you run the following code, the output is a full datetime output in the Query Results.

SELECT CAST(GETDATE() AS DATE)

The issue in question was already reported in a connect Feedback and identified as an error by Microsoft, however they posted it as fixed and under test 2/7/2008 so the issue still exists in the Feb CTP which is what brought up the question.

However, while searching for this I happened upon another Feedback that provides some good return information from Microsoft about the implicit conversions that occur during usage of the various date/time datatypes in SQL 2008. Like all other datatypes in SQL, the newer date/time types introduced in 2008 have a conversion hierarchy which is (highest to lowest):

  • DATETIMEOFFSET
  • DATETIME2
  • DATETIME
  • DATE or TIME

What this means is that when a DATE variable is compared to a DATETIME datatype, the DATE variable will be implicitly converted to a DATETIME. This is important to keep in mind when using these datatypes.

The other important thing that the feedback on this page provides is that the output of the DATEADD() function in TSQL has a DATETIME return datatype. This means that if you have a DATE variable @DateVar that you are doing a comparison with using

DATEADD(dd, 1, @DateVar)

against a DATE column in a table, you force an implicit conversion of the column in the table to the DATETIME datatype for the matchup.

Thursday, May 1, 2008

Speaking at the Orlando VMWare Forum

I will be speaking at the VMWare Forum in Orlando on May 22, 2008. If you are interested in virtualizing SQL Server, or seeing how SQL Server can perform when virtualized, I will be covering these topics at this event.