NetInverse Developers Blog

March 7, 2009
SQL DMV and CROSS APPLY
Category: SQL — Tags: , — admin @ 11:38 pm

Use DMV and CROSS APPLY to Get Details of SQL’s Cached Query Plans

SQL 2005’s dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. For example, following query fetches SQL server’s cached query plans. You can use the refCounts and useCounts for performance analysis.

       SELECT  cached.*,
               sqltext.*
         FROM  sys.dm_exec_cached_plans cached
  CROSS APPLY  sys.dm_exec_sql_text (cached.plan_handle) AS sqltext

You can click here to see the screen snapshot of the query output.

Note: CROSS APPLY, a new feature of SQL Server 2005, is quite simple: an INNER (or outer) JOIN between a table and a table-valued function.

sys.dm_exec_sql_text: Returns the text of the SQL batch that is identified by the specified sql_handle. This table-valued function replaces the system function fn_get_sql.

sys.dm_exec_cached_plans: Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

Default Trace in SQL Server 2005
Category: SQL — Tags: — admin @ 11:29 pm

SQL Server 2005 has a default trace running. Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur. You can execute the following command to view it:

SELECT * FROM fn_trace_gettable(
   'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc', default);
GO

The trace is stored in the LOG directory for your SQL Server instance (i.e. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\) at the root of where your SQL Server is installed. To determine if the trace is configured to run, execute sp_configure and review the ‘default trace enabled’ option. When the config_value and run_value are equal to 1, then this trace is running. You can use sp_configure command to turn it off too.

You can search “Default Trace enabled Option” in the SQL Help to find more detailed information about the default trace.

Move SQL DB File to New Location
Category: SQL — Tags: — admin @ 11:22 pm

The server has two drives, and the default location for tempdb is c:\mssql7\data, can we change it to somewhere in D drives?

Some one said that he used T-SQL to move it before. While someone told me that it can not be moved. Do you know how?

Thank you!

As the question asked above, you may want to move your database to a new location for better performance. Besides Detach/Attach database, you can simply follow the steps below using ALTER DATABASE MODIFY FILE:

    -- Step1: put the database offline
    ALTER DATABASE [Products] SET OFFLINE;
    GO

    -- Step2: move the file to the new location
    ALTER DATABASE [Products] MODIFY FILE (NAME = [Products],
          FILENAME = 'd:\mssql\data\Products.mdf')
    GO

    -- Step3: bring the database online
    ALTER DATABASE [Products] SET ONLINE
    GO
Use COALESCE (Transact-SQL) to generate a list
Category: SQL — Tags: , — admin @ 11:13 pm

COALESCE (Transact-SQL)

Returns the first nonnull expression among its arguments. (From MSDN)

COALESCE is essentially same as below:

            CASE
               WHEN (expression1 IS NOT NULL) THEN expression1
               ...
               WHEN (expressionN IS NOT NULL) THEN expressionN
               ELSE NULL
            END

Example:

In this sample, we use COALESCE to generate a flat list. You can specify a delimiter to seperate the items in the list.

            CREATE TABLE [dbo].[student](
                [id] [int] NOT NULL,
                [name] [nvarchar](50),
                [ssn] [varchar](11),
             CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED
            (
                [id] ASC
            )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
            ) ON [PRIMARY]
            GO

            INSERT student VALUES(1, 'andrew', '201-98-9238')
            INSERT student VALUES(2, 'lindsay', '656-89-9238')
            INSERT student VALUES(3, 'david', '555-22-1111')
            GO

            DECLARE @list varchar(1024)
            SELECT @list = COALESCE(@list + '|', '') + name
            FROM student

            PRINT @list

Here is the result set:

            andrew|lindsay|david
SQL 2005: New Resource Database
Category: SQL — Tags: — admin @ 11:02 pm

SQL 2005 has introduced a new system database referred as Resource database, namely RDB. Its mdf file name is ‘mssqlsystemresource.mdf’. Resource database is a read-only database that contains all the system objects that are included with SQL Server 2005. All system objects such as sys.objects are physically stored in the Resource database, but they logically appear in the sys schema of every database.

You can run

    SELECT [name], object_definition(object_id) from sys.system_objects

from any database, which will give a same output.

SQL 2005: How to rebuild the master database
Category: SQL — Tags: — admin @ 10:53 pm

The master database stores your logins and, most importantly, the pointers to all of your databases. Without the master database, you can’t successfully start SQL Server. To recover your master database, you need to use the Rebuild Wizard (Rebuildm.exe), located in the \Program Files\Microsoft SQL Server\80\Tools\BINN directory.

However, you may not know that RebuildM.exe utility has retired since SQL 2005. In case your system database gets corrupted, how you rebuild Master database?

You will need to use the setup.exe on the installation CD to rebuild system databases. RebuildM.exe now has been merged into the main setup program.

March 5, 2009
Dynamic SQL Query Performance
Category: SQL — Tags: , , — admin @ 9:41 pm

Dynamic(Ad hoc) SQL query can be as performant as stored procedures if you use it correctly. SQL server compiles it, caches the compiled query plan and re-uses it next time. If not, SQL server may have to re-compile the query every time, and which can be sometimes 100 times slower!

For example:

     String query = @"SELECT *
                        FROM employees e
                  INNER JOIN EmployeeDetails et
                          ON e.employeeid = et.employeeid
                       WHERE e.employeeid = @id";

     sqlCmd.CommandType = System.Data.CommandType.Text;
     SqlParameter idParam = new SqlParameter(
         "@id", System.Data.SqlDbType.Int, 0);
     sqlCmd.Parameters.Add(idParam);
     sqlCmd.Parameters[0].Value = 100;
     sqlReader = sqCmd.ExecuteReader();

If you use SQL Tracer, you will see the SQL server executes following statement:

 EXEC sp_executesql N'SELECT * FROM employees e
 INNER JOIN EmployeeDetails et ON e.employeeid = ee.employeeid
 WHERE e.employeeid = @id', N'@id int',@id=100

In this case, SQL server can cache the query plan and re-used it effectively. Because the statement is parameterized, even the value of @id changes, the compiled statement remains same and can be always re-used.

A common mistake of using dynamic SQL is like below:

      String query = @"SELECT *
                         FROM employees e
                   INNER JOIN EmployeeDetails et
                           ON e.employeeid = ee.employeeid
                        WHERE e.employeeid = " + id.ToString();

     sqlCmd.CommandType = System.Data.CommandType.Text;
     sqlReader = sqCmd.ExecuteReader();

If you trace again, this time you will see SQL server executes the following instead:

 SELECT * FROM employees e INNER JOIN EmployeeDetails et
 ON e.employeeid = ee.employeeid WHERE e.employeeid = 100

SQL server can cache and re-use above statement as well. However, since the statement is not parameterized, if the
employeeid changes, SQL server will have to re-compile the statement. The consequence is low cache hit and SQL server will have a lot of recompiling, the overall performance will be poor.

So the take away here is, you can use dynamic SQL query, but do use SqlParameter to make your dynamic SQL statement parameterized.

« Newer Posts

©2009 NetInverse. All rights reserved. Powered by WordPress