How to execute SQLCMD from SSIS

If you have ever needed to execute SQLCMD from an SSIS package, you can use the Execute SQL Task component along with xp_cmdshell to do the job.

I used the following script inside the Execute SQL Task component to make it work:

DECLARE @Server SYSNAME
DECLARE @SQL NVARCHAR(500)
SET @Server = @@SERVERNAME

SET @SQL = 'EXEC xp_cmdshell ''SQLCMD -S ' + @Server + ' -d <DBNAME> -q "<SomeProc>"'

EXEC sp_executesql @sql

Dynamic SQL vs. Stored Procedures

     So much has been written about the pros and cons of Dynamic SQL vs. Stored Procedures, with a lot of venom coming from both sides. As a DBA, I really push hard for the use of Stored Procedures as the only way to access or manipulate data, but as an ex-developer, I see the allure of Dynamic SQL.

     The purpose of this post is not add to yet another article to the "flame ridden" posts already out there, but to get a sense of where people stand and what people are using in their shops. Please reply to this post with which side you come down on, your job title (if applicable), and either an existing article or brief summary that best describes why you feel the way you do.

LINQ to SQL and Entity Framework vs. Stored Procedures

An interesting presentation by SQL MVP Bob Beauchemin outlining the pros and cons of LINQ to SQL and Entity Framework vs. Stored Procedures.

Finding Stored Procedures Related to Replication

If you have transactional or merge replication configured in your environment, you may want to know what objects were create by replication in your subscribing database. For Stored Procedures, there’s nothing in the sys.objects or sys.procedures catalog views that denotes that a procedure is used with replication. Fortunately, there is a system table created in the subscribing database called MSreplication_objects. The query below will give you the Stored Procedures created by replication and the articles associated with them.

SELECT [publisher]
      ,[publisher_db]
      ,[publication]
      ,[object_name]
      ,[object_type]
      ,[article]
  FROM [MSreplication_objects]
 WHERE [object_type] = 'P'

Now that we know the procs that are related to replication, we can exclude them from sys.objects or sys.procedures when looking for user procs in the database.

SELECT *
 FROM   sys.procedures
 WHERE  name NOT IN (SELECT [object_name]
                     FROM   MSreplication_objects
                     WHERE  [object_type] = 'P')

Backing Up an Analysis Services Database

There are a lot of DBA’s out there who have to administer SQL Analysis Services, but haven’t the slightest clue on how to do anything more than set it up and grant security. After the database has been setup and cubes are being created, it will need to be backed up just like the SQL databases you are already familiar with. So how do you backup an Analysis Services database when you can’t use the T-SQL commands you know and love? The answer is Analysis Services commands.

Start by opening up a new Analysis Services XMLA Query and log into the Analysis Services instance. You can do this by selecting File -> New -> Analysis Services XMLA Query

It will open up what looks to be a T-SQL window but it will only accept XML commands against the Analysis Services database. The following query will back up a single Analysis Services database without encryption or compression.

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <Object>
    <DatabaseID>Insert SSAS Database name here</DatabaseID>
  </Object>
  <File>file_location\file_name.abf</File>
  <AllowOverwrite>true</AllowOverwrite>
</Backup>

Insert your own values into the <DatabaseID >, <File>,<AllowOverwrite> fields and run the command. Once you have it working, you can schedule it in a SQL Agent Job just like any other backup job that you may have out there. In the job step, select a type of “SQL Server Analysis Services Command”

A more in-depth reference be found here: http://msdn.microsoft.com/en-us/library/cc917611.aspx#XSLTsection124121120120

WHAT SQL TABLES AND INDEXES ARE TAKING UP SPACE IN MEMORY?

Ever have a SQL Server run low on memory? Unless you’re buying “big iron” for every server or never consolidate SQL instances, chances are you have. Ever wonder “What’s taking up all the memory?”, Probably. Luckily, with DMV’s introduced in SQL 2005, we can get a snapshot of the tables and indexes that are occupying memory inside the SQL Buffer Pool.

As we all know, indexes are a blessing for reading data from SQL, but not much thought is given to overhead associated with indexes. Not only is there more work to be done when an insert, update or delete on the base table (or clustered index), the newly created index will also start residing in memory as it is used in queries. Also, an index can become less useful over time as the columns in the index become less unique; resulting in index scans rather than index seeks. While this may cause query performance to suffer; in the context in memory, this causes more 8KB index pages to be read into memory, consequently bloating your Buffer Pool.

To view the tables and indexes that are taking up residence in you SQL Server memory, the following query can be ran from the master database. The query makes use of the sp_msforeachdb stored procedure to get the names of the objects in memory from each database . Below are some notes on the result set returned by the query:

·         This query will only return tables and indexes in memory from User databases. Master, Tempdb, MSDB, and Model are omitted.

·         Indexes with an ID of 1 are the clustered index for the table. Indexes with an ID of 0 are tables that do not have a clustered index and are referred to as Heaps. All others are non-clustered indexes.

·         Not only will you be able to see the size of each object in memory by megabytes, I’ve also included some usage statistics on those indexes. These statistics will give you an idea on how your index is being used and how effective it is.

·         Documentation on the system tables and DMV’s used in the query can be found below

o   sys.dm_db_index_usage_stats

o   sys.indexes

o   sys.partitions

o   sys.dm_os_buffer_descriptors

o   sys.allocation_units

·         This query does not return information on other objects in memory such as the ones that live in the Procedure Cache or those governed by the Lock Manager.

Query Below:

IF OBJECT_ID('tempdb..#Objects') IS NOT NULL
  DROP TABLE #Objects

 CREATE TABLE #Objects

    (
      ObjectName SYSNAME ,
      ObjectID INT ,
      IndexName SYSNAME ,
      IndexID INT ,
      BufferSize INT ,
      BufferCount INT ,
      DBName SYSNAME
    )
 GO

 EXEC sp_msforeachdb 'use ?;

 IF DB_ID(''?'') > 4
 insert into #Objects(ObjectName,
                      ObjectID,
                      IndexName,
                      IndexID,
                      BufferSize,
                      BufferCount,
                       DBName)
 SELECT OBJECT_NAME(P.Object_ID),
        P.Object_ID, 
        Coalesce(I.Name,''**Heap**''),   
        P.Index_ID,
        COUNT(*)/128, 
        COUNT(*),
        db_name()    
   FROM sys.allocation_units AS AU INNER JOIN sys.dm_os_buffer_descriptors AS OSB    
    ON AU.allocation_unit_id = OSB.allocation_unit_id    
                                     INNER JOIN sys.partitions AS P    
    ON AU.container_id = P.hobt_id 
                                    INNER JOIN sys.indexes AS I
    ON P.index_id = I.index_id
   AND P.object_id = I.object_id     
 WHERE OSB.database_id = DB_ID()    
 GROUP BY P.object_id, P.index_id,Coalesce(I.name,''**Heap**'')'
 GO

SELECT  #Objects.DBName ,

        #Objects.ObjectName AS TableName ,
        #Objects.IndexID,       
        #Objects.IndexName ,       
        #Objects.BufferSize ,
        #Objects.BufferCount ,
        IUS.user_seeks ,
        IUS.user_scans ,
        IUS.user_lookups ,
        IUS.user_updates ,
        IUS.last_user_seek ,
        IUS.last_user_scan ,
        IUS.last_user_lookup ,
        IUS.last_user_update ,
        IUS.system_seeks ,
        IUS.system_scans ,
        IUS.system_lookups ,
        IUS.system_updates ,
        IUS.last_system_seek ,
        IUS.last_system_lookup ,
        IUS.last_system_update ,
        IUS.last_system_scan
FROM    #Objects INNER JOIN sys.dm_db_index_usage_stats IUS
     ON #Objects.DBName = DB_NAME(IUS.database_id)
    AND #Objects.objectid = IUS.OBJECT_ID
    AND #Objects.indexid = IUS.index_id
ORDER BY 5 DESC

GO