Monthly Archives: September 2008

A few interesting things to note about this graphic.

1. The query is an INSERT into myVIEW
2. It is using tempdb to process the INSERT. I think it is because I had to add a SELECT * FROM inserted trigger to even get INSERTs to work. In addition I had to add a WHERE clause matching the partioned tables CHECK constraint and explicitly insert to the appropriate table.
3. I thought tempdb needed to have the data files all the same size to be utilized. So here we can see the files which are the same size are used, the bigger one is not.

ps. This is a test. The files will be the same size after the server is restarted.

Example:

EXEC [Northwind]..sp_columns_rowset N'Products', N'dbo', NULL 

Try this procedure as well.

CREATE  PROCEDURE dbo.rp_ScriptTableColumnsList (
 @vcDatabaseName varchar(128),
 @vcTable varchar(128),
 @bFormatted bit,
 @bQuotedNames bit,
 @bColumnListOnly bit,
 @vcOUTSQL varchar(8000) OUTPUT
)
AS
/*
** Procedure
 dbo.rp_ScriptTableColumnsList  


** Description
 Useful utility to script out columns in varying forms for use in calling procedures.
 Can run for any table in any database by creating a dynamic script, and existing script
  in database parameter in use. 

** Parameters 

** Requirements 

** Usage 

DECLARE @RC int
DECLARE @vcDatabaseName varchar(128)
DECLARE @vcTable varchar(128)
DECLARE @bFormatted bit
DECLARE @bQuotedNames bit
DECLARE @bColumnListOnly bit
DECLARE @vcOUTSQL varchar(8000)
SELECT 
 @vcDatabaseName = 'pubs', 
 @vcTable = 'authors', 
 @bFormatted = 1, 
 @bQuotedNames = 1, 
 @bColumnListOnly = 1
EXEC @RC = [master_admin].[dbo].[rp_ScriptTableColumnsList] 
 @vcDatabaseName, @vcTable, @bFormatted, @bQuotedNames, @bColumnListOnly, @vcOUTSQL OUTPUT 
PRINT @vcOUTSQL 

** Changes
  2008-04-21 John Perry Created 

*/
set nocount on 

DECLARE  
 @vcSQL varchar(8000),
 @vcSQLSource varchar(8000),
 @vcFormatting varchar(2),
 @vcSELECT varchar(10) 

IF @bFormatted = 1
 SET @vcFormatting = char(10)+ char(9)
ELSE
 SET @vcFormatting = ''
 
IF @bColumnListOnly = 1
 SET @vcSELECT = CASE
  WHEN @bFormatted = 0 THEN '' ELSE CHAR(9) END
ELSE
 SET @vcSELECT = 'SELECT ' + @vcFormatting 

CREATE TABLE #tmpColumns (
 [name] [sysname] NOT NULL ,
 [id] [int] NOT NULL ,
 [length] [smallint] NOT NULL ,
 [colid] [smallint] NOT NULL ,
 [colorder] [smallint] NOT NULL 
)  

SET @vcSQLSource = 'USE ' + @vcDatabaseName  + '
 INSERT INTO #tmpColumns
 SELECT c.name, c.id, c.length, c.colid, c.colorder
 FROM  dbo.syscolumns c
 JOIN dbo.sysobjects o
 ON o.id = c.id
  WHERE o.name = ''' + @vcTable + '''
 ORDER BY
  colorder' 

-- Add the columns to the local temp table for the database in USE (within query).
EXEC (@vcSQLSource) 

--
-- Create initial list of columns and query from results in temp table.
--
SELECT @vcSQL =
 COALESCE(@vcSQL + ', ' + @vcFormatting , @vcSELECT ) + 
 CASE 
 WHEN @bQuotedNames = 1 THEN
  QUOTENAME(name)
 ELSE
  name
 END
FROM #tmpColumns 

-- Clean up.
DROP TABLE #tmpColumns 

--
-- Complete and format the SQL statement with the TABLE FROM statement, as needed.
--
IF @bColumnListOnly = 0
 SET @vcSQL = @vcSQL + 
  CASE
  WHEN @bFormatted = 0 THEN SPACE(1) ELSE CHAR(10) END
  + 'FROM ' + @vcDatabaseName + '.dbo.' + @vcTable 

IF @bFormatted = 0
 SET @vcSQL = REPLACE(@vcSQL, @vcFormatting, '') 

--
-- Final return of requested SQL string.
--
SET @vcOUTSQL = LTRIM(@vcSQL) 
GO

Run trace for one database at a time. 

The events and data columns used by the Index Tuning Wizard are 
    SQL:BatchCompleted and the
    RPC:Completed events in the TSQL category, and the
    EventClass and Text data columns.

Save as trace file or table. NOTE: ITW won't run on a read-only database.

Try running these examples.

PRINT 'Int to Hex' 
SELECT CONVERT(VARBINARY(8), 15987699) 
 
PRINT 'Hex to Int' 
SELECT CONVERT(INT, 0xF3F3F3)

A good place to start is with Brad McGehee.

sql-server-performance

Fill factor = 100 fills the pages completely.

Fill factor = 0 leaves space in the index non-leaf pages .

Source