Results 1 to 2 of 2

Thread: MSSQL T-SQL Data Warehouse Performance

  1. #1
    Join Date
    Dec 2005
    Posts
    1

    MSSQL T-SQL Data Warehouse Performance

    Hi Guys,

    Long time reading, new member of databasejournal. Hoping that some of you guru's can help me. I am currently in the final phases of testing for a MS SQL data warehouse that I have developed and need some pointers for increasing T-SQL insert performance. The warehouse is currently running on a 16 processor Server (don't know exact specs; its sitting in the data center in another country) and the disks are on a storage utility. The largest fact table currently has 52 Million records.

    We have setup the server with a disk array and have the tempdb, T-Logs and Data files split over the array according to common practice.

    So I have a few questions that I'm hoping some people can answer.

    1. Is there anyway to get an insert statement to operate as fast as a DTS Transformation task? Essentially the main difference I can discern between these two methods is that one is logged and the other isn't. When you sp_who2 during the DTS insert command is 'BULK INSERT'. Is there anyway through T-SQL to get it do do a BULK/Non Logged insert from table to table? I've stumbled across Bulk Update in sql server books but can't find a specific table lock for it.

    2. After inserting the data into a staging table the process then sets the key fields in the staging table by looking up the ID by matching the value in the staging with a value in the lookup table. Would it be beneficial to add an index to the value fields in the staging table? AND (keeping in mind this is a one off operation) is this benefit offset by the time spent creating and applying the index?

    3. Is the index benefit relationship of the inverted U shaped variety rather than a direct relationship? e.g 10M rows=decrease perf;20M= increase perf; 30M = decreased performance? And if this is so, as I suspect, is there anyway of calculating this?

    4. One of the problems I seem to be encountering is running out of log space (despite having 500gig at my disposal). This is of course due to me testing various different indexes as per above. Is there anyway to calculate a transactions T-Log cost when a; Applying the index & b; When updating a value that sits in the index?

    5. Is XLOCK the best performance table hint to use for INSERTS & UPDATES as concurrency is not an issue?

    6. Would wrapping these simple one step sql statements up into BEGIN TRANS <statement> COMMIT TRANS increase or decrease performance? (I think the latter but want to check)

    Any help would be greatly appreciated.

    Thanks
    Sean

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    1. No. BULK INSERT only works with text file input. Bulk update lock only works for BULK INSERT or bcp. You can put database in Bulk_Logged recovery model and try using SELECT INTO if possible. It is also considered a bulk operation.

    2. Is the value highly selective?. If yes, then index will help.

    3. I did not quite get the question. Index works well if it is highly selective meaning less than 10% duplicate values. Otherwise SQL Server optimizer may ignore it. Other side of this coin is, during insert indexes are expensive to maintain. In a data load scenario, you should create index after you load all data.

    4. Use bulk logged or simple recovery model and keep transactions smaller. Do not issue large insert/update/delete, break them down into multiple statements affecting subset of data.

    5. If only one process is loading the table, using TABLOCK helps as SQL server does not have to worry about maintaining too much lock info.

    6. By default any DML auto commits in SQL server so wrapping individual statements in BEGIN/END Tran does not help. It will take more time for the parser to parse these statements for nothing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •