SQL Server Performance

Database Restore Performance Increase

Discussion in 'Performance Tuning for DBAs' started by Steven Trout, Jun 27, 2011.

  1. Steven Trout New Member

    I have a 160GB database on SQL 2000 (SP4). In an attempt to speed up a stored procedure I restored the database on another server so that I could test with it. The same procedure that ran 45 minutes on the LIVE database took seconds on the TEST database!. I couldnt believe it. The test server has the same # CPUS and same memory (virutal), same disk system (SAN), same SQL version / service pack.

    The query does a simple DELETE statement on a large number of tables in the database. The DELETE statement does not join other tables. It only deletes where style_id (INT) = X for each table.

    On the LIVE server I already reindex the tables on a regular basis but I did it again just prior to running the query. I tried updating statistics on the tables with FULLSCAN. The delete statements are run in a stored procedure so I used sp_recompile on the stored procedure and even reboot the server to make sure. I thought perhaps ESX host the LIVE server ran on was performing better than the TEST even though they are identical hosts so I swapped the host the virtual machines were running on with no improvement.

    Confident the issue must be an OS fragementation issue I used contig.exe and even tried DisKeeper with no real improvement. The database has been around for about years and obviously grown over time.

    So here is the question...Could the TEST server be performing that much better just because I restored the database from a backup? Do I need to run something like DBCC SHRINKDATABASE WITH NOTRUNCATE on the LIVE server to replicate?
  2. satya Moderator

    Welcome to the forums.
    You should take the current working processes, number of user connections and load on TEST server and its quite common that when you restore a database for the first time when SP is called it will compile a plan which treats to be good.

    In your case to compare similar execution timings on PRODUCTION you migth look at the estimated execution plan for that SP and see if there is any better option you can try to avoid any table/index scans.

    Do not attempt to SHRINK the database when you have performed the database maintenance activites.
  3. Steven Trout New Member

    Thank you for your response. During off hours the execution times are very similar so I dont believe that is the problem. The DELETE statement is deleting 'WHERE style_id = X' and style_id is part of the primary key. The resulting rows to be deleted are maybe 1000-5000 out of several million so it SHOULD NOT reach the tipping point to cause an INDEX SCAN, but that is what I am seeing. That is probably the part I should have focused on. The TEST server is doing an index seek and the LIVE is doing an index scan. I cannot figure out why the same query is acting differently between the servers even after reindexing and sp_recompile. My thought with the SHRINK was to run without truncating so it will rearange the contents of the database. Honestly I kind of stumbled across the NOTRUNCATE option since I normally dont shrink a database:

    "The NOTRUNCATE option, with or without specifying target_percent, performs the actual data movement operations of DBCC SHRINKDATABASE including the movement of allocated pages from the end of a file to unallocated pages in the front of the file. However, the free space at the end of the file is not returned to the operating system and the physical size of the file does not change."
  4. satya Moderator

    The SHRINK operation will always have a negative effect of arranging the pages and irrespective of NOTRUNCATE option that you use. In order to settle the performance issues you must look at the query execution plan, parallel processes that are running during the same time in addition to TEMPDB configuration on live server.

    SHRINK operation do get you the performance, it will only reduce the size of data or log file.
    Index scan means its a table scan in such cases you can look at the query hints, as I see you are using SQL 2000 it is hard to take help of SQL Server in proposing missing indexes which si a new feature in SQL SErver 2008 verison.
  5. Steven Trout New Member

    Then what is the point of "the movement of allocated pages from the end of a file to unallocated pages in the front of the file"? I was hoping it would help reduce some internal fragmentation in the same way restoring a database would write the database and tables on the disk from beginning to end.

    The TEST and LIVE server use the exact same database so the indexes are all the same. I have tried changing the MDOP settings with no improvement.
  6. satya Moderator

    Is your database hosted with multiple files and filegroups?
    Lets say if your database is designed to have PRIMARY filegroup for system data storage, one for data and one for indexes then you should be able to address that fragmentation in the files. You are right stating the SHRINK operation will reorder the pages but it will have negative effect on REINDEX as it is wipe out that information. Unless it is must you should not execute SHRINK of database data file, I would say ok for transaction log file though.
  7. Steven Trout New Member

    Ok. Like I said it was a kind of a stretch, but I couldnt think of anything else to try... So I'm back to square one... I cant figure out why its doing an index scan for a simple statement like 'delete from table where style_id = 1' when style_id is part of a primary key, I have reindexed, run sp_recompile and the resulting rows are WAY less than the tipping point for what should cause an index scan. What other factors am I missing that determine scan vs seek? The only three I know of are if the index is fragmented the execution plan is old or the resulting rows are over a certain % of the entire table.
  8. Steven Trout New Member

    The delete statements are part of a TRANSACTION if that makes any difference. Again, I run the same query on TEST and LIVE. Its another stretch but I'm out of ideas. Here is the script I'm running and one of the stores procedures it calls. The others are pretty much the same for differnt tables.

    EXEC hk_style_delete_hist_$sp @style_id
    EXEC hk_style_delete_hist_oh_$sp @style_id
    EXEC hk_style_delete_hist_cmp_$sp @style_id
    EXEC hk_style_delete_oo_all_$sp @style_id
    EXEC hk_style_delete_hist_flsh_$sp @style_id
    DELETE from housekeeping_queue where housekeeping_queue_id = @queue_id

    create proc dbo.hk_style_delete_hist_$sp @entity_id DECIMAL(15,0)
    Version:1.00 Date:2001/07/04
    Udani Perera
    Delete statements for hist_oh tables (style, style_clr, sku)
    delete from hist_style_loc_wk where style_id = @entity_id
    delete from hist_style_loc_pd where style_id = @entity_id
    delete from hist_style_loc_yr where style_id = @entity_id
    delete from hist_style_loc_li where style_id = @entity_id
    delete from hist_style_chn_wk where style_id = @entity_id
    delete from hist_style_chn_pd where style_id = @entity_id
    delete from hist_style_chn_yr where style_id = @entity_id
    delete from hist_style_chn_li where style_id = @entity_id
    delete from hist_styleclr_loc_wk where style_id = @entity_id
    delete from hist_styleclr_loc_pd where style_id = @entity_id
    delete from hist_styleclr_loc_yr where style_id = @entity_id
    delete from hist_styleclr_loc_li where style_id = @entity_id
    delete from hist_styleclr_chn_wk where style_id = @entity_id
    delete from hist_styleclr_chn_pd where style_id = @entity_id
    delete from hist_styleclr_chn_yr where style_id = @entity_id
    delete from hist_styleclr_chn_li where style_id = @entity_id
    delete from hist_sku_loc_wk where style_id = @entity_id
    delete from hist_sku_loc_pd where style_id = @entity_id
    delete from hist_sku_loc_yr where style_id = @entity_id
    delete from hist_sku_loc_li where style_id = @entity_id
    delete from hist_sku_chn_wk where style_id = @entity_id
    delete from hist_sku_chn_pd where style_id = @entity_id
    delete from hist_sku_chn_yr where style_id = @entity_id
    delete from hist_sku_chn_li where style_id = @entity_id

  9. Steven Trout New Member

    Is there a way to date the execution plan? Maybe its not being updated like I think it is... It occurred to me because I also noticed that even with the statistics set to auto-update they don't appear to update either unless I schedule it or manually do it.
  10. Steven Trout New Member

    Another very interesting development. The style_id is actually a decimal (12,0) not an integer. The stored procedures were being called with a different scale. For example 641.000000 instead of 641. I can understand why that might cause a problem. It has to cast the decimal, right? I can see on the LIVE server it runs an index scan when I use 641.000000 and an index seek with 641. The interesting part is on the TEST server it does an index seek for both. Why would that be?

Share This Page