Site Search:
Sign in | Join | Help

SQL Server (T-SQL)

Comments and notes on SQL Server 2000, 2005, and T-SQL

October 2007 - Posts

  • SQL Server optimization

    I've written several articles on SQL optimization, the most complete is here.

    http://4penny.net/SQL-Server-Troublshooting.aspx

     but I've recently come across some other useful tips that I'm going to post here until I have the time to organize them into an article.

    If you are experiencing speed issues with SQL Server:

    Check physical disk fragmentation (Windows system not SQL)

    Check what SQL maintenance routines are being run automatically and how frequently they are run. I would recommend the following:

    1. Reorganize Data and Index pages, changing free space percentage to 25% (Weekly)

    2. Check Database Integrity, do this prior to backup schedule.

    3. Update Statistics used by query optimiser (Daily/Nightly depending on maintenance window available). There is also a setting to see how much of the data is sampled during this procedure, you need to set this to the maximum you can I would suggest at least a 50% sample. The higher the sample percentage, the longer the maintenance job will run, so this again depends on the amount time you have available in a maintenance window.

    4. Ensure Auto update statistics is set against the databases.

    5. If it is a dedicated SQL server, enable the Boost SQL server priority on windows at the server level. Note: On some machines, this can have a detrimental effect as it can sometimes boost the priority that high that system processes do not get much processor time. Basically this setting is one of those that try in isolation so the effects are distinct. If enabling this is detrimental disable the option. The same applies if this option is already enabled, try disabling it. I know this sounds odd, but I have seen it help some systems and hurt others.

    6. Revaluate hardware infrastructure, if the SQL server is stressing, maybe time to put in more memory and if required install Win Server 2003 enterprise and SQL Enterprise to take advantage of memory above the 3-4 GB limit. Or if you already have enterprise versions of these servers, with additional memory, then ensure the options to use memory in excess of 3-4GB are enabled. I believe there are a couple of settings, some in SQL and some in the operating system.

    Posted Oct 15 2007, 03:56 PM by Steve Gray
    [Edit Tags]

  • Simple Code Example for a Cursor and an ID Column Insert

    I frequently get asked how to code an insert that has an ID column that increments. There are a couple of workarounds for this lamentable lapse, but here's how to do it with a cursor. 

    DECLARE @itemnmbr VARCHAR(31)
DECLARE @intCounter INT


SET @intCounter = 1


DECLARE curName CURSOR KEYSET FOR 
 select TOP 5 itemnmbr
  from iv00101


OPEN curName


FETCH NEXT FROM curName INTO @itemnmbr 
WHILE (@@fetch_status = 0)
BEGIN
 INSERT INTO myTable (idColumn, dataColumn) VALUES (@intCounter,@itemnmbr)
 SET @intCounter = @intCounter + 1


 FETCH NEXT FROM curName INTO @itemnmbr 
END


CLOSE curName
DEALLOCATE curName
GO

    Posted Oct 02 2007, 03:58 PM by Steve Gray
    [Edit Tags]

  • Code to reset the identity seed value in SQL Server

    [/code language="sql"]

    --drop and create a table
    drop table t6
    CREATE TABLE t6(id int IDENTITY);
    INSERT t6 DEFAULT VALUES;
    INSERT t6 DEFAULT VALUES;
    INSERT t6 DEFAULT VALUES;
    INSERT t6 DEFAULT VALUES;
    INSERT t6 DEFAULT VALUES;
    INSERT t6 DEFAULT VALUES;

    --look at the result of our handiwork
    select * from t6
    select ident_current( 't6')

    --delete two lines, and look again
    delete t6 where id = 6
    delete t6 where id = 5
    select * from t6

    --reset the seed, and look again
    declare @intID int
    select @intID = max(id)  from t6
    DBCC CHECKIDENT('t6', RESEED, @intID)
    INSERT t6 DEFAULT VALUES;
    select * from t6

    [/code]

    Posted Oct 01 2007, 12:26 PM by Steve Gray
    [Edit Tags]