Few SQL questions

cnicola · 2007-07-11

Few things I am curious about:

1. Always rowlock: I see people say if you turn that on it will use A LOT of memory. What is A LOT? I have 16GB on the server with 14GB allocated to the server (Navision 4.00SP3 with a DB of 10-20Gb for now but will surely go soon to 100GB + ).

2. Sift levels. Let's say I have 2 keys (simplified example):
- Customer No., Customer PO No.
- Customer No., Posting Date

both with SIFT on Amount. We set maintain SIFT levels to both levels: Customer No. and Customer No., Customer PO No. / Posting Date.
That means in theory I would have the Customer No. sift level twice (I know it is 2 different tables in SQL). If I disabled the Customer No. level on second key when I use that second key in code and do a calcsums on Amount with Customer No. filter only would Navision use the SIFT level from first key or just do an actual table sum? (Hope that made some sense)

3. I heard that putting the SIFT tables on it own disk array helps. Is that true?

stryk · 2007-07-11

Hi!

"My two Cents":

1) "Always Rowlock" adds the Query Hint ROWLOCK to the statement, which means that row-locking is enforced, lock escalation is prevented.
But usually you want to have lock escalation, e.g. as it is faster to maintain a single range lock instead of thousands of row locks. Of course, the higher the locking level, the higher is the risk of getting conflicts with other processes.
One big misunderstanding is, that without "Always Rowlock" the SQL Server does not perform row-locking. This is non-sense, SQL Server always starts locking on the lowest level possible (usually the row-level), and then could escalate.
I recommend to disable this setting. If you then encounter blocking problems, these usually could be solved in a different way.
Have in mind, that each lock has to be administered in the master db. "Always Rowlock" could generate quite a lot of "traffic" for the master db; and it's getting worse, if the master db e.g. is stored on slow drive C:\ or something ...

2) Check this out: http://www.mibuso.com/forum/viewtopic.php?t=18933

3) Sure it helps. Storing SIFT tables separately means to have a dedicated filgroup for them. Hence, SQL Server could process this file with another Thread, speeding up processing (if enough CPU power is available). If further this file is stored on a dedicated dphysical disk, you benefit from the dedicated I/O as well.
This is an advice from the "NAV Hardware Sizing Guide". Unfortunately it does not say how to implement this :evil:
Well, it's tricky business, first you have to rename all original SIFT tables, then re-create them - original name - in the new filegroup. Then copy the data from the old SIFT tables into the new ones (Same procedure when partitioning tables). Sounds simple, but requires some smart TSQL scripting ... or has anybody an easier way for this task? Maybe some utility? Hmm, maybe I'll try to generate one :idea:

Best regards,
Jörg

stryk · 2007-07-11

stryk wrote:

Well, it's tricky business, first you have to rename all original SIFT tables, then re-create them - original name - in the new filegroup. Then copy the data from the old SIFT tables into the new ones (Same procedure when partitioning tables). Sounds simple, but requires some smart TSQL scripting ... or has anybody an easier way for this task? Maybe some utility? Hmm, maybe I'll try to generate one :idea:

Well, I was just fiddling with this issue. Seems to be not that tricky, looks like the MOVE TO clause does the trick. Have to run some tests, but I guess that I could provide a TSQL script for "SIFT Movement" soon ...

cnicola · 2007-07-11

Hi Stryk,

1) I know that SQL starts with row lock. It is just that I would love to have it always rowlocking. I have a rather large Sales Line table and about half of the company changing it 20K lines at a time ... Soo among other things was wondering if that would have helped.
In a way rowlocking is really not important if that hint does not carry over to the SIFT tables since that effectively pretty much makes it table lock.
2) I read the thread but still don't feel answers my question.
3) Keep me posted if you get a script. I thought you would be able to do it from the Management Studio.

stryk · 2007-07-12

cnicola wrote:

2) I read the thread but still don't feel answers my question.

Hmm, should have. You have two SIFT Indexes:
- Customer No., Customer PO No.
- Customer No., Posting Date
If you follow the principle to only enable the pre-last bucket - the first level in aggregation - all higher levels e.g. "Customer No." could be calculated from them. In this case it does not matter which SIFT table C/SIDE uses.

cnicola wrote:

3) Keep me posted if you get a script. I thought you would be able to do it from the Management Studio.

Script is coming soon. Of course you could do everything with Mgmt.Studio, but that would be quite annoying to fiddle with dozens of SIFT tables manually ...

colin_leversuch-roberts · 2007-07-12

in general terms you should allow sql server to decide which locking to make .. trying to force rowlocks may give performance issues.
By default sql server will row lock, it will choose page locks if it is better. The cost relates to the number of locks required, each requires the same amount of memory, so if the table ( or index ) is narrow and you want to lock 20k rows - 20k locks - if there are 10 rows per page then it will only require 2k page locks. At certain points sql server may escalate to an extent lock, 8 pages, generally if you select locks for greater than 50% of the table then it will excalate to a table lock, or before if resource is low.
If your row size is over 4k then a page lock will equal a row lock, you can also juggle rows to pages through fillfactor and padding. Note that using high fill factors may well force lock escalation by requiring a greater number of locks for a range update. The whole process of locks vs resource is quite complex and trying to outhink ( or tell sql server you know better on how to execute commands ) may well lead to big performance problems.

stryk · 2007-07-14

OK, here it comes, a TSQL script to install the stored procedure "ssi_movesift" to move the SIFT tables incl. Indexes to a dedicated filegroup.

Version: 0.90 - BETA - SQL Server 2005
No guarantee, no warranty, no support - use at own risk, blame someone else.

Installation:

Copy the following Code as new query to Management Studio and execute it within context of your NAV database:

/*********************************************************/
/***              STRYK System Improvement             ***/
/***    Performance Optimization & Troubleshooting     ***/
/***  (c) 2007, STRYK System Improvement, Jörg Stryk   ***/
/***                   www.stryk.info                  ***/
/*********************************************************/

if exists (select * from dbo.sysobjects 
where id = object_id(N'[dbo].[ssi_movesift]') and objectproperty(id, N'IsProcedure') = 1)
drop procedure [dbo].[ssi_movesift]
go

create procedure dbo.ssi_movesift
  @filegroup varchar(128) = '',    -- Hides info-messages
  @script tinyint = 1              -- Create just TSQL script 
with encryption
as

print '---------------------------------------------------------'
print '---              STRYK System Improvement             ---'
print '---    Performance Optimization & Troubleshooting     ---'
print '---  (c) 2007, STRYK System Improvement, Jörg Stryk   ---'
print '---                   www.stryk.info                  ---'
print '---------------------------------------------------------'
print '---           Version 0.90, Date: 14.07.2007          ---'
print ''

if not exists(select * from sys.filegroups where [name] = @filegroup) begin
  raiserror ('ERROR: Invalid Parameter @filegroup: %i', 15, 1, @filegroup)
  return
end
if (@script not in (0,1)) begin
  raiserror ('ERROR: Invalid Parameter @script: %i', 15, 1, @script)
  return
end

declare @sift_tab_id int, 
        @fld_name varchar(128),
        @nci_id int,
        @nci_name varchar(128), 
        @statement varchar(8000)

set nocount on
set statistics io off
set transaction isolation level serializable

if @script = 1 begin
  print '/* Begin >> */'
  print 'USE [' + db_name() + ']'
  print 'GO'
  print 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'
end else
  print 'Moving SIFT tables to filegroup "' + @filegroup + '" ...'

-- temporary table for sorting by table-name
create table #sift_tab ([tab_id] int, [tab_name] varchar(250))
insert into #sift_tab select [object_id], [name] from sys.objects where 
  (objectproperty([object_id], N'IsUserTable') = 1) and ([name] like ('%$' + '[0-9]' + '%')) and right([name], 1) in ('0','1','2','3','4','5','6','7','8','9')

-- cursor SIFT tables
declare sift_tab_cur cursor fast_forward for select [tab_id] from #sift_tab order by [tab_name]
open sift_tab_cur
fetch next from sift_tab_cur into @sift_tab_id
while @@fetch_status = 0 begin
  -- check for valid SIFT record
  if (not exists(select [name] from sys.columns where ([object_id] = @sift_tab_id) and ([name] like 's' + '[0-9]' + '%')))
    or
     (not exists(select [name] from sys.columns where ([object_id] = @sift_tab_id) and ([name] like 'f' + '[0-9]' + '%')))
    or
     (exists(select [name] from sys.columns where ([object_id] = @sift_tab_id) and ([name] like 's' + '[0-9]' + '%') and (type_name([user_type_id]) <> 'decimal')))
    or
     (exists(select [name] from sys.columns where ([object_id] = @sift_tab_id) and ([name] = 'timestamp')))
    goto End_Step

  -- Create statement
  set @statement =
    'BEGIN TRANSACTION
ALTER TABLE dbo.[' + object_name(@sift_tab_id) + '] DROP CONSTRAINT [' + object_name(@sift_tab_id) + '_idx] WITH (MOVE TO [' + @filegroup + '])'

  set @statement = @statement + 
'
ALTER TABLE dbo.[' + object_name(@sift_tab_id) + '] WITH NOCHECK ADD CONSTRAINT [' + object_name(@sift_tab_id) + '_idx] PRIMARY KEY CLUSTERED ([bucket],'

  -- Constraint Field cursor
  declare fld_cur cursor fast_forward for select [name] from sys.columns where [object_id] = @sift_tab_id and [name] like 'f%'
  open fld_cur
  fetch next from fld_cur into @fld_name
  while @@fetch_status = 0 begin
    set @statement = @statement + '[' + @fld_name + '],'
    fetch next from fld_cur into @fld_name
  end
  close fld_cur
  deallocate fld_cur

  set @statement = left(@statement, len(@statement) - 1)
  set @statement = @statement + ') ON [' + @filegroup + ']'

  -- NCI cursor
  if exists(select * from sys.indexes where ([object_id] = @sift_tab_id) and (isnull(indexproperty([object_id],[name], 'IsClustered'),0) = 0) and (isnull(indexproperty([object_id],[name], 'IsAutoStatistics'),0) = 0) and (isnull(indexproperty([object_id],[name], 'IsStatistics'),0) = 0)) begin
    declare nci_cur cursor fast_forward for select [index_id],[name] from sys.indexes where ([object_id] = @sift_tab_id) and (isnull(indexproperty([object_id],[name], 'IsClustered'),0) = 0) and (isnull(indexproperty([object_id],[name], 'IsAutoStatistics'),0) = 0) and (isnull(indexproperty([object_id],[name], 'IsStatistics'),0) = 0)
    open nci_cur
    fetch next from nci_cur into @nci_id, @nci_name
    while @@fetch_status = 0 begin
      set @statement = @statement +
'
CREATE INDEX [' + @nci_name + '] ON dbo.[' + object_name(@sift_tab_id) + '] (' 

      declare fld_cur cursor fast_forward for 
        select SC.[name] from sys.index_columns IC 
        join sys.columns SC on SC.[object_id] = IC.[object_id] and SC.[column_id] = IC.[column_id] 
        where (IC.[object_id] = @sift_tab_id) and (IC.[index_id] = @nci_id) and (IC.[is_included_column] = 0)
        order by IC.[key_ordinal]
      open fld_cur
      fetch next from fld_cur into @fld_name
      while @@fetch_status = 0 begin
        set @statement = @statement + '[' + @fld_name + '],'
        fetch next from fld_cur into @fld_name
      end
      close fld_cur
      deallocate fld_cur

      set @statement = left(@statement, len(@statement) - 1)
      set @statement = @statement + ')'

      if exists(select * from sys.index_columns where ([object_id] = @sift_tab_id) and ([index_id] = @nci_id) and ([is_included_column] = 1)) begin
        set @statement = @statement + ' INCLUDE('

        declare fld_cur cursor fast_forward for 
          select SC.[name] from sys.index_columns IC 
          join sys.columns SC on SC.[object_id] = IC.[object_id] and SC.[column_id] = IC.[column_id] 
          where (IC.[object_id] = @sift_tab_id) and (IC.[index_id] = @nci_id) and (IC.[is_included_column] = 1)
          order by IC.[key_ordinal]
        open fld_cur
        fetch next from fld_cur into @fld_name
        while @@fetch_status = 0 begin
          set @statement = @statement + '[' + @fld_name + '],'
          fetch next from fld_cur into @fld_name
        end
        close fld_cur
        deallocate fld_cur

        set @statement = left(@statement, len(@statement) - 1)
        set @statement = @statement + ')'
      end

      set @statement = @statement + ' WITH (DROP_EXISTING = ON) ON [' + @filegroup + ']'

      fetch next from nci_cur into @nci_id, @nci_name
    end
    close nci_cur
    deallocate nci_cur
  end

  set @statement = @statement +
'
COMMIT TRANSACTION
GO'

  if @script = 1
    print @statement
  else begin
    begin transaction
    exec(@statement)
    if @@trancount > 0 commit transaction
  end  
  End_Step:
  fetch next from sift_tab_cur into @sift_tab_id
end
close sift_tab_cur
deallocate sift_tab_cur
drop table #sift_tab
if @@trancount > 0 commit transaction
if @script = 1
  print '/* << End */'
else
  print 'Finished.'

Syntax:

exec ssi_movesift
   [@filegroup =] {'FilegroupName'}
   [,] [@script = {0|1 (default)}

@filegroup specifies the filgroup to which to move the SIFT tables; thus it must exists (to be created manually ahead)
script specifies whether to generate a TSQL script for movement; 0 = NO = instant execution; 1 = YES = TSQL (default). It's recommended to use the scripting option to check the output.

Example:

exec ssi_movesift 'SIFTGroup'

This will generate a TSQL script executing these statements:

BEGIN TRANSACTION
ALTER TABLE dbo.[CRONUS International Ltd_$17$0] DROP CONSTRAINT [CRONUS International Ltd_$17$0_idx] WITH (MOVE TO [SIFTGroup])
ALTER TABLE dbo.[CRONUS International Ltd_$17$0] WITH NOCHECK ADD CONSTRAINT [CRONUS International Ltd_$17$0_idx] PRIMARY KEY CLUSTERED ([bucket],[f3],[f4]) ON [SIFTGroup]
CREATE INDEX [CRONUS International Ltd_$17$0_hlp_idx] ON dbo.[CRONUS International Ltd_$17$0] ([f4]) WITH (DROP_EXISTING = ON) ON [SIFTGroup]
CREATE INDEX [ssi_CovIdx] ON dbo.[CRONUS International Ltd_$17$0] ([bucket],[f3],[f4],[s17],[s53],[s54],[s68],[s69],[s70]) WITH (DROP_EXISTING = ON) ON [SIFTGroup]
COMMIT TRANSACTION
GO

So, what is does is this:
1. Drop the CLustered Index/PK constraint and move the leaf nodes - the table - to the new filegroup
2. Re-Create the PK/CI constraint in new filegroup
3. Re-Create all Non-Clustered indexes in new filegroup (includes standard NCI and cusomized ones, e.g. if the SIFT table was pre-tuned; INCLUDES supported)

Again, this is considered a BETA version as it is just briefly tested :bug:
I would appreciate to get your comments, test-results and enhancements.
Once it has been approved I'll put it to the download section.

Have fun!

Howdy, Stranger!

Quick Links

Categories

Few SQL questions

Comments